CREATE TABLE

The CREATE TABLE statement defines a table. The definition must include its name and the names and attributes of its columns. The definition can include other attributes of the table, such as its primary key and its table space.

CREATE TABLE clauses have been extended to include options for defining and dropping table check constraints, and specifying columns with user-defined defaults.

Invocation

This statement can be embedded in an application program or issued interactively.

Authorization

To execute the CREATE TABLE command, users must possess one (or more) privileges listed in at least one of the following:

  • DBADM, DBCTRL, DBMAINT or CREATETS authority for the database.
  • Overall SYSADM or SYSCTRL authority for the location.
  • CREATETAB privilege for the database that is implicitly or explicitly specified by the IN clause.

Additional privileges might be required in the following conditions:

  • The clause IN, LIKE or FOREIGN KEY is specified.
  • The data type of a column is a distinct type.
  • The table space is implicitly created.
  • A fullselect is specified.
  • A column is defined as a security label column.

If the statement is embedded in an application program, the privilege set is the privileges that are held by the authorization ID of the owner of the plan or package.

  • If the privilege set lacks SYSADM or SYSCTRL authority, DBADM authority for the database, or DBCTRL authority for the database, the qualifier (owner) of the table must be the same as the authorization ID of the owner of the plan or package.
  • If the privilege set includes SYSADM or SYSCTRL authority, DBADM authority for the database, or DBCTRL authority for the database, the qualifier (owner) of the table can be any authorization ID.

If the statement is dynamically prepared, the privilege set is the privileges that are held by the SQL authorization ID of the process.

  • If the privilege set lacks SYSADM or SYSCTRL authority, DBADM authority for the database, or DBCTRL authority for the database, the qualifier (owner) of the table must be the same as one of the authorization IDs of the process and the privilege set that is held by that authorization ID includes all privileges needed to create the table.
  • If the privilege set includes SYSADM or SYSCTRL authority, DBADM authority for the database, or DBCTRL authority for the database, the qualifier (owner) of the table can be any authorization ID.

Syntax

Bold text indicates clauses or options that are supported only syntactically.

CREATE TABLE table-name
{ 
    (column-definition 
        [unique-constraint] 
        [referential-constraint] 
        [select-command] 
        [check-constraint] [,...]) 
     | LIKE {table-name | view-name} [INCLUDING IDENTITY [COLUMN ATTRIBUTES]]
     | materialized-query-definition
}
    [WITH RESTRICT ON DROP]
    [{IN [database-name.]tablespace-name 
        | IN DATABASE database-name}]
    [partitioning-clause]
    [EDITPROC program-name]
    [VALIDPROC program-name]
    [AUDIT {NONE | CHANGES | ALL}]
    [OBID integer]
    [DATA CAPTURE {NONE | CHANGES}]
    [WITH RESTRICT ON DROP]
    [CCSID {ASCII | EBCDIC | UNICODE}]
     [{VOLATILE | NOT VOLATILE} [CARDINALITY]]

unique-constraint:

{UNIQUE | PRIMARY KEY}(column-name[,...])

referential-constraint:

FOREIGN KEY [constraint-name] (column-name[,...]) 
    references-clause

references-clause:

REFERENCES referenced-table
    [column-name ,...]
    [ON DELETE {RESTRICT | NO ACTION | CASCADE | SET NULL}]
    [ENFORCED | NOT ENFORCED]
    [ENABLE QUERY OPTIMIZATION]

check-constraint:

[CONSTRAINT constraint-name] CHECK (check-condition)

column-definition:

column-name data-type 
    [NOT NULL]
    [column-constraint]
    [{UNIQUE | PRIMARY KEY}] 
    [FOR {BIT | SBCS | MIXED} DATA] 
    [default-clause] 
    [GENERATED {ALWAYS | BY DEFAULT} [as-identity-clause]
    [references-clause] 
    [check-constraint] 
    [FIELDPROC program-name [(constant[,...])]]
    [AS SECURITY LABEL]

default-clause:

[WITH] DEFAULT 
[{constant | USER | CURRENT SQLID | NULL | cast-function-name ({constant | USER | CURRENT SQLID | NULL})}]

data-type:

{built-in-data-type | distinct-type-name}

built-in-data-type:

SMALLINT |
{INTEGER | INT} |
{DECIMAL | DEC | NUMERIC} [integer[,...]] |
{FLOAT [integer] | REAL [PRECISION] | DOUBLE} |
{ {CHARACTER | CHAR} [(integer)] | {CHARACTER | CHAR} VARYING (integer) | VARCHAR (integer) } 
[FOR {SBCS | MIXED | BIT} DATA] [CCSID {EBCDIC | ASCII}] |
{{CHARACTER | CHAR} LARGE OBJECT | CLOB} [(integer [K|M|G])] } 
[FOR {SBCS | MIXED} DATA] [CCSID {EBCDIC | ASCII}] } |
{BINARY LARGE OBJECT | BLOB} (integer [K|M|G]) |
{GRAPHIC [(integer)] | VARGRAPHIC (integer) | DBCLOB (integer [K|M|G])} [CCSID {EBCDIC | ASCII}] | 
{DATE | TIME | TIMESTAMP} |
ROWID

as-identity-clause:

AS IDENTITY
[({START WITH {numeric-constant | 1} 
| INCREMENT BY {numeric-constant | 1} 
| MINVALUE numeric-constant | NOMINVALUE
| MAXVALUE numeric-constant | NOMAXVALUE
| CYCLE | NOCYCLE
| ORDER | NO ORDER
| {CACHE 20 | NO CACHE | CACHE integer}}][,..])]

partitioning-clause:

PARTITION BY [RANGE] (partition-expression[,...]) (partition-element[,...]) 

partition-expresion:

column-name [NULLS LAST] [ASC | DESC]

partition-element:

PARTITION integer ENDING [AT] (constant[,...]) [INCLUSIVE]

materialized-query-definition:

[(column-name[,...])] AS (fullselect)
{WITH NO DATA [copy-options] | refreshable-table-options}

copy-options:

[EXCLUDING IDENTITY [COLUMN ATTRIBUTES] | 
INCLUDING IDENTITY [COLUMN ATTRIBUTES]]
[EXCLUDING [COLUMN] DEFAULTS | INCLUDING [COLUMN] DEFAULTS | USING TYPE DEFAULTS]

refreshable-table-options:

DATA INITIALLY DEFERRED REFRESH DEFERRED
[[MAINTAINED BY SYSTEM | MAINTAINED BY USER] 
[ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION] ...]

Parameters:

table-name
Any long identifier naming the table. The name must uniquely identify the table, and must not match any other table, view, alias, or synonym name existing within the current location. If qualified, the table name consists of a two-part or three-part name. If a three-part name is specified, the first part must match the value of the CURRENT SERVER special register -- that is, the current location. In any case, the AuthID qualifying the table name is the table owner.
column-name
Any long identifier naming the column. Each column in a table must have an unqualified column name, unique within the table. Dependent tables (tables containing a foreign key referencing a primary key in another table) can have up to 749 columns named, while nondependent tables (tables containing the primary key) may have 750 named columns. Column names are optional only when the select-command statement is included, in which case the column names from the source table are used.
constant
FIELDPROC clause: a parameter passed to the field procedure when it is invoked during execution of the CREATE TABLE statement. A parameter list is optional, and the number of parameters and the data type of each are determined by the field procedure. The maximum length of the parameter list is 254 bytes, including commas but excluding insignificant blanks and the delimiting parentheses.

[WITH] DEFAULT clause: names the default value for the column. The value of the constant must conform to the rules for assigning that value to the column. A default value can be specified for a column with a data type of LONG VARCHAR or LONG VARGRAPHIC and a maximum length of 254 bytes.

A character or graphic string constant must be short enough so that its UTF-8 representation requires no more than 1536. In addition, a hexadecimal graphic string constant (GX) cannot be specified.

data-type
Specifies the column data type. Data type is optional only when a select-command statement is included. FOR BIT DATA is optional; it is for use with CHAR, VARCHAR and LONG VARCHAR columns to indicate that the contents of the field values are to be treated as bit (binary) data. This is important for COBOL programs that use character and variable character fields to store integers, structures, etc. If not specified as FOR BIT DATA, the field is assumed to contain ASCII characters. Note that the FOR BIT DATA clause must precede the NULL specification for the field.

To conform with DB2 mainframe-specific operations, the XDB Server also records the FOR SBCS DATA and FOR MIXED DATA clause options in the appropriate system catalog tables.

NOT NULL is an optional instruction that prevents the column from containing null values.

NOT NULL WITH DEFAULT prevents columns from containing null values. When an attempt is made to place a NULL value in such a column, a default value is inserted. The default value is dependent on the data type as shown in the following table.

built-in-data-type
Specifies the data type of the column as one of the following built-in data types, and for character string data types, specifies the subtype. If you define the table with a LOB column (CLOB, DBCLOB, or BLOB), you must also define a ROWID column.

[WITH] DEFAULT clause: In a given column definition neither NOT NULL nor DEFAULT can be specified more than once. Both NOT NULL and DEFAULT cannot be specified. Omitting NOT NULL and DEFAULT implies that DEFAULT NULL is intended.

Data Type Default Value
Numeric 0
Date current date
Time current time
Timestamp current timestamp
Character Blanks
Varchar Blanks
Graphic Blanks
select-command
Can consist of any SELECT statement that retrieves data values from a database and inserts them into the newly created table. See CREATE With SELECT below.
column-name
Names a column on which a key is to be created. Up to 64 columns can be included in the key. If creating a PRIMARY key, a unique index must be created on the table; in this index exactly the same column names must appear, in exactly the same order as in the PRIMARY KEY constraint. The index will be designated the primary key. See PRIMARY KEY below and the CREATE GLOBAL TEMPORARY TABLE command for more information.
constraint-name
Defines a FOREIGN KEY constraint (which cannot be a unique index). If constraint-name is not specified in the command, the system generates a constraint name by taking the first eight characters of the first column name and checking for uniqueness. If not unique, the XDB Server adds a number to the first seven characters of the this first column name, and checks for uniqueness again. If necessary, additional character(s) at the end of the column name string are replaced with numbers until the name becomes unique among all constraint names on the table.

Also defines a table CHECK constraint within the CONSTRAINT...CHECK clause. The constraint-name is an SQL long identifier. A constraint-name cannot be specified more than once for the same table (see CONSTRAINT keyword).

check-condition
Provides a shorthand method of defining a check constraint that applies to a single column.

A check condition can consist of a single predicate, or two or more predicates joined by AND or OR. The first operand of each predicate must be the name of a column of the table. The second operand can be a column name or a constant. A check-condition can evaluate to unknown if a column that is an operand of the predicate is null. A check-condition that evaluates to unknown does not violate the check constraint (see CHECK clause).

For conformance with the SQL standard, if CHECK is specified in the column definition of column C, no columns other than C should be referenced in the check condition of the check constraint. The effect is the same as if the check condition were specified as a separate clause.

referenced-table
Identifies the table referenced by the referential integrity constraint (or the parent table containing the primary key referenced).

The ON DELETE or ON UPDATE (XDB mode only) clause tells the system how to handle dependent records in this table when a corresponding record in the parent table is being deleted. The RESTRICT option (default) prevents the record from being deleted from the parent table. The CASCADE option deletes or updates the dependent records from the current table, and allows the deletion or updating of the parent record. The SET NULL option causes the foreign key fields in the dependent records to be set to NULL before the parent record is deleted.

Description

XDB Server tables can be created using the CREATE TABLE command. Tables can also be created using SQLWizard. For more information on SQLWizard, see the SQLWizard User's Guide. The XDB Server supports a CREATE with SELECT command which allows a table to be created and populated in one command execution. The CREATE LIKE command allows the creation of a table using an existing table or view specification. See CREATE LIKE below.

The following example creates the table INVENT:

CREATE TABLE invent 
    (item_no SMALLINT NOT NULL, 
     description CHAR (12),
     model CHAR (6),
     type CHAR 12,
     weight FLOAT,
     qoh INTEGER,
     unit_cost DECIMAL(6,3),
     price MONEY)

The INVENT table will have the structure shown in the following table:

Column Name Data Type
item_no SMALLINT (nulls not permitted)
Description CHAR 12
Model CHAR 6
Type CHAR 12
Weight FLOAT
Qoh INTEGER
unit_cost DECIMAL(6,3)
Price MONEY

Once a table has been created, data can be entered or indexes created. The column types and the number of columns can be changed using the ALTER TABLE command. The table name can be changed by the RENAME command. The table can also be altered or renamed using SQLWizard. (For more information on SQLWizard, see the SQLWizard User's Guide.)

With DB2 V7, XDB locations (either EBCDIC or ASCII) can now contain Unicode data. If you create a table with CCSID UNICODE, all character data in corresponding tables is stored in UTF-8 format and all graphic data is stored in UTF-16 format.

FOR subtype DATA
Specifies a subtype for a character string column, which is a column with a data type of CHAR, VARCHAR, LONG VARCHAR, or CLOB. Do not use the FOR DATA clause with columns of any other data type (including any distinct type). subtype can be one of the following:
SBCS Column holds single-byte data.
MIXED Column holds mixed data. Do not specify MIXED if the value of field MIXED DATA on installation panel DSNTIPF is NO.
BIT Column holds BIT data. Do not specify BIT for a CLOB column.

If you do not specify the FOR clause, the column is defined with a default subtype. The default is SBCS when the value of field MIXED DATA on installation panel DSNTIPF is NO. The default is MIXED when the value is YES.

A security label column is always considered SBCS data, regardless of the encoding scheme of the table.

XDB Server operating environment does not support the CLOB data type at this time.

GRAPHIC (integer)
For a fixed-length graphic string of length integer, which can range from 1 to 127. If the length specification is omitted, a length of 1 character is assumed.
VARGRAPHIC (integer)
For a varying-length graphic string of maximum length integer, which must range from 1 to n/2, where n is the maximum row size minus 2 bytes. An integer longer than 127 defines a long string column.
ROWID

For a row ID type.

A table can have only one ROWID column. The values in a ROWID column are unique for every row in the table and cannot be updated. You must specify NOT NULL with ROWID.

NOT NULL

Prevents the column from containing null values.

PRIMARY KEY

Provides a shorthand method of defining a primary key composed of a single column. Thus, if PRIMARY KEY is specified in the definition of column C, the effect is the same as if the PRIMARY KEY(C) clause is specified as a separate clause.

The NOT NULL clause must be specified with this clause. Each column-name must be an unqualified name that identifies a column of the table except a LOB or ROWID column (including a distinct type that is based on a LOB or ROWID), and the same column must not be identified more than once. The number of identified columns must not exceed 64. In addition, the sum of the length attributes of the columns must not be greater than 2000 -2m, where m is the number of varying-length columns in the key.

The table is marked as unavailable until its primary index is explicitly created unless the CREATE TABLE statement is processed by the schema processor. In that case, DB2 implicitly creates an index to enforce the uniqueness of the primary key and the table definition is considered complete.

UNIQUE

Provides a shorthand method of defining a unique key composed of a single column. Thus, if UNIQUE is specified in the definition of column C, the effect is the same as if the UNIQUE(C) clause is specified as a separate clause.

The NOT NULL clause must be specified with this clause. UNIQUE cannot be specified more than once in a column definition and must not be specified if the PRIMARY KEY clause is specified in the column definition or if the definition is for a LOB column. The number of identified columns must not exceed 64. In addition, the sum of the length attributes of the columns must not be greater than 2000 -2m, where m is the number of varying-length columns in the key.

Each column-name must be an unqualified name that identifies a column of the table except a LOB column, and the same column must not be identified more than once.

DEFAULT

The default value assigned to the column in the absence of a value specified on INSERT or LOAD. Do not specify DEFAULT for a ROWID column, an identity column (a column that is defined AS IDENTITY) or a security label column (a column that is defined AS SECURITY LABEL); DB2 generates default values. If a value is not specified after DEFAULT, the default value depends on the data type of the column, as follows:

Data Type Default Value
Numeric 0
Fixed-length string Blanks
Varying-length string A string of length 0
Date CURRENT DATE
Time CURRENT TIME
Timestamp CURRENT TIMESTAMP
Distinct type The default of the source data type

A default value other than the one that is listed above can be specified in one of the following forms, except for a LOB column. The only form that can be specified for a LOB column is DEFAULT NULL. Unlike other varying-length strings, a LOB column can only have the default value of a zero-length string as listed above or null.

GENERATED

Specifies that XDB generates values for the column. GENERATED must be specified if the column is to be considered an identity column. If the data type of the column is a ROWID (or a distinct type that is based on a ROWID), the default is GENERATED ALWAYS.

ALWAYS Indicates that XDB will always generate a value for the column when a row is inserted into the table. ALWAYS is the recommended value unless you are using data propagation.
BY DEFAULT Indicates that XDB will generate a value for the column when a row is inserted into the table unless a value is specified.

For a ROWID column, XDB uses a specified value only if it is a valid row ID value that was previously generated by XDB and the column has a unique, single-column index. Until this index is created on the ROWID column, the SQL INSERT statement and the LOAD utility cannot be used to add rows to the table. If the value of special register CURRENT RULES is 'STD' when the CREATE TABLE statement is processed, XDB implicitly creates the index on the ROWID column. The name of this index is 'I' followed by the first ten characters of the column name followed by seven randomly generated characters. If the column name is less than ten characters, XDB adds underscore characters to the end of the name until it has ten characters. The implicitly created index has the COPY NO attribute.

For an identity column, XDB inserts a specified value but does not verify that it is a unique value for the column unless the identity column has a unique, single-column index. Without a unique index, XDB can guarantee unique values only among the set of system-generated values.

BY DEFAULT is the recommended value only when you are using data propagation.

AS SECURITY LABEL

Specifies that the column will contain security label values. This also indicates that the table is defined with multilevel security with row level granularity. A table can have only one security label column. To define a table with a security label column, the primary authorization ID of the statement must have a valid security label, and the RACF SECLABEL class must be active. In addition, the following conditions are also required:

  • The data type of the column must be CHAR(8).
  • The subtype of the column must be SBCS.
  • The column must be defined with the NOT NULL and WITH DEFAULT clauses.
  • No field procedures, check constraints, or referential constraints are defined on the column.
  • An edit procedure is not defined on the table.
  • You are using z/OS Version 1 Release 5 or later.
AS IDENTITY

Specifies that the column is an identity column for the table. A table can have only one identity column. AS IDENTITY can be specified only if the data type for the column is an exact numeric type with a scale of zero (SMALLINT, INTEGER, DECIMAL with a scale of zero, or a distinct type based on one of these types).

An identity column is implicitly NOT NULL.

START WITH numeric-constant Specifies the first value for the identity column. The value can be any positive or negative value that could be assigned to the column without non-zero digits exisiting to the right of the decimal point. The default is 1.
INCREMENT BY numeric-constant Specifies the interval between consecutive values of the identity column. The value can be any positive or negative value (including 0) that does not exceed the value of a large integer constant, and could be assigned to the column without any non-zero digits existing to the right of the decimal point. The default is 1.

If this value is negative, the values for the identity column descend. If this value is 0 or positive, the values for the identity column ascend. The default is 1.

MINVALUE or NO MINVALUE

Specifies the minimum value at which a descending identity column either cycles or stops generating values or an ascending identity column cycles to after reaching the maximum value. The default is NO MINVALUE.

NO MINVALUE

Specifies that the minimum end point of the range of values for the identity column has not be set. In such a case, the default value for MINVALUE becomes one of the following:

  • For an ascending identity column, the value is the START WITH value or 1 if START WITH was not specified.
  • For a descending identity column, the value is the minimum value of the data type of the column.
MINVALUE numeric-constant

Specifies the numeric constant that is the minimum value that is generated for this identity column. This value can be any positive or negative value that could be assigned to this column without non-zero digits existing to the right of the decimal point. The value must be less than or equal to the maximum value.

MAXVALUE or NO MAXVALUE

Specifies the maximum value at which an ascending identity column either cycles or stops generating values or a descending identity column cycles to after reaching the minimum value. The default is NO MAXVALUE.

NO MAXVALUE

Specifies that the minimum end point of the range of values for the identity column has not be set. In such a case, the default value for MAXVALUE becomes one of the following:

  • For an ascending identity column, the value is the maximum value of the data type of the column.
  • For a descending identity column, the value is the START WITH value or -1 if START WITH is not specified.
MAXVALUE numeric-constant

Specifies the numeric constant that is the maximum value that is generated for this identity column. This value can be any positive or negative value that could be assigned to this column without non-zero digits existing to the right of the decimal point. The value must be greater than or equal to the minimum value.

NO CYCLE or CYCLE

Specifies whether this identity column should continue to generate values after reaching either its maximum or minimum value.

NO CYCLE

Specifies that values will not be generated for the identity column after the maximum or minimum value has been reached. This is the default.

CYCLE

Specifies that values continue to be generated for the identity column after the maximum or minimum value has been reached. If this option is used, after an ascending identity column reaches the maximum value, it generates its minimum value. After a descending identity column reaches its minimum value, it generates its maximum value. The maximum and minimum values for the identity column determine the range that is used for cycling.

When CYCLE is in effect, duplicate values can be generated by DB2 for an identity column. However, if a unique index exists on the identity column and a non-unique value is generated for it, an error occurs.

CACHE or NO CACHE

This clause has no functional equivalent in the XDB Server operating environment.

NO ORDER or ORDER

Specifies whether the identity column values must be generated in order of request. The default is NO ORDER.

NO ORDER

Specifies that the values do not need to be generated in order of request.

ORDER

Specifies that the values are generated in order of request. Specifying ORDER may disable the caching of values. ORDER applies only to a single-application process.

In a data sharing environment, if the CACHE and NO ORDER options are in effect, multiple caches can be active simultaneously, and the requests for identity values from different DB2 members may not result in the assignment of values in strict numeric order. For example, if members DB2A and DB2B are using the identity column, and DB2A gets the cache values 1 to 20 and DB2B gets the cache values 21 to 40, the actual order of values assigned would be 1,21,2 if DB2A requested a value first, then DB2B requested, and then DB2A again requested. Therefore, to guarantee that identity values are generated in strict numeric order among multiple DB2 members using the same identity column, specify the ORDER option.

PARTITION BY RANGE

Specifies the range partitioning scheme for the table (the columns used to partition the data). When this clause is specified, the table space is complete, and it is not necessary to create a partitioned index on the table. If this clause is used, the ENDING AT clause cannot be used on a subsequent CREATE INDEX statement for this table.

If this clause is specified, the IN database-name.table-space-name clause is required. This clause applies only to tables in a partitioned table space.

This clause is only implemented syntactically in the XDB Server operating environment.

partition-expression

Specifies the key data over which the range is defined to determine the target data partition of the data.

column-name

Specifies the columns of the key. Each column-name must identify a column of the table. Do not specify more than 64 columns, the same column more than once, a LOB column, a column with a distinct type that is based on a LOB data type, or a qualified column name. The sum of length attributes of the columns must not be greater than 255 - n, where n is the number of columns that can contain null values.

NULLS LAST

Specifies that null values are treated as positive infinity for purposes of comparison.

ASC

Puts the entries in ascending order by the column. ASC is the default.

DESC

Puts the entries in descending order by the column.

partition-element

Specifies ranges for a data partitioning key and the table space where rows of the table in the range will be stored.

PARTITION integer

integer is the physical number of a partition in the table space. A PARTITION clause must be specified for every partition of the table space. In this context, highest means highest in the sorting sequences of the columns. In a column defined as ascending (ASC), highest and lowest have their usual meanings. In a column defined as descending (DESC), the lowest actual value is highest in the sorting sequence.

This clause is only implemented syntactically in the XDB Server operating environment.

ENDING AT(constant, ...)

constant defines the limit key for a partition boundary. Specify at least one constant after ENDING AT in each PARTITION clause. You can use as many constants as there are columns in the key. The concatenation of all the constants is the highest value of the key for ascending and the lowest for descending. The use of constants to define key values is subject to the following rules:

  • The first constant corresponds to the first column of the key, the second constant to the second column, and so on. Each constant must conform to the rules for assigning that value to the column. A hexadecimal string constant (GX) cannot be specified.

    Using fewer constants than there are columns in the key has the same effect as using the highest or lowest values for the omitted columns, depending on whether they are ascending or descending.

  • The highest value of the key in any partition must be lower than the highest value of the key in the next partition for ascending cases.
  • The constants specified for the last partition are enforced. The value specified for the last partition is the highest value of the key that can be placed in the table. Any key values greater than the value specified for the last partition are out of range.
  • The precision and scale of a decimal constant must not be greater than the precision and scale of its corresponding column.
  • If a string constant is longer or shorter than required by the length attribute of its column, the constant is either truncated or padded on the right to the required length. If the column is ascending, the padding character is X'FF'. If the column is descending, the padding character is X'00'.
  • If a key includes a ROWID column or a column with a distinct type that is based on a ROWID data type, then 17 bytes of the constant that is specified for the corresponding ROWID column are considered.
INCLUSIVE

Specifies that the specified range values are included in the data partition.

CREATE With SELECT

The CREATE command may include an optional SELECT command (XDB mode only) that selects data from an existing table to insert into the new table. The order of the selected columns defines the column order of the new table. However, the number of columns selected does not need to match the number of columns defined for the new table. If the new table has more columns than the SELECT statement, the extra columns are assigned NULL values. If the new table has fewer columns than the SELECT command, the system uses only as many of the first columns as will fit into the new table. If no column names are given in the create command, the column names listed in the SELECT command are used to create the table.

Any search condition can be used in the SELECT statement including a join condition or a subselect. See WHERE Clause for more information on search conditions. The columns selected do not have to match the data type of the new table columns, but they must be type-compatible. The new table, for example, accepts floating point data in a column defined as an integer -- however, the fractional part of the data is truncated. See the ALTER TABLE command for a type compatibility matrix. When converting from character data to another type, if the conversion is not possible the new value will be NULL.

Three examples of CREATE with SELECT are given below:

CREATE TABLE parispno 
    (pno CHAR 2, pname CHAR 6, city CHAR 6, 
     weight DECIMAL(4,3)) 
    SELECT pno, pname, city 
        FROM part 
        WHERE city = "PARIS"
CREATE TABLE lowinv 
    SELECT * 
        FROM partsupp 
        WHERE qty < 400
CREATE TABLE (a, b, c) 
    SELECT A.a, B.b, C.c 
        FROM A, B, C 
        WHERE A.x = B.x 
          AND B.x = C.x
Subselects and Locations

When populating new tables with subselects, the subselect cannot reference a location other than the one referenced elsewhere in the command. That is, a referenced table and all subselects in a single SQL statement must be from the same location.

Invalid:

CREATE TABLE tutorial.tutorial.sample 
    SELECT * FROM tutorial.tutorial.employee 
        WHERE e_no IN 
            SELECT e_no FROM debloc.debbie.employee 
                WHERE st = "MD";

Valid:

CREATE TABLE tutorial.tutorial.sample 
    SELECT * FROM tutorial.tutorial.employee 
        WHERE e_no IN 
            SELECT e_no FROM tutorial.debbie.employee 
                WHERE st = "MD";
CREATE LIKE

The LIKE table-name or LIKE view-name clause is used to create a new table with the same definition as an existing table or view. The columns of the table will have exactly the same names and column specifications (data type, null permission, etc.) as the source table or view. However, neither the primary nor foreign keys, nor any check option existing on the source table or view are defined for the new table. Data from the source table is NOT transferred to the new table.

An example of the CREATE TABLE...LIKE command is:

CREATE TABLE personnel LIKE employee

Creating a table like a view

If the LIKE clause is specified and the definition of the table is being based on a view, the view must not include a ROWID column or an identity column. A view column is considered to be an identity column if the corresponding column of the table or view indirectly or directly maps to the name of an identity column in a base table with these exceptions:

  • The select-list of the view definition identifies the same identity column more than once.
  • The select-list of the view definition references multiple identity columns and thus involves a join.
  • A column in the view definition includes an expression that refers to an identity column.
IN or IN DATABASE

Specifies a database-name alone (preceded by keyword IN DATABASE), or both a tablespace-name with an optional database-name qualifier (preceded by keyword IN). If a database is named, it must be described in the system catalog for the current location, and must not be the DSNDB06 catalog database.

If a table space name is not explicitly specified, a table space is created implicitly within database-name. In these cases, the XDB Server checks the privileges held by the user for either SYSADM or SYSCTRL authority for the location, DBADM, DBCTRL or DBMAINT authority for the database, or the CREATETS privilege for the database. The table space name is taken from the table name specified in the CREATE TABLE command. Except for the name, the other attributes of this table space match those that would result from a CREATE TABLESPACE statement minus all its optional clauses.

If tablespace-name is specified, this name must not reference an already existing table space that was implicitly created. If both a database and table space are specified, the table space must belong to the named database. If only a table space is named, it must belong to the default DSNDB04 database for the current location. If the table space references a stogroup, then any tables created with that table space name are stored in the path specified for the stogroup.

A subselect and an IN DATABASE clause cannot be used in the same CREATE TABLE statement. Instead, create the table in the database and then use an INSERT INTO command with a subselect to populate the table.

For example:

Invalid:

CREATE TABLE tutorial.debbie.employee
SELECT * FROM tutorial.tutorial.employee 
    WHERE st = "MD"
IN DATABASE debdb;

Valid:

CREATE TABLE tutorial.debbie.employee (e_no SMALLINT NOT NULL, 
    lname CHAR 10 NOT NULL, 
    fname CHAR 10 NOT NULL, street CHAR 20, 
    city CHAR 15, st CHAR 2, zip CHAR 5, 
    dept CHAR 4, payrate MONEY, com DECIMAL(2,2))
IN DATABASE debdb;
INSERT INTO tutorial.debbie.employee 
    SELECT * FROM tutorial.tutorial.employee 
        WHERE st = "MD";
OBID integer

Identifies the OBID to be used for this table. An OBID is the identifier for an object's internal descriptor.

The OBID keyword option is used with ROSHARE databases, and can be viewed by querying the SYSIBM.SYSTABLES catalog table. The integer must not identify an existing or previously used OBID of the database. If you omit OBID, DB2 generates a value.

The following statement retrieves the value of OBID:

SELECT OBID
    FROM SYSIBM.SYSTABLES
    WHERE CREATOR = 'ccc' AND NAME = 'nnn';

Here, nnn is the table name and ccc is the table's creator

UNIQUE

Defines a unique key composed of the identified columns. Each column name must be an unqualified name that identifies a column of the table, and the same column must not be identified more than once. A unique key is a duplicate if it is the same as the primary key or a previously defined unique key. A unique key on a single column can be defined in a column definition. In a given column definition neither UNIQUE nor PRIMARY KEY can be specified more than once, nor can UNIQUE and PRIMARY KEY both be specified for the same column.

PRIMARY KEY

A table can have at most one constraint condition specified as a PRIMARY KEY. The purpose of the primary key is to enforce entity integrity -- that is, to uniquely identify each row of a table. Note that the columns in the primary key must be defined as NOT NULL when the columns are specified.

Note:

You must create an index for a primary key or a unique key if the CREATE TABLE statement is processed by the XDB Server. XDB Server does not implicitly create an index.

A primary key on a single column can be defined in a column definition. In a given column definition neither UNIQUE nor PRIMARY KEY can be specified more than once, nor can UNIQUE and PRIMARY KEY both be specified for the same column.

You cannot create foreign keys on tables that are in a pending state. A table is in a pending state if a primary key clause has been specified but a unique index has not been built on those columns.

For example, to define the SNO and PNO columns as the primary key of the PARTSUPP table, use the command:

CREATE TABLE partsupp(sno SMALLINT NOT NULL, 
    pno SMALLINT NOT NULL, 
    qty SMALLINT, PRIMARY KEY (sno, pno))

To define the SNO column as the primary key of the SUPPLIER table, use a command like:

CREATE TABLE supplier(sno CHAR 3 NOT NULL, 
    sname CHAR 5, status SMALLINT, 
    city CHAR 6, PRIMARY KEY (sno))
FOREIGN KEY

A FOREIGN KEY constraint is used to enforce referential integrity -- the relationship between a referencing (or dependent) table and a referenced (or parent) table. The referential integrity rule requires that, for any value in the dependent columns, there must exist a row in the parent table such that the value of the dependent columns equals the value of the corresponding columns in the primary key of the parent table. This is enforced as follows:

  1. When an update or insert is performed on the dependent table, the set of values placed in the referencing columns must match a set of values that exists in the parent table.
  2. If an attempt is made to delete or update a row of the parent table that contains values matching those in a dependent table, the system uses the ON DELETE or ON UPDATE (XDB Server extension only, not available on DB2 or other modes) clause to determine the delete rule to use. The options are:
    • RESTRICT

      Prevents record deletion from the parent table if dependents exist. RESTRICT is the default value.

    • NO ACTION

      Prevents records from being deleted from the parent table (as in the RESTRICT option), except the referential constraint is enforced at the end of the statement. This is the default setting if the CURRENT RULES special register is STD (SQL standard).

    • CASCADE

      Deletes the dependent records from the current table and then deletes the parent records.

    • SET NULL

      Causes each nullable column in the foreign key of each dependent record to be set to NULL before the corresponding parent records are deleted.

Note that whenever a cyclical situation exists (for example a foreign key of table B references the primary key of table A, and a foreign key of table A references a primary key of table B, the delete rules should be the same for all foreign keys, and the SET NULL option should not be used.

In order to create a referential integrity constraint you must create a FOREIGN KEY constraint on the referencing table. The FOREIGN KEY constraint must reference the existing PRIMARY KEY of the referenced table.

Foreign key columns do not need to have the same names as the corresponding columns in the PRIMARY KEY of the referenced table, but the columns must match in type, number and order.

Each column-name must be an unqualified name that identifies a column of the table except a LOB, ROWID, or security label column, and the same column must not be identified more than once. The number of identified columns must not exceed 64, and the sum of their length attributes must not exceed 255 minus the number of columns that allow null values. The referential constraint is a duplicate if the FOREIGN KEY and parent table are the same as the FOREIGN KEY and parent table of a previously defined referential constraint. The specification of a duplicate referential constraint is ignored with a warning.

A constraint name is used to uniquely identify a FOREIGN KEY constraint, and is stored in the appropriate system catalog table. The constraint name should be a short identifier (up to eight characters) that is unique among all constraints built on the table. Since this constraint name is also used in error messages to indicate constraint violation, it is important to use a meaningful name.

Although it is good practice to provide a meaningful constraint name, if one is not supplied, the system automatically generates one by taking the first eight characters of the first column (appearing in the column list) and then checking for uniqueness. If this name is not unique, the XDB Server takes the first seven characters of the first column name plus a number. If necessary one letter at a time is removed from the end of this constraint name and replaced with a number, until the constraint name becomes unique among all constraint names existing for the table.

Indexes on Foreign Keys

The XDB Server automatically builds an index on your FOREIGN KEY columns. The system will generate the name of the index and store it in the REFKEYNAME column of the SYSIBM.SYSREF catalog table. DB2, in contrast, does not automatically create an index on FOREIGN KEY columns. If you are using your DB2 data definition language (DDL) script, and this script contains CREATE INDEX commands on FOREIGN KEY columns, you can end up with redundant indexes. For optimal performance, you should modify your DB2 DDL script to remove any CREATE INDEX commands on FOREIGN KEY columns.

REFERENCES

The references portion of the referential-constraint clause is a separate clause. A single column can be identified as a foreign key of a referential constraint by using the references-clause in a column definition. Define a key that consists of more than one column by listing the column names to which the REFERENCES privilege applies.

Do not specify references-clause in the definition of a LOB, ROWID, or security label column; a LOB, ROWID, or security label column cannot be a foreign key.

A foreign key can reference a unique key or a primary key. When a foreign key refers to a nonprimary unique key, you must specify the columns of the key. If the foreign key includes a column defined as a distinct type, the corresponding column of the nominated parent key must be the same distinct type.

ENFORCED or NOT ENFORCED

Indicates whether or not the referential constraint is enforced by DB2 during normal operations, such as insert, update, or delete.

ENFORCED

Specifies that the referential constraint is enforced by the DB2 during normal operations (such as insert, update, or delete) and that it is guaranteed to be correct. This is the default.

NOT ENFORCED

Specifies that the referential constraint is not enforced by DB2 during normal operations, such as insert, update, or delete. This option should only be used when the data that is stored in the table is verified to conform to the constraint by some other method than relying on the database manager.

ENABLE QUERY OPTIMIZATION

Specifies that the constraint can be used for query optimization. DB2 uses the information in query optimization using materialized query tables with the assumption that the constraint is correct. This is the default.

LIKE table-name or view-name

Specifies that the columns of the table have exactly the same name and description as the columns of the identified table or view. The name specified after LIKE must identify a table or view that exists at the current server, and the privilege set must implicitly or explicitly include the SELECT privilege on the identified table or view. An identified view must not include a column that is an explicitly defined ROWID column (including a distinct type that is based on a ROWID) or an identity column.

The use of LIKE is an implicit definition of n columns, where n is the number of columns in the identified table or view. The implicit definition includes all attributes of the n columns as they are described in SYSCOLUMNS with these exceptions:

  • When a table is identified in the LIKE clause and a column in the table has a field procedure, the corresponding column of the new table has the same field procedure and the field description. However, the field procedure is not invoked during the execution of the CREATE TABLE statement.
  • When a table is identified in the LIKE clause and a column in the table an identity column, the corresponding column of the new table inherits only the data type of the identity column; none of the identity attributes of the column are inherited unless the INCLUDING IDENTITY clause is specified.
  • When a table is identified in the LIKE clause and a column in the table is a security label column, the corresponding column of the new table inherits only the data type of the security label column; none of the security label attributes of the column are inherited.
  • When a table is identified in the LIKE clause and the table contains a ROWID column (explicitly-defined, hidden, or both), the corresponding columns of the new table inherits the ROWID columns.
  • When a view is identified in the LIKE clause, the default value that is associated with the corresponding column of the new table depends on the column of the underlying base table for the view. If the column of the base table does not have a default, the new column does not have a default. If the column of the base table has a default, the default of the new column is:
    • Null if the column of the underlying base table allows nulls.
    • The default for the data type of the underlying base table if the underlying base table does not allow nulls.
  • When a table that uses table-controlled partitioning is identified in the LIKE clause, the new table does not inherit that table's partitioning scheme. If desired, these partition boundaries can be added by specifying ALTER TABLE with the ADD PARTITION BY RANGE clause.
  • The CCSID of the column is determined by the implicit or explicit CCSID clause.

The above defaults are chosen regardless of the current default of the base table column. Also, no column in the new table has a field procedure because the catalog descriptions of view columns do not include field procedures.

The implicit definition does not include any other attributes of the identified table or view. For example, the new table does not have a primary key or foreign key. The table is created in the table space implicitly or explicitly specified by the IN clause, and the table has any other optional clause only if the optional clause is specified.

EXCLUDING IDENTITY COLUMN ATTRIBUTES or INCLUDING IDENTITY COLUMN ATTRIBUTES

Specifies whether identity column attributes are inherited from the definition of the source of the result table.

EXCLUDING IDENTITY COLUMN ATTRIBUTES

Specifies that identity column attributes are not inherited from the source result table definition. This is the default.

INCLUDING IDENTITY COLUMN ATTRIBUTES

Specifies that, if available, identity column attributes (such as START WITH, INCREMENT BY, and CACHE values) are inherited from the definition of the source table. These attributes can be inherited if the element of the corresponding column in the table, view, or fullselect is the name of a column of a table or the name of a column of a view that directly or indirectly maps to the column name of a base table with the identity attribute. In other cases, the columns of the new temporary table do not inherit the identity attributes. The columns of the new table do not inherit the identity attributes in the following cases:

  • The select list of the fullselect includes multiple instances of an identity column name (that is, selecting the same column more than once).
  • The select list of the fullselect includes multiple identity columns (that is, it involves a join).
  • The identity column is included in an expression in the select list.
  • The fullselect includes a set operation (union).
Using an identity column

When a table has an identity column, DB2 can automatically generate unique, sequential numeric values for the column as rows are inserted into the table. Thus, identity columns are ideal for primary keys. Identity columns and ROWID columns are similar in that both types of columns contain values that DB2 generates and guarantees as unique. ROWID columns are used in large object (LOB) table spaces and can be useful in direct-row access. ROWID columns contain values of the ROWID data type, which returns a 40-byte VARCHAR value that is not regularly ascending or descending. ROWID data values are therefore not well suited to many application uses, such as generating employee numbers or product numbers. For data that is not LOB data and that does not require direct-row access, identity columns are usually a better approach, because identity columns contain existing numeric data types and can be used in a wide variety of uses for which ROWID values would not be suitable.

When a table is recovered to a point-in-time, it is possible that a large gap in the sequence of generated values for the identity column might result. For example, assume a table has an identity column that has an incremental value of 1 and that the last generated value at time T1 was 100 and DB2 subsequently generates values up to 1000. Now, assume that the table space is recovered back to time T1. The generated value of the identity column for the next row that is inserted after the recovery completes will be 1001, leaving a gap from 100 to 1001 in the values of the identity column.

Sometimes you may need to change the attributes of an identity column, For example, if you had defined an identity column with a data type of SMALLINT and then run out of assignable values, you need to redefine the column as INTEGER. To change the attributes of an identity column, you unload the data from the table, drop the table, recreate the table, and reload athe data.

But when you recreate the table, you must specify GENERATED BY DEFAULT and a new START WITH value for the identify column.Using GENERATED BY DEFAULT allows LOAD to reload the previously existing identity column values. You cannot use GENERATED ALWAYS in this case, but not using it is not a problem since DB2 always generates a value if a column value is not provided during insertion of an identity column defined as GENERATED BY DEFAULT. The new START WITH value should be the next value in the sequence from where the original sequence of values left off; this value is the next value that DB2 would generate first.

WITH RESTRICT ON DROP

Indicates that the table cannot be dropped. Also, the database and table space that contain the table cannot be dropped.

If a table space is named, it must not be one that was created implicitly, be a partitioned table space that already contains a table, or be a LOB table space. If you name a partitioned table space, you cannot load or use the table until its partitioned index is created.

CCSID

The CCSID clause is only syntactically supported for EBCDIC and ASCII. This is because the definition of the XDB location itself defines how character data is stored.

If you specify UNICODE, your character and graphic data is stored as UNICODE within the table.

VOLATILE or NOT VOLATILE

Specifies how DB2 is to choose access to the table.

VOLATILE

Specifies that index access should be used on this table whenever possible for SQL operations. However, be aware that list prefetch and certain other optimization techniques are disabled when VOLATILE is used.

One instance in which use of VOLATILE may be desired is for a table whose size can vary greatly. If statistics are taken when the table is empty or has only a few rows, those statistics might not be appropriate when the table has many rows. Another instance in which use of VOLATILE may be desired is for a table that contains groups of rows, as defined by the primary key on the table. All but the last column of the primary key of such a table indicate the group to which a given row belongs. The last column of the primary key is the sequence number indicating the order in which the rows are to be read from the group. VOLATILE maximizes concurrency of operations on rows within each group, since rows are usually accessed in the same order for each operation.

NOT VOLATILE

Specifies that SQL access to this table should be based on the current statistics. NOT VOLATILE is the default.

CARDINALITY

An optional keyword that currently has no effect, but that is provided for DB2 family compatibility.

materialized-query-definition

Specifies that the column definitions are based on the result of a fullselect. If materialized-query-table-options are specified, the table is a materialized query table and the REFRESH TABLE statement can be used to populate the table with the results of the fullselect.

column-name

Names the columns in the table. If a list of column names is specified, it must consist of as many names as there are columns in the result table of the fullselect. Each column-name must be unique and unqualified. If a list of column names is not specified, the columns of the table inherit the names of the columns of the result table of the fullselect.

A list of column names must be specified if the result table of the fullselect has duplicate column names or an unnamed column. An unnamed column is a column derived from a constant, function, expression, or set operation that is not named using the AS clause of the select list.

AS(fullselect)

Specifies that the table definition is based on the column definitions from the result of a query expression. The use of AS (fullselect) is an implicit definition of n columns for the table, where n is the number of columns that would result from the fullselect. The columns of the new table are defined by the columns that result from the fullselect. Every select list element must have a unique name. The AS clause can be used in the select-clause to provide unique names. The implicit definition includes the column name, data type, and nullability characteristic of each of the result columns of fullselect.

If the fullselect is specified, for the statement that is embedded or prepared dynamically, the owner of the table being created must have the SELECT privilege on the tables or views referenced in the fullselect, or the privilege set must include SYSADM or DBADM authority for the database in which the tables of the fullselect reside. Having SELECT privilege means that the owner has at least one of the following authorizations:

  • Ownership of the tables or views referenced in the fullselect
  • The SELECT privilege on the tables and views referenced in the fullselect
  • SYSADM authority
  • DBADM authority for the database in which the tables of the fullselect reside

The rules for establishing the qualifiers for names used in the fullselect are the same as the rules used to establish the qualifiers for table-name. The fullselect must not refer to host variables or include parameter markers.

When WITH NO DATA is specified, the fullselect must not:

  • Reference a remote object.
  • Result in a column having a ROWID data type or a distinct type based on ROWID.
  • Result in a column having a BLOB, CLOB, or DBCLOB data type or a distinct type based on these data types.
  • Contain PREVIOUS VALUE or a NEXT VALUE expression.
  • Include an INSERT statement in the FROM clause. When a materialized query table is defined, the column attributes, such as DEFAULT and IDENTITY, are not inherited from the fullselect.

When DISABLE QUERY OPTIMIZATION is specified, the following additional restrictions apply:

  • The fullselect cannot contain a reference to a created global temporary table or a declared global temporary table.
  • The fullselect cannot reference another materialized query table.

When a materialized query table is defined with ENABLE QUERY OPTIMIZATION specified, the following additional restrictions apply:

  • The fullselect must be a subselect.
  • The subselect cannot include a function that is nondeterministic or has external actions. For example, a user-defined function that is defined with either EXTERNAL ACTION or NOT DETERMINISTIC or the RAND built-in function cannot be referenced.
  • The subselect cannot contain any predicates that include subqueries.
  • The subselect cannot contain:
    • A nested table expression or view that requires temporary materialization
    • A join using the INNER JOIN syntax
    • An outer join
    • A special register
    • A scalar fullselect
    • A row expression predicate
    • Sideway references
  • The outermost SELECT list of the subselect must not reference data that is encoded with different CCSID sets.
  • If the subselect references a view, the fullselect in the view definition must satisfy the preceding restrictions.

When fullselect does not satisfy the restrictions, an error occurs.

WITH NO DATA

Specifies that the query is used only to define the attributes of the new a table. The table is not populated using the results of the query and the REFRESH TABLE statement cannot be used. When the WITH NO DATA clause is specified, the table is not considered a materialized query table.

The columns of the table are defined based on the definitions of the columns that result from the fullselect.

copy-options

Specifies whether identity column attributes and column defaults are inherited from the definition of the source of the result table.

EXCLUDING IDENTITY COLUMN ATTRIBUTES or INCLUDING IDENTITY COLUMN ATTRIBUTES

Specifies whether identity column attributes are inherited.

EXCLUDING IDENTITY COLUMN ATTRIBUTES

Specifies that identity column attributes are not inherited from the definition of the source of the result table. This is the default.

INCLUDING IDENTITY COLUMN ATTRIBUTES

Specifies that, if available, identity column attributes (such as START WITH, INCREMENT BY, and CACHE values) are inherited from the definition of the source table. These attributes can be inherited if the element of the corresponding column in the table, view, or fullselect is the name of a column of a table or the name of a column of a view that directly or indirectly maps to the column name of a base table with the identity attribute. In other cases, the columns of the new temporary table do not inherit the identity attributes. The columns of the new table do not inherit the identity attributes in the following cases:

  • The select list of the fullselect includes multiple instances of an identity column name (that is, selecting the same column more than once).
  • The select list of the fullselect includes multiple identity columns (that is, it involves a join).
  • The identity column is included in an expression in the select list.
  • The fullselect includes a set operation (union).
EXCLUDING COLUMN DEFAULTS, INCLUDING COLUMN DEFAULTS, or USING TYPE DEFAULTS

Specifies whether column defaults are inherited.

EXCLUDING COLUMN DEFAULTS

Specifies that the column defaults are not inherited from the definition of the source table. The default values of the column of the new table are either null or there are no default values. If the column can be null, the default is the null value. If the column cannot be null, there is no default value, and an error occurs if a value is not provided for a column on INSERT for the new table.

INCLUDING COLUMN DEFAULTS

Specifies that column defaults for each updatable column of the definition of the source table are inherited. Columns that are not updatable do not have a default defined in the corresponding column of the created table.

USING TYPE DEFAULTS

Specifies that the default values for the table depend on data type of the columns that result from fullselect, as follows:

Data type Default value
Numeric 0
Fixed-length string Blanks
Varying-length string A string length of 0
Date CURRENT DATE
Time CURRENT TIME
Timestamp CURRENT TIMESTAMP
refreshable-table-options

Specifies the options for a refreshable materialized query table.

DATA INITIALLY DEFERRED

Specifies that the data is not inserted into the materialized query table when it is created. Use the REFRESH TABLE statement to populate the materialized query table, or use the INSERT statement to insert data into a user-maintained materialized query table.

REFRESH DEFERRED

Specifies that the data in the table can be refreshed at any time using the REFRESH TABLE statement. The data in the table only reflects the result of the query as a snapshot at the time when the REFRESH TABLE statement is processed or when it was last updated for a user-maintained materialized query table.

MAINTAINED BY SYSTEM or MAINTAINED BY USER

Specifies how the data in the materialized query table is maintained.

MAINTAINED BY SYSTEM

Specifies that the materialized query table is maintained by the system. Only the REFRESH statement is allowed on the table. This is the default.

MAINTAINED BY USER

Specifies that the materialized query table is maintained by the user, who can use the LOAD utility or the INSERT, DELETE, UPDATE, SELECT FOR UPDATE, or REFRESH TABLE statements on the table.

ENABLE QUERY OPTIMIZATION or DISABLE QUERY OPTIMIZATION

Specifies whether this materialized query table can be used for optimization.

ENABLE QUERY OPTIMIZATION

Specifies that the materialized query table can be used for query optimization. If the fullselect specified does not satisfy the restrictions for query optimization, an error occurs. This is the default.

DISABLE QUERY OPTIMIZATION

Specifies that the materialized query table cannot be used for query optimization. The table can still be queried directly.

CONSTRAINT

Names a table check constraint. If a constraint-name is not specified, a unique constraint name is derived from the name of the first column in the check-condition specified in the definition of the table check constraint.

CHECK

Table check constraints can be defined on individual columns within the CREATE TABLE command. The check constraint is the check condition that defines the values that designated columns of the table can contain, with the following restrictions:

  • It can refer only to columns of table table-name; however, the columns cannot be LOB or ROWID columns.
  • It can be up to 3800 bytes long, not including redundant blanks.

It must not contain any of the following:

  • Subselects
  • Built-in or user-defined functions
  • Cast functions other than those created when the distinct type was created
  • Host variables
  • Parameter markers
  • Special registers
  • Columns that include a field procedure
  • CASE Expressions
  • Quantified predicates
  • EXISTS predicates
  • Row expressions
  • DISTINCT predicates
  • GX literals (hexadecimal graphic string constants)

For example, the CREATE TABLE command below builds the EMPLOYEE table with the following constraints:

  • Employee names must be a value other than NULL (this constraint is equivalent to specifying NOT NULL).
  • The Department number must be 1050.
  • The commission rate must be less than or equal to six percent.
CREATE TABLE EMPLOYEE
(
e_no         SMALLINT
lname        CHAR 10              CHECK (lname IS NOT NULL),
fname        CHAR 10              CHECK (fname IS NOT NULL),
street       CHAR 20,
city         CHAR 15,
st           CHAR 2,
zip          CHAR 5,
dept         CHAR 4               CHECK (dept='1050'),
payrate      MONEY,
com          DECIMAL(2,2),
CONSTRAINT   com CHECK (com <= 06)
)

A table check constraint is not enforced on the default value of a column when the constraint is defined. A table check constraint is enforced on the default value of a column when rows are inserted or updated.

The syntax of a table check constraint is checked when the constraint is defined but the meaning is not checked. A table check constraint is not checked for consistency with other types of constraints.

Check Constraint Storage

Table check information is stored in two new catalog tables:

  • SYSIBM.SYSCHECKS

    Contains one row for each table check constraint defined on a table.

  • SYSIBM.SYSCHECKDEP

    Contains a row for each reference to a column in a table check constraint.

Enforcing Check Constraints

After table check constraints are defined on a table, any change must satisfy those constraints if it is made by an INSERT or UPDATE statement. A row satisfies a check constraint if its condition evaluates either to true or to unknown. A condition can evaluate to unknown for a row if one of the columns named contains the null value for that row. For INSERT or UPDATE statements, table check constraints are enforced on views and their underlying tables.

CURRENT RULES Special Register

For populated tables, the enforcement of a table check constraint is determined by the value, STD or DB2, of the CURRENT RULES special register.

  • If the value is STD, a check constraint is enforced immediately when it is defined. If a row does not conform, the table check constraint is not added to the table and an error occurs.
  • If the value is DB2, the check constraint is added to the table description but its enforcement is deferred. Because there might be rows in the table that violate the check constraint, the table is placed in check pending status.
DEFAULT

You can define a table column that has a default value other than null. When a row is inserted and no value is specified for the column, the default you defined is assigned to the column.

The default value depends on the data type of the column. The DEFAULT clause allows you to specify a default value in one of the following forms:

  • A constant
  • The execution time value of the USER special register
  • The SQL authorization ID (SQLID) of the process
  • The null value
NOT NULL clause

In a given column definition neither NOT NULL nor DEFAULT can be specified more than once. Both NOT NULL and DEFAULT cannot be specified. Omitting NOT NULL and DEFAULT implies that DEFAULT NULL is intended.

For example, if you want to record the identity of anyone inserting rows into a table, define the table with two additional columns:

PRIMARY_ID CHAR(8) WITH DEFAULT USER,
SQL_ID CHAR(8) WITH DEFAULT CURRENT SQLID,

You can allow updates and inserts to the table only through a view that omits those columns. Then, the primary authorization ID and the SQLID of the process are added by default.

Examples

Constraint Examples

The following shows how referential integrity can be used to enforce common business rules by defining relationships between the CUSTOMER, ORDERS, PRODUCTS, ITEMS, and EMPLOYEE tables in the TUTORIAL location.

All of the tables named already exist in the TUTORIAL location. However, if the tables did not already exist (or if you create them in another location), you could specify the referential integrity constraints at the same time you created the table.

The order in which the commands are executed is important. You cannot create a table that references another table until the referenced table is created. For example, the CUSTOMER table must be created before the ORDERS table.

Business Rule 1
An order can be placed for a customer only if complete customer information is available.

Database Relationships

Before inserting or updating into the ORDERS table, check for matching C_NO values in the CUSTOMER table. Do not delete CUSTOMER table record if orders for customer exist in the ORDERS table.
Primary key: customer(c_no)
Foreign key: orders(o_no)
Delete rule: RESTRICT
Business Rule 2
Orders must be taken by an employee of the company. If a salesperson for an order is unknown, the order is treated as a house order.

Database Relationships

Before updating or inserting into the ORDERS table, check whether S_NO value has matching E_NO in the EMPLOYEE table. Before deleting EMPLOYEE table records, set S_NO of corresponding ORDERS table records to null.
Primary key: employee(e_no)
Foreign key: orders(s_no)
Delete rule: SET NULL
Business Rule 3
A request for an item must always be associated with a specific order.

Database Relationships

Before inserting or updating the ITEMS table, check whether O_NO columns have matching O_NO values in the ORDERS table. If ORDERS record is deleted, delete matching ITEMS table records.
Primary key: orders(o_no)
Foreign key: items(o_no)
Delete rule: CASCADE
Business Rule 4
Only items that are carried can be requested on an order.

Database Relationships

Before inserting or updating records in ITEMS, make sure P_NO matches a P_NO in the PRODUCTS table. Prevent deletion of a PRODUCTS table record if dependent ITEMS table records exist.
Primary key: products(p_no)
Foreign key: items(p_no)
Delete rule: RESTRICT
Batch SQL Example
As presented, these commands could be run through interactive or batch SQL (with SQLWizard).
CREATE TABLE customer (c_no SMALLINT NOT NULL, 
    company CHAR(25), address CHAR(20), city CHAR(15), 
    state CHAR(2), zip CHAR(5), phone CHAR(14), 
    balance MONEY,
PRIMARY KEY (c_no));
CREATE UNIQUE INDEX custpkey ON customer(c_no)
CREATE TABLE employee (e_no SMALLINT NOT NULL, 
    lname CHAR(10), fname CHAR(10), street CHAR(20), 
    city CHAR(15), st CHAR(2), zip CHAR(5), dept CHAR(4), 
    payrate MONEY, com DECIMAL(2,2),PRIMARY KEY (e_no));
CREATE UNIQUE INDEX emppkey ON employee(e_no)
CREATE TABLE products (p_no SMALLINT NOT NULL, 
    description CHAR(18), price MONEY,PRIMARY KEY (p_no));
CREATE UNIQUE INDEX prodpkey ON products(p_no)
CREATE TABLE orders (o_no SMALLINT NOT NULL, 
    c_no SMALLINT, o_date DATE, s_no SMALLINT,
    PRIMARY KEY (o_no), 
    FOREIGN KEY bad_cust (c_no) 
        REFERENCES customer ON DELETE RESTRICT,
    FOREIGN KEY bad_emp (s_no) 
        REFERENCES employee ON DELETE SET NULL);
CREATE UNIQUE INDEX ordpkey ON orders(o_no)
CREATE TABLE items (o_no SMALLINT, p_no SMALLINT, 
    quantity SMALLINT, price MONEY, 
    FOREIGN KEY bad_ord(o_no) 
        REFERENCES orders ON DELETE CASCADE,
    FOREIGN KEY bad_prod(p_no) 
        REFERENCES products ON DELETE CASCADE);

If the tables already exist you can specify the referential constraints using the ALTER TABLE command. This simply requires that you append the appropriate PRIMARY KEY and FOREIGN KEY clauses onto an ALTER TABLE table-name clause. The CREATE INDEX commands would remain the same, but should be issued before the ALTER TABLE ... PRIMARY KEY commands.

Examining the System Catalog

Creating, dropping or altering a table causes the system to automatically update the sysibm.systables and sysibm.syscolumns tables. You can query the system catalog tables for table, view, column and index information. The query results can be formatted into your own reports. See XDB Server System Tables for a description of the system catalog tables.

Optionally, you can use SQLWizard to examine table definitions interactively. In addition, the Catalog Browser function of SQLWizard lists table, view and index definitions.

If a column is indexed, the name(s) of the index or indexes are listed after the column name, separated by a slash (/). Index type indicator codes (see the following table) appear in parentheses after the index name.

Next, the data type of the column is listed. The NOT NULL or NOT NULL WITH DEFAULT clauses specifies the null permission of the column. If omitted, nulls are permitted in the column.
Indicator Meaning
no letter Regular index (duplicate values permitted)
p Primary key
c Candidate (Unique) key
f Foreign key
u Unique index
no number Single column index
# Compound index; # is sequence number indicating order of column in a compound index