CREATE TABLE

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is implicitly or explicitly specified.

Authorization

The privilege set that is defined below must include at least one of the following:

• The CREATETAB privilege for the database explicitly specified by the IN clause.

  If the IN clause is not specified, the CREATETAB privilege on database DSNDB04 is required.

• DBADM, DBCTRL, or DBMAINT authority for the database explicitly specified by the IN clause. If the IN clause is not specified, DBADM, DBCTRL, or DBMAINT authority for database DSNDB04 is required.
• SYSADM or SYSCTRL authority
• System DBADM

If the table space is created implicitly, the privilege set that is defined below must include at least one of the following:

• The CREATETS privilege for the database explicitly specified by the IN clause.

  If the IN clause is not specified, the CREATETS privilege on database DSNDB04 is required.

• DBADM, DBCTRL, or DBMAINT authority for the database explicitly specified by the IN clause. If the IN clause is not specified, DBADM, DBCTRL, or DBMAINT authority for database DSNDB04 is required.
• SYSADM or SYSCTRL authority

The privilege set must also have the USE privilege for the following objects:

• For the table space if one is specified in the IN clause
• For the default buffer pool and default storage group of the database if a database is specified in the IN clause

If you specify a table space name, you must also have the SYSADM or SYSCTRL authority or the DBADM authority for the database.

For tables that are created in an implicit database, the database authority must be held on DSNDB04.

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.

See the description of the appropriate clauses for details about these privileges.

Privilege set: If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the package.

If the application is bound in a trusted context with the ROLE AS OBJECT OWNER clause specified:

• A role is the owner of the table that is being created
• The privilege set is the set of privileges that are held by that role
• The schema qualifier (implicit or explicit) must be the same as the role, unless the role has the CREATEIN privilege on the schema, or SYSADM, SYSCTRL, or system DBADM authority

Otherwise, an authorization ID is the owner of the package, and the following rules apply:

• If the privilege set lacks the CREATEIN privilege on the schema, SYSADM authority, SYSCTRL authority, and System DBADM authority, the schema qualifier (implicit or explicit) must be the same as the authorization ID of the owner of the package.
• If the privilege set lacks SYSADM authority, SYSCTRL authority, and System DBADM authority, and the table is explicitly qualified, the authorization ID that is the same as the schema name must have all the necessary privileges to create the table, and that authorization ID is the owner of the table. Otherwise, the authorization ID of the owner of the package must have all the necessary privileges to create the table, and that authorization ID is the owner of the table.
• If the privilege set includes SYSADM authority, SYSCTRL authority, or system DBADM authority, the schema qualifier (implicit or explicit) can be any schema name. However, if the table is explicitly qualified, the authorization ID that is the same as the schema name is the owner of the table. Otherwise, the authorization ID of the owner of the package is the owner of the table.
• If the privilege set includes DBADM authority and DBCTRL authority for the database, the schema qualifier (implicit or explicit) can be any schema name. However, if the table is explicitly qualified, the authorization ID that is the same as the schema name is the owner of the table. Otherwise, the authorization ID of the owner of the package is the owner of the table.

If the statement is dynamically prepared, the privilege set is the privileges that are held by the SQL authorization ID of the process unless the process is within a trusted context and the ROLE AS OBJECT OWNER clause is in effect. When ROLE AS OBJECT OWNER is in effect, the privileges set is the privileges that are held by the role that is associated with the primary authorization ID of the process, and the owner of the table is that role. The schema qualifier (implicit or explicit) must be the same as that role, unless the role has CREATEIN privilege on the schema, or SYSADM authority, SYSCTRL authority, or System DBADM authority.

For the case where the SQL authorization ID of the process holds the privileges, the following rules apply:

• If the privilege set lacks CREATEIN privilege on the schema, SYSADM authority, SYSCTRL authority, and System DBADM authority, the schema qualifier must be the same as one of the authorization IDs of the process.
• If the privilege set lacks SYSADM authority, SYSCTRL authority, and System DBADM authority, and the table is explicitly qualified, then the authorization ID that is the same as the schema name must have all the necessary privileges to create the table, and that authorization ID is the owner of the table. Otherwise, the SQL authorization ID of the process must include all privileges that are needed to create the table, and that authorization ID is the owner of the table.
• If the privilege set includes SYSADM authority, SYSCTRL authority, or System DBADM authority, the schema qualifier can be any schema name. However, if the table is explicitly qualified, then the authorization ID that is the same as the schema name is the owner of the table. Otherwise, the SQL authorization ID of the process is the owner of the table.

Description

table-name

Names the table. The name, including the implicit or explicit qualifier, must not identify a table, view, alias, or synonym that exists at the current server or a table that exists in the SYSIBM.SYSPENDINGOBJECTS catalog table. The unqualified name must not be the same as an existing synonym.

If the name is qualified, the name can be a two-part or three-part name. If a three-part name is used, the first part must match the value of field DB2®LOCATION NAME on installation panel DSNTIPR at the current server. (If the current server is not the local DB2, this name is not necessarily the name in the CURRENT SERVER special register.)

column-definition

column-name

Names a column of the table. For a dependent table, up to 749 columns can be named. For a table that is not a dependent, this number is 750. Do not qualify column-name and do not use the same name for more than one column of the table.

built-in-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. For more information about defining a table with a LOB column (CLOB, BLOB, or DBCLOB), see Creating a table with LOB columns.

If IN ACCELERATOR is specified, not all data types are supported. For example, DECFLOAT, LOB, ROWID, TIMESTAMP WITH TIME ZONE, and XML are not supported. The IBM DB2 Analytics Accelerator for z/OS: Stored Procedures Reference has a complete list of supported data types.

SMALLINT

For a small integer.

INTEGER or INT

For a large integer.

BIGINT

For a big integer.

DECIMAL(integer,integer) or DEC(integer,integer)

DECIMAL(integer) or DEC(integer)

DECIMAL or DEC

For a decimal number. The first integer is the precision of the number. That is, the total number of digits, which can range from 1 to 31. The second integer is the scale of the number. That is, the number of digits to the right of the decimal point, which can range from 0 to the precision of the number.

You can use DECIMAL(p) for DECIMAL(p,0) and DECIMAL for DECIMAL(5,0).

You can also use the word NUMERIC instead of DECIMAL. For example, NUMERIC(8) is equivalent to DECIMAL(8). Unlike DECIMAL, NUMERIC has no allowable abbreviation.

DECFLOAT( integer)

For a decimal floating-point number. The value of integer must be either 16 or 34 and represents the number of significant digits that can be stored. If integer is omitted, the DECFLOAT column will be capable of representing 34 significant digits.

FLOAT(integer)

FLOAT

For a floating-point number. If integer is between 1 and 21 inclusive, the format is single precision floating-point. If the integer is between 22 and 53 inclusive, the format is double precision floating-point.

You can use DOUBLE PRECISION or FLOAT for FLOAT(53).

REAL

For single precision floating-point.

DOUBLE or DOUBLE PRECISION

For double precision floating-point

CHARACTER(integer) or CHAR(integer)

CHARACTER or CHAR

For a fixed-length character string of length integer, which can range from 1 to 255. If the length specification is omitted, a length of 1 character is assumed.

VARCHAR(integer), CHAR VARYING(integer), or CHARACTER VARYING(integer)

For a varying-length character string of maximum length integer, which can range from 1 to the maximum record size minus 10 bytes. See Table 3 to determine the maximum record size.

FOR subtype DATA

Specifies a subtype for a character string column, which is a column with a data type of CHAR, VARCHAR, or CLOB. Do not use the FOR subtype 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 unless the CCSID UNICODE clause is also specified, or the table is being created in a Unicode table space or database.

BIT

Column holds BIT data. Do not specify BIT for a CLOB column.

Only character strings are valid when subtype is BIT.

If you do not specify the FOR clause, the column is defined with a default subtype. For ASCII or EBCDIC data:

• 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.

For Unicode data, the default subtype is MIXED.

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

CLOB(integer [K|M|G]), CHAR LARGE OBJECT(integer [K|M|G]), or CHARACTER LARGE OBJECT(integer [K|M|G])

CLOB, CHAR LARGE OBJECT, or CHARACTER LARGE OBJECT

For a character large object (CLOB) string of the specified maximum length in bytes. The maximum length must be in the range of 1 to 2 147 483 647. A CLOB column has a varying-length. It cannot be referenced in certain contexts regardless of its maximum length. For more information, see Restrictions using LOBs.

When integer is not specified, the default length is 1M. The maximum value that can be specified for integer depends on whether a units indicator is also specified as shown in the following list.

integer

The maximum value for integer is 2 147 483 647. The maximum length of the string is integer.

integer K

The maximum value for integer is 2 097 152. The maximum length is 1024 times integer.

integer M

The maximum value for integer is 2048. The maximum length is 1 048 576 times integer.

integer G

The maximum value for integer is 2. The maximum length is 1 073 741 824 times integer.

If you specify a value that evaluates to 2 gigabytes (2 147 483 648), DB2 uses a value that is one byte less, or 2 147 483 647.

GRAPHIC(integer)

GRAPHIC

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.

DBCLOB(integer [K|M|G])

DBCLOB

For a double-byte character large object (DBCLOB) string of the specified maximum length in double-byte characters. The maximum length must be in the range of 1 through 1 073 741 823. A DBCLOB column has a varying-length. It cannot be referenced in certain contexts regardless of its maximum length. For more information, see Restrictions using LOBs.

When integer is not specified, the default length is 1M. The meaning of integer K|M|G is similar to CLOB. The difference is that the number specified is the number of double-byte characters.

BINARY(integer)

A fixed-length binary string of length integer. The integer can range from 1 through 255. If the length specification is omitted, a length of 1 byte is assumed.

BINARY VARYING(integer) or VARBINARY(integer)

A varying-length binary string of maximum length integer, which can range from 1 through 32704. The length is limited by the page size of the table space.

BLOB (integer [K|M|G] or BINARY LARGE OBJECT(integer [K|M|G])

BLOB or BINARY LARGE OBJECT

For a binary large object (BLOB) string of the specified maximum length in bytes. The maximum length must be in the range of 1 through 2 147 483 647. A BLOB column has a varying-length. It cannot be referenced in certain contexts regardless of its maximum length. For more information, see Restrictions using LOBs.

When integer is not specified, the default length is 1M. The meaning of integer K|M|G is the same as for CLOB.

DATE

For a date.

TIME

For a time.

TIMESTAMP(integer) WITHOUT TIME ZONE

For a timestamp. integer specifies the optional timestamp precision attribute and must be in the range from 0 to 12. The timestamp precision denotes the number of fractional second digits that are included in the timestamp. The default is 6.

TIMESTAMP(integer) WITH TIME ZONE

For a timestamp with time zone. integer specifies the optional timestamp precision attribute and must be in the range from 0 to 12. The timestamp precision denotes the number of fractional second digits that are included in the timestamp. The default is 6.

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.

XML

For an XML document. Only well-formed XML documents can be inserted into an XML column.

If the XML column is the first XML column that you create for the table, a BIGINT DOCID column is implicitly created and is used to store a unique document identifier for the XML columns of a row.

XMLSCHEMA

Specifies one or more XML schemas that are used to validate the XML value. The same XML schema can not be specified more than one time.

If the XML value has already been validated, for example, the XML value is the result of the DSN_XMLVALIDATE function or from an XML column with a type modifier, and the XML schema against which the XML value is validated is one of the schemas specified in the XML-type-modifier, DB2 accepts the XML value without revalidation.

XML-schema-specification

Specifies one XML schema. The XML schema can be identified by using either the registered XML-schema-name or the schema's target namespace followed by an optional schema location. Any XML schema that is referenced in this clause must be registered in the XML schema repository prior to use.

ID registered-XML-schema-name

Identifies an XML schema by using its registered-XML-schema-name. The name must uniquely identify an existing XML schema in the XML schema repository at the current server. If no XML schema by this name exists, an error is returned.

The schema qualifier must be SYSXSR.

URI target-namespace

Specifies the target namespace URI of the XML schema. The value for the target-namespace URI is a character string constant which is not empty. The URI must be the target namespace of a registered XML schema and, if no LOCATION clause is specified, it must uniquely identify the registered XML schema.

NO NAMESPACE

Specifies that the XML schema has no target namespace. There must be a registered XML schema that has no target namespace. If no LOCATION clause is specified, there must be only one such registered XML schema.

LOCATION schema-location

Specifies the XML schema location URI of the XML schema. The value of schema-location is a character string constant that is not empty. The schema location URI, combined with the target namespace URI, must identify a registered XML schema.

ELEMENT element-name

Specifies the name of the global element declaration. element-name must match the local name of the root element node in the instance XML document. The namespace name of the root element node must be the same as the target namespace URI.

distinct-type-name

Specifies the data type of the column is a distinct type (a user-defined data type). The length, precision, and scale of the column are respectively the length, precision, and scale of the source type of the distinct type. The privilege set must implicitly or explicitly include the USAGE privilege on the distinct type.

The encoding scheme of the distinct type must be the same as the encoding scheme of the table. The subtype for the distinct type, if it has the attribute, is the subtype with which the distinct type was created.

If the column is to be used in the definition of the foreign key of a referential constraint, the data type of the corresponding column of the parent key must have the same distinct type.

NOT NULL

Prevents the column from containing null values. Omission of NOT NULL implies that the column can contain null values.

column-constraint

The column-constraint of a column-definition provides a shorthand method of defining a constraint composed of a single column. Thus, if a column-constraint is specified in the definition of column C, the effect is the same as if that constraint were specified as a unique-constraint, referential-constraint, or check-constraint in which C is the only identified column.

CONSTRAINT constraint-name

Names the constraint. If a constraint name is not specified, a unique constraint name is generated. If the name is specified, it must be different from the names of any referential, check, primary key, or unique key constraints previously specified on the table.

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. PRIMARY KEY cannot be specified more than one time in a column definition, and must not be specified if the UNIQUE clause is specified in the definition. This clause must also not be specified if the definition is for one of the following types of columns:

• a LOB column
• a ROWID column
• a DECFLOAT column
• a distinct type column that is based on a LOB, ROWID, or DECFLOAT data type
• an XML column
• a row change timestamp column
• a column in an accelerator-only table

The table is marked as unavailable until its primary index is explicitly created unless the CREATE TABLE statement is processed by the schema processor or the table space that contains the table is implicitly created. In that case, DB2 implicitly creates an index to enforce the uniqueness of the primary key and the table definition is considered complete. (For more information about implicitly created indexes, see Implicitly created indexes.)

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 one time 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 one of the following types of columns:

• a LOB column
• a ROWID column
• a DECFLOAT column
• a distinct type column that is based on a LOB, ROWID, or DECFLOAT data type
• an XML column
• a row change timestamp column
• a column in an accelerator-only table

The table is marked as unavailable until all the required indexes are explicitly created unless the CREATE TABLE statement is processed by the schema processor or the table space that contains the table is implicitly created. In that case, DB2 implicitly creates the indexes that are required for the unique keys and the table definition is considered complete. (For more information about implicitly created indexes, see Implicitly created indexes.)

references-clause

The references-clause of a column-definition provides a shorthand method of defining a foreign key composed of a single column. Thus, if references-clause is specified in the definition of column C, the effect is the same as if the references-clause were specified as part of a FOREIGN KEY clause in which C is the only identified column.

Do not specify references-clause in the definition of the following types of columns because these types of columns cannot be a foreign key:

• a LOB column
• a ROWID column
• a DECFLOAT column
• a distinct type column that is based on a LOB, ROWID, or DECFLOAT data type
• an XML column
• a row change timestamp column
• a security label column

CHECK (check-condition)

CHECK (check-condition) provides a shorthand method of defining a check constraint that applies to a single column. 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.

DEFAULT

Specifies the default value that is assigned to the column in the absence of a value specified on an insert or update operation or LOAD. DEFAULT must not be specified more than one time in the same column-definition. Do not specify DEFAULT for the following types of columns because DB2 generates default values:

• An identity column (a column that is defined AS IDENTITY)
• A ROWID column (or a distinct type that is based on a ROWID)
• A row change timestamp column
• A row-begin column
• A row-end column
• A transaction-start-id column
• An XML column

If IN ACCELERATOR is specified, do not specify DEFAULT for a column.

Do not specify a value after the DEFAULT keyword for a security label column. DB2 provides the default value for a security label column.

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

Big integer

0

Fixed-length character string

Blanks

Fixed-length graphic string

Blanks

Fixed-length binary string

Hexadecimal zeros

Varying-length string

A string of length 0

Inline BLOB

Hexadecimal zeros

Inline CLOB

Blanks

Inline DBCLOB

Blanks

Date

CURRENT DATE

Time

CURRENT TIME

TIMESTAMP(integer) WITHOUT TIME ZONE

CURRENT TIMESTAMP(p) WITHOUT TIME ZONE where p is the corresponding timestamp precision.

TIMESTAMP(integer) WITH TIME ZONE

CURRENT TIMESTAMP(p) WITH TIME ZONE where p is the corresponding timestamp precision.

If the column is defined as timestamp with time zone the default value must include a time zone.

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:

• WITH DEFAULT for a default value of an empty string
• DEFAULT NULL for a default value of null

Omission of NOT NULL and DEFAULT from a column-definition, for a column other than an identity column, is an implicit specification of DEFAULT NULL. For an identity column, it is an implicit specification of NOT NULL, and DB2 generates default values.

constant

Specifies a constant as the default value for the column. The value of the constant must conform to the rules for assigning that value to the column.

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

In addition, the length of the constant value cannot be greater than the INLINE LENGTH attribute for LOB columns.

SESSION_USER or USER

Specifies the value of the SESSION_USER (USER) special register at the time of an SQL data change statement or LOAD as the default value for the column. If SESSION_USER is specified, the data type of the column must be a character string with a length attribute greater than or equal to 8 characters when the value is expressed in CCSID 37.If the data type of the column is an inline CLOB, the INLINE LENGTH attribute must be greater than or equal to 8 characters when the value is expressed as CCSID 37.

CURRENT SQLID

Specifies the value of the SQL authorization ID of the process at the time of an insert or update operation or LOAD as the default value for the column. If CURRENT SQLID is specified, the data type of the column must be a character string with a length attribute greater than or equal to the length attribute of the CURRENT SQLID special register.If the data type of the column is an inline CLOB, the INLINE LENGTH attribute must be greater than or equal to the length attribute of the CURRENT SQLID special register.

NULL

Specifies null as the default value for the column. If NOT NULL is specified, DEFAULT NULL must not be specified with the same column-definition.

cast-function-name

The name of the cast function that matches the name of the distinct type for the column. A cast function can only be specified if the data type of the column is a distinct type.

The schema name of the cast function, whether it is explicitly specified or implicitly resolved through function resolution, must be the same as the explicitly or implicitly specified schema name of the distinct type.

constant

Specifies a constant as the argument. The constant must conform to the rules of a constant for the source type of the distinct type.The length of the constant cannot be greater than the INLINE LENGTH attribute for LOB columns.

SESSION_USER or USER

Specifies the value of the SESSION_USER (USER) special register at the time a row is inserted as the default for the column. The source type of the distinct type of the column must be a CHAR, VARCHAR, or inline CLOB with a length attribute (inline length attribute for CLOB) that is greater than or equal to the length attribute of the SESSION_USER special register.

CURRENT SQLID

Specifies the value of the CURRENT SQLID special register at the time a row is inserted as the default for the column. The source type of the distinct type of the column must be a CHAR, VARCHAR, or inline CLOB with a length attribute (or inline length attribute for CLOB) that is greater than or equal to the length attribute of the CURRENT SQLID special register.

NULL

Specifies the NULL value as the argument.

In a given column definition:

• DEFAULT and FIELDPROC cannot both be specified.
• NOT NULL and DEFAULT NULL cannot both be specified.

Table 1 summarizes the effect of specifying the various combinations of the NOT NULL and DEFAULT clauses on the CREATE TABLE statement column-description clause.

Table 1. Effect of specifying combinations of the NOT NULL and DEFAULT clauses

If NOT NULL is:	And DEFAULT is:	The effect is:
Specified1	Omitted	An error occurs if a value is not provided for the column on an insert or update operation or LOAD.
	Specified without an operand	The system defined nonnull default value is used.
	constant	The specified constant is used as the default value.
	SESSION_USER	The value of the SESSION_USER special register at the time of an insert or update operation or LOAD is used as the default value.
	CURRENT SQLID	The SQL authorization ID of the process at the time of an insert or update operation or LOAD is used as the default value.
	NULL	An error occurs during the execution of CREATE TABLE.
Omitted	Omitted	Equivalent to an implicit specification of DEFAULT NULL.
	Specified without an operand	The system defined nonnull default value is used.
	constant	The specified constant is used as the default value.
	SESSION_USER	The value of the SESSION_USER special register at execution time is used as the default value.
	CURRENT SQLID	The SQL authorization ID of the process is used as the default value.
	NULL	Null is used as the default value.
Note: The table does not apply to a column with a ROWID data type or to an identity column.

GENERATED

Specifies that DB2 generates values for the column. GENERATED must be specified if the column is to be considered one of the following types of columns:

• An identity column
• A row change timestamp column.
• A ROWID column
• A row-begin column
• A row-end column
• A transaction-start-id column

GENERATED must only be specified for these types of columns. GENERATED must not be specified with default-clause in a column definition.

ALWAYS

Specifies that DB2 always generates a value for the column when a row is inserted or updated and a default value must be generated. ALWAYS is the default and recommended value.

BY DEFAULT

Specifies that DB2 will generate a value for the column when a row is inserted or updated and a default value must be generated, unless an explicit value is specified.

For a row change timestamp column, DB2 inserts or updates a specified value but does not verify that the value is unique for the column unless the row change timestamp column has a unique constraint or a unique index that specifies only the row change timestamp column.

For a ROWID column, DB2 uses a specified value only if it is a valid row ID value that was previously generated by DB2 and the column has a unique, single-column index. Until this index is created on the ROWID column, the SQL insert or update operation and the LOAD utility cannot be used to add rows to the table. If the table space is explicitly created and the value of the CURRENT RULES special register is 'STD' when the CREATE TABLE statement is processed, or if the table space is implicitly created, DB2 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, DB2 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, DB2 inserts a specified value but does not verify that it a unique value for the column unless the identity column has a unique, single-column index.

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

FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP

Specifies that the column is a timestamp column for the table. DB2 generates a value for the column for each row as the row is inserted, and for any row in which any column is updated. The value that is generated for a row change timestamp column is a timestamp that corresponds to the insert or update time of the row. If multiple rows are inserted or updated with a single statement, the value for the row change timestamp column might be different for each row.

A table can only have one row change timestamp column.

If data-type is specified, it must be TIMESTAMP WITHOUT TIME ZONE with a precision of 6.

A row change timestamp column cannot have a DEFAULT clause. NOT NULL must be specified for a row change timestamp column.

AS TRANSACTION START ID

Specifies that the value is assigned by DB2 whenever a row is inserted into the table or any column in the row is updated. DB2 assigns a unique timestamp value per transaction or the null value. The null value is assigned to the transaction-start-ID column if the column is nullable. Otherwise, the value is generated using the time-of-day clock during execution of the first data change statement in the transaction that requires a value to be assigned to a row-begin column or transaction-start-ID column in the table, or when a row in a system-period temporal table is deleted. If multiple rows are inserted or updated within a single SQL transaction, the values for the transaction-start-ID column are the same for all the rows and are unique from the values that are generated for the column for another transaction.

A transaction-start-ID column is required for a system-period temporal table.

A table can have only one transaction-start-ID column. If a data type is not specified, the column is defined as TIMESTAMP(12) WITHOUT TIME ZONE. If a data type is specified, it must be TIMESTAMP(12) WITHOUT TIME ZONE or TIMESTAMP(12) WITH TIME ZONE. If the column is defined as TIMESTAMP WITH TIME ZONE, the values are stored in UTC, with a time zone of +00:00. A transaction-start-ID column cannot have a DEFAULT clause. A transaction-start-ID column is not updatable.

A value for a transaction-start-ID column is composed of a TIMESTAMP(9) value that is unique per transaction per data sharing member followed by 3 digits that indicate the data sharing member number.

AS ROW BEGIN

Specifies that the column contains timestamp data and that the values are generated by DB2. DB2 generates a value for the column for each row as the row is inserted, and for every row in which any column is updated. The generated value is a timestamp that corresponds to the start time that is associated with the most recent transaction. If multiple rows are inserted with a single SQL unit of work, the values for the transaction start timestamp column are the same.

A row-begin column is intended to be used for a system-period temporal table.

For a system-period temporal table, DB2 ensures the uniqueness of the generated values for a row-begin column across transactions. If multiple rows are inserted or updated within a single SQL transaction, the values for the row-begin column are the same for all the rows and are unique from the values that are generated for the column for another transaction. A row-begin column is required as the begin column of a SYSTEM_TIME period.

A table can have only one column defined as AS ROW BEGIN. If a data type is specified, it must be TIMESTAMP(12) WITHOUT TIME ZONE or TIMESTAMP(12) WITH TIME ZONE. If the column is defined as TIMESTAMP(12) WITH TIME ZONE, the values are stored in UTC, with a time zone of +00:00. If no data type is specified, the column is defined as TIMESTAMP(12) WITHOUT TIME ZONE. A column defined as a row-begin column cannot have a DEFAULT clause, and must be defined as NOT NULL.

A row-begin column is not updatable.

A value for a row-begin column is composed of a TIMESTAMP(9) value that is unique per transaction per data sharing member followed by 3 digits that indicate the data sharing member number.

AS ROW END

Specifies that a value for the data type of the column is assigned by DB2 whenever a row is inserted or any column in the row is updated. The value that is assigned for a TIMESTAMP WITHOUT TIME ZONE column is the TIMESTAMP value '9999-12-30-00.00.00.000000000000'. The value that is assigned for a TIMESTAMP WITH TIME ZONE column is the TIMESTAMP value '9999-12-30-00.00.00.000000000000 +00:00'.

A row-end column is required as the second column of a SYSTEM_TIME period.

A table can have only one row-end column. If a data type is not specified, the column is defined as TIMESTAMP(12) WITHOUT TIME ZONE. If a data type is specified, it must be TIMESTAMP(12) WITHOUT TIME ZONE or TIMESTAMP(12) WITH TIME ZONE. If the column is defined as TIMESTAMP WITH TIME ZONE, the values are stored in UTC, with a time zone of +00:00. A row-end column cannot have a DEFAULT clause and must be defined as NOT NULL. A row-end column is not updatable.

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, BIGINT, DECIMAL with a scale of zero, or a distinct type based on one of these types).

An identity column is implicitly NOT NULL. An identity column cannot have a WITH DEFAULT clause.

Defining a column AS IDENTITY does not necessarily ensure the uniqueness of the values. To ensure uniqueness of the values, define a unique, single-column index on the identity column.

If IN ACCELERATOR is specified, AS IDENTITY must not be specified.

START WITH numeric-constant

Specifies the first value that is generated for the identity column. The value can be any positive or negative value that could be assigned to the column without non-zero digits existing to the right of the decimal point.

If a value is not explicitly specified when the identity column is defined, the default is the MINVALUE for an ascending identity column and the MAXVALUE for a descending identity column. This value is not necessarily the value that would be cycled to after reaching the maximum or minimum value for the identity column. The START WITH clause can be used to start the generation of values outside the range that is used for cycles. The range used for cycles is defined by MINVALUE and MAXVALUE.

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.

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.

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 is not specified.
• For a descending identity column, the value is the minimum value of the data type of the column.

The default is NO MINVALUE.

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.

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 associated with the column.
• For a descending identity column, the value is the START WITH value -1 if START WITH is not specified.

The default is NO MAXVALUE.

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.

CYCLE or NO CYCLE

Specifies whether this identity column should continue to generate values after reaching either its maximum or minimum value. The default is NO CYCLE.

NO CYCLE

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

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 integer-constant or NO CACHE

Specifies whether to keep some preallocated values in memory. Preallocating and storing values in the cache improves the performance of inserting rows into a table. The default is CACHE 20.

NO CACHE

Specifies that values for the identity column and sequences are not preallocated and stored in the cache, ensuring that values will not be lost in the case of a system failure. In this case, every request for a new value for the identity column or sequence results in synchronous I/O.

In a data sharing environment, use NO CACHE if you need to guarantee that the identity column and sequence values are generated in the order in which they are requested.

CACHE integer-constant

Specifies the maximum number of values of the identity column sequence that DB2 can preallocate and keep in memory.

During a DB2 shutdown, all cached identity column values and sequence values that are yet to be assigned will be lost and will not be used. Therefore, the value that is specified for CACHE also represents the maximum number of identity column values and sequence values that will be lost during a DB2 shutdown.

The minimum value is 2.

In a data sharing environment, you can use the CACHE and NO ORDER options to allow multiple DB2 members to cache sequence values simultaneously.

ORDER or NO 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 might 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 might 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.

FIELDPROC program-name

Designates program-name as the field procedure exit routine for the column. A field procedure can be specified only for a column with a length attribute that is not greater than 255 bytes. FIELDPROC can only be specified for columns that are a built-in character string or graphic string data types. The column must not be one of the following:

• a LOB column
• a security label column
• a row change timestamp column
• a column with the TIMESTAMP WITH TIME ZONE data type
• a column in an accelerator-only table

The field procedure encodes and decodes column values: before a value is inserted in the column, it is passed to the field procedure for encoding. Before a value from the column is used by a program, it is passed to the field procedure for decoding. A field procedure could be used, for example, to alter the sorting sequence of values entered in the column.

The field procedure is also invoked during the processing of the CREATE TABLE statement. When so invoked, the procedure provides DB2 with the column's field description. The field description defines the data characteristics of the encoded values. By contrast, the information you supply for the column in the CREATE TABLE statement defines the data characteristics of the decoded values.

Related information:

• Field procedures
• Character and graphic string comparisons

constant

Is a parameter that is passed to the field procedure when it is invoked. A parameter list is optional. The nth parameter specified in the FIELDPROC clause on CREATE TABLE corresponds to the nth parameter of the specified field procedure. The maximum length of the parameter list is 254 bytes, including commas but excluding insignificant blanks and the delimiting parentheses.

If you omit FIELDPROC, the column has no field procedure.

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. A security label column cannot be defined for an accelerator-only table. 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.
• The WITH DEFAULT clause must not specify a default value (DB2 determines the default value)
• No field procedures, check constraints, or referential constraints are defined on the column.
• No edit procedure for the table can be defined with row attribute sensitivity.

For information about using multilevel security, see DB2 Administration Guide.

IMPLICITLY HIDDEN

Specifies that the column is not visible in the result for SQL statements unless you explicitly refer to the column by name. For example, assuming that the table T1 includes a column that is defined with the IMPLICITLY HIDDEN clause, the result of a SELECT * would not include the implicitly hidden column. However, the result of a SELECT statement that explicitly refers to the name of the implicitly hidden column would include that column in the result table.

IMPLICITLY HIDDEN must not be specified for a column that is defined as a ROWID, or a distinct type that is based on a ROWID. IMPLICITLY HIDDEN must not be specified for all columns of a table. If IN ACCELERATOR is specified, IMPLICITLY HIDDEN must not be specified.

INLINE LENGTH integer

Specifies the maximum length for the column, if the column is a LOB column and the table is in a universal table space. INLINE LENGTH cannot be specified if the column is not a LOB column (or a distinct type that is based on a LOB), if the table is not in a universal table space, or if the table is an accelerator-only table.

For BLOB and CLOB columns, integer specifies the maximum number of bytes that are stored in the base table space for the column. integer must be between 0 and 32680 (inclusive) for a BLOB or CLOB column.

For a DBCLOB column, integer specifies the maximum number of double-byte characters that are stored in the table space for the column. integer must be between 0 and 16340 (inclusive) for a DBCLOB column.

If INLINE LENGTH is specified, the value of integer cannot be greater than the maximum length of the LOB column.

If the INLINE LENGTH clause is not specified, the maximum length of the LOB column depends on the following conditions:

• If a distinct type is not used or the distinct type that is used has been created without the INLINE LENGTH attribute, the LOB column will use the value of the LOB INLINE LENGTH parameter on installation panel DSNTIPD as the default inline length when the value of LOB INLINE LENGTH does not exceed the maximum length of the LOB column. If the value of LOB INLINE LENGTH exceeds the maximum length of the LOB column, the maximum length is the inline length of this LOB column.
• If a distinct type that has been created with the INLINE LENGTH attribute is used, the LOB column inherits the inline length from the distinct type.

Regardless of how the length is determined, the inline length of the LOB cannot be greater than its maximum length.

period-definition

PERIOD

Defines a period for the table. begin-column-name must not be the same as end-column-name. The data type, length, precision, and scale for begin-column-name must be the same as for end-column-name.

If IN ACCELERATOR is specified, PERIOD must not be specified.

SYSTEM_TIME (begin-column-name,end-column-name)

Defines a system period with the name SYSTEM_TIME. There must not be a column in the table with the name SYSTEM_TIME. A table can have only one SYSTEM_TIME period. begin-column-name must be defined as AS ROW BEGIN and end-column-name must be defined as AS ROW END.

BUSINESS_TIME (begin-column-name,end-column-name)

Defines an application period with the name BUSINESS_TIME. There must not be a column in the table with the name BUSINESS_TIME. A table can have only one BUSINESS_TIME period. begin-column-name and end-column-name must be defined as DATE or TIMESTAMP(6) WITHOUT TIME ZONE, and the columns must be defined as NOT NULL. begin-column-name and end-column-name must not identify a column that is defined with a GENERATED clause.

An implicit check constraint is generated to ensure that the value of end-column-name is greater than the value of begin-column-name. The name of the implicitly created check constraint is DB2_GENERATED_CHECK_CONSTRAINT_FOR_BUSINESS_TIME, and that name must not be defined as the name of an existing check constraint.

begin-column-name

Identifies the column that records the beginning of the period of time in which a row is valid. The name must identify a column that exists in the table and must not be the same as a column that is used in the definition of another period for the table. begin-column-name must not be the same as end-column-name. The data type and precision for begin-column-name must be the same as for end-column-name.

For a SYSTEM_TIME period, begin-column-name must be defined as AS ROW BEGIN.

For a BUSINESS_TIME period, the column must not be defined with a GENERATED clause.

end-column-name

Identifies the column that records the end of the period of time in which a row is valid. In the history table that is associated with a system-period temporal table, the history table column that corresponds to end-column-name in the system-period temporal table is set to reflect the deletion of the row. The name must identify a column that exists in the table and must not be the same as a column that is used in the definition of another period for the table.

For a SYSTEM_TIME period, end-column-name must be defined as AS ROW END.

For a BUSINESS_TIME period, the column must not be defined with a GENERATED clause.

unique-constraint

CONSTRAINT constraint-name

Names the constraint. If a constraint name is not specified, a unique constraint name is generated. If a name is specified, it must be different from the names of any referential, check, primary key, or unique key constraints previously specified on the table.

PRIMARY KEY(column-name,...)

Defines a primary key composed of the identified columns. The clause must not be specified more than one time and the same column must not be identified more than one time. The identified columns must be defined as NOT NULL. Each column-name must be an unqualified name that identifies a column of the table except for the following types of columns:

• a LOB column
• a ROWID column
• a DECFLOAT column
• a distinct type column that is based on a LOB, ROWID, or DECFLOAT
• an XML column
• a row change timestamp 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.

The table is marked as unavailable until its primary index is explicitly created unless the table space is explicitly created and the CREATE TABLE statement is processed by the schema processor, or the table space is implicitly created. In that case, DB2 implicitly creates an index to enforce the uniqueness of the primary key and the table definition is considered complete. (For more information about implicitly created indexes, see Implicitly created indexes.)

BUSINESS_TIME WITHOUT OVERLAPS can be specified as the last item in the list. If BUSINESS_TIME WITHOUT OVERLAPS is specified, the list must include at least one column-name or key-expression. When WITHOUT OVERLAPS is specified, the values for the rest of the specified keys are unique with respect to the time for the BUSINESS_TIME period. When BUSINESS_TIME WITHOUT OVERLAPS is specified, the columns of the BUSINESS_TIME period must not be specified as part of the constraint. The specification of BUSINESS_TIME WITHOUT OVERLAPS adds the following to the constraint:

• The end column of the BUSINESS_TIME period in ascending order
• The begin column of the BUSINESS_TIME period in ascending order

UNIQUE(column-name,…)

Defines a unique key composed of the identified columns. Each column-name must be an unqualified name that identifies a column of the table. Each identified column must be defined as NOT NULL. The same column must not be identified more than one time. The following types of columns cannot be identified:

• a LOB column
• a ROWID column
• a DECFLOAT column
• a distinct type column that is based on a LOB, ROWID, or DECFLOAT
• a row change timestamp 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.

A unique key is a duplicate if it is the same as the primary key or a previously defined unique key. The specification of a duplicate unique key is ignored with a warning.

The table is marked as unavailable until all the required indexes are explicitly created unless the table space is explicitly created and the CREATE TABLE statement is processed by the schema processor, or the table space is implicitly created. In these cases, DB2 implicitly creates the indexes that are required for the unique keys and the table definition is considered complete. (For more information about implicitly created indexes, see Implicitly created indexes.)

BUSINESS_TIME WITHOUT OVERLAPS can be specified as the last item in the list. If BUSINESS_TIME WITHOUT OVERLAPS is specified, the list must include at least one column-name or key-expression. When WITHOUT OVERLAPS is specified, the values for the rest of the specified keys are unique with respect to the time for the BUSINESS_TIME period. When BUSINESS_TIME WITHOUT OVERLAPS is specified, the columns of the BUSINESS_TIME period must not be specified as part of the constraint. The specification of BUSINESS_TIME WITHOUT OVERLAPS adds the following to the constraint:

• The end column of the BUSINESS_TIME period in ascending order
• The begin column of the BUSINESS_TIME period in ascending order

referential-constraint

CONSTRAINT constraint-name

Names the referential constraint. If a constraint name is not specified, a unique constraint name is generated. If a name is specified, it must be different from the names of any referential, check, primary key, or unique key constraints previously specified on the table.

FOREIGN KEY (column-name,...) references-clause

Each specification of the FOREIGN KEY clause defines a referential constraint. The table being created is the child table for the referential constraint.

The foreign key of the referential constraint is composed of the identified columns. Each column-name must be an unqualified name that identifies a column of the table. The same column must not be identified more than one time. The column cannot be any of the following types of columns:

• a LOB column
• a ROWID column
• a DECFLOAT column
• an XML column
• a row change timestamp column
• a security label column
• a column in an accelerator-only table

The number of identified columns must not exceed 64. The sum of the column 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.

The foreign key of the referential constraint cannot reference a parent key that contains BUSINESS_TIME WITHOUT OVERLAPS.

REFERENCES table-name (column-name,...)

The table name that is specified after REFERENCES must identify a table that exists at the current server¹. The table name must not identify one of the following tables:

• A catalog table
• A directory table
• A declared global temporary table
• A history table

In the following discussion, let T2 denote an identified table and let T1 denote the table that you are creating (T1 and T2 cannot be the same table¹).

T2 must have a unique index. The privilege set must include the ALTER or REFERENCES privilege on the parent table, or the REFERENCES privilege on the columns of the nominated parent key.

The parent key of the referential constraint is composed of the identified columns. Each column-name must be an unqualified name that identifies a column of T2. The same column must not be identified more than one time. The identified column cannot be any of the following types of columns:

• a LOB column
• a ROWID column
• a DECFLOAT column
• an XML column
• a row change timestamp column
• a security label column

The list of column names in the parent key must match the list of column names in a primary key or unique key in the parent table T2. The column names must be specified in the same order as in the primary key or unique key. If any of the referenced columns in T2 has a non-numeric data type, T2 and T1 must use the same encoding scheme.

If a list of column names is not specified, T2 must have a primary key. Omission of a list of column names is an implicit specification of the columns of the primary key for T2.

The specified foreign key must have the same number of columns as the parent key of T2 and, except for their names, default values, null attributes and check constraints, the description of the nth column of the foreign key must be identical to the description of the nth column of the nominated parent 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. If a column of the foreign key has a field procedure, the corresponding column of the nominated parent key must have the same field procedure and an identical field description. A field description is a description of the encoded value as it is stored in the database for a column that has been defined to have an associated field procedure.

The table space that contains T1 must be available to DB2. If T1 is populated, its table space is placed in a check pending status. A table in a segmented table space is populated if the table is not empty. A table in a table space that is not segmented is considered populated if the table space has ever contained any records.

The referential constraint that is specified by a FOREIGN KEY clause defines a relationship in which T2 is the parent and T1 is the dependent. A description of the referential constraint is recorded in the catalog.

ON DELETE

The delete rule of the relationship is determined by the ON DELETE clause. For more on the concepts used here, see Referential constraints.

SET NULL must not be specified unless some column of the foreign key allows null values. The default value for the rule depends on the value of the CURRENT RULES special register when the CREATE TABLE statement is processed. If the value of the register is 'DB2', the delete rule defaults to RESTRICT; if the value is 'STD', the delete rule defaults to NO ACTION.

The delete rule applies when a row of T2 is the object of a DELETE or propagated delete operation and that row has dependents in T1. Let p denote such a row of T2. Then:

• If RESTRICT or NO ACTION is specified, an error occurs and no rows are deleted.
• If CASCADE is specified, the delete operation is propagated to the dependents of p in T1.
• If SET NULL is specified, each nullable column of the foreign key of each dependent of p in T1 is set to null.

Let T3 denote a table identified in another FOREIGN KEY clause (if any) of the CREATE TABLE statement. The delete rules of the relationships involving T2 and T3 must be the same and must not be SET NULL if:

• T2 and T3 are the same table.
• T2 is a descendent of T3 and the deletion of rows from T3 cascades to T2.
• T2 and T3 are both descendents of the same table and the deletion of rows from that table cascades to both T2 and T3.

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.

check-constraint

CONSTRAINT constraint-name

Names the check constraint. The constraint name must be different from the names of any referential, check, primary key, or unique key constraints previously specified on the table.

If 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 check constraint.

CHECK (check-condition)

Defines a check constraint. At any time, the check-condition must be true or unknown for every row of the table. 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. A check-condition is a search condition, with the following restrictions:

• It can refer only to columns of table table-name.
• The columns cannot be the following types of columns:
  • LOB columns
  • ROWID columns
  • DECFLOAT columns
  • distinct type columns that are based on LOB, ROWID, and DECFLOAT data types
  • XML columns
  • security label columns
  • columns in an accelerator-only table
• 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 specifications
  • 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
  • ROW CHANGE expressions
  • Row expressions
  • DISTINCT predicates
  • GX constants (hexadecimal graphic string constants)
  • Sequence references
  • OLAP specifications
• If a check-condition refers to a LOB column (including a distinct type that is based on a LOB), the reference must occur within a LIKE predicate.
• The AND and OR logical operators can be used between predicates. The NOT logical operator cannot be used.
• The first operand of every predicate must be the column name of a column in the table.
• The second operand in the check-condition must be either a constant or a column name of a column in the table.
  • If the second operand of a predicate is a constant, and if the constant is:
    • A floating-point number, then the column data type must be floating point.
    • A decimal number, then the column data type must be either floating point or decimal.
    • An integer number, then the column data type must not be a small integer.
    • A small integer number, then the column data type must be small integer.
    • A decimal constant, then its precision must not be larger than the precision of the column.
  • If the second operand of a predicate is a column, then both columns of the predicate must have:
    • The same data type.
    • Identical descriptions with the exception that the specification of the NOT NULL and DEFAULT clauses for the columns can be different, and that string columns with the same data type can have different length attributes.

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 that is specified after LIKE must identify a table or view that exists at the current server or a declared temporary table. A view cannot contain columns of length 0.

LIKE must not reference an accelerator-only table or be used with the IN ACCELERATOR clause.

The privilege set must implicitly or explicitly include the SELECT privilege on the identified table or view. If the identified table or view contains a column with a distinct type, the USAGE privilege on the distinct type is also needed. An identified table must not be an auxiliary table or a clone table. 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), an identity column, or a row change timestamp column.

The use of LIKE is an implicit definition of n columns, where n is the number of columns in the identified table (including implicitly hidden columns) or view. A column of the new table that corresponds to an implicitly hidden column in the existing table will also be defined as implicitly hidden. The implicit definition includes all attributes of the n columns as they are described in SYSCOLUMNS with the following 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 view is identified in the LIKE clause, none of the columns of the new table will have a field procedure. This is true even in the case that a column of a base table underlying the view has a field procedure defined.
• When a table is identified in the LIKE clause and a column in the table is 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 that contains a ROWID column is identified in the LIKE clause, the corresponding column of the new table inherits the ROWID column, regardless of whether the column has the IMPLICITLY HIDDEN attribute.
• When a table is identified in the LIKE clause and the table contains row change timestamp column, a transaction-start-ID column, a row-begin column, or a row-end column, the corresponding column of the new table inherits only the data type of the original column. The new column is not considered a generated column.
• When a table is identified in the LIKE clause and the table contains an inline LOB column, the corresponding columns of the new table will inherit the inline attribute if the table is in an universal table space. Otherwise, the inline attribute of the table identified in the LIKE clause is ignored.
• 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.
  The above defaults are chosen regardless of the current default of the base table column. The existence of an INSTEAD OF trigger does not affect the inheritance of default values.
• When a table that uses table-controlled partitioning is identified in the LIKE clause, the new table does not inherit partitioning scheme of that table. You can add these partition boundaries 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. For more information, see the CCSID clause.
• When a table is identified in the LIKE clause and the table includes a period, the new table does not inherit the period.
• When the table that is identified in the LIKE clause is a system-period temporal table, the new table is not a system-period temporal table.
• When the table that is identified in the LIKE clause has row access controls or column access controls activated, the new table does not inherit the row access controls or the column access controls.

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.

copy-options

copy-options

Specifies whether identity column attributes, row change timestamp 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 from the definition of the source of the result table.

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 one time).
• 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.

EXCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES or INCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES

Specifies whether row change timestamp column attributes are inherited from the definition of the source of the result table.

EXCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES

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

INCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES

Specifies that, if available, row change timestamp column attributes 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 defined as a row change timestamp column. In other cases, the columns of the new temporary table do not inherit the row change timestamp column attributes. The columns of the new table do not inherit the row change timestamp attributes in the following cases:

• The select list of the fullselect includes multiple instances of a row change timestamp column name (that is, selecting the same column more than one time).
• The select list of the fullselect includes multiple row change timestamp column names (that is, it involves a join).
• The row change timestamp column is included in an expression in the select list.
• The fullselect includes a set operation (such as union).

EXCLUDING COLUMN DEFAULTS, INCLUDING COLUMN DEFAULTS, or USING TYPE DEFAULTS

Specifies whether column defaults are inherited from the source result table definition. EXCLUDING COLUMN DEFAULTS, INCLUDING COLUMN DEFAULTS, and USING TYPE DEFAULTS must not be specified if the LIKE clause is specified.

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 an insert or update operation, or LOAD 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. The existence of an INSTEAD OF trigger for a view does not affect the inheritance of default values.

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 character string

Blanks

Fixed-length graphic string

Blanks

Fixed-length binary string

Hexadecimal zeros

Varying-length string

A string of length 0

Fixed-length char or fixed-length graphic

A string of blanks

Fixed-length binary

Hexadecimal zeros

Date

CURRENT DATE

Time

CURRENT TIME

Timestamp(integer) without time zone

CURRENT TIMESTAMP(p) WITHOUT TIME ZONE where p is the corresponding timestamp precision.

Timestamp(integer) with time zone

CURRENT TIMESTAMP(p) WITH TIME ZONE where p is the corresponding timestamp precision.

as-result-table

as-result-table

Specifies that the column definitions of the table are based on the result 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.

AS (fullselect)

Specifies that the table definition is based on the column definitions from the result of the fullselect. 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, length, precision, scale, and nullability characteristic of each of the result columns of fullselect. The length of each column must not be 0. Other column attributes, such as DEFAULT and IDENTITY, are not inherited from the fullselect. A column of the new table that corresponds to an implicitly hidden column of a base table referenced in the fullselect is not considered hidden in the new table. The generated column attributes are not inherited from the fullselect. That is, a new column of the table is not considered as a generated column. A FIELDPROC is inherited for a column if the corresponding select item of the fullselect is a column that can be mapped to a column of a base table or a view. The new table contains a security label column if only one table in the fullselect contains a security label column and the primary authorization ID of the statement has a valid security label.

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.

If IN ACCELERATOR is specified, AS (fullselect) cannot be specified.

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

Additional privileges might be necessary for accessing other objects that are referenced in the fullselect.

The fullselect must not:

• Result in a column having a ROWID, BLOB, CLOB, DBCLOB, or XML data type or a distinct type based on these data types.
• Include multiple security label columns.
• Include a PREVIOUS VALUE or a NEXT VALUE expression.
• Refer to host variables or include parameter markers.
• Include an SQL data change statement in the FROM clause.
• Include an outermost SELECT list that references data that is encoded with different CCSID sets.
• Reference a remote object.
• Reference an accelerator-only table.

WITH NO DATA

Specifies that the query is used only to define the attributes of the new table. The table is not populated using the results of the fullselect and the REFRESH TABLE statement cannot be used.

If the tables that are specified in the use row access controls or column access controls, the row access controls and the column access controls are not defined for the new table.

materialized-query-definition

materialized-query-definition

Specifies that the column definitions of the materialized query table are based on the result of a fullselect. If materialized-query-table-options are specified, 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 the fullselect. 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, length, precision, scale, and nullability characteristic of each of the result columns of fullselect. The length of each column must not be a 0. Other column attributes, such as DEFAULT, IDENTITY, and unique constraints, are not inherited from the fullselect. A column of the new table that corresponds to an implicitly hidden column of a base table referenced in the fullselect is not considered hidden in the new table. The generated column attributes are not inherited from the fullselect. That is, the new column of the materialized query table is not considered as a generated column. A FIELDPROC is inherited for a column if the corresponding select item of the fullselect is a column that can be directly mapped to a column of a base table or a view in the FROM clause of the fullselect. The materialized query table contains a security label column if only one table in the fullselect contains a security label column and the primary authorization ID of the statement has a valid security label.

Authorization for creating materialized query tables:

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

Additional privileges might be necessary for accessing other objects that are referenced in the fullselect.

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 following restrictions apply when creating materialized query tables. When fullselect does not satisfy the restrictions, an error occurs:

General restrictions: The following restrictions apply:

• The length of each result column of the fullselect must not be 0.
• The fullselect cannot contain a column of a LOB or XML data type.
• No more than one table in the fullselect can contain a security label column.
• The fullselect must not contain a period specification.
• The outermost SELECT list must not reference data that is encoded with different CCSID sets.
• The object that is specified in the FROM clause of the fullselect cannot be a view with columns of length 0.
• The fullselect cannot contain a reference to a created global temporary table, a declared global temporary table, an accelerator-only table, a directory table, or another materialized query table.
• If IN ACCELERATOR is specified, materialized-query-definition cannot be specified.
• The fullselect cannot directly or indirectly reference a base table that has been activated for the row or column access control or a base table for which a row permission or a column mask has been defined.
• The fullselect must not refer to host variables or include parameter markers.

Additional restrictions when ENABLE QUERY OPTIMIZATION is in effect:

• The fullselect must be a subselect.
• The subselect cannot include the following:
  • A special register
  • A scalar fullselect
  • A row change timestamp column
  • A ROW CHANGE expression
  • An expression for which implicit time zone values apply (for example, cast a timestamp to a timestamp with time zone)
  • The RAND built-in function
  • The RID built-in function
  • A user-defined scalar or table function that is not deterministic or that has external actions
  • Any predicates that include a subquery
  • A row expression predicate
  • A join using the INNER JOIN syntax, or an outer join
  • A lateral correlation
  • A nested table expression or view that requires temporary materialization
  • A direct or indirect reference to a table that uses activated row or column access controls, or a table for which row or column access controls have been defined.
  • A FETCH FIRST clause
• If a table with a security label is referenced, the security label column must be referenced in the outer select list of the subselect.
• If the subselect references a view, the fullselect in the view definition must satisfy all other restrictions.

refreshable-table-options

Specifies the options for a refreshable materialized query table. The ORDER BY clause is allowed, but it is used only by REFRESH. The ORDER BY clause can improve the locality of reference of data in the 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, an SQL data change statement, a SELECT from data change statement, or REFRESH TABLE SQL 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.

ENABLE QUERY OPTIMIZATION is the default.

The fullselect must not contain a period specification.

DISABLE QUERY OPTIMIZATION

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

IN

IN database-name.table-space-name or IN DATABASE database-name

Identifies the database and table space in which the table is created. Both forms are optional.

If you specify both a database and a table space, the database must be described in the catalog on the current server. The database must not be DSNDB06 or a work file database. The table space must belong to the database that you specify and must not be an XML table space.

If you specify a database but not a table space, a table space is implicitly created in database-name. The name of the table space is derived from the name of the table. The qualifier of the table space is the same as the qualifier of the table. The buffer pool that is used is the default buffer pool for user data that is specified on installation panel DSNTIP1. If you specify a table space but not a database, the database that contains the table space is used.

If you specify neither a table space or a database, a database is implicitly created with the name DSNxxxxx, where xxxxx is a five-digit number. A table space is also implicitly created.

If you specify a table space, it cannot be one of the following table spaces:

• A table space that was created implicitly
• A partitioned or a partition-by-growth table space that already contains a table
• A LOB table space
• A table space that already contains a system-period temporal table or a history table

If you specify a partitioned table space, you cannot load or use the table until its partitioned scheme is created.

You cannot specify the name of an implicitly created database, That is, you specify a database name that is eight characters, DSNxxxxx, where xxxxx is a five-digit number.

To create a table space implicitly, the privilege set must have: SYSADM or SYSCTRL authority; DBADM, DBCTRL, or DBMAINT authority for the database; or the CREATETS privilege for the database. You must also have the USE privilege for the default buffer pool in the database and default storage group.

For implicitly created table spaces, DB2 selects the buffer pool as described in Implicitly defined table spaces.

IN ACCELERATOR accelerator-name

Specifies that the table is an accelerator-only table. accelerator-name identifies the accelerator in which the table will be defined.

You can specify an alias (logical name) for accelerator-name. For more information, see Using an alias for an accelerator.

If you specify an accelerator-only table, the table and the data of the table exists only in the accelerator, not in DB2. However, the table and column definition of the accelerator-only table are contained in DB2 catalog tables.

partitioning-clause block

PARTITION BY RANGE or PARTITION BY SIZE

Specifies the partitioning scheme for the table.

PARTITION BY RANGE

Specifies the range partitioning scheme for the table (the columns that are 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. PARTITION BY RANGE must not be specified for a table that is created in a partition-by-growth table space. If IN ACCELERATOR is specified, PARTITION BY RANGE must not be specified.

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 or the same column more than one time. 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. Do not specify a qualified column name.

A timestamp with time zone column (or a column with a distinct type that is based on the timestamp with time zone data type) can only be specified as the last column in a partitioning key.

Do not specify a column for column-name if the column is defined as follows:

• a LOB column (or a column with a distinct type that is based on a LOB data type)
• a BINARY column (or a column with a distinct type that is based on a BINARY data type)
• a VARBINARY column (or a column with a distinct type that is based on a VARBINARY data type)
• a DECFLOAT column (or a column with a distinct type that is based on a DECFLOAT data type)
• an XML column
• a row change timestamp column

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.

ENDING AT (constant, MAXVALUE, or MINVALUE, ...)

Defines the limit key for a partition boundary. Specify at least one value (constant, MAXVALUE, or MINVALUE) after ENDING AT in each PARTITION clause. You can use as many values as there are columns in the key. The concatenation of all values is the highest value of the key for ascending and the lowest for descending.

constant

Specifies a constant value with a data type that must conform to the rules for assigning that value to the 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'. The precision and scale of a decimal constant must not be greater than the precision and scale of its corresponding column. A hexadecimal string constant (GX) cannot be specified.

MAXVALUE

Specifies a value greater than the maximum value for the limit key of a partition boundary (that is, all X'FF' regardless of whether the column is ascending or descending). If all of the columns in the partitioning key are ascending, a constant or the MINVALUE clause cannot be specified following MAXVALUE. After MAXVALUE is specified, all subsequent columns must be MAXVALUE.

MINVALUE

Specifies a value that is smaller than the minimum value for the limit key of a partition boundary (that is, all X'00' regardless of whether the column is ascending or descending). If all of the columns in the partitioning key are descending, a constant or the MAXVALUE clause cannot be specified following MINVALUE. After MINVALUE is specified, all subsequent columns must be MINVALUE.

The key values are subject to the following rules:

• The first value corresponds to the first column of the key, the second value to the second column, and so on. Using fewer values 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 values 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.
• If the concatenation of all the values exceeds 255 bytes, only the first 255 bytes are considered.
• If a key includes a ROWID column or a column with a distinct type that is based on a ROWID data type, 17 bytes of the constant that is specified for the corresponding ROWID column are considered.
• If a null value is specified for the partitioning key and the key is ascending, an error is returned unless MAXVALUE is specified. If the key is descending, an error is returned unless MINVALUE is specified..

HASH SPACE integerK|M|G

Specifies the amount of fixed hash space to preallocate for the partition that is associated with the partition-element. If HASH SPACE is omitted from the partition element, the HASH SPACE value from the ORGANIZE BY clause is used. If IN ACCELERATOR is specified, HASH SPACE must not be specified.

If HASH SPACE is not specified, each partition will use the HASH SPACE value specified in organization-clause.

The HASH SPACE keyword in partition-element must only be specified if organization-clause is also specified.

K

Indicates that the integer value is to be multiplied by 1024 to specify the hash space size in bytes. The integer must be between 256 and 268435456.

M

Indicates that the integer value is to be multiplied by 1048576 to specify the hash space size in bytes. The integer must be between 1 and 262144.

G

Indicates that the integer value is to be multiplied by 1073741824 to specify the hash space size in bytes. The integer must be between 1 and 256 for a partition by range table and must be between 1 and 131072 for a non-partitioned table.

If a value greater than 4G is specified, the data sets for the table space are associated with a DFSMS data class that has been specified with extended format and extended addressability.

INCLUSIVE

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

PARTITION BY SIZE

Specifies that the table is created in a partition-by-growth table space. If the IN clause is also specified, the IN clause must identify a partition-by-growth table space.

If IN ACCELERATOR is specified, PARTITION BY SIZE must not be specified.

EVERY integer G

Specifies that the table is to be partitioned by growth, every integer G bytes. integer must not be greater than 256. If the IN clause identifies a table space, integer must be the same as the DSSIZE value that is in effect for the table space that will contain the table.

organization-clause

ORGANIZE BY HASH

Specifies that a hash is to be used for the data organization of the table.

If PARTITION BY RANGE is specified, and the IN clause specifies a table space, the table space must be a partition by range universal table space, and cannot be a table space with PAGENUM RELATIVE.

If PARTITION BY RANGE is not specified, and an IN clause is specified, the IN clause must identify a partition-by-growth universal table space.

ORGANIZE BY HASH must not be specified if the table is defined with APPEND YES.

ORGANIZE BY HASH must not be specified if the table is using basic row format.

If IN ACCELERATOR is specified, ORGANIZE BY HASH must not be specified.

UNIQUE

Specifies that DB2 enforces uniqueness of the hash key columns, preventing the table from containing two or more rows with the same value of the hash key.

(column-name,...)

The list of column names defines the hash key that is used to determine where a row will be placed. Each column-name must be an unqualified name that identifies a column of the table. The same column must not be specified more than once and the specified columns must be defined as NOT NULL. The number of specified columns must not exceed 64, and the sum of their length attributes must not exceed 255. A specified column cannot be any of the following types:

• a LOB column
• a DECFLOAT column
• a XML column
• a distinct type column that is based on one of the preceding data types

If PARTITION BY RANGE is also specified, the list of column names must specify all of the column names that are specified in partition-expression for the table, and must specify the column names in the same order as partition-expression. If the ORGANIZE BY clause contains more columns than partition-expression, partition-expression determines the partition number.

HASH SPACE integerK|M|G

Specifies the amount of fixed hash space to preallocate for the table. If the table is partitioned by range, this is the space for each partition.

The default is 64M for a table in a partition-by-growth universal table space or 64M for each partition of a partition by range universal table space.

K

Indicates that the integer value is to be multiplied by 1024 to specify the hash space size in bytes. The integer must be between 256 and 268435456.

M

Indicates that the integer value is to be multiplied by 1048576 to specify the hash space size in bytes. The integer must be between 1 and 262144.

G

Indicates that the integer value is to be multiplied by 1073741824 to specify the hash space size in bytes. The integer must be between 1 and 256 for a partition by range table and must be between 1 and 131072 for a non-partitioned table.

If a value greater than 4G is specified, the data sets for the table space are associated with a DFSMS data class that has been specified with extended format and extended addressability.

Other options

EDITPROC program-name

Identifies the user-written code that implements the edit procedure for the table. The edit procedure must exist at the current server. The procedure is invoked during the execution of an SQL data change statement or LOAD and all row retrieval operations on the table.

An edit routine receives an entire table row, and can transform that row in any way. Also, it receives a transformed row and must change the row back to its original form.

You must not specify an edit routine for a table with a LOB column.

For information on writing an EDITPROC exit routine, see Edit procedures.

WITH ROW ATTRIBUTES

Specifies that the edit procedure parameter list contains an address for the description of a row. WITH ROW ATTRIBUTES must not be specified for a table with an identity, LOB, XML, ROWID, or SECURITY LABEL column. WITH ROW ATTRIBUTES is the default. When WITH ROW ATTRIBUTES is specified, the column names in the table must not be longer than 18 EBCDIC SBCS characters in length.

WITHOUT ROW ATTRIBUTES

Specifies that the description of the row is not provided to the edit procedure. On entry to the edit procedure, the address for the row description in the parameter list contains a value of zero.

VALIDPROC program-name

Designates program-name as the validation exit routine for the table. Writing a validation exit routine is described in Validation routines.

The validation routine can inhibit a load, insert, update, or delete operation on any row of the table: before the operation takes place, the procedure is passed the row. The values that are represented by any LOB or XML columns in the table are not passed to the validation routine. On an insert or update operation, if the table has a security label column and the user does not have write-down privilege, the user's security label value is passed to the validation routine as the value of the column. After examining the row, the procedure returns a value that indicates whether the operation should proceed. A typical use is to impose restrictions on the values that can appear in various columns. If IN ACCELERATOR is specified, VALIDPROC must not be specified.

A table can have only one validation procedure at a time. In an ALTER TABLE statement, you can designate a replacement procedure or discontinue the use of a validation procedure.

If you omit VALIDPROC, the table has no validation routine.

AUDIT

Identifies the types of access to this table that causes auditing to be performed. For information about audit trace classes, see DB2 Administration Guide.

If a materialized query table is refreshed with the REFRESH TABLE statement, the auditing also occurs during the REFRESH TABLE operation. AUDIT works as usual for LOAD and SQL data change operations on a user-maintained materialized query table.

NONE

Specifies that no auditing is to be done when this table is accessed. This is the default.

CHANGES

Specifies that auditing is to be done when the table is accessed during the first insert, update, or delete operation. However, the auditing is done only if the appropriate audit trace class is active.

ALL

Specifies that auditing is to be done when the table is accessed during the first operation of any kind performed by a utility or application process. However, the auditing is done only if the appropriate audit trace class is active and the access is not performed with COPY, RECOVER, REPAIR, LOAD with a dummy input data set, or any stand-alone utility.

If the table is subsequently altered with an ALTER TABLE statement, the ALTER TABLE statement is audited for successful and failed attempts in the following cases, if the appropriate audit trace class is active:

• AUDIT attribute is changed to NONE, CHANGES, or ALL on an audited or non-audited table.
• AUDIT CHANGES or AUDIT ALL is in effect.

If IN ACCELERATOR is specified, AUDIT NONE, CHANGES, and ALL must not be specified.

OBID integer

Identifies the OBID to be used for this table. An OBID is the identifier for an object's internal descriptor. The integer must be greater than 1 and 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 creator of the table.

DATA CAPTURE

Specifies whether the logging of the following actions on the table is augmented by additional information:

• SQL data change operations
• Adding columns (using the ADD COLUMN clause of the ALTER TABLE statement)
• Changing columns (using the ALTER COLUMN clause of the ALTER TABLE statement)

For guidance on intended uses of the expanded log records, see:

• The description of data propagation to IMS™ in IMS DataPropagator: An Introduction
• The instructions for using Remote Recovery Data Facility (RRDF) in Remote Recovery Data Facility Program Description and Operations
• The instructions for reading log records in DB2 Administration Guide

If a materialized query table is refreshed with the REFRESH TABLE statement, the logging of the augmented information occurs during the REFRESH TABLE operation. DATA CAPTURE works as usual for insert, update, and delete operations on a user-maintained materialized query table.

A table with data that is stored only in an accelerator-only table cannot be defined with this attribute.

NONE

Do not record additional information to the log. This is the default.

CHANGES

Write additional data about SQL updates to the log. Information about the values that are represented by any LOB or XML columns is not available. Do not specify DATA CAPTURE CHANGES for tables that reside in table spaces that specify NOT LOGGED.

WITH RESTRICT ON DROP

Indicates that the table can be dropped only by using REPAIR DBD DROP. In addition, the database and table space that contain the table can be dropped only by using REPAIR DBD DROP.

The WITH RESTRICT ON DROP clause can be removed using the ALTER TABLE statement with the DROP RESTRICT ON DROP clause. After the WITH RESTRICT ON DROP clause is removed from the definition of the table, the table, the database, and the containing table space can be dropped using the DROP statement.

CCSID encoding-scheme

Specifies the encoding scheme for string data stored in the table. If the IN clause is specified with a table space, the value must agree with the encoding scheme that is already in use for the specified table space. The specific CCSIDs for SBCS, mixed, and graphic data are determined by the table space or database specified in the IN clause. If the IN clause is not specified, the value specified is used for the table being created as well as for the table space that DB2 implicitly creates. The specific CCSIDs for SBCS, mixed, and graphic data are determined by the default CCSIDs for the server for the specified encoding scheme. The valid values are ASCII, EBCDIC, and UNICODE.

If IN ACCELERATOR is specified, a column cannot be defined as ASCII mixed or graphic. IBM DB2 Analytics Accelerator for z/OS: Stored Procedures Reference contains a complete description of encoding schemes allowed in an accelerator.

If the CCSID clause is not specified, the encoding scheme for the table depends on the IN clause:

• If the IN clause is specified, the encoding scheme already in use for the table space or database specified in the IN clause is used.
• If the IN clause is not specified, the encoding scheme of the new table is the same as the scheme for the table that is specified in the LIKE clause.

If the CCSID clause is specified for a materialized query table, the encoding scheme specified in the clause must be the same as the scheme for the result CCSID of the fullselect. The CCSID must also be the same as the CCSID of the table space for the table being created.

VOLATILE or NOT VOLATILE

Specifies how DB2 is to choose access to the table.

VOLATILE

Specifies that DB2 is to use index access to the table whenever possible for SQL operations. However, be aware that list prefetch and certain other optimization techniques might be disabled when VOLATILE is used.

One instance in which you might want to use VOLATILE 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 you might want to use VOLATILE 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. If IN ACCELERATOR is specified, VOLATILE must not be specified. For this usage, the primary index must be the only index that is defined on the table, and list prefetch is disabled to ensure the sequence in which the rows are locked.

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.

LOGGED or NOT LOGGED

Specifies whether changes that are made to the data in the implicitly created table space are recorded in the log. This parameter applies to the implicitly created table space and to all indexes of this table. XML table spaces and indexes associated with the XML table spaces inherit the logging attribute from the associated base table space. Auxiliary indexes also inherit the logging attribute from the associated base table space.

Do not specify LOGGED or NOT LOGGED if the table space name is specified by using the IN table-space-name clause or if the IN ACCELERATOR clause is specified.

LOGGED

Specifies that changes that are made to the data in the implicitly created table space are recorded in the log.

LOGGED is the default.

NOT LOGGED

Specifies that changes that are made to data in the implicitly created table space are not recorded in the log.

NOT LOGGED prevents undo and redo information from being recorded in the log. However, control information for the implicitly created table space will continue to be recorded in the log.

COMPRESS YES or COMPRESS NO

Specifies whether data compression applies to the rows of the implicitly created table space. The default is specified by the value of the subsystem parameter USE DATA COMPRESSION.

If the IN table-space-name clause or the IN ACCELERATOR clause is specified, COMPRESS YES or COMPRESS NO must not be specified.

COMPRESS YES

Specifies that data compression applies to the rows of the implicitly created table space. The rows are not compressed until the LOAD or REORG utility is run on the table in the implicitly created table space.

COMPRESS NO

Specifies that data compression is not used for the rows of the implicitly created table space.

APPEND NO or APPEND YES

Specifies whether append processing is used for the table. The APPEND clause must not be specified for a table that is created in a work file table space.

NO

Specifies that append processing is not used for the table. For insert and LOAD operations, DB2 will attempt to place data rows in a well clustered manner with respect to the value in the row's cluster key column.

NO is the default.

YES

Specifies that data rows are to be placed into the table by disregarding the clustering during insert and LOAD operations.

DSSIZE integer G

Specifies the maximum size for the implicitly created partition-by-growth or range-partitioned universal table space. This value is only applied to the implicitly created base table space, not to any associated implicitly created XML or LOB table spaces.

Do not specify DSSIZE integer G if any of the following conditions are true:

• The table space name is specified by using the IN table-space-name clause.
• The PARTITION BY clause includes the EVERY integer-constant G clause.
• The statement contains an accelerator-only table.

The default is specified by the value of the subsystem parameter MAXIMUM PARTITION SIZE.

For more detailed information about the DSSIZE clause, refer to CREATE TABLESPACE.

BUFFERPOOL bpname

Specifies the buffer pool be use for the implicitly created table space and determines the page size of the table space. For 4KB, 8KB, 16KB and 32KB page buffer pools, the page sizes are 4 KB, 8 KB, 16 KB, and 32 KB, respectively.

bpname must identify an activated buffer pool. The privilege set must include SYSADM authority, SYSCTRL authority, or the USE privilege on the buffer pool.

Do not specify BUFFERPOOL bpname if the table space name is specified by using the IN table-space-name clause or the IN ACCELERATOR clause is specified.

If you do not specify the BUFFERPOOL clause, DB2 selects the buffer pool as described in Implicitly defined table spaces.

Refer to Naming conventions for more information about bpname.

MEMBER CLUSTER

Specifies that data that is inserted by an insert operation is not clustered by the implicit clustering index (the first index) or the explicit clustering index. DB2 places the data in the implicitly created table space based on available space.

Do not specify MEMBER CLUSTER if the table space name is specified by using the IN table-space-name clause or if IN ACCELERATOR clause is specified.

TRACKMOD YES or TRACKMOD NO

Specifies whether DB2 tracks modified pages in the space map pages of the implicitly created table space. The default is specified by the value of the subsystem parameter TRACK MODIFIED PAGES.

Do not specify TRACKMOD YES or TRACKMOD NO if the table space name is specified by using the IN table-space-name clause or if using the IN ACCELERATOR clause.

TRACKMOD YES

Changed pages are tracked in the space map pages to help improve performance of incremental image copies.

TRACKMOD NO

Changed pages are not tracked in the space map pages. DB2 uses the LRSN value in each page to determine whether a page has been changed.

Notes

Owner privileges:

The owner of the table has all table privileges (see GRANT (table or view privileges)) with the ability to grant these privileges to others. For more information about ownership of the object, see Authorization, privileges, permissions, masks, and object ownership.

Table design:

Designing tables is part of the process of database design. For more information, see DB2 database design .

Creating a table in a segmented table space:

A table cannot be created in a segmented table space if any of the following conditions are true:

• The available space in the data set is less than the segment size specified for the table space, and
• The data set cannot be extended.

Creating a table with graphic and mixed data columns:

You cannot create an ASCII or EBCDIC table with a GRAPHIC, VARGRAPHIC, or DBCLOB column or a CHAR, VARCHAR, or CLOB column defined as FOR MIXED DATA when the setting for installation option MIXED DATA is NO.

Creating a table with distinct type columns based on LOB, ROWID, and DECFLOAT columns:

Because a distinct type is subject to the same restrictions as its source type, all the syntactic rules that apply to LOB columns (CLOB, DBCLOB, and BLOB), ROWID columns, and DECFLOAT columns apply to distinct type columns that are based on LOBs, row IDs, and DECFLOATs. For example, a table cannot have both an explicitly defined ROWID column and a column with a distinct type that is based on a row ID.

Tables with inline LOB columns:

If the 32K page size is exceeded for a table in an universal table space, DB2 recalculates the record size using 0 as the inline length for LOB columns that do not specify the INLINE LENGTH clause. After the recalculation, if the 32K page size is still exceeded, the CREATE TABLE statement returns an error.

You cannot create a table with an inline LOB column in a table space that has basic row format.

Creating a table with LOB columns:

A table with a LOB column (CLOB, DBCLOB, or BLOB) must also have a ROWID column and one or more auxiliary tables. When you create the table, DB2 implicitly generates a ROWID column for you. This is called an implicitly hidden ROWID column, and DB2:

• Creates the column with a name of DB2_GENERATED_ROWID_FOR_LOBSnn.

  DB2 appends nn only if the column name already exists in the table, replacing nn with 00 and incrementing by 1 until the name is unique within the row.

• Defines the column as GENERATED ALWAYS.
• Appends the implicitly hidden ROWID column to the end of the row after all the other explicitly defined columns.

For example, assume that DB2 generated an implicitly hidden ROWID column named DB2_GENERATED_ROWID_FOR_LOBS for table MYTABLE. The result table for a SELECT * statement for table MYTABLE would not contain that ROWID column. However, the result table for SELECT COL1, DB2_GENERATED_ROWID_FOR_LOBS would include the implicitly hidden ROWID column.

If the MIXED DATA subsystem parameter is set to yes, and a lowercase or mixed case hexadecimal constant is specified as the default value for a LOB column, the CREATE TABLE statement returns an error.

The definition of the table is marked incomplete until an auxiliary table is created in a LOB table space for each LOB column in the base table and index is created on each auxiliary table. The auxiliary table stores the actual values of a LOB column. If you create a table with a LOB column in a partitioned table space, there must be one auxiliary table defined for each partition of the base table space.

Unless DB2 implicitly creates the LOB table space, auxiliary table, and index on the auxiliary table for each LOB column in the base table, you need to create these objects using the CREATE TABLESPACE, CREATE AUXILIARY TABLE, and CREATE INDEX statements.

If the table space that contains the table is explicitly created and the value of the CURRENT RULES special register is 'STD' when the CREATE TABLE statement is processed, or the table space that contains the table is implicitly created, DB2 implicitly creates the LOB table space, auxiliary table, and index on the auxiliary table for each LOB column in the base table.

The privilege set must include the following privileges:

• The USE privilege on the buffer pool and the storage group that is used by the XML objects
• If the base table space is explicitly created, CREATETS is also required on the database that contains the table (DSNDB04 if the database is implicitly created)

DB2 chooses the names of implicitly created objects using these conventions:

LOB table space

Name is 8 characters long, consisting of an 'L' followed by 7 random characters.

auxiliary table

Name is 18 characters long. The first five characters of the name are the first five characters of the name of the base table. The second five characters are the first five characters of the name of the LOB column. The last eight characters are randomly generated. If a base table name or a LOB column name is less than five characters, DB2 adds underscore characters to the name to pad it to a length of five characters.

index on the auxiliary table

Name is 18 characters long. The first character of the name is an 'I'. The next ten characters are the first ten characters of the name of the auxiliary table. The last seven characters are randomly generated. The index has the COPY NO attribute.

The other attributes of these implicitly created objects are those that would have been created by their respective CREATE statements with all optional clauses omitted, with the following exceptions:

• The database name is the database name of the base table.
• If the LOB table space is implicitly created, the buffer pool is determined by the DEFAULT BUFFER POOL FOR USER LOB DATA fields of installation panel DSNTIP1. The appropriate USE privilege is required on that buffer pool.

Utility REPORT TABLESPACESET identifies the LOB table spaces that DB2 implicitly created.

Creating a table with an XML column:

When a table is created with an XML column, an XML table space, XML table, and a node ID index and document ID index are implicitly created.

The privilege set must include the following privileges:

• The USE privilege on the buffer pool and the storage group that is used by the XML objects
• If the base table space is explicitly created, CREATETS is also required on the database that contains the table (DSNDB04 if the database is implicitly created)

The buffer pool for the XML table space is determined by the DEFAULT BUFFER POOL FOR USER XML DATA fields of installation panel DSNTIP1. The appropriate USE privilege is required on that buffer pool.

The XML table space will have a larger DSSIZE than the base table space if the base table space is partitioned by range. If the base table space is partitioned by growth, the default DSSIZE of 4GB will be used for the XML table space. The DSSIZE for an XML table space that is associated with a partitioned by range base table space is determined as follows.

Table 2. Default DSSIZE for XML table spaces, given base table space DSSIZE and page size

Base table space DSSIZE	4KB base page size	8KB base page size	16KB base page size	32KB base page size
1GB - 4GB	4GB	4GB	4GB	4GB
8GB	32GB	16GB	16GB	16GB
16GB	64GB	32GB	16GB	16GB
32GB	64GB	64GB	32GB	16GB
64GB	64GB	64GB	64GB	32GB
128GB	256GB	256GB	128GB	64GB
256GB	256GB	256GB	256GB	128GB

For example: for a base table space that has a DSSIZE of 8GB and a page size of 8KB, the XML table space will have a DSSIZE of 16GB.

Naming convention for implicitly created XML objects:

Implicitly created XML table spaces names will be Xyyynnnn, where yyy is derived from the first three bytes of the base table name (if the name is shorter than 3, yyy is padded with X). nnnn is a numeric string that will start at 0000 and be incremented by 1 until a unique number is found.

Implicitly created XML table names will be Xyyyyyyyyyyyyyyyyyynnn, where yyyyyyyyyyyyyyyyyy is the first 18 UTF-8 bytes of the base table name or of the entire name if it is less than 18. nnn will only be appended if the name already exists in the table. If the name already exists, nnn will be replaced with 000 and will be incremented by 1 until the name is unique.

Implicitly created document ID index names will be I_DOCIDyyyyyyyyyyyyyyyyyynnn, where yyyyyyyyyyyyyyyyyy is the first 18 UTF-8 bytes of the base table name or the entire name if it is less than 18. nnn will only be appended if the index already exists in the table. If the index already exists, nnn will be replaced with 000 and will be incremented by 1 until the name is unique.

Implicitly created node ID index names will be I_NODEIDyyyyyyyyyyyyyyyyyynnn, where yyyyyyyyyyyyyyyyyy is the first 18 UTF-8 bytes of the XML table name or the entire name if it is less than 18. nnn will only be appended if the index already exists in the table. If the index already exists, nnn will be replaced with 000 and will be incremented by 1 until the name is unique.

Creating a table with an identity column:

When a table has an identity column, DB2 can automatically generate 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. 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 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.

If you want to ensure that an identity column has unique values, create a unique index on the column.

Creating a table with a LONG VARCHAR or LONG VARGRAPHIC column:

Although the syntax LONG VARCHAR and LONG VARGRAPHIC is allowed for compatibility with previous releases of DB2, its use is not encouraged. VARCHAR(integer) and VARGRAPHIC(integer) is the recommended syntax, because after the CREATE TABLE statement is processed, DB2 considers a LONG VARCHAR column to be VARCHAR and a LONG VARGRAPHIC column to be VARGRAPHIC.

When a column is defined using the LONG VARCHAR or LONG VARGRAPHIC syntax, DB2 determines the length attribute of the column. You can use the following information, which is provided for existing applications that require the use of the LONG VARCHAR or LONGVARGRAPHIC syntax, to calculate the byte count and the character count of the column.

To calculate the byte count, use this formula:

2*(INTEGER((INTEGER((m-i-k)/j))/2))

Where:

m

Is the maximum row size (8 less than the maximum record size)

i

Is the sum of the byte counts of all columns in the table that are not LONG VARCHAR or LONG VARGRAPHIC

j

is the number of LONG VARCHAR and LONG VARGRAPHIC columns in the table

k

k is the number of LONG VARCHAR and LONG VARGRAPHIC columns that allow nulls

To find the character count:

1. Find the byte count.
2. Subtract 2.
3. If the data type is LONG VARGRAPHIC, divide the result by 2. If the result is not an integer, drop the fractional part.

If the IN DATABASE clause is specified:

If you specify IN DATABASE (either explicitly or by default), but do not specify a table space, a table space is implicitly created in database-name. The name of the table space is derived from the table name. The qualifier of the table space is the same as the qualifier of the table. The owner of the table space is SYSIBM.

If range-partitioning is not specified, the implicitly created table space is a partition-by-growth table space with MAXPARTITIONS 256, SEGSIZE 32, and DSSIZE 4G.

If range-partitioning is specified, the table space will be partitioned based on the number of parts specified on the CREATE TABLE statement with defaults of SEGSIZE 32, LOCKSIZE ROW, and LOCKMAX SYSTEM.

If the IN clause is not specified:

If you do not specify the IN clause, the DB2 subsystem will implicitly create a table space as described previously, but the DB2 subsystem will also choose a database. The DB2 subsystem creates a name in the form of DSNnnnnn, where nnnnn is between 00001 and the maximum value of the sequence SYSIBM.DSNSEQ_IMPLICITDB, which has a default of 10000, inclusive. The owner of the database is SYSIBM.

• If DSNnnnnn already exists and is an implicitly created database, the DB2 subsystem creates the table in that database.
• If DSNnnnnn does not exist, the DB2 subsystem creates a database with the name DSNnnnnn.

If DSNnnnnn cannot be created because of a deadlock, timeout, or resource unavailable condition, the DB2 subsystem increments nnnnn by one and tries the resultant database name. If the DB2 subsystem reaches the maximum value of the sequence SYSIBM.DSNSEQ_IMPLICITDB, and the corresponding database name is not available, the DB2 subsystem sets nnnnn to 00001 and tries the resultant database name. If the DB2 subsystem attempts to create the table a number of times that is equal to the maximum value of the sequence SYSIBM.DSNSEQ_IMPLICITDB without success, an error occurs.

Implicitly created table space attributes:

The attributes of the implicitly created table space can be changed by using the ALTER TABLESPACE statement.

Defining a system-period temporal table:

A system-period temporal table definition includes the following:

• A system period named SYSTEM_TIME which is defined using a row-begin column and a row-end column.
• A transaction-start-ID column.
• A system-period data versioning definition which includes the name of the associated history table.

To ensure that the history table cannot be implicitly dropped when a system-period temporal table is dropped, use the WITH RESTRICT ON DROP clause in the definition of the history table.

Defining an application-period temporal table:

An application-period temporal table definition includes an application period named BUSINESS_TIME. The application period is defined using a begin timestamp column and an end timestamp column.

Data change operations on an application-period temporal table might result in an automatic insert of one or two additional rows when a row is updated or deleted. When an update or delete of a row in an application-period temporal table is specified for a portion of the period that is represented by that row, the row is updated or deleted and one or two rows are automatically inserted to represent the portion of the row that is not changed. New values are generated for each generated column in an application-period temporal table for each row that is automatically inserted as a result of an update or delete operation on the table. If a generated column is defined as part of a unique or primary key, parent key in a referential constraint, or unique index, it is possible that an automatic insert will violate a constraint or index, in which case an error is returned.

Bitemporal tables:

A table that is defined for system-period data versioning and contains a BUSINESS_TIME period is referred to as a bitemporal table.

Considerations for transaction-start-ID columns:

A transaction-start-ID column contains a null value if the column allows null values. A row-begin column which is unique from other row-begin column values that are generated for other transactions exists with the transaction-start-ID column. Given that the column might contain null values, consider using one of the following methods when retrieving a value from the column:

COALESCE ( transaction_start_id_col, row_begin_col)
CASE WHEN transaction_start_id_col IS NOT NULL
	THEN transaction_start_id_col 
		ELSE row_begin_col 
END

If the IN clause is specified with ORGANIZE BY HASH:

If you specify IN DATABASE (either explicitly or by default), and ORGANIZE BY HASH, DB2 will calculate an optimum buffer pool for hash organization based on the definition of the table and validate the calculated buffer pool with the buffer pool of the explicitly created table space. If the buffer pool sizes are different, DB2 will return an error.

If the table is in a range-partitioned universal table space, the DSSIZE value for the table space must be large enough to fit the HASH SPACE specification for each partition.

If the table is in a partition-by-growth table space, the total space calculated from the DSSIZE and MAXPARTITIONS values for the table space must be large enough for the implicitly or explicitly specified HASH SPACE.

If the IN clause is not specified with ORGANIZE BY HASH:

If you do not specify IN DATABASE (either explicitly or by default), DB2 will use the default DSSIZE of 4G for each partition for a range-partition universal table space or use the value that is specified in the partitioning clause. The hash space value that is specified on CREATE TABLE will be validated, per part, to ensure that the specified DSSIZE is adequate. If the DSSIZE is not adequate, an error will be returned.

If the maximum number of partitions needed for the specified hash space is more than the maximum number of partitions allowed, DB2 to will return an error.

If the selected buffer pool is not available, a error will be returned.

Creating a table with hash organization and LOB columns:

If the table space is a partition-by-growth universal table space, DB2 will preallocate as many partitions as needed depending on the value specified for HASH SPACE. If DB2 needs to implicitly create the LOB object in a new partition, the privilege set for the implicitly created LOB objects must include the USE privilege on the buffer pool for the LOB table space.

Hash space and DB2 page size:

If the specified hash space is less than or equal to 64 MB (the DB2 default), DB2 will add extra space for DB2 system pages. If the specified hash space is greater than 64 MB, DB2 will use part of the hash space for DB2 system pages. The amount of space needed for DB2 system pages depends on SEGSIZE and PAGESIZE. The larger the SEGSIZE and/or PAGESIZE becomes, the larger the requirement for DB2 system pages. DB2 can reserve up to 5 MB for system pages for the highest SEGSIZE value (64) and PAGESIZE value (32K).

Hash space and DSSIZE:

Depending on certain table space characteristics, DB2 needs to reserve space for the hash overflow area. Therefore, the amount of hash space cannot be equal to the DSSIZE value. The maximum amount of hash space that can be specified is approximately 20% less than the DSSIZE value. DB2 returns an error if the amount of hash space is too large. If the amount of hash space is too large, specify a larger value of DSSIZE, or decrease the amount of hash space.

Specifying APPEND with ORGANIZE BY HASH:

Append processing is not applicable to tables with hash organization since there is no key clustering in hash organization. For insert operations into tables with hash organization, DB2 will use the internal hash algorithm to determine the location of the row.

Restrictions for tables with hash organization:

Tables that use hash organization are subject to the following restrictions:

• A table that is defined to use hash organization cannot be created in a LOB table space or XML table space.
• ORGANIZE BY HASH must not be specified if the table space is defined with the MEMBER CLUSTER clause.
• The MAXROWS clause is applicable only to the hash overflow area of the table space for tables with hash organization. The fixed hash area of each page will contain as many rows as it can hold, up to a maximum of 255.
• The ORGANIZE BY HASH UNIQUE (column-list) clause is required when specifying HASH SPACE integer K|M|G in the partition-element. The organization-clause applies to the entire table and the partition-element clause applies at the partition level.
• DB2 will automatically create a hash overflow index when a table is created with hash organization.

Implicitly created table spaces:

If the table space is implicitly created, all of the following required system objects will also be implicitly created:

• The enforcing primary key index
• The enforcing unique key index
• Any necessary LOB table spaces, auxiliary table spaces, and auxiliary indexes
• The ROWID index (if the ROWID column is defined as GENERATED BY DEFAULT)

When DB2 implicitly creates a base table space for a table with LOB columns that can have inline LOBs, DB2 creates the base table space in reordered row format, regardless of the value of the RRF subsystem parameter.

When DB2 implicitly creates a table space for a table with hash organization, DB2 creates the table space in reordered row format, regardless of the value of the RRF subsystem parameter.

Implicitly created indexes:

When the PRIMARY KEY or UNIQUE clause is used in the CREATE TABLE statement and the CREATE TABLE statement is processed by the schema processor or the table space that contains the table is implicitly created, DB2 implicitly creates the unique indexes used to enforce the uniqueness of the primary or unique keys.

When a ROWID column is defined as GENERATED BY DEFAULT in the CREATE TABLE statement, and the CREATE TABLE statement is processed by SET CURRENT RULES = 'STD' or the table space that contains the table is implicitly created, DB2 implicitly creates the unique indexes used to enforce the uniqueness of the ROWID column.

The privilege set must include the USE privilege of the buffer pool.

Each index is created as if the following CREATE INDEX statement were issued:

CREATE UNIQUE INDEX xxx ON table-name (column1,...)

Where:

• xxx is the name of the index that DB2 generates.
• table-name is the name of the table specified in the CREATE TABLE statement.
• (column1,...) is the list of column names that were specified in the UNIQUE or PRIMARY KEY clause of the CREATE TABLE statement, or the column is a ROWID column that is defined as GENERATED BY DEFAULT.

For more information about the schema processor, see DB2 Administration Guide.

In addition, if the table space that contains the table is implicitly created, DB2 will check the DEFINE DATA SET subsystem parameter to determine whether to define the underlying data set for the index space of the implicitly created index on the base table.

If DEFINE DATA SET is NO, the index is created as if the following CREATE INDEX statement is issued:

CREATE UNIQUE INDEX xxx ON table-name (column1,...) DEFINE NO

Maximum record size:

The maximum record size of a table depends on the page size of the table space, whether the table space is organized for hash access, and whether the EDITPROC clause is specified, as shown in Table 3.

The initial page size of the table space is the size of its buffer, which is determined by the BUFFERPOOL clause that was explicitly or implicitly specified when the table space was created. When the record size reaches 90 percent of the maximum record size for the page size of the table space, the next largest page size is automatically used.

Table 3. Maximum record size, in bytes

	Page Size = 4KB	Page Size = 8KB	Page Size = 16KB	Page Size = 32KB
Non-hash table	4056	8138	16330	32714
Non-hash table with EDITPROC=YES	4046	8128	16320	32704
Hash table (hash home page)	3817	7899	16091	32475
Hash table with EDITPROC=YES (hash home page)	3807	7889	16081	32465

The maximum record size corresponds to the maximum length of a VARCHAR column if that column is the only column in the table.

If the table space that contains the table is implicitly created, the proper buffer pool size is chosen according to the actual record size. If the record size reaches 90% of the maximum record size for the page size of the table space, the next largest page size will be used. Table 4 shows 90% of the maximum record size:

Table 4. 90% of Maximum record size, in bytes

	Page Size = 4KB	Page Size = 8KB	Page Size = 16KB	Page Size = 32KB
Non-hash table	3650	7324	14697	29443
Non-hash table with EDITPROC=YES	3641	7315	14688	29434
Hash table (hash home page)	3435	7109	14482	29228
Hash table with EDITPROC=YES (hash home page)	3426	7100	14473	29219

Byte counts:

The sum of the byte counts of the columns must not exceed the maximum row size of the table. The maximum row size is eight less than the maximum record size.

For columns that do not allow null values, Table 5 gives the byte counts of columns by data type. For columns that allow null values, the byte count is one more than shown in the table.

Table 5. Byte counts of columns by data type

Data Type	Byte Count
INTEGER	4
SMALLINT	2
BIGINT	8
FLOAT(n)	If n is between 1 and 21, the byte count is 4. If n is between 22 and 53, the byte count is 8.
DECIMAL	INTEGER(p/2)+1, where p is the precision
DECFLOAT(16)	9
DECFLOAT(34)	17
CHAR(n)	n
VARCHAR(n)	n+2
CLOB	6
Inline CLOB	6 + inline byte count
GRAPHIC(n)	2n
VARGRAPHIC(n)	2n+2
DBCLOB	6
Inline DBCLOB	6 + (inline char count * 2)
BINARY(n)	n
VARBINARY(n)	n+2
BLOB	6
Inline BLOB	6 + inline byte count
DATE	4
TIME	3
TIMESTAMP(p) WITHOUT TIME ZONE	INTEGER((p+1)/2) + 7 where p is the precision
TIMESTAMP(p) WITH TIME ZONE	INTEGER((p+1)/2) + 9 where p is the precision
ROWID	19
distinct type	The length of the source data type upon which the distinct type was based
XML	6 - If column cannot contain multiple versions of an XML document. 14 - If column can contain multiple versions of an XML document.

Related information:

• See XML versions.

Creating a materialized query table:

If the fullselect in the CREATE TABLE statement contains a SELECT *, the select list of the subselect is determined at the time the materialized query table is created. In addition, any references to user-defined functions are resolved at the same time. The isolation level at the time when the CREATE TABLE statement is executed is the isolation level for the materialized query table. After a materialized query table is created, the REFRESH_TIME column of the row for the table in the SYSIBM.SYSVIEWS catalog table contains the default timestamp.

The owner of a materialized query table has all the table privileges with the grant option on the table irrespective of whether the owner has the necessary privileges on the base tables, views, functions, and sequences.

No unique constraints or unique indexes can be created for materialized query tables. Thus, a materialized query table cannot be a parent table in a referential constraint.

When you are creating user-maintained materialized query tables, you should create the materialized query table with query optimization disabled and then enable the table for query optimization after it is populated. Otherwise, DB2 might rewrite queries to use the empty materialized query table, and you will not get accurate results.

Considerations for implicitly hidden columns:

A column that is defined as implicitly hidden is not part of the result table of a query that specifies * in a SELECT list. However, an implicitly hidden column can be explicitly referenced in a query. For example, an implicitly hidden column can be referenced in the SELECT list or in a predicate in a query. Additionally, an implicitly hidden column can be explicitly referenced in a COMMENT, CREATE INDEX statement, ALTER TABLE statement, INSERT statement, MERGE statement, UPDATE statement, or RENAME statement. An implicitly hidden column can be referenced in a referential constraint. A REFERENCES clause that does not contain a column list refers implicitly to the primary key of the parent table. It is possible that the primary key of the parent table includes a column defined as implicitly hidden. Such a referential constraint is allowed.

If the SELECT list of the fullselect of a materialized query definition explicitly refers to an implicitly hidden column, that column will be part of the materialized query table.

If the SELECT list of the fullselect of a view definition (CREATE VIEW statement) explicitly refers to an implicitly hidden column, that column will be part of the view, however the view column is not considered 'hidden'.

Restrictions on field procedures, edit procedures, and validation exit procedures:

Field procedures, edit procedures, and validation exit procedures cannot be used on tables that have column names that are larger than 18 EBCDIC bytes. If you have tables that have field procedures or validation exit procedures and you add a column where the column name is larger than 18 bytes, the field procedures and validation exit procedures for the table will be invalidated.

Consider using triggers to replace the functionality on field procedures, edit procedures, and validation exit procedures on tables where the column names are larger than 18 EBCDIC bytes.

Restrictions on certain SQL statements in the same unit of work as CREATE TABLE:

• A CREATE TABLE statement that contains a PARTITION BY clause should not be followed in the same unit of work by SQL statements that change data.
• A CREATE TABLE statement that contains an IN ACCELERATOR clause should be issued in a separate unit of work from other SQL statements.

Creating a table while a utility runs:

You cannot use CREATE TABLE while a DB2 utility has control of the table space implicitly or explicitly specified by the IN clause.

Restriction on table spaces when there are pending changes to the definition:

A CREATE TABLE statement is not allowed if there are pending changes to the definition of the table space.

Alternative syntax and synonyms:

To provide compatibility with previous releases of DB2 or other products in the DB2 family, DB2 supports the following clauses:

• NOCACHE (single clause) as a synonym for NO CACHE
• NOCYCLE (single clause) as a synonym for NO CYCLE
• NOMINVALUE (single clause) as a synonym for NO MINVALUE
• NOMAXVALUE (single clause) as a synonym for NO MAXVALUE
• NOORDER (single clause) as a synonym for NO ORDER
• PART integer VALUES can be specified as an alternative to PARTITION integer ENDING AT.
• VALUES as a synonym for ENDING AT
• DEFINITION ONLY as a synonym for WITH NO DATA
• SUMMARY between CREATE and TABLE
• TIMEZONE can be specified as an alternative to TIME ZONE.