The CREATE TABLE statement defines a table at the current server. The definition must include its name and the names and attributes of its columns. The definition may include other attributes of the table such as primary key.
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
The privileges held by the authorization ID of the statement must include at least one of the following:
The privileges held by the authorization ID of the statement must include at least one of the following:
If SQL names are specified and a user profile exists that has the same name as the library into which the table is created, and that name is different from the authorization ID of the statement, then the privileges held by the authorization ID of the statement must include at least one of the following:
To define a foreign key, the privileges held by the authorization ID of the statement must include at least one of the following on the parent table:
If a field procedure is defined, the privileges
held by the authorization ID of the statement must include at least
one of the following: 

If the LIKE clause or select-statement is specified, the privileges held by the authorization ID of the statement must include at least one of the following on the tables or views specified in these clauses:
If a distinct type is referenced, the privileges held by the authorization ID of the statement must include at least one of the following:
For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View and Corresponding System Authorities When Checking Privileges to a Distinct Type.
.-,------------------------------------------. V | >>-CREATE TABLE--table-name--+-(----+-column-definition----------------------+-+--)-+--> | +-LIKE--+-table-name-+--+--------------+-+ | | | '-view-name--' '-copy-options-' | | | +-unique-constraint----------------------+ | | +-referential-constraint-----------------+ | | '-check-constraint-----------------------' | +-LIKE--+-table-name-+--+--------------+---------------+ | '-view-name--' '-copy-options-' | +-as-result-table--+--------------+--------------------+ | '-copy-options-' | '-materialized-query-definition------------------------' (1) >--+----------------------+-------------------------------------> '-NOT LOGGED INITIALLY-' .-CARDINALITY-. .-NOT VOLATILE--+-------------+-. >--+-------------------------------+--+---------------------+---> | .-CARDINALITY-. | '-RCDFMT--format-name-' '-VOLATILE--+-------------+-----' >--+------------------+--+---------------------+--------------->< '-media-preference-' +-distribution-clause-+ '-partitioning-clause-' media-preference .-UNIT ANY-. |--+-UNIT SSD-+-------------------------------------------------|
column-definition |--column-name--+-------------------------------------+---------> | .-COLUMN-. | '-FOR--+--------+--system-column-name-' (1) >--data-type----------------------------------------------------> .----------------------------------------------------------------------. V | (4) >----+------------------------------------------------------------------+-+------| +-default-clause---------------------------------------------------+ | .-GENERATED ALWAYS-----. (2) | +-+----------------------+------+--------------------------------+-+ | '-GENERATED BY DEFAULT-' +-identity-options---------------+ | | '-as-row-change-timestamp-clause-' | +-NOT NULL---------------------------------------------------------+ | .-NOT HIDDEN--------. | +-+-------------------+--------------------------------------------+ | '-IMPLICITLY HIDDEN-' | +-column-constraint------------------------------------------------+ +-FIELDPROC--external-program-name--+--------------------+---------+ | | .-,--------. | | | | V | | | | '-(----constant-+--)-' | | (3) | '-datalink-options-------------------------------------------------' data-type |--+-built-in-type------+---------------------------------------| '-distinct-type-name-'
built-in-type |--+-+---SMALLINT---+----------------------------------------------------------------------------------------------------------+--| | +-+-INTEGER-+--+ | | | '-INT-----' | | | '---BIGINT-----' | | .-(5,0)------------------------. | +-+-+-DECIMAL-+-+--+------------------------------+-------------------------------------------------------------------------+ | | '-DEC-----' | | .-,0--------. | | | '-+-NUMERIC-+-' '-(--integer--+-----------+--)-' | | '-NUM-----' '-, integer-' | | .-(--53--)------. | +-+-FLOAT--+---------------+-+----------------------------------------------------------------------------------------------+ | | '-(--integer--)-' | | | +-REAL---------------------+ | | | .-PRECISION-. | | | '-DOUBLE--+-----------+----' | | .-(--34--)-. | +---DECFLOAT--+----------+--------------------------------------------------------------------------------------------------+ | '-(--16--)-' | | .-(--1--)-------. | +-+-+-+-CHARACTER-+--+---------------+-------------------------------+--+----------------+---------------------+------------+ | | | '-CHAR------' '-(--integer--)-' | +-FOR BIT DATA---+ | | | | '-+-+-CHARACTER-+--VARYING-+--(--integer--)--+-----------------+-' +-FOR SBCS DATA--+ | | | | | '-CHAR------' | '-allocate-clause-' +-FOR MIXED DATA-+ | | | | '-VARCHAR----------------' '-ccsid-clause---' | | | | .-(--1M--)-------------. | | | '-----+-+-CHARACTER-+--LARGE OBJECT-+------+----------------------+--+-----------------+--+----------------+-' | | | '-CHAR------' | '-(--integer--+---+--)-' '-allocate-clause-' +-FOR SBCS DATA--+ | | '-CLOB------------------------' +-K-+ +-FOR MIXED DATA-+ | | +-M-+ '-ccsid-clause---' | | '-G-' | | .-(--1--)-------. | +-+---GRAPHIC----+---------------+----------------------------+--+--------------+-------------------------------------------+ | | '-(--integer--)-' | '-ccsid-clause-' | | +-+-GRAPHIC VARYING-+--(--integer--)--+-----------------+---+ | | | '-VARGRAPHIC------' '-allocate-clause-' | | | | .-(--1M--)-------------. | | | '---DBCLOB----+----------------------+--+-----------------+-' | | '-(--integer--+---+--)-' '-allocate-clause-' | | +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-+-NATIONAL CHARACTER-+--+---------------+-------------------------------+---------------------+--+------------------+-+ | | | +-NATIONAL CHAR------+ '-(--integer--)-' | | '-normalize-clause-' | | | | '-NCHAR--------------' | | | | | '-+-+-NATIONAL CHARACTER-+--VARYING-+--(--integer--)--+-----------------+-' | | | | | +-NATIONAL CHAR------+ | '-allocate-clause-' | | | | | '-NCHAR--------------' | | | | | '-NVARCHAR------------------------' | | | | .-(--1M--)-------------. | | | '-----+-+-NATIONAL CHARACTER-+--LARGE OBJECT-+------+----------------------+--+-----------------+-' | | | '-NCHAR--------------' | '-(--integer--+---+--)-' '-allocate-clause-' | | '-NCLOB--------------------------------' +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-BINARY--+---------------+------------------------------+-----------------+--------------------------------------------+ | | | '-(--integer--)-' | | | | | '-+-BINARY VARYING-+--(--integer--)--+-----------------+-' | | | | '-VARBINARY------' '-allocate-clause-' | | | | .-(--1M--)-------------. | | | '---+-BLOB----------------+----+----------------------+--+-----------------+-' | | '-BINARY LARGE OBJECT-' '-(--integer--+---+--)-' '-allocate-clause-' | | +-K-+ | | +-M-+ | | '-G-' | +-+-DATE-------------------+------------------------------------------------------------------------------------------------+ | | .-(--0--)-. | | | +-TIME--+---------+------+ | | | .-(--6--)-. | | | '-TIMESTAMP--+---------+-' | | .-(--200--)-----. | +---DATALINK--+---------------+--+-----------------+--+--------------+------------------------------------------------------+ | '-(--integer--)-' '-allocate-clause-' '-ccsid-clause-' | +---ROWID-------------------------------------------------------------------------------------------------------------------+ '---XML--+-----------------+--+--------------+------------------------------------------------------------------------------' '-allocate-clause-' '-ccsid-clause-'
allocate-clause |--ALLOCATE--(--integer--)--------------------------------------| ccsid-clause |--CCSID--integer--+------------------+-------------------------| '-normalize-clause-' normalize-clause .-NOT NORMALIZED-. |--+-NORMALIZED-----+-------------------------------------------| default-clause .-WITH-. |--+------+--DEFAULT--+-------------------------------------------------+--| +-constant----------------------------------------+ +-USER--------------------------------------------+ +-NULL--------------------------------------------+ +-CURRENT_DATE------------------------------------+ +-CURRENT_TIME------------------------------------+ +-CURRENT_TIMESTAMP-------------------------------+ '-cast-function-name--(--+-constant----------+--)-' +-USER--------------+ +-CURRENT_DATE------+ +-CURRENT_TIME------+ '-CURRENT_TIMESTAMP-' identity-options |--AS IDENTITY--+------------------------------------------------------+--| | .--------------------------------------------. | | V .-1----------------. (1) | | '-(----+-START WITH--+-numeric-constant-+---+-----+--)-' | .-1----------------. | +-INCREMENT BY--+-numeric-constant-+-+ | .-NO MINVALUE----------------. | +-+-MINVALUE--numeric-constant-+-----+ | .-NO MAXVALUE----------------. | +-+-MAXVALUE--numeric-constant-+-----+ | .-NO CYCLE-. | +-+-CYCLE----+-----------------------+ | .-CACHE--20------. | +-+-NO CACHE-------+-----------------+ | '-CACHE--integer-' | | .-NO ORDER-. | '-+-ORDER----+-----------------------'
as-row-change-timestamp-clause |--FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP---------------| column-constraint |--+-----------------------------+--+-+-PRIMARY KEY-+--------------+--| '-CONSTRAINT--constraint-name-' | '-UNIQUE------' | +-references-clause------------+ '-CHECK--(--check-condition--)-' datalink-options .-LINKTYPE URL-. .-NO LINK CONTROL--------------------------. |--+--------------+--+------------------------------------------+--| '-FILE LINK CONTROL--+-file-link-options-+-' '-MODE DB2OPTIONS---' file-link-options .--------------------------------------. V | (1) |------+-INTEGRITY ALL----------------+---+---------------------| +-+-READ PERMISSION FS-+-------+ | '-READ PERMISSION DB-' | +-+-WRITE PERMISSION FS------+-+ | '-WRITE PERMISSION BLOCKED-' | +-RECOVERY NO------------------+ '-+-ON UNLINK RESTORE-+--------' '-ON UNLINK DELETE--'
as-result-table |--+------------------------------------------------------------+--> '-(--column-name--+-------------------------------------+--)-' | .-COLUMN-. | '-FOR--+--------+--system-column-name-' >--AS--(--select-statement--)--+-WITH NO DATA-+-----------------| '-WITH DATA----' copy-options .---------------------------------------------------------------. | .-COLUMN ATTRIBUTES-. | | .-EXCLUDING IDENTITY--+-------------------+-. | V | .-COLUMN ATTRIBUTES-. | | |----+-+-INCLUDING IDENTITY--+-------------------+-+-------------+-+--| | .-COLUMN-. | | .-EXCLUDING--+--------+--DEFAULTS-. | | | .-COLUMN-. | | +-+-INCLUDING--+--------+--DEFAULTS-+-----------------------+ | '-USING TYPE DEFAULTS-------------' | | .-COLUMN ATTRIBUTES-. | | .-EXCLUDING IMPLICITLY HIDDEN -+-------------------+-. | | | .-COLUMN ATTRIBUTES-. | | +-+-INCLUDING IMPLICITLY HIDDEN -+-------------------+-+----+ | .-COLUMN ATTRIBUTES-. | | .-EXCLUDING ROW CHANGE TIMESTAMP -+-------------------+-. | | | .-COLUMN ATTRIBUTES-. | | '-+-INCLUDING ROW CHANGE TIMESTAMP -+-------------------+-+-'
unique-constraint |--+-----------------------------+--+-PRIMARY KEY-+--(----------> '-CONSTRAINT--constraint-name-' '-UNIQUE------' .-,-----------. V | >----column-name-+--)-------------------------------------------| referential-constraint |--+-----------------------------+--FOREIGN KEY-----------------> '-CONSTRAINT--constraint-name-' .-,-----------. V | >--(----column-name-+--)--references-clause---------------------| references-clause |--REFERENCES--table-name--+-----------------------+------------> | .-,-----------. | | V | | '-(----column-name-+--)-' .-ON DELETE NO ACTION--------. .-ON UPDATE NO ACTION-. (1) >--+----------------------------+--+---------------------+------| '-ON DELETE--+-RESTRICT----+-' '-ON UPDATE RESTRICT--' +-CASCADE-----+ +-SET NULL----+ '-SET DEFAULT-' check-constraint |--+-----------------------------+------------------------------> '-CONSTRAINT--constraint-name-' >--CHECK--(--check-condition--)---------------------------------| distribution-clause |--IN--NODEGROUP----nodegroup-name----+-------------------------------------------+--| | .-,-----------. | | V | | '-DISTRIBUTE BY HASH--(----column-name-+--)-'
partitioning-clause .-RANGE-. |--PARTITION BY--+-+-------+--range-partition-spec-+------------| '-HASH--hash-partition-spec-------' range-partition-spec .-,--------------------. .-,-----------------. V | V | |--(----partition-expression-+--)--(----partition-element-+--)--| partition-expression .-NULLS LAST--. |--column-name--+-------------+---------------------------------| '-NULLS FIRST-' partition-element |--+-+---------------------------+--boundary-spec--+------------------+-+--| | '-PARTITION--partition-name-' '-media-preference-' | | (1) | '-boundary-spec--EVERY--(--integer-constant--+--------+--)-----------' +-DAY----+ +-DAYS---+ +-MONTH--+ +-MONTHS-+ +-YEAR---+ '-YEARS--' boundary-spec |--+-----------------+--+---------------+-----------------------| '-starting-clause-' '-ending-clause-' starting-clause .-,------------. .-FROM-. V | .-INCLUSIVE-. |--STARTING--+------+--+-(----+-constant-+-+--)-+--+-----------+--| | +-MINVALUE-+ | '-EXCLUSIVE-' | '-MAXVALUE-' | '-+-constant-+-----------' +-MINVALUE-+ '-MAXVALUE-'
ending-clause .-,------------. .-AT-. V | .-INCLUSIVE-. |--ENDING--+----+--+-(----+-constant-+-+--)-+--+-----------+----| | +-MINVALUE-+ | '-EXCLUSIVE-' | '-MAXVALUE-' | '-+-constant-+-----------' +-MINVALUE-+ '-MAXVALUE-' hash-partition-spec .-,-----------. V | |--(----column-name-+--)--INTO--integer--PARTITIONS-------------| materialized-query-definition |--+------------------------------------------------------------+--> '-(--column-name--+-------------------------------------+--)-' | .-COLUMN-. | '-FOR--+--------+--system-column-name-' >--AS--(--select-statement--)--refreshable-table-options--------| refreshable-table-options .----------------------------------------. V (1) | |--+-DATA INITIALLY DEFERRED--+--REFRESH DEFERRED--------+-MAINTAINED BY USER-------------+-+--| '-DATA INITIALLY IMMEDIATE-' | .-ENABLE QUERY OPTIMIZATION--. | '-+-DISABLE QUERY OPTIMIZATION-+-'
If SQL names were specified, the table will be created in the schema specified by the implicit or explicit qualifier.
If system names were specified, the table will be created in the schema that is specified by the qualifier. If not qualified:
Defines the attributes of a column. There must be at least one column definition and no more than 8000 column definitions.
The sum of the row buffer byte counts of the
columns must not be greater than 32766 or, if a VARCHAR, VARGRAPHIC,
or VARBINARY column is specified, 32740. Additionally, if a LOB or
XML column is specified, the sum of the row data byte counts of the
columns must not be greater than 3 758 096 383 at the time of insert
or update. For information about the byte counts of columns according
to data type, see Maximum row sizes.
If the system-column-name is not specified, and the column-name is not a valid system-column-name, a system column name is generated. For more information about how system column names are generated, see Rules for Column Name Generation.
You can use DECIMAL(p) for DECIMAL(p,0), and DECIMAL for DECIMAL(5,0).
You can use NUMERIC(p) for NUMERIC(p,0), and NUMERIC for NUMERIC(5,0).
The maximum length must be in the range of 1 through 1 073 741 823. If the length specification is omitted, a length of 1 megabyte is assumed. A DBCLOB is not allowed in a distributed table.
The maximum length must be in the range of 1 through 1 073 741 823. If the length specification is omitted, a length of 1 megabyte is assumed. The CCSID is 1200. An NCLOB is not allowed in a distributed table.
A DATALINK value is an encapsulated value with a set of built-in scalar functions. The DLVALUE function creates a DATALINK value. The following functions can be used to extract attributes from a DATALINK value.
A DataLink cannot be part of any index. Therefore, it cannot be included as a column of a primary key, foreign key, or unique constraint.
XML
For
an XML document. Only well-formed documents can be inserted into an
XML column. The CCSID for the column cannot be 65535. The maximum
length of the column is always 2 147 483 647 bytes.
An XML column has the following restrictions:
Specifies
for VARCHAR, VARGRAPHIC, VARBINARY, XML, and LOB types the space to
be reserved for the column in each row. Column values with lengths
less than or equal to the allocated value are stored in the fixed-length
portion of the row. Column values with lengths greater than the allocated
value are stored in the variable-length portion of the row and require
additional input/output operations to retrieve. The allocated value
may range from 1 to maximum length of the string, subject to the maximum
row buffer size limit. For information about the maximum row buffer
size, see Maximum row sizes. If FOR MIXED
DATA or a mixed data CCSID is specified, the range is 4 to the maximum
length of the string. If the allocated length specification is omitted,
an allocated length of 0 is assumed. For VARGRAPHIC, the integer is
the number of DBCS or Unicode graphic characters. If a constant is
specified for the default value and the ALLOCATE length is less than
the length of the default value, the ALLOCATE length is assumed to
be the length of the default value.
For XML columns,
the CCSID must not be 65535. If a CCSID is not specified for an XML
column, the CCSID is established at the time the CREATE TABLE is executed
according to the SQL_XML_DATA_CCSID QAQQINI option setting. The default
CCSID is 1208. See XML Values for a
description of this option.
For a list of valid CCSIDs, see CCSID values. CCSID 1208 (UTF-8) or 1200 (UTF-16) data can contain combining characters. Combining character support allows a resulting character to be comprised of more than one character. After the first character, up to 300 different non-spacing accent characters (umlauts, accent, etc.) can follow in the data string. If the resulting character is one that is already defined in the character set, that character has more than one representation. Normalization replaces the string of combining characters with the hex value of the defined character. This ensures that the same character is represented in a single consistent way. If normalization is not performed, two strings that look identical will not compare equal.
For
an XML column, the default is NULL unless NOT NULL is specified; in
that case there is no default.
If a value is not specified following
the DEFAULT keyword, then: | Data type | Default value |
|---|---|
| Numeric | 0 |
| Fixed-length character or graphic string | Blanks |
| Fixed-length binary string | Hexadecimal zeros |
| Varying-length string | A string length of 0 |
| Date | The current date at the time of INSERT |
| Time | The current time at the time of INSERT |
| Timestamp | The current timestamp at the time of INSERT |
| Datalink | A value corresponding to DLVALUE('','URL','') |
| distinct-type | The default value of the corresponding source type of the distinct type. |
Omission of NOT NULL and DEFAULT from a column-definition is an implicit specification of DEFAULT NULL.
NULL is the only default value allowed for a datalink column.
| Data Type | Cast Function Name |
|---|---|
| Distinct type N based on a BINARY, VARBINARY, BLOB, CLOB, or DBCLOB | BINARY, VARBINARY, BLOB, CLOB, or DBCLOB * |
| Distinct type N based on a DATE, TIME, or TIMESTAMP | N (the user-defined cast function
that was generated when N was created) ** or |
| Distinct type N based on other data types | N (the user-defined cast function that was generated when N was created) ** |
| BINARY, VARBINARY, BLOB, CLOB, or DBCLOB | BINARY, VARBINARY, BLOB, CLOB, or DBCLOB * |
| DATE, TIME, or TIMESTAMP | DATE, TIME, or TIMESTAMP * |
| Notes:
* The name of the function must match the name of the data type (or the source type of the distinct type) with an implicit or explicit schema name of QSYS2. ** The name of the function must match the name of the distinct type for the column. If qualified with a schema name, it must be the same as the schema name for the distinct type. If not qualified, the schema name from function resolution must be the same as the schema name for the distinct type. |
|
If the value specified is not valid, an error is returned.
or updated
and a default value must be generated.
ALWAYS is the recommended
value.
or updated and
a default value must be generated, unless an explicit value is specified.
For a ROWID column, the database manager uses a specified value, but it must be a valid unique row ID value that was previously generated by the database manager or DB2® for i.
For an identity column or row change timestamp column, the database manager inserts or updates a specified value but does not verify that it is a unique value for the column unless the identity column or row change timestamp column has a unique constraint or a unique index that solely specifies the identity column or row change timestamp column.
distributed table
. 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, or NUMERIC with
a scale of zero, or a distinct type based on one of these data types).
If a DECIMAL or NUMERIC data type is specified, the precision must
not be greater than 31. An identity column is implicitly NOT NULL.
If a value is not explicitly specified when the identity column is defined, the default is the MINVALUE for an ascending sequence and the MAXVALUE for a descending sequence. This value is not necessarily the value that a sequence would cycle to after reaching the maximum or minimum value of the sequence. The START WITH clause can be used to start a sequence outside the range that is used for cycles. The range used for cycles is defined by MINVALUE and MAXVALUE.
If the value is zero or positive, the sequence of values for the identity column ascends. If the value is negative, the sequence of values descends.
If a value is not explicitly specified when the identity column is defined, this is the maximum value of the data type for an ascending sequence; or the START WITH value, or -1 if START WITH was not specified, for a descending sequence.
If a value is not explicitly specified when the identity column is defined, this is the START WITH value, or 1 if START WITH was not specified, for an ascending sequence; or the minimum value of the data type (and precision, if DECIMAL) for a descending sequence.
In certain situations, such as system failure, all cached identity column values that have not been used in committed statements are lost, and thus, will never be used. The value specified for the CACHE option is the maximum number of identity column values that could be lost in these situations.
When CYCLE is in effect, duplicate values can be generated by the database manager for an identity column. If a unique constraint or unique index exists on the identity column, and a non-unique value is generated for it, an error occurs.
If the clause is not specified, a unique constraint name is generated by the database manager.
This clause must not be specified in more
than one column definition and must not be specified at all if the
UNIQUE clause is specified in the column definition.
The
column must not be a LOB, DATALINK, or XML column. 
If
a sort sequence is specified, the column must not contain a field
procedure.
When a primary key is added, a CHECK constraint is implicitly added to enforce the rule that the NULL value is not allowed in the column that makes up the primary key.
This clause cannot be specified more than once
in a column definition and must not be specified if PRIMARY KEY is
specified in the column definition.
The column must
not be a LOB, DATALINK, or XML column. 
If a
sort sequence is specified, the column must not contain a field procedure.
is a declared global temporary table
or a distributed table.
The column cannot be a row change timestamp
column.
ROWID, XML, and DATALINK with FILE
LINK CONTROL columns cannot be referenced in a CHECK constraint. For
additional restrictions see, check-constraint.
FIELDPROC
Designates
an external-program-name as the field procedure exit routine
for the column. It must be an ILE program that does not contain SQL.
It cannot be a service program.
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, it is passed
to the field procedure for decoding.
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 supplied for the column in the
CREATE TABLE statement defines the data characteristics of the decoded
values.
A field procedure cannot be defined for a column
that is a ROWID or DATALINK or a distinct type based on a ROWID or
DATALINK. The column must not be an identity column or a row change
timestamp column. The column must not have a default value of CURRENT
DATE, CURRENT TIME, CURRENT TIMESTAMP, or USER. The nullability attribute
of the encoded and decoded form of the field must match. The column
cannot be referenced in a check condition, unless it is referenced
in a NULL predicate. If it is part of a foreign key, the corresponding
parent key column must use the same field procedure. See SQL Programming for
more details on how to create a field procedure.
If FILE LINK CONTROL is specified, each file can only be linked once. That is, its URL can only be specified in a single FILE LINK CONTROL column in a single table.
The use of LIKE is an implicit definition of n columns, where n is the number of columns in the identified table or view. The implicit definition includes the following attributes of the n columns (if applicable to the data type):
FIELDPROC (only copied for table-name)
If the LIKE clause is specified immediately following the table-name and not enclosed in parenthesis, the following column attributes are also included, otherwise they are not included (the default value, identity, row change timestamp, and hidden attributes can also be controlled by using the copy-options):
The implicit definition does not include any other optional attributes of the identified table or view. For example, the new table does not automatically include primary keys, foreign keys, or triggers. The new table has these and other optional attributes only if the optional clauses are explicitly specified.
If the specified table or view is a non-SQL created physical file or logical file, any non-SQL attributes are removed. For example, the date and time format will be changed to ISO.
If INCLUDING IDENTITY is not specified, the table will not have an identity column.
Do not specify INCLUDING COLUMN DEFAULTS, if you specify USING TYPE DEFAULTS.
If INCLUDING COLUMN DEFAULTS is not specified, the default values are not inherited.
| Data type | Default value |
|---|---|
| Numeric | 0 |
| Fixed-length character or graphic string | Blanks |
| Fixed-length binary string | Hexadecimal zeros |
| Varying-length string | A string length of 0 |
| Date | The current date at the time of INSERT |
| Time | The current time at the time of INSERT |
| Timestamp | The current timestamp at the time of INSERT |
| Datalink | A value corresponding to DLVALUE('','URL','') |
XML![]() |
There is no default value![]() |
| distinct-type | The default value of the corresponding source type of the distinct type. |
Do not specify USING TYPE DEFAULTS if INCLUDING COLUMN DEFAULTS is specified.
If INCLUDING IMPLICITLY HIDDEN COLUMN ATTRIBUTES is not specified, the table will not have any implicitly hidden columns.
If INCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES is not specified, the table will not have a row change timestamp column.
A list of column names must be specified if the result table of the select-statement has duplicate column names or an unnamed column. An unnamed column is a column derived from a constant, function, expression, or set operation (UNION or INTERSECT) that is not named using the AS clause of the select list.
If the system-column-name is not specified, and the column-name is not a valid system-column-name, a system column name is generated. For more information about how system column names are generated, see Rules for Table Name Generation.
The implicit definition includes the following attributes of the n columns (if applicable to the data type):
FIELDPROC
The following attributes are not included (they may be included by using the copy-options):
The implicit definition does not include any other optional attributes of the identified table or view. For example, the new table does not automatically include a primary key or foreign key from a table. The new table has these and other optional attributes only if the optional clauses are explicitly specified.
The select-statement must not refer to variables or include parameter markers.
The select-statement must not contain a PREVIOUS VALUE or a NEXT VALUE expression. The UPDATE, SKIP LOCKED DATA, and USE AND KEEP EXCLUSIVE LOCKS clauses may not be specified.
If the clause is not specified, a unique constraint name is generated by the database manager.
Each column-name must be an unqualified
name that identifies a column of the table. The same column must not
be identified more than once. The column must not be a LOB, DATALINK,
or XML column.
If a sort sequence is specified,
the column must not contain a field procedure.
The number of
identified columns must not exceed 120, and the sum of their byte
counts must not exceed 32766-n, where n is the number
of columns specified that allow nulls. For information about byte-counts
see Table 1.
The unique index is created as part of the system physical file, not a separate system logical file. When a primary key is added, a CHECK constraint is implicitly added to enforce the rule that the NULL value is not allowed in any of the columns that make up the primary key.
Each column-name must be an unqualified name
that identifies a column of the table. The same column must not be
identified more than once. The column must not be a LOB, DATALINK,
or XML column.
If a sort sequence is specified,
the column must not contain a field procedure.
The number of
identified columns must not exceed 120, and the sum of their byte
counts must not exceed 32766-n, where n is the number
of columns specified that allows nulls. For information about byte-counts
see Table 1.
A unique index on the identified column is created during the execution of the CREATE TABLE statement. The unique index is created as part of the system physical file, not as a separate system logical file.
If the clause is not specified, a unique constraint name is generated by the database manager.
FOREIGN KEY is not allowed if the table is a declared
global temporary table or a distributed table.
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 once. The column must not be a LOB, DATALINK,
or XML column and must not be a row change timestamp column.
If a sort sequence is specified, the column must not
contain a field procedure.
The number of identified columns must
not exceed 120, and the sum of their lengths must not exceed 32766-n,
where n is the number of columns specified that allow nulls.
must not identify a catalog table, a declared temporary
table, or a distributed table. If the parent is a partitioned table,
the unique index that enforces the parent unique constraint must be
non-partitioned.
A referential constraint is a duplicate if its foreign key, parent key, and parent table are the same as the foreign key, parent key, and parent table of a previously specified referential constraint. Duplicate referential constraints are allowed, but not recommended.
Let T2 denote the identified parent table and let T1 denote the table being created.
The specified foreign key must have the same number of columns as the parent key of T2. The description of the nth column of the foreign key and the description of the nth column of that parent key must have identical data types, lengths, CCSIDs, and FIELDPROCs.
The column must not be a LOB, DATALINK,
or XML column and must not be a row change timestamp column. 
If a sort sequence is specified, the column must not
contain a field procedure.
The number of identified columns must
not exceed 120, and the sum of their byte counts must not exceed 32766-n,
where n is the number of columns specified that allow nulls.
For information about byte-counts see Table 1. The list of column names must be identical to the list of column names in the primary key of T2 or a UNIQUE constraint that exists on T2. The names need not be specified in the same order as in the primary key; however, they must be specified in corresponding order to the list of columns in the foreign key clause. If a column name list is not specified, then T2 must have a primary key. Omission of the column name list is an implicit specification of the columns of that primary key.
The referential constraint specified by a FOREIGN KEY clause defines a relationship in which T2 is the parent and T1 is the dependent.
SET NULL must not be specified unless some column of the foreign key allows null values.
CASCADE must not be specified if T1 contains a DataLink column with FILE LINK CONTROL.
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.
SET
NULL is not allowed if the dependent table is a partitioned table
and a foreign key column is also a partitioning key.
SET DEFAULT is not allowed if the dependent table is
a partitioned table and a foreign key column is also a partitioning
key.
The update rule applies when a row of T2 is the object of an UPDATE or propagated update operation and that row has dependents in T1. Let p denote such a row of T2.
If the clause is not specified, a unique constraint name is generated by the database manager.
The check-condition is a search-condition except:
The result of any expression in the check-condition cannot
be a ROWID, XML, or DATALINK with FILE LINK CONTROL data type. It
cannot reference any column that has a field procedure, unless the
column is referenced in a NULL predicate.
Global variables
| ATAN2 | DECRYPT_DB | GETHINT | ROUND_TIMESTAMP |
CARDINALITY![]() |
DIFFERENCE | IDENTITY_VAL_LOCAL | SCORE![]() |
CONTAINS![]() |
DLURLCOMPLETE 1 | INSERT | SOUNDEX |
| CURDATE | DLURLPATH | MAX_CARDINALITY![]() |
TIMESTAMP_FORMAT |
| CURTIME | DLURLPATHONLY | MONTHNAME | TIMESTAMPDIFF |
| DATAPARTITIONNAME | DLURLSCHEME | MONTHS_BETWEEN | TRUNC_TIMESTAMP |
| DATAPARTITIONNUM | DLURLSERVER | NEXT_DAY | VARCHAR_FORMAT |
| DAYNAME | DLVALUE | NOW | WEEK_ISO |
| DBPARTITIONNAME | ENCRYPT_AES | RAISE_ERROR | XMLPARSE![]() |
| DECRYPT_BINARY | ENCRYPT_RC2 | RAND | XMLVALIDATE![]() |
| DECRYPT_BIT | ENCRYPT_TDES | REPEAT | XSLTRANSFORM![]() |
| DECRYPT_CHAR | GENERATE_UNIQUE | REPLACE | |
1 For DataLinks with an attribute of FILE LINK CONTROL and READ PERMISSION DB. |
|||
For more information about search-condition, see Search conditions. For more information about check constraints involving LOB data types and expressions, see the Database Programming topic collection.
Any changes made to the table by INSERT, DELETE, or UPDATE statements in the same unit of work after the table is created by this statement are not logged (journaled).
At the completion of the current unit of work, the NOT LOGGED INITIALLY attribute is deactivated and all operations that are done on the table in subsequent units of work are logged (journaled).
The NOT LOGGED INITIALLY option is useful for situations where a large result set needs to be created with data from an alternate source (another table or a file) and recovery of the table is not necessary. Using this option will save the overhead of logging (journaling) the data.
ACTIVATE NOT LOGGED INITIALLY is ignored if the table has a DATALINK column with FILE LINK CONTROL.
Indicates to the optimizer whether the cardinality of table table-name can vary significantly at run time. Volatility applies to the number of rows in the table, not to the table itself. The default is NOT VOLATILE.
Indicates the record format name of the table.
If a record format name is not specified, the format-name is the same as the system-object-name of the table.

Specifies the preferred storage media for the table or partition.

A LOB, DATALINK, XML,
or IDENTITY column is not allowed in a distributed table.
The DB2 Multisystem product must be installed to create a distributed table. For more information about distributed tables, see the DB2 Multisystem topic collection.
The
columns that make up the partitioning key must be a subset of the
columns that make up any unique constraints over the table. Floating
point, decimal floating-point, LOB, XML, DataLink, ROWID, and columns
that have a field procedure cannot be used in a partitioning key.
Specifies that ranges of column values are used to determine
the target data partition when inserting a row into the table. The
number of partitions must not exceed 256.
Decimal floating-point, LOB,
XML, DataLink, ROWID, row change timestamp columns, and any column
with a field procedure cannot be used in a partitioning key.
The number of identified columns must not exceed 120. The sum of length attributes of the columns must not be greater than 2000.
If the clause is not specified, a unique partition name is generated by the database manager.
The starting value of the first data partition is the specified STARTING value. The starting value of each subsequent partition is the starting value of the previous partition + integer-constant. If the starting-clause specified EXCLUSIVE, the starting value of every partition is EXCLUSIVE. Otherwise, the starting value of every partition is INCLUSIVE.
The ending value of every partition of the range is (start + integer-constant - 1). If the ending-clause specified EXCLUSIVE, the ending value of every partition is EXCLUSIVE. Otherwise, the ending value of every partition is INCLUSIVE.
The number of partitions added is determined by adding integer-constant repeatedly to the STARTING value until the ENDING value is reached. For example:
CREATE TABLE FOO
(A INT)
PARTITION BY RANGE(A)
(STARTING(1) ENDING(10) EVERY(2))
is equivalent to the following CREATE TABLE statement:
CREATE TABLE FOO
(A INT)
(PARTITION BY RANGE(A)
(STARTING(1) ENDING(2),
STARTING(3) ENDING(4),
STARTING(5) ENDING(6),
STARTING(7) ENDING(8),
STARTING(9) ENDING(10))
In the case of dates and timestamps, the EVERY value must be a labeled duration. For example:
CREATE TABLE FOO
(A DATE)
PARTITION BY RANGE(A)
(STARTING('2001-01-01') ENDING('2010-01-01') EVERY(3 MONTHS))
Specifies that the hash function is used to determine
the target data partition when inserting a row into the table.
The
columns that make up the partitioning key must be a subset of the
columns that make up any unique constraints over the table. Floating
point, decimal floating-point, LOB, XML, date, time, timestamp, DataLink,
ROWID, row change timestamp columns,
identity columns,
and
columns with a field procedure cannot be used in a partitioning key.
A list of column names must be specified if the result table of the select-statement has duplicate column names or an unnamed column. An unnamed column is a column derived from a constant, function, expression, or set operation (UNION or INTERSECT) that is not named using the AS clause of the select list.
If the system-column-name is not specified, and the column-name is not a valid system-column-name, a system column name is generated. For more information about how system column names are generated, see Rules for Table Name Generation.
The implicit definition includes the following attributes of the n columns (if applicable to the data type):
FIELDPROC
The following attributes are not included:
The implicit definition does not include any other optional attributes of the identified table or view. For example, the new table does not automatically include a primary key or foreign key from a table. The new table has these and other optional attributes only if the optional clauses are explicitly specified.
The select-statement must
not refer to variables or global variables, or include parameter markers.
If an expression in the SELECT clause of the select-statement is
not a column name, then the expression must not reference a column
with a field procedure.

The select-statement must not contain a PREVIOUS VALUE or a NEXT VALUE expression. The UPDATE, SKIP LOCKED DATA, and USE AND KEEP EXCLUSIVE LOCKS clauses may not be specified.
A materialized query table whose select-statement contains a GROUP BY clause is summarizing data from the tables referenced in the select-statement. Such a materialized query table is also known as a summary table. A summary table is a specialized type of materialized query table.
When a materialized query table is defined, the following select-statement restrictions apply:
When a materialized query table is defined with ENABLE QUERY OPTIMIZATION, the following additional select-statement restrictions apply:
Table attributes: Tables are created as physical files. When a table is created, the file wait time and record wait time attributes are set to the default that is specified on the WAITFILE and WAITRCD keywords of the Create Physical File (CRTPF) command.
SQL tables are created so that space used by deleted rows will be reclaimed by future insert requests. This attribute can be changed via the command CHGPF and specifying the REUSEDLT(*NO) parameter. For more information about the CHGPF command, see CL Reference.
A distributed table is created on all of the servers across which the table is distributed. For more information about distributed tables, seeDB2 Multisystem.
Table journaling: When a table is created, journaling may be automatically started.
Table ownership: If SQL names were specified:
If system names were specified, the owner of the table is the user profile or group user profile of the job executing the statement.
Table authority: If SQL names are used, tables are created with the system authority of *EXCLUDE to *PUBLIC. If system names are used, tables are created with the authority to *PUBLIC as determined by the create authority (CRTAUT) parameter of the schema.
If the owner of the table is a member of a group profile (GRPPRF keyword) and group authority is specified (GRPAUT keyword), that group profile will also have authority to the table.
Using an identity column: When a table has an identity column, the database manager 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 the database manager generates. ROWID columns 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 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 (using RMVJRNCHG), it is possible that a large gap in the sequence of generated values for the identity column might result. For example, assume a table has an identity column that has an incremental value of 1 and that the last generated value at time T1 was 100 and the database manager subsequently generates values up to 1000. Now, assume that the table 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.
When CYCLE is specified duplicate values for a column may be generated even when the column is GENERATED ALWAYS, unless a unique constraint or unique index is defined on the column.
Creating materialized query tables: To ensure that the materialized query table has data before being used by a query:
The isolation level at the time when the CREATE TABLE statement is executed is the isolation level for the materialized query table. The isolation-clause can be used to explicitly specify the isolation level.
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
statement, CREATE INDEX statement, ALTER TABLE statement, INSERT statement,
MERGE statement, or UPDATE 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'.
Partitioned table performance: The larger the number of partitions in a partitioned table, the greater the overhead in SQL data change and SQL data statements. You should create a partitioned table with the minimum number of partitions that are required to minimize this overhead. It is also highly recommended that a parallelism degree greater than one be considered when accessing a partitioned table.
Syntax alternatives: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:
If a partition-number is not specified, a unique partition number is generated by the database manager.
There are two maximum row size restrictions referred to in the description of column-definition.
The maximum row buffer size is 32766 or, if a VARCHAR,
VARGRAPHIC, VARBINARY, LOB, or XML column is specified, 32740.
The maximum row data size is 3 758 096 383, if a LOB
or XML column is specified. If a LOB or XML column is not specified,
then the maximum row data size is 32766 or, if a VARCHAR, VARGRAPHIC,
or VARBINARY column is specified, 32740.
To determine the length of a row buffer and row data or both, add the corresponding length of each column of that row based on the byte counts of the data type.
The follow table gives the byte counts of columns
by data type for columns that do not allow null values. If any column
allows null values, one byte is required for every eight columns.
A column that has a field procedure could have a different count based
on the result of the field procedure.
| Data Type | Row Buffer Byte Count | Row Data Byte Count |
|---|---|---|
| SMALLINT | 2 | 2 |
| INTEGER | 4 | 4 |
| BIGINT | 8 | 8 |
| DECIMAL( p, s) | The integral part of (p/2) + 1 | The integral part of (p/2) + 1 |
| NUMERIC( p, s) | p | p |
| FLOAT (single precision) | 4 | 4 |
| FLOAT (double precision) | 8 | 8 |
| DECFLOAT(16) | 8 | 8 |
| DECFLOAT(34) | 16 | 16 |
| CHAR( n) | n | n |
| VARCHAR( n) | n+2 | n+2 |
| CLOB( n) | 29+pad | n+29 |
| GRAPHIC(n) | n*2 | n*2 |
| VARGRAPHIC (n) | n*2+2 | n*2+2 |
| DBCLOB( n) | 29+pad | n*2+29 |
| BINARY( n) | n | n |
| VARBINARY( n) | n+2 | n+2 |
| BLOB( n) | 29+pad | n+29 |
| DATE | 10 | 4 |
| TIME | 8 | 3 |
| TIMESTAMP | 26 | 10 |
| DATALINK( n) | n+24 | n+24 |
| ROWID | 42 | 28 |
XML ![]() |
29+pad![]() |
2 147 483 647![]() |
| distinct-type | The byte count for the source type. | The byte count for the source type. |
| Notes:
pad is a value from 1 to 15 necessary for boundary alignment. |
||
The non-standard syntax of LONG VARCHAR, LONG VARGRAPHIC,
and LONG VARBINARY is supported, but deprecated. The alternative standard
syntax of VARCHAR(integer), VARGRAPHIC(integer), and VARBINARY(integer)
is preferred. VARCHAR(integer), VARGRAPHIC(integer), and VARBINARY(integer)
are recommended. After the CREATE TABLE statement is processed, the
database manager considers a LONG VARCHAR column to be VARCHAR, a
LONG VARGRAPHIC column to be VARGRAPHIC, and a LONG VARBINARY column
to be VARBINARY. The maximum length is calculated in a product-specific
fashion that is not portable.
LONG VARBINARY 1 
For
a varying length binary string whose maximum length is determined
by the amount of space available in the row.
The maximum length of a LONG column is determined as follows. Let:
i
be the sum of the row buffer byte
counts of all columns in the table that are not LONG VARCHAR, LONG
VARGRAPHIC, or LONG VARBINARY
j
be the number of LONG VARCHAR, LONG
VARGRAPHIC, and LONG VARBINARY columns in the table
The length of each LONG VARCHAR and LONG VARBINARY
column is INTEGER((32716 - i-((k+7)/8))/j).
The length of each LONG VARGRAPHIC column is determined by taking the length calculated for a LONG VARCHAR column and dividing it by 2. The integer portion of the result is the length.
There are specific instances when the system generates a system table, view, index, or column name. These instances and the name generation rules are described in the following sections.
A system-column-name is generated if the system-column-name is not specified when a table or view is created and the column-name is not a valid system-column-name.
If the column-name does not contain special characters and is longer than 10 characters, a 10-character system-column-name will be generated as:
For example:
The system-column-name for LONGCOLUMNNAME would be LONGC00001
If the column name is delimited:
For example:
The system-column-name for "abc" would be ABC__00001 The system-column-name for "COL2.NAME" would be COL2_00001 The system-column-name for "C 3" would be C_3__00001 The system-column-name for "??" would be Q____00001 The system-column-name for "*column1" would be QCOLU00001
A system name will be generated if a table, view, alias, or index is created with either:
The SQL name or its corresponding system name may both be used in SQL statements to access the file once it is created. However, the SQL name is only recognized by DB2 for i and the system name must be used in other environments.
There are two separate methods for generating the system name:
The data area is subject to the following restrictions:
If any of the above conditions are not satisfied or any error occurs while accessing the starting value in the data area, the default name generation rules will be used as if the data area did not exist at all.
If the data area meets all of the restrictions above, the generated name will be the same as if the default name generation rules below except that after the first 5 (or 4) characters of the name, the unique number will initially contain the 5 digits specified in the data area (instead of '00001' or '0001').
For example, if the value of the data area was '?????00999':
The system name for "??" would be "__00999" The system name for "longtablename" would be "lon00999" The system name for "LONGTableName" would be LONG00999 The system name for "A b " would be "A_b00999"
If the name does not contain special characters and is longer than 10 characters, a 10-character system name will be generated as:
For example:
The system name for LONGTABLENAME would be LONGT00001
If the SQL name contains special characters, the system name is generated as:
In addition:
For example:
The system name for "??" would be "__0001" The system name for "longtablename" would be "long0001" The system name for "LONGTableName" would be LONG0001 The system name for "A b " would be "A_b0001"
SQL ensures the system name is unique by searching the cross reference file. If the name already exists in the cross reference file, the number is incremented until the name is no longer a duplicate.
If a unique name cannot be determined using the above rules, an additional character is added to the counter in the name, and the number is incremented until a unique name can be found or the range is exhausted. For example, if creating "longtablename" and names "long0001" through "long9999" already exist, the name would become "lon00001".
Example 1: Given administrative authority, create a table named ‘ROSSITER.INVENTORY' with the following columns:
CREATE TABLE ROSSITER.INVENTORY
(PARTNO SMALLINT NOT NULL,
DESCR VARCHAR(24),
QONHAND INT)
Example 2: Create a table named DEPARTMENT with the following columns:
The primary key is column DEPTNO.
CREATE TABLE DEPARTMENT (DEPTNO CHAR(3) NOT NULL, DEPTNAME VARCHAR(36) NOT NULL, MGRNO CHAR(6), ADMRDEPT CHAR(3) NOT NULL, LOCATION CHAR(16), PRIMARY KEY(DEPTNO))
Example 3: Create a table named REORG_PROJECTS which has the same column definitions as the columns in the view PRJ_LEADER.
CREATE TABLE REORG_PROJECTS
LIKE PRJ_LEADER
Example 4: Create an EMPLOYEE2 table with an identity column named EMP_NO. Define the identity column so that DB2 for i will always generate the values for the column. Use the default value, which is 1, for the first value that should be assigned and for the incremental difference between the subsequently generated consecutive numbers.
CREATE TABLE EMPLOYEE2
( EMPNO INTEGER GENERATED ALWAYS AS IDENTITY,
ID SMALLINT,
NAME CHAR(30),
SALARY DECIMAL(5,2),
DEPTNO SMALLINT)
Example 5: Assume a very large transaction table named TRANS contains one row for each transaction processed by a company. The table is defined with many columns. Create a materialized query table for the TRANS table that contains daily summary data for the date and amount of a transaction.
CREATE TABLE STRANS
AS (SELECT YEAR AS SYEAR, MONTH AS SMONTH, DAY AS SDAY, SUM(AMOUNT) AS SSUM
FROM TRANS
GROUP BY YEAR, MONTH, DAY )
DATA INITIALLY DEFERRED
REFRESH DEFERRED
MAINTAINED BY USER