Creating Tables | DB2 9 Fundamentals: Certification Study Guide

In Chapter 4, "Working with Databases and Database Objects," we saw that tables present data as a collection of unordered rows with a fixed number of columns; each column contains values of the same data type or one of its subtypes, each row contains a set of values for each column available, and usually the columns in a table are logically related. Like many of the other database objects available, tables can be created using a GUI tool that is accessible from the Control Center. In this case, the tool is the Create Table Wizard, and it can be activated by selecting the appropriate action from the Tables menu.

Tables can also be created using the CREATE TABLE SQL statement. However, this statement is probably the most complex SQL statement available (in fact, over 60 pages of the DB2 SQL Reference, Volume 2 manual are devoted to this statement alone). Because this statement is so complex, its syntax can be quite intimidating. Fortunately, you do not have to know all the nuances of the CREATE TABLE statement to pass the DB2 9 Fundamentals certification exam (Exam 730). Still, you do need to know the basics, and the remainder of this section is devoted to the CREATE TABLE statement and to the syntax you need to be familiar with. With that said, let's begin by taking a look at the simplest form of the CREATE TABLE SQL statement.

In its simplest form, the syntax for the CREATE TABLE SQL statement is:

 CREATE TABLE [TableName]([Element], ...) <IN [TablespaceName]> <INDEX IN [TablespaceName]> <LONG IN [TablespaceName]>

where:

TableName	Identifies the name that is to be assigned to the table to be created. (A table name must be unique within the schema the table is to be defined in.)
Element	Identifies one or more columns, UNIQUE and primary key constraints, referential constraints, CHECK constraints, and/or informational constraints to be included in the table definition. The syntax used for defining each of these elements varies according to the element being defined.
TablespaceName	Identifies the table spaces in which the table and its regular data, indexes, and/or long data/large object data are to be stored.

The basic syntax used to define a column is:

 [ColumnName][DataType] <NOT NULL> <WITH DEFAULT <[DefaultValue] | CURRENT DATE | CURRENT TIME | CURRENT TIMESTAMP | NULL>> <UniqueConstraint> <CheckConstraint> <ReferentialConstraint>

where:

ColumnName	Identifies the unique name to be assigned to the column that is to be created.
DataType	Identifies the data type (built-in or user-defined) that is to be assigned to the column to be created; the data type specified determines the kind of data values that can be stored in the column. Table 6-4 contains a list of the data type definitions that are valid.
DefaultValue	Identifies the value that is to be provided for the column in the event no value is supplied when an insert or update operation is performed against the table.
UniqueConstraint	Identifies a UNIQUE or primary key constraint that is to be associated with the column.
CheckConsraint	Identifies a CHECK constraint that is to be associated with the column.
ReferentialConstraint	Identifies a referential constraint that is to be associated with the column.

Table 6-4: Data Type Definitions That Can Be Used with the CREATE TABLE Statement
Open table as spreadsheet
Data Type	Definition(s)
Small integer	SMALLINT
Integer	INTEGER INT
Big Integer	BIGINT
Decimal	DECIMAL(Precision, Scale) DEC(Precision, Scale) NUMERIC(Precision, Scale) NUM(Precision, Scale) where Precision is any number between 1 and 31; Scale is any number between 0 and Precision
Single-precision floating-point	REAL FLOAT (Precision) where Precision is any number between 1 and 24
Double-Precision Floating-Point	DOUBLE FLOAT(Precision) where Precision is any number between 25 and 53
Fixed-Length Character String	CHARACTER(Length) <FOR BIT DATA> CHAR(Length) <FOR BIT DATA> where Length is any number between 1 and 254. See footnote for the FOR BIT DATA phrase.
Varying-Length Character String	CHARACTER VARYING(MaxLength) <FOR BIT DATA> CHAR VARYING(MaxLength) <FOR BIT DATA> VARCHAR(MaxLength) <FOR BIT DATA> where MaxLength is any number between 1 and 32,672. See footnote for the FOR BIT DATA clause.
Long Varying-Length Character String	GRAPHIC(Length) where Length is any number between 1 and 127
Varying-Length Double-Byte Character String	VARGRAPHIC(MaxLength) where MaxLength is any number between 1 and 16,336
Long Varying-Length Double-Byte Character String	LONG VARGRAPHIC
Date	DATE
Time	TIME
Timestamp	TIMESTAMP
Binary Large Object	BINARY LARGE OBJECT(Length <K \| M \| G>) BLOB(Length <K \| M \| G>) where Length is any number between 1 and 2,147,483,647; if K (for kilobyte) is specified, Length is any number between 1 and 2,097,152; if M (for megabyte) is specified, Length is any number between 1 and 2,048; if G (for gigabyte) is specified, Length is any number between 1 and 2.
Character Large Object	CHARACTER LARGE OBJECT(Length <K \| M \| G>) CHAR LARGE OBJECT(Length <K \| M \| G>) CLOB(Length <K \| M \| G>) where Length is any number between 1 and 2,147,483,647; if K (for kilobyte) is specified, Length is any number between 1 and 2,097,152; if M (for megabyte) is specified, Length is any number between 1 and 2,048; if G (for gigabyte) is specified, Length is any number between 1 and 2.
Double-Byte Character Large Object	DBCLOB(Length <K \| M \| G>) where Length is any number between 1 and 1,073,741,823; if K (for kilobyte) is specified, Length is any number between 1 and 1,048,576; if M (for megabyte) is specified, Length is any number between 1 and 1,024; if G (for gigabyte) is specified, Length is must be 1.
XML Document	XML
Label-Based Access Control (LBAC) Security Label	DB2SECURITYLABEL
Note: If the FOR BIT DATA option is used with any character string data type definition, the contents of the column to which the data type is assigned are treated as binary data. As a result, code page conversions are not performed if data is exchanged between other systems, and all comparisons made are done in binary, regardless of the collating sequence used by the database.

The syntax used to create a unique or primary key constraint as part of a column definition is:

 <CONSTRAINT [ConstraintName]> [UNIQUE | PRIMARY KEY]

where:

ConstraintName

Identifies the unique name that is to be assigned to the constraint to be created.

The syntax used to create a check constraint as part of a column definition is:

 <CONSTRAINT [ConstraintName]> CHECK ([CheckCondition]) <ENFORCED | NOT ENFORCED> <ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION>

where:

ConstraintName	Identifies the unique name that is to be assigned to the constraint to be created.
CheckCondition	Identifies a condition or test that must evaluate to TRUE before a value can be stored in the column.

And finally, the syntax used to create a referential constraint as part of a column definition is:

 <CONSTRAINT [ConstraintName]> REFERENCES [PKTableName]<([PKColumnName], ...) > <ON UPDATE [RESTRICT | NO ACTION]> <ON DELETE [CASCADE | SET NULL | RESTRICT | NO ACTION]> <ENFORCED | NOT ENFORCED> <ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION>

where:

ConstraintName	Identifies the unique name that is to be assigned to the constraint to be created.
PKTableName	Identifies the name of the parent table that is to participate in the referential constraint.
PKColumnName	Identifies the column(s) that make up the parent key of the parent table that is to participate in the referential constraint.

If the NOT ENFORCED clause is specified as part of a constraint's definition an informational constraint will be created and the constraint will not be enforced during insert and update processing. If the ENABLE QUERY OPTIMIZATION clause is specified, the DB2 optimizer will evaluate the information provided about the constraint when generating an access plan in response to a query. (When the ENABLE QUERY OPTIMIZATION is used, the constraint will be imposed when SELECT statements are issued against the table; and records stored in the table that do not conform to the constraint are not returned.)

Therefore, if you wanted to create a table that had three columns in it, two of which use an integer data type and another that uses a fixed-length character string data type, you could do so by executing a CREATE TABLE SQL statement that looks something like this:

 CREATE TABLE employees   (empid INTEGER,   name CHAR(50)   dept INTEGER)

If you wanted to create the same table such that the EMPID column had both the NOT NULL constraint and a primary key constraint associated with it, you could do so by executing a CREATE TABLE statement that looks something like this:

 CREATE TABLE employees   (empid INTEGER NOT NULL PRIMARY KEY,   name CHAR(50)   dept INTEGER)

If you wanted to create the same table such that the DEPT column participates in a referential constraint with a column named DEPTID that resides in a table named DEPARTMENT, you could do so by executing a CREATE TABLE statement that looks something like this:

 CREATE TABLE employees   (empid INTEGER,   name CHAR(50)   dept INTEGER REFERENCES department (deptid))

And finally, if you wanted to create the same table such that the EMPID column has an informational constraint associated with it, you could do so by executing a CREATE TABLE statement that looks something like this:

 CREATE TABLE employees   (empid INTEGER NOT NULL     CONSTRAINT inf_cs CHECK (empid BETWEEN 1 AND 100)     NOT ENFORCED     ENABLE QUERY OPTIMIZATION,   name CHAR(50)   dept INTEGER)

As you can see from these examples, a UNIQUE constraint, a CHECK constraint, a referential constraint, and/or an informational constraint that involves a single column can be defined as part of that particular column's definition. But what if you needed to define a constraint that encompasses multiple columns in the table? Or what if you want to separate the constraint definitions from the column definitions? You do this by defining a constraint as another element, rather than as an extension to a single column's definition. The basic syntax used to define a UNIQUE constraint as an individual element is:

 <CONSTRAINT [ConstraintName]> [UNIQUE | PRIMARY KEY] ([ColumnName], ...)

where:

ConstraintName	Identifies the unique name that is to be assigned to the constraint to be created.
ColumName	Identifies one or more columns that are to be part of the UNIQUE or primary key constraint to be created.

The syntax used to create a CHECK constraint as an individual element is the same as the syntax used to create a CHECK constraint as part of a column definition:

 <CONSTRAINT [ConstraintName]> CHECK ([CheckCondition]) <ENFORCED | NOT ENFORCED> <ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION>

where:

ConstraintName	Identifies the unique name that is to be assigned to the constraint to be created.
CheckCondition	Identifies a condition or test that must evaluate to TRUE before a value can be stored in the column.

And finally, the syntax used to create a referential constraint as an individual element is:

 <CONSTRAINT [ConstraintName]> FOREIGN KEY ([ColumnName] ,...) REFERENCES [PKTableName]<([PKColumnName] ,...) > <ON UPDATE [NO ACTION | RESTRICT]> <ON DELETE [CASCADE | SET NULL | NO ACTION | RESTRICT]> <ENFORCED | NOT ENFORCED> <ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION>

where:

ConstraintName	Identifies the unique name that is to be assigned to the constraint to be created.
ColumnName	Identifies one or more columns that are to be part of the referential constraint to be created.
PKTableName	Identifies the name of the parent table that is to participate in the referential constraint.
PKColumnName	Identifies the column(s) that make up the parent key of the parent table that is to participate in the referential constraint.

Thus, a table that was created by executing a CREATE TABLE statement that looks something like this:

 CREATE TABLE employees   (empid INTEGER NOT NULL PRIMARY KEY,   name CHAR(50)   dept INTEGER REFERENCES department(deptid))

could also be created by executing a CREATE TABLE statement that looks something like this:

 CREATE TABLE employees   (empid INTEGER NOT NULL,   name CHAR(50)   dept INTEGER,   PRIMARY KEY (empid),   FOREIGN KEY (dept) REFERENCES department(deptid))

Creating Tables with XML Columns

Earlier, we saw that the XML data type can be used to store well-formed XML documents in their native format. Although XML values are processed in an internal representation that is not a string and not directly comparable to string values, the process for defining an XML column is the same as that used to define a string column: You simply assign the XML data type to a column when you create a table definition using the CREATE TABLE statement.

For example, if you want to create a table that has two columns in it, one of which uses the integer data type and another that uses the XML data type, you can do so by executing a CREATE TABLE SQL statement that looks something like this:

 CREATE TABLE emp_resume   (empid INTEGER NOT NULL PRIMARY KEY,   resume XML)

A table can contain any number of XML columns; however, each XML column used has the following restrictions:

It cannot be part of any index except an XML index.
It cannot be included as a column of a primary key or UNIQUE constraint.
It cannot be a foreign key of a referential constraint.
It cannot have a specified default value or a WITH DEFAULT clause-if the column is nullable, the default value for the column is the null value.
It cannot be used in a table with a distribution key.
It cannot be used in range-clustered or range-partitioned tables.

In addition, XML columns can be referenced in a CHECK constraint only if the CHECK constraint contains the VALIDATED predicate. (The VALIDATED predicate checks to see whether an XML value has been validated using the XMLVALIDATE() function. The XMLVALIDATE() function returns a copy of the input XML value, augmented with information obtained from XML schema validation, including default values and type annotations. If the value of the column is null, the result of the VALIDATED predicate is unknown; otherwise, the result is either TRUE or FALSE.)

Therefore, if you wanted to add a check constraint that uses the VALIDATED predicate to the XML column in the table we created earlier, you could do so by executing a CREATE TABLE SQL statement that looks something like this:

 CREATE TABLE emp_resume   (empid INTEGER NOT NULL PRIMARY KEY,   resume XML,   CONSTRAINT valid_check CHECK (resume IS VALIDATED))

Once this statement is executed, whenever an attempt is made to insert or update XML data in the EMP_RESUME table, a check is performed to ensure the data represents a valid, well-formed XML document. (If it does not, the insert/update operation fails and no changes are made to the table.)

Creating Tables with Identity Columns

Many times, base tables are designed such that a single column will be used to store a unique identifier that represents an individual record (or row). More often than not, this identifier is a number that is sequentially incremented each time a new record is added to the table. Numbers for such columns can be generated using a before trigger, or the DB2 Database Manager can automatically generate numbers for such a column if the column is defined as an identity column. Identity columns are created by specifying the GENERATED…AS IDENTITY clause along with one or more of the identity column attributes available, as part of the column definition. The syntax used to create an identity column is:

 [ColumnName][DataType] GENERATED <ALWAYS | BY DEFAULT> AS IDENTITY <(   <START WITH [1 | StartingValue]>   <INCREMENT BY [1 | IncrementValue]>   <NO MINVALUE | MINVALUE [MinValue]>   <NO MAXVALUE | MAXVALUE [MaxValue]>   <NO CYCLE | CYCLE>   <CACHE 20 | NO CACHE | CACHE [CacheSize]>   <NO ORDER | ORDER> )>

 [ColumnName][DataType] GENERATED <ALWAYS | BY DEFAULT> AS (Expression)

where:

ColumnName	Identifies the unique name that is to be assigned to the identity column to be created.
DataType	Identifies the numeric data type that is to be assigned to the identity column to be created. The data type specified must be a numeric data type with a scale of 0; therefore, the following values are valid for this parameter: SMALLINT, INTEGER, BIGINT, DECIMAL or NUMERIC, or a user-defined data type that is based on any of these data types.
StartingValue	Identifies the first value that is to be assigned to the identity column to be created.
IncrementValue	Identifies the interval that is to be used to calculate each consecutive value that is to be assigned to the identity column to be created.
MinValue	Identifies the smallest value that can be assigned to the identity column to be created.
MaxValue	Identifies the largest value that can be assigned to the identity column to be created.
CacheSize	Identifies the number of values of the identity sequence that are to be generated at one time and kept in memory.
Expression	Identifies an expression or user-defined external function that is to be used to generate values for the identity column to be created.

If the CYCLE clause is specified as part of the identity column's definition, values will continue to be generated for the column after any minimum or maximum value specified has been reached: After an ascending identity column reaches the maximum value allowed, a new minimum value will be generated and the cycle will begin again; after a descending identity column reaches the minimum value allowed, a new maximum value will be generated and the cycle will repeat itself.

Thus, if you wanted to create a table that had a simple identity column in it for which the DB2 Database Manager will always generate a value, you could do so by executing a CREATE TABLE SQL statement that looks something like this:

 CREATE TABLE employees   (empid INTEGER GENERATED ALWAYS AS IDENTITY,   name CHAR(50)   dept INTEGER)

On the other hand, if you want to create a table that has a simple identity column in it for which the DB2 Database Manager will generate a value if no value is explicitly provided, you could do so by executing a CREATE TABLE SQL statement that looks something like this:

 CREATE TABLE employees   (empid INTEGER GENERATED BY DEFAULT AS IDENTITY,   name CHAR(50)   dept INTEGER)

In the first example, the SQL statement "INSERT INTO employees VALUES (1, 'SCHIEFER', 50)" would fail because the DB2 Database Manager is expected to provide values for the EMPID column. In the second example, the same SQL statement would succeed because the DB2 Database Manager is only expected to provide a value for the EMPID column if no value is explicitly provided.

It is important to note that a table can have only one identity column. All identity columns are implicitly assigned a NOT NULL constraint; identity columns cannot have a default constraint.

Creating Partitioned Tables

Table partitioning (also referred to as range partitioning) is a data organization scheme in which table data is divided across multiple storage objects called data partitions or ranges based on values in one or more columns. Each data partition is stored separately and the storage objects used can be in different table spaces, in the same table space, or a combination of the two. Table partitioning improves performance and eliminates the need to create a partitioned database using the Data Partitioning Feature.

Data from a given table is partitioned into multiple storage objects based on the specifications provided in the PARTITION BY clause of the CREATE TABLE statement. The syntax for the PARTITION BY clause is:

 <PARTITION BY <RANGE>  ([ColumnName] <NULLS LAST | NULLS FIRST> ,...)  (STARTING <FROM>    <(> [Start | MINVALUE | MAXVALUE] < ,...)>    <INCLUSIVE | EXCLUSIVE>   ENDING <AT>    <(> [End | MINVALUE | MAXVALUE] < ,...)>    <INCLUSIVE | EXCLUSIVE>   EVERY <(>[Constant]<DurationLabel><)> )>

 <PARTITION BY <RANGE>  ([ColumnName] <NULLS LAST | NULLS FIRST> ,...)  (<PARTITION [PartitionName]>  STARTING <FROM>    <(> [Start | MINVALUE | MAXVALUE] < ,...)>    <INCLUSIVE | EXCLUSIVE>   ENDING <AT>    <(> [End | MINVALUE | MAXVALUE] < ,...)>    <INCLUSIVE | EXCLUSIVE>   <IN [TableSpaceName]> )>

where:

ColumnName	Identifies one or more columns, by name, whose values are to be used to determine which data partition a particular row is to be stored in. (The group of columns specified make up the partitioning key for the table.)
PartitionName	Identifies the unique name that is to be assigned to the data partition to be created.
Start	Specifies the low end of the range for each data partition.
End	Specifies the high end of the range for each data partition.
Constant	Specifies the width of each data partition range when the automatically generated form of the syntax is used. Data partitions will be created starting at the STARTING FROM value and will contain this number of values in the range. This form of the syntax is only supported if the partitioning key consists of a single column that has been assigned a numeric, date, time, or timestamp data type.
DurationLabel	Identifies the duration that is associated with the Constant value specified if the partitioning key column has been assigned a date, time, or timestamp data type. The following values are valid for this parameter: YEAR, YEARS, MONTH, MONTHS, DAY, DAYS, HOUR, HOURS, MINUTE, MINUTES, SECOND , SECONDS, MICROSECOND, and MICROSECONDS.
TableSpaceName	Identifies the table space that each data partition is to be stored in.

Thus, if you wanted to create a table that is partitioned such that each quarter's data is stored in a different data partition, and each partition resides in a different table space, you could do so by executing a CREATE TABLE SQL statement that looks something like this:

 CREATE TABLE part_table   (col1 DATE,   col2 NUMERIC(5,2))   IN tbsp0, tbsp1, tbsp2, tbsp3   PARTITION BY RANGE (col1 NULLS FIRST)     (STARTING '1/1/2006' ENDING '12/31/2006'     EVERY 3 MONTHS)

On the other hand, if you wanted to create a table that is partitioned such that rows with numerical values that fall in the range of 0 to 9 are stored in one partition that resides in one table space, rows with numerical values that fall in the range of 10 to 19 are stored in another partition that resides in another table space, and so on, you could do so by executing a CREATE TABLE SQL statement that looks something like this:

you could do so by executing a CREATE TABLE SQL statement that looks something like this:

 CREATE TABLE part_table   (col1   INT   col2    CHAR(3))   PARTITION BY (col1 NULLS FIRST)     (STARTING 0 ENDING 9 IN tbsp0,     STARTING 10 ENDING 19 IN tbsp1,     STARTING 20 ENDING 29 IN tbsp2,     STARTING 30 ENDING 39 IN tbsp3)

Advantages of using table partitioning include:

Easy roll-in and roll-out of data: Rolling in partitioned table data allows a new range to be easily incorporated into a partitioned table as an additional data partition. Rolling out partitioned table data allows you to separate ranges of data from a partitioned table easily for subsequent purging or archiving. Data can be quickly rolled in and out by using the ATTACH PARTITION and DETACH PARTITION clauses of the ALTER TABLE statement.
Easier administration of large tables: Table level administration becomes more flexible because administrative tasks can be performed on individual data partitions. Such tasks include detaching and reattaching of a data partition, backing up and restoring individual data partitions, and reorganizing individual indexes. In addition, time-consuming maintenance operations can be shortened by breaking them down into a series of smaller operations. For example, backup operations can be performed at the data partition level when each data partition is placed in separate table spaces. Thus, it is possible to back up one data partition of a partitioned table at a time.
Flexible index placement: With table partitioning, indexes can be placed in different table spaces, allowing for more granular control of index placement.
Better query processing: When resolving queries, one or more data partitions may be automatically eliminated, based on the query predicates used. This functionality, known as Data Partition Elimination, improves the performance of many decision support queries because less data has to be analyzed before a result data set can be returned.

Creating Tables That Are Like Existing Tables

At times, it may be desirable to create a new table that has the same definition as an existing table. To perform such an operation, you could execute a CREATE TABLE statement that looks identical to the CREATE TABLE statement that was used to define the original table. Or better still, you could use a special form of the CREATE TABLE statement. The syntax for this form of the CREATE TABLE statement is:

 CREATE TABLE [TableName] LIKE [SourceTable] <[INCLUDING | EXCLUDING] COLUMN DEFAULTS> <[INCLUDING | EXCLUDING] IDENTITY COLUMN ATTRIBUTES>

where:

TableName	Identifies the unique name that is to be assigned to the table to be created.
SourceTable	Identifies the name of an existing table whose structure is to be used to define the table to be created.

Thus, if you wanted to create an empty table named 2ND_QTR_SALES that has the same structure as an existing table named 1ST_QTR_SALES, you could do so by executing a CREATE TABLE SQL statement that looks something like this:

 CREATE TABLE 2nd_qtr_sales LIKE 1st_qtr_sales

When this form of the CREATE TABLE is executed, the table that is created will have the same number of columns as the source table specified, and these columns will have the same names, data types, and nullability characteristics as those of the source table. Unless the EXCLUDING COLUMN DEFAULTS option is specified, any default constraints defined for columns in the source table will be copied to the new table as well. However, no other attributes of the source table will be duplicated. Thus, the table that is created will not contain UNIQUE constraints, referential constraints, triggers, or indexes that have been defined for the source table used. (If the target table needs these characteristics, they must be created separately after the target table is created.)