The CREATE TABLE statement defines a table. The definition must include its name and the names and attributes of its columns. The definition can include other attributes of the table, such as its primary key or check constraints.
To create a created temporary table, use the CREATE GLOBAL TEMPORARY TABLE statement. To declare a declared temporary table, use the DECLARE GLOBAL TEMPORARY TABLE statement.
This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).
If a subtable is being defined, the authorization ID must be the same as the owner of the root table of the table hierarchy.
>>-CREATE TABLE--table-name-------------------------------------> >--+-| element-list |----------------------------+--●-----------> +-OF--type-name1--+-------------------------+-+ | '-| typed-table-options |-' | +-LIKE--+-table-name1-+--+------------------+-+ | +-view-name---+ '-| copy-options |-' | | '-nickname----' | +-| as-result-table |--+------------------+---+ | '-| copy-options |-' | +-| materialized-query-definition |-----------+ '-| staging-table-definition |----------------' >--+----------------------------------------------------------------------+--> | .-,-------------------------. | | .-DIMENSIONS-. V | | '-ORGANIZE BY--+-+------------+--(----+-column-name-----------+-+--)-+-' | | .-,-----------. | | | | V | | | | '-(----column-name-+--)-' | '-KEY SEQUENCE--| sequence-key-spec |-----------------' .-DATA CAPTURE NONE----. >--●--+----------------------+--●--+------------------------+---> '-DATA CAPTURE CHANGES-' '-| tablespace-clauses |-' >--●--+-------------------------+--●----------------------------> '-| distribution-clause |-' .-COMPRESS NO--. >--+-------------------------+--●--+--------------+-------------> '-| partitioning-clause |-' '-COMPRESS YES-' >--●--+-------------------+--●--+-----------------------+-------> '-VALUE COMPRESSION-' '-WITH RESTRICT ON DROP-' >--●--+----------------------+--●--+--------------------+-------> '-NOT LOGGED INITIALLY-' '-CCSID--+-ASCII---+-' '-UNICODE-' >--●--+------------------------------+--●-----------------------> '-SECURITY POLICY--policy name-' >--+----------------------------------------------------------------+->< | .-,-------------------------------------------. | | V .-ADD-. | | '-OPTIONS--(----+-----+--table-option-name--string-constant-+--)-' element-list .-,------------------------------. V | |--(----+-| column-definition |------+-+--)---------------------| +-| unique-constraint |------+ +-| referential-constraint |-+ '-| check-constraint |-------' column-definition |--column-name--+-------------------+--+--------------------+---| | (1) | '-| column-options |-' '-| data-type |-----' data-type |--+-| built-in-type |----+-------------------------------------| +-distinct-type-name---+ +-structured-type-name-+ '-REF--(type-name2)----' 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 BYTE)------------. | +-+-+-+-CHARACTER-+--+---------------------+----------+--+--------------------+-+-+ | | | '-CHAR------' | .-BYTE-. | | | (2) | | | | | | '-(integer-+------+-)-' | '-------FOR BIT DATA-' | | | | | .-BYTE-. | | | | | '-+-VARCHAR----------------+--(integer-+------+-)-' | | | | '-+-CHARACTER-+--VARYING-' | | | | '-CHAR------' | | | | .-(1M)-------------. | | | '-+-CLOB------------------------+--+------------------+-----------------------' | | '-+-CHARACTER-+--LARGE OBJECT-' '-(integer-+---+-)-' | | '-CHAR------' +-K-+ | | +-M-+ | | '-G-' | | .-(1)-------. | +-+-GRAPHIC--+-----------+-------+------------------------------------------------+ | | '-(integer)-' | | | +-VARGRAPHIC--(integer)--------+ | | | .-(1M)-------------. | | | '-DBCLOB--+------------------+-' | | '-(integer-+---+-)-' | | +-K-+ | | +-M-+ | | '-G-' | | .-(1)-------. | +-+-+-+-NCHAR-------------------+--+-----------+------+-------+-------------------+ | | | '-NATIONAL--+-CHAR------+-' '-(integer)-' | | | | | | '-CHARACTER-' | | | | | '-+-NVARCHAR-------------------------+--(integer)-' | | | | +-NCHAR VARYING--------------------+ | | | | '-NATIONAL--+-CHAR------+--VARYING-' | | | | '-CHARACTER-' | | | | .-(1M)-------------. | | | '-+-NCLOB---------------------------+--+------------------+-' | | +-NCHAR LARGE OBJECT--------------+ '-(integer-+---+-)-' | | '-NATIONAL CHARACTER LARGE OBJECT-' +-K-+ | | +-M-+ | | '-G-' | | .-(1M)-------------. | +-+-BLOB----------------+--+------------------+-----------------------------------+ | '-BINARY LARGE OBJECT-' '-(integer-+---+-)-' | | +-K-+ | | +-M-+ | | '-G-' | +-+-DATE-------------------------+------------------------------------------------+ | +-TIME-------------------------+ | | | .-(--6--)-------. | | | '-TIMESTAMP--+---------------+-' | | '-(--integer--)-' | +-XML-----------------------------------------------------------------------------+ | .-SYSPROC.-. (3) (4) | '-+----------+--DB2SECURITYLABEL--------------------------------------------------' column-options .--------------------------------------------------------------------------------------------------. V | |----+----------------------------------------------------------------------------------------------+-+--| +-NOT NULL-------------------------------------------------------------------------------------+ | (5) | +-| lob-options |------------------------------------------------------------------------------+ | (6) | +-SCOPE--+-typed-table-name-+------------------------------------------------------------------+ | '-typed-view-name--' | +-+-----------------------------+--+-+-PRIMARY KEY-+-----------------------------------------+-+ | '-CONSTRAINT--constraint-name-' | '-UNIQUE------' | | | +-| references-clause |-----------------------------------+ | | '-CHECK--(--check-condition--)--| constraint-attributes |-' | +-| generated-column-definition |--------------------------------------------------------------+ | (7) | +-INLINE LENGTH--integer-----------------------------------------------------------------------+ +-COMPRESS SYSTEM DEFAULT----------------------------------------------------------------------+ | .-COLUMN-. | +-+--------+--SECURED WITH--security-label-name------------------------------------------------+ | .-NOT HIDDEN------------. | | | (8) | | '-+-IMPLICITLY HIDDEN-----+--------------------------------------------------------------------' lob-options .-LOGGED-----. .-NOT COMPACT-. |--●--+------------+--●--+-------------+--●---------------------| '-NOT LOGGED-' '-COMPACT-----' references-clause |--REFERENCES--+-table-name-+--+-----------------------+--------> '-nickname---' | .-,-----------. | | V | | '-(----column-name-+--)-' >--| rule-clause |--| constraint-attributes |-------------------| rule-clause .-ON DELETE NO ACTION-----. .-ON UPDATE NO ACTION-. |--●--+-------------------------+--●--+---------------------+--●--| '-ON DELETE--+-RESTRICT-+-' '-ON UPDATE RESTRICT--' +-CASCADE--+ '-SET NULL-' constraint-attributes .-ENFORCED-----. .-ENABLE QUERY OPTIMIZATION--. |--●--+--------------+--●--+----------------------------+--●----| '-NOT ENFORCED-' '-DISABLE QUERY OPTIMIZATION-' generated-column-definition |--+-| default-clause |------------------------------------------------+--| | .-ALWAYS-----. | +-GENERATED--+------------+--+-| identity-options |---------------+-+ | '-BY DEFAULT-' '-| as-row-change-timestamp-clause |-' | | .-ALWAYS-. | '-GENERATED--+--------+--AS--(--generation-expression--)------------' default-clause .-WITH-. |--+------+--DEFAULT--+--------------------+--------------------| '-| default-values |-' default-values |--+-constant-------------------------------------------+-------| +-datetime-special-register--------------------------+ +-user-special-register------------------------------+ +-CURRENT SCHEMA-------------------------------------+ +-NULL-----------------------------------------------+ +-cast-function--(--+-constant------------------+--)-+ | +-datetime-special-register-+ | | +-user-special-register-----+ | | '-CURRENT SCHEMA------------' | +-EMPTY_CLOB()---------------------------------------+ +-EMPTY_DBCLOB()-------------------------------------+ +-EMPTY_NCLOB()--------------------------------------+ '-EMPTY_BLOB()---------------------------------------' identity-options |--AS IDENTITY--+----------------------------------------------------------+--| | .--------------------------------------------. | | V (9) .-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-constant-' | | .-NO ORDER-. | '-+-ORDER----+-----------------------' as-row-change-timestamp-clause (10) |---------FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP--------| unique-constraint |--+-----------------------------+--+-UNIQUE------+-------------> '-CONSTRAINT--constraint-name-' '-PRIMARY KEY-' .-,-----------. V | >--(----column-name-+--)----------------------------------------| referential-constraint |--+-----------------------------+--FOREIGN KEY-----------------> '-CONSTRAINT--constraint-name-' .-,-----------. V | >--(----column-name-+--)--| references-clause |-----------------| check-constraint |--+-----------------------------+------------------------------> '-CONSTRAINT--constraint-name-' >--CHECK--(--| check-condition |--)-----------------------------> >--| constraint-attributes |------------------------------------| check-condition |--+-search-condition----------+--------------------------------| '-| functional-dependency |-' functional-dependency |--+-column-name-----------+--DETERMINED BY--+-column-name-----------+--| | .-,-----------. | | .-,-----------. | | V | | | V | | '-(----column-name-+--)-' '-(----column-name-+--)-' typed-table-options |--+---------------------------+--+------------------------+----| +-HIERARCHY--hierarchy-name-+ '-| typed-element-list |-' '-| under-clause |----------' under-clause |--UNDER--supertable-name--INHERIT SELECT PRIVILEGES------------| typed-element-list .-,-----------------------------. V | |--(----+-| OID-column-definition |-+-+--)----------------------| +-| with-options |----------+ +-| unique-constraint |-----+ '-| check-constraint |------' OID-column-definition |--REF IS--OID-column-name--USER GENERATED----------------------| with-options |--column-name--WITH OPTIONS--| column-options |----------------| as-result-table |--+-----------------------+--AS--(--fullselect--)--------------> | .-,-----------. | | V | | '-(----column-name-+--)-' >--WITH NO DATA-------------------------------------------------| materialized-query-definition |--+-----------------------+--AS--(--fullselect--)--------------> | .-,-----------. | | V | | '-(----column-name-+--)-' >--| refreshable-table-options |--------------------------------| copy-options |--●--+-------------------------------------+--●----------------> | .-COLUMN-. | '-+-INCLUDING-+--+--------+--DEFAULTS-' '-EXCLUDING-' .-COLUMN ATTRIBUTES-. .-EXCLUDING IDENTITY--+-------------------+-. >--+-------------------------------------------+--●-------------| | .-COLUMN ATTRIBUTES-. | '-INCLUDING IDENTITY--+-------------------+-' refreshable-table-options |--DATA INITIALLY DEFERRED--REFRESH--+-DEFERRED--+--●-----------> '-IMMEDIATE-' .-ENABLE QUERY OPTIMIZATION--. >--+----------------------------+--●----------------------------> '-DISABLE QUERY OPTIMIZATION-' >--+-----------------------------------+--●---------------------| '-MAINTAINED BY--+-SYSTEM---------+-' +-USER-----------+ '-FEDERATED_TOOL-' staging-table-definition |--+-------------------------------+--FOR--table-name2----------> | .-,-------------------. | | V | | '-(----staging-column-name-+--)-' >--PROPAGATE IMMEDIATE------------------------------------------| sequence-key-spec .-,-----------------------------------------------------------------------. V .-AT-. | |--(----column-name--+------------------------------+--ENDING--+----+--constant-+--)--> | .-FROM-. | '-STARTING--+------+--constant-' >--+-ALLOW OVERFLOW----+--+------------------+------------------| '-DISALLOW OVERFLOW-' '-PCTFREE--integer-' tablespace-clauses |--+---------------------------------------+--●-----------------> | .-,---------------. | | V | .-CYCLE----. | '-IN----tablespace-name-+--+----------+-' '-NO CYCLE-' >--+--------------------------------+---------------------------> | (11) | '-INDEX IN--tablespace-name------' >--+------------------------------+-----------------------------| | .-,---------------. | | V | | '-LONG IN----tablespace-name-+-' distribution-clause .-,-----------. .-HASH-. V | |--DISTRIBUTE BY--+-+------+--(----column-name-+--)-+-----------| '-REPLICATION---------------------' partitioning-clause .-RANGE-. |--PARTITION BY--+-------+--| range-partition-spec |------------| range-partition-spec .-,------------------------. V | |--(----| partition-expression |-+--)---------------------------> .-,---------------------. V | >--(----| partition-element |-+--)------------------------------| partition-expression .-NULLS LAST--. |--column-name--+-------------+---------------------------------| '-NULLS FIRST-' partition-element |--+-+---------------------------+--| boundary-spec |--| partition-tablespace-options |-+--| | '-PARTITION--partition-name-' | '-| boundary-spec |--EVERY--+-(--constant--+-------------------------+--)-+----------' | | (12) | | | '-| duration-label |------' | '-constant--+-------------------------+-------' | (12) | '-| duration-label |------' boundary-spec (13) |--+-| starting-clause |-------| ending-clause |-+--------------| '-| ending-clause |---------------------------' starting-clause .-,------------. .-FROM-. V | |--STARTING--+------+--+-(----+-constant-+-+--)-+---------------> | +-MINVALUE-+ | | '-MAXVALUE-' | '-+-constant-+-----------' +-MINVALUE-+ '-MAXVALUE-' .-INCLUSIVE-. >--+-----------+------------------------------------------------| '-EXCLUSIVE-' ending-clause .-,------------. .-AT-. V | .-INCLUSIVE-. |--ENDING--+----+--+-(----+-constant-+-+--)-+--+-----------+----| | +-MINVALUE-+ | '-EXCLUSIVE-' | '-MAXVALUE-' | '-+-constant-+-----------' +-MINVALUE-+ '-MAXVALUE-' partition-tablespace-options |--+---------------------+--+---------------------------+-------> '-IN--tablespace-name-' '-INDEX IN--tablespace-name-' >--+--------------------------+---------------------------------| '-LONG IN--tablespace-name-' duration-label |--+-YEAR---------+---------------------------------------------| +-YEARS--------+ +-MONTH--------+ +-MONTHS-------+ +-DAY----------+ +-DAYS---------+ +-HOUR---------+ +-HOURS--------+ +-MINUTE-------+ +-MINUTES------+ +-SECOND-------+ +-SECONDS------+ +-MICROSECOND--+ '-MICROSECONDS-'
System-maintained materialized query tables and user-maintained materialized query tables are referred to by the common term materialized query table, unless there is a need to identify each one separately.
For more details, see Row Size Limit.
If precision and scale are not specified, the default values of 5,0 are used. The words NUMERIC and NUM can be used as synonyms for DECIMAL and DEC.
If the precision is not specified, a default value of 34 is used.
The meaning of the integer K | M | G is the same as for BLOB.
If the length specification is omitted, a length of 1 048 576 (1 megabyte) is assumed.
It is not possible to specify the FOR BIT DATA clause for CLOB columns. However, a CHAR FOR BIT DATA string can be assigned to a CLOB column, and a CHAR FOR BIT DATA string can be concatenated with a CLOB string.
The meaning of the integer K | M | G is similar to that for BLOB. The differences are that the number specified is the number of double-byte characters, and that the maximum size is 1 073 741 823 double-byte characters.
If the length specification is omitted, a length of 1 048 576 double-byte characters is assumed.
The meaning of the integer K | M | G is similar to that for BLOB. The differences are that the number specified is the number of double-byte characters, and that the maximum size is 1 073 741 823 double-byte characters.
If the length specification is omitted, a length of 1 048 576 double-byte characters is assumed.
The length may be in the range of 1 byte to 2 147 483 647 bytes.
If integer by itself is specified, that is the maximum length.
If integer K (in either upper- or lowercase) is specified, the maximum length is 1 024 times integer. The maximum value for integer is 2 097 152.
If integer M is specified, the maximum length is 1 048 576 times integer. The maximum value for integer is 2 048.
If integer G is specified, the maximum length is 1 073 741 824 times integer. The maximum value for integer is 2.
If a multiple of K, M or G that calculates out to 2 147 483 648 is specified, the actual value used is 2 147 483 647 (or 2 gigabytes minus 1 byte), which is the maximum length for a LOB column.
If the length specification is omitted, a length of 1 048 576 (1 megabyte) is assumed.
BLOB(50K) BLOB(50 K) BLOB (50 K)
When a column of type XML is created, an XML path index is created on that column. A table-level XML region index is also created when the first column of type XML is created. The name of these indexes is 'SQL' followed by a character timestamp (yymmddhhmmssxxx). The schema name is SYSIBM.
If a column is defined using a distinct type, then the data type of the column is the distinct type. The length and the scale of the column are respectively the length and the scale of the source type of the distinct type.
If a column defined using a distinct type is a foreign key of a referential constraint, then the data type of the corresponding column of the primary key must have the same distinct type.
If a column is defined using a structured type, then the static data type of the column is the structured type. The column may include values with a dynamic type that is a subtype of structured-type-name.
A column defined using a structured type cannot be used in a primary key, unique constraint, foreign key, index key or distribution key (SQLSTATE 42962).
If a column is defined using a structured type, and contains a reference-type attribute at any level of nesting, that reference-type attribute is unscoped. To use such an attribute in a dereference operation, it is necessary to specify a SCOPE explicitly, using a CAST specification.
If NOT NULL is not specified, the column can contain null values, and its default value is either the null value or the value provided by the WITH DEFAULT clause.
IMPLICITLY HIDDEN must only be specified for a ROW CHANGE TIMESTAMP column (SQLSTATE 42867). The ROW CHANGE TIMESTAMP FOR table-designator expression will resolve to an IMPLICITLY HIDDEN ROW CHANGE TIMESTAMP column.
IMPLICITLY HIDDEN must not be specified for all columns of the table (SQLSTATE 428GU).
NOT LOGGED has no effect on a commit or rollback operation; that is, the database's consistency is maintained even if a transaction is rolled back, regardless of whether or not the LOB value is logged. The implication of not logging is that during a roll forward operation, after a backup or load operation, the LOB data will be replaced by zeros for those LOB values that would have had log records replayed during the roll forward. During crash recovery, all committed changes and changes rolled back will reflect the expected results.
A scope must be specified for any column that is intended to be used as the left operand of a dereference operator or as the argument of the DEREF function. Specifying the scope for a reference type column may be deferred to a subsequent ALTER TABLE statement to allow the target table to be defined, usually in the case of mutually referencing tables.
If this clause is omitted, an 18 byte long identifier that is unique among the identifiers of existing constraints defined on the table is generated by the system. (The identifier consists of "SQL" followed by a sequence of 15 numeric characters generated by a timestamp-based function.)
When used with a PRIMARY KEY or UNIQUE constraint, the constraint-name may be used as the name of an index that is created to support the constraint.
A primary key cannot be specified if the table is a subtable (SQLSTATE 429B3) because the primary key is inherited from the supertable.
A ROW CHANGE TIMESTAMP column cannot be used as part of a primary key (SQLSTATE 429BV).
See PRIMARY KEY within the description of the unique-constraint below.
A unique constraint cannot be specified if the table is a subtable (SQLSTATE 429B3) since unique constraints are inherited from the supertable.
See UNIQUE within the description of the unique-constraint below.
See references-clause under referential-constraint below.
If a column is defined as XML, a default value cannot be specified (SQLSTATE 42613). The only possible default is NULL.
If the column is based on a column of a typed table, a specific default value must be specified when defining a default. A default value cannot be specified for the object identifier column of a typed table (SQLSTATE 42997).
If a column is defined using a distinct type, then the default value of the column is the default value of the source data type cast to the distinct type.
If a column is defined using a structured type, the default-clause cannot be specified (SQLSTATE 42842).
Omission of DEFAULT from a column-definition results in the use of the null value as the default for the column. If such a column is defined NOT NULL, then the column does not have a valid default.
If the value specified is not valid, an error is returned (SQLSTATE 42894).
Although not explicitly required, to ensure uniqueness of the values, define a unique single-column index on generated IDENTITY columns.
An identity column is implicitly NOT NULL. An identity column cannot have a DEFAULT clause (SQLSTATE 42623).
If this value is negative, this is a descending sequence. If this value is 0, or positive, this is an ascending sequence. The default is 1.
When CYCLE is in effect, DB2 may generate duplicate values for an identity column. Although not explicitly required, a unique, single-column index should be defined on the generated column to ensure uniqueness of the values, if unique values are desired. If a unique index exists on such an identity column and a non-unique value is generated, an error occurs (SQLSTATE 23505).
When this option is specified, the values of the identity column are not stored in the cache. In this case, every request for a new identity value results in synchronous I/O to the log.
If a new value is needed for the identity column and there are no unused values available in the cache, the allocation of the value involves waiting for I/O to the log. However, when a new value is needed for the identity column and there is an unused value in the cache, the allocation of that identity value can happen more quickly by avoiding the I/O to the log.
In the event of a database deactivation, either normally or due to a system failure, all cached sequence values that have not been used in committed statements are lost; that is, they will never be used. The value specified for the CACHE option is the maximum number of values for the identity column that could be lost in case of database deactivation. (If a database is not explicitly activated, using the ACTIVATE command or API, when the last application is disconnected from the database, an implicit deactivation occurs.)
The minimum value is 2 (SQLSTATE 42815). The default value is CACHE 20.
A table can only have one ROW CHANGE TIMESTAMP column (SQLSTATE 428C1). If data-type is specified, it must be TIMESTAMP or TIMESTAMP(6) (SQLSTATE 42842). A ROW CHANGE TIMESTAMP column cannot have a DEFAULT clause (SQLSTATE 42623). NOT NULL must be specified for a ROW CHANGE TIMESTAMP column (SQLSTATE 42831).
The data type for the column is based on the result data type of the generation-expression. A CAST specification can be used to force a particular data type and to provide a scope (for a reference type only). If data-type is specified, values are assigned to the column according to the appropriate assignment rules. A generated column is implicitly considered nullable, unless the NOT NULL column option is used. The data type of a generated column and the result data type of the generation-expression must have equality defined (see "Assignments and comparisons"). This excludes columns and generation expressions of type LOB data types, XML, structured types, and distinct types based on any of these types (SQLSTATE 42962).
For a column of data type XML or LOB, integer indicates the maximum byte size of the internal representation of an XML document or LOB data to store in the base table row. XML documents that have a larger internal representation are stored separately from the base table row in an auxiliary storage object. This takes place automatically. There is no default inline length for XML type columns. If the XML document or LOB data is stored inlined in the base table row, there is an additional overhead. For LOB data, the overhead is 4 bytes.
Maximum LOB length in bytes | Minimum explicit INLINE LENGTH |
---|---|
1,024 | 68 |
8,192 | 92 |
65,536 | 116 |
524,000 | 140 |
4,190,000 | 164 |
134,000,000 | 196 |
536,000,000 | 220 |
1,070,000,000 | 252 |
1,470,000,000 | 276 |
2,147,483,647 | 312 |
The explicit INLINE LENGTH value cannot exceed 32 673. For a structured type or XML data type, it must be at least 292 (SQLSTATE 54010).
Allowing system default values to be stored in this manner causes a slight performance penalty during insert and update operations on the column because of extra checking that is done.
The base data type must not be a DATE, TIME, TIMESTAMP, XML, or structured data type (SQLSTATE 42842). If the base data type is a varying-length string, this clause is ignored. String values of length 0 are automatically compressed if a table has been set with VALUE COMPRESSION.
The number of identified columns must not exceed 64, and the sum of their stored lengths must not exceed the index key length limit for the page size. For column stored lengths, see Byte Counts. For key length limits, see "SQL limits". No LOB, XML, distinct type based on one of these types, or structured type can be used as part of a unique key, even if the length attribute of the column is small enough to fit within the index key length limit for the page size (SQLSTATE 54008).
The set of columns in the unique key cannot be the same as the set of columns in the primary key or another unique key (SQLSTATE 01543). (If LANGLEVEL is SQL92E or MIA, an error is returned, SQLSTATE 42891.)
A unique constraint cannot be specified if the table is a subtable (SQLSTATE 429B3), because unique constraints are inherited from the supertable.
The description of the table as recorded in the catalog includes the unique key and its unique index. A unique bidirectional index, which allows forward and reverse scans, will automatically be created for the columns in the sequence specified with ascending order for each column. The name of the index will be the same as the constraint-name if this does not conflict with an existing index in the schema where the table is created. If the index name conflicts, the name will be SQL, followed by a character timestamp (yymmddhhmmssxxx), with SYSIBM as the schema name.
The number of identified columns must not exceed 64, and the sum of their stored lengths must not exceed the index key length limit for the page size. For column stored lengths, see Byte Counts. For key length limits, see "SQL limits". No LOB, XML, distinct type based on one of these types, or structured type can be used as part of a primary key, even if the length attribute of the column is small enough to fit within the index key length limit for the page size (SQLSTATE 54008).
The set of columns in the primary key cannot be the same as the set of columns in a unique key (SQLSTATE 01543). (If LANGLEVEL is SQL92E or MIA, an error is returned, SQLSTATE 42891.)
Only one primary key can be defined on a table.
A primary key cannot be specified if the table is a subtable (SQLSTATE 429B3) because the primary key is inherited from the supertable.
The description of the table as recorded in the catalog includes the primary key and its primary index. A unique bidirectional index, which allows forward and reverse scans, will automatically be created for the columns in the sequence specified with ascending order for each column. The name of the index will be the same as the constraint-name if this does not conflict with an existing index in the schema where the table is created. If the index name conflicts, the name will be SQL, followed by a character timestamp (yymmddhhmmssxxx), with SYSIBM as the schema name.
If the table has a distribution key, the columns of a unique-constraint must be a superset of the distribution key columns; column order is unimportant.
Let T1 denote the object table of the statement. The foreign key of the referential constraint is composed of the identified columns. Each name in the list of column names must identify a column of T1 and the same column must not be identified more than once.
The number of identified columns must not exceed 64, and the sum of their stored lengths must not exceed the index key length limit for the page size. For column stored lengths, see Byte Counts. For key length limits, see "SQL limits". NoLOB, XML, distinct type based on one of these types, or structured type column can be used as part of a foreign key (SQLSTATE 42962). There must be the same number of foreign key columns as there are in the parent key and the data types of the corresponding columns must be compatible (SQLSTATE 42830). Two column descriptions are compatible if they have compatible data types (both columns are numeric, character strings, graphic, date/time, or have the same distinct type).
A referential constraint is a duplicate if its foreign key, parent key, and parent table or parent nickname are the same as the foreign key, parent key, and parent table or parent nickname of a previously specified referential constraint. Duplicate referential constraints are ignored, and a warning is returned (SQLSTATE 01543).
In the following discussion, let T2 denote the identified parent table, and let T1 denote the table being created (or altered). (T1 and T2 may be the same table).
The list of column names must match the set of columns (in any order) of the primary key or a unique constraint that exists on T2 (SQLSTATE 42890). If a column name list is not specified, then T2 must have a primary key (SQLSTATE 42888). Omission of the column name list is an implicit specification of the columns of that primary key in the sequence originally specified.
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. Omission of the clause is an implicit specification of ON DELETE NO ACTION.
If T1 is delete-connected to T2 through multiple paths, defining two SET NULL rules with overlapping foreign key definitions is not allowed. For example: T1 (i1, i2, i3). Rule1 with foreign key (i1, i2) and Rule2 with foreign key (i2, i3) is not allowed.
If any row in T1 is affected by two different rules, an error occurs and no rows are deleted.
A referential constraint cannot be defined if it would cause a table to be delete-connected to itself by a cycle involving two or more tables, and where one of the delete rules is RESTRICT or SET NULL (SQLSTATE 42915).
A referential constraint that would cause a table to be delete-connected to either itself or another table by multiple paths can be defined, except in the following cases (SQLSTATE 42915):
If any row in T1 is affected by different delete rules, the result would be the effect of all the actions specified by these rules. AFTER triggers and CHECK constraints on T1 will also see the effect of all the actions. An example of this is a row that is targeted to be set null through one delete-connected path to an ancestor table, and targeted to be deleted by a second delete-connected path to the same ancestor table. The result would be the deletion of the row. AFTER DELETE triggers on this descendant table would be activated, but AFTER UPDATE triggers would not.
In applying the above rules to referential constraints, in which either the parent table or the dependent table is a member of a typed table hierarchy, all the referential constraints that apply to any table in the respective hierarchies are taken into consideration.
The difference between NO ACTION and RESTRICT is described in the "Notes" section.
If a check constraint is specified as part of a column-definition, a column reference can only be made to the same column. Check constraints specified as part of a table definition can have column references identifying columns previously defined in the CREATE TABLE statement. Check constraints are not checked for inconsistencies, duplicate conditions, or equivalent conditions. Therefore, contradictory or redundant check constraints can be defined, resulting in possible errors at execution time.
The search-condition "IS NOT NULL" can be specified; however, it is recommended that nullability be enforced directly, using the NOT NULL attribute of a column. For example, CHECK (salary + bonus > 30000) is accepted if salary is set to NULL, because CHECK constraints must be either satisfied or unknown, and in this case, salary is unknown. However, CHECK (salary IS NOT NULL) would be considered false and a violation of the constraint if salary is set to NULL.
Check constraints with search-condition are enforced when rows in the table are inserted or updated. A check constraint defined on a table automatically applies to all subtables of that table.
A functional dependency is not enforced by the database manager during normal operations such as insert, update, delete, or set integrity. The functional dependency might be used during query rewrite to optimize queries. Incorrect results might be returned if the integrity of a functional dependency is not maintained.
If UNDER is not specified, an object identifier column must be specified (refer to the OID-column-definition). This object identifier column is the first column of the table. The object ID column is followed by columns based on the attributes of type-name1.
The columns of the table include the object identifier column of the supertable with its type modified to be REF(type-name1), followed by columns based on the attributes of type-name1 (remember that the type includes the attributes of its supertype). The attribute names cannot be the same as the OID column name (SQLSTATE 42711).
Other table options, including table space, data capture, not logged initially, and distribution key options cannot be specified. These options are inherited from the supertable (SQLSTATE 42613).
If an option is already specified as part of the type definition (in CREATE TYPE), the options specified here override the options in CREATE TYPE.
Column default and identity column attributes may be included or excluded, based on the copy-attributes clauses. The implicit definition does not include any other attributes of the identified table, view or nickname. Thus the new table does not have any unique constraints, foreign key constraints, triggers, or indexes. 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.
When a table is identified in the LIKE clause and that table contains a ROW CHANGE TIMESTAMP column, the corresponding column of the new table inherits only the data type of the ROW CHANGE TIMESTAMP column. The new column is not considered to be a generated column. The table identified in the LIKE clause must not contain a ROW CHANGE TIMESTAMP column that is also defined as IMPLICITLY HIDDEN (SQLSTATE 42867).
If LIKE table-name is specified and table-name identifies a base table, created temporary table, or declared temporary table, then INCLUDING COLUMN DEFAULTS is the default. If LIKE table-name is specified and table-name identifies a nickname, then INCLUDING COLUMN DEFAULTS has no effect and column defaults are not copied.
This clause is the default, except when LIKE table-name is specified and table-name identifies a base table, created temporary table, or declared temporary table.
A list of column names must be specified if the result table of the fullselect has duplicate column names of an unnamed column (SQLSTATE 42908). 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.
Every select list element must have a name (use the AS clause for expressions). The as-result-table defines attributes of the table.
The fullselect cannot include a data-change-table-reference clause (SQLSTATE 428FL).
Any valid fullselect that does not reference a typed table or a typed view can be specified.
The columns of the table are defined based on the definitions of the columns that result from the fullselect. If the fullselect references a single table in the FROM clause, select list items that are columns of that table are defined using the column name, data type, and nullability characteristic of the referenced table.
A list of column names must be specified if the result table of the fullselect has duplicate column names of an unnamed column (SQLSTATE 42908). 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.
Every select list element must have a name (use the AS clause for expressions). The materialized-query-definition defines attributes of the materialized query table. The option chosen also defines the contents of the fullselect as follows.
The fullselect cannot include a data-change-table-reference clause (SQLSTATE 428FL).
A materialized query table whose fullselect contains a GROUP BY clause is summarizing data from the tables referenced in the fullselect. Such a materialized query table is also known as a summary table. A summary table is a specialized type of materialized query table.
When specifying this option, the select clause in the CREATE TABLE statement cannot contain a reference to a base table (SQLSTATE 428EC).
Column Name | Data Type | Column Description |
---|---|---|
GLOBALTRANSID | CHAR(8) FOR BIT DATA | The global transaction ID for each propagated row |
GLOBALTRANSTIME | CHAR(13) FOR BIT DATA | The timestamp of the transaction |
OPERATIONTYPE | INTEGER | Operation for the propagated row, either insert, update, or delete. |
A list of column names must be specified if any of the columns of the associated materialized query table duplicates any of the generated column names (SQLSTATE 42711).
The contents of the staging table can be used to refresh the materialized query table, by invoking the REFRESH TABLE statement, if the contents of the staging table are consistent with the associated materialized query table and the underlying source tables.
A clustering block index is automatically maintained for each specified dimension, and a block index, consisting of all columns used in the clause, is maintained if none of the clustering block indexes includes them all. The set of columns used in the ORGANIZE BY clause must follow the rules for the CREATE INDEX statement that specifies CLUSTER.
Each column name specified in the ORGANIZE BY clause must be defined for the table (SQLSTATE 42703). A dimension cannot occur more than once in the dimension list (SQLSTATE 42709). The dimensions cannot contain a ROW CHANGE TIMESTAMP column (SQLSTATE 429BV) or an XML column (SQLSTATE 42962).
Pages of the table are arranged in blocks of equal size, which is the extent size of the table space, and all rows of each block contain the same combination of dimension values.
A table can be both a multidimensional clustering (MDC) table and a partitioned table. Columns in such a table can be used in both the range-partition-spec and in the MDC key. Note that table partitioning is multi-column, not multidimensional.
For a partitioned MDC table created by DB2 Version 9.7 Fix Pack 1 or later releases, the block indexes are partitioned. The partitioned block index placement follows the general partitioned index storage placement rule. All index partitions for a given data partition, including MDC block indexes, share a single index object. By default, the index partitions for each specific data partition reside in the same table space as the data partition. This can be overridden with the partition level INDEX IN clause.
For MDC tables created by DB2 V9.7 or earlier releases, the block indexes are nonpartitioned and remain nonpartitioned if they are rebuilt. MDC tables with partitioned block indexes can co-exist in the same database as MDC tables with nonpartitioned block indexes. To change nonpartitioned block indexes to partitioned block indexes, use an online table move to migrate the MDC table.
A unique index entry will automatically be created in the catalog for the columns in the key sequence specified with ascending order for each column. The name of the index will be SQL, followed by a character timestamp (yymmddhhmmssxxx), with SYSIBM as the schema name. An actual index object is not created in storage, because the table organization is ordered by this key. If a primary key or a unique constraint is defined on the same columns as the range-clustered table sequence key, this same index entry is used for the constraint.
For the key sequence specification, a check constraint exists to reflect the column constraints. If the DISALLOW OVERFLOW clause is specified, the name of the check constraint will be RCT, and the check constraint is enforced. If the ALLOW OVERFLOW clause is specified, the name of the check constraint will be RCT_OFLOW, and the check constraint is not enforced.
The DISALLOW OVERFLOW clause cannot be specified if the table is a range-clustered materialized query table (SQLSTATE 429BG).
If the table is a typed table, then this option is not supported (SQLSTATE 428DH or 42HDR).
A maximum of one IN clause is allowed at the table level. All data table spaces used by a table must have the same page size and extent size. If they do not all have the same prefetch size, a warning is returned. If all table spaces have AUTOMATIC prefetch size, no warning is returned.
If only one table space is specified, all table parts are stored in this table space. This clause cannot be specified when creating a subtable (SQLSTATE 42613), because the table space is inherited from the root table of the table hierarchy.
If this clause is not specified, the database manager chooses a table space (from the set of existing table spaces in the database) with the smallest sufficient page size on which the authorization ID of the statement has USE privilege.
Partitioned tables can have their data partitions spread across multiple table spaces. When multiple table spaces are specified, all of the table spaces must exist, and they must all be either SMS or regular DMS or large DMS table spaces (SQLSTATE 42838). The authorization ID of the statement must hold the USE privilege on all of the specified table spaces.
The sufficient page size of a table is determined by either the byte count of the row or the number of columns. For more information, see Row Size Limits.
Specifying which table space will contain indexes can be done when a table is created or, in the case of partitioned tables, it can be done by specifying the IN clause of the CREATE INDEX statement for a nonpartitioned index. Checking for the USE privilege on the table space is done at table creation time, not when an index is created later.
The specified table space must exist. It can be a regular table space if it is the same table space in which the data is stored; otherwise, it must be a large DMS table space on which the authorization ID of the statement holds the USE privilege. It must also be in the same database partition group as tablespace-name (SQLSTATE 42838).
Specifying which table space will contain long, LOB, or XML columns can only be done when a table is created. Checking for the USE privilege is done at table creation time, not when a long or LOB column is added later.
For rules governing the use of the LONG IN clause with partitioned tables, see "Large object behavior in partitioned tables".
If none of the columns satisfies the requirements for a default distribution key, the table is created without one. Such tables are allowed only in table spaces that are defined on single-partition database partition groups.
For tables in table spaces that are defined on single-partition database partition groups, any collection of columns with data types that are valid for a distribution key can be used to define the distribution key. If you do not specify this clause, no distribution key is created.
For restrictions related to the distribution key, see Rules.
The numeric literals used in the range specification are governed by the rules for numeric literals. All of the numeric literals (except the decimal floating-point special values) used in ranges corresponding to numeric columns are interpreted as integer, floating-point or decimal constants, in accordance with the rules specified for numeric constants. As a result, for decimal floating-point columns, the minimum and maximum numeric constant value that can be used in the range specification of a data partition is the smallest DOUBLE value and the largest DOUBLE value, respectively. Decimal floating-point special values can be used in the range specification. All decimal floating-point special values are interpreted as greater than MINVALUE and less than MAXVALUE.
The table partitioning columns cannot contain a ROW CHANGE TIMESTAMP column (SQLSTATE 429BV). The number of identified columns must not exceed 16 (SQLSTATE 54008).
If the INDEX IN clause is not specified on the CREATE TABLE or the CREATE INDEX statement, the index is placed in the same table space as the first visible or attached partition of the table.
The partition-element level INDEX IN clause only affects the storage of partitioned indexes. Storage of the index is as follows:
The INDEX IN clause can only be specified if the data table spaces are DMS table spaces and the table space specified by the INDEX IN clause is a DMS table space. If the data table space is an SMS table space, an error is returned (SQLSTATE 42839).
The specified table space must exist. It can be a regular table space if it is the same table space in which the data is stored; otherwise, it must be a large DMS table space on which the authorization ID of the statement holds the USE privilege. It must also be in the same database partition group as tablespace-name (SQLSTATE 42838).
Specifying which table space will contain long, LOB, or XML columns can only be done when a table is created. Checking for the USE privilege is done at table creation time, not when a long or LOB column is added later.
For rules governing the use of the LONG IN clause with partitioned tables, see "Large object behavior in partitioned tables".
If the partitioning key column is a numeric type, the starting value of the first partition is the value specified in the starting-clause. The ending value for the first and all other partitions is calculated by adding the starting value of the partition to the increment value specified as constant in the EVERY clause. The starting value for all other partitions is calculated by taking the starting value for the previous partition and adding the increment value specified as constant in the EVERY clause.
If the partitioning key column is a DATE or a TIMESTAMP, the starting value of the first partition is the value specified in the starting-clause. The ending value for the first and all other partitions is calculated by adding the starting value of the partition to the increment value specified as a labeled duration in the EVERY clause. The starting value for all other partitions is calculated by taking the starting value for the previous partition and adding the increment value specified as a labeled duration in the EVERY clause.
For a numeric column, the EVERY value must be a positive numeric constant, and for a datetime column, the EVERY value must be a labeled duration (SQLSTATE 53045).
The null value is stored using three bytes. This is the same or less space than when VALUE COMPRESSION is not active for columns of all data types, with the exception of CHAR(1). Whether or not a column is defined as nullable has no affect on the row size calculation. The zero-length data values for columns whose data type is VARCHAR, VARGRAPHIC, LONG VARCHAR, LONG VARGRAPHIC, CLOB, DBCLOB, BLOB, or XML are to be stored using two bytes only, which is less than the storage required when VALUE COMPRESSION is not active. When a column is defined using the COMPRESS SYSTEM DEFAULT option, this also allows the system default value for the column to be stored using three bytes of total storage. The row format that is used to support this determines the byte counts for each data type, and tends to cause data fragmentation when updating to or from the null value, a zero-length value, or the system default value.
All catalog changes and storage related information are logged, as are all operations that are done on the table in subsequent units of work.
Host variables in the application are always in the application code page, regardless of the CCSID of any tables in the SQL statements that are invoked. DB2 will perform code page conversions as necessary to convert data between the application code page and the section code page. The registry variable DB2CODEPAGE can be set at the client to change the application code page.
The remote server (the server name that was specified in the CREATE SERVER statement) must be specified in the OPTIONS clause. The OPTIONS clause can also be used to override the schema or the unqualified name of the remote base table that is being created.
It is recommended that a schema name be specified. If a remote schema name is not specified, the qualifier for the table name is used. If the table name has no qualifier, the authorization ID of the statement is used.
If an unqualified name for the remote base table is not specified, table-name is used.
Table T1 is a parent of table T3; delete rule as noted below.
Table T2 is a parent of table T3; delete rule CASCADE.
CREATE VIEW V1 AS SELECT * FROM T1 UNION ALL SELECT * FROM T2
DELETE FROM V1
If table T1 is a parent of table T3 with a delete rule of RESTRICT, a restrict violation will be raised (SQLSTATE 23001) if there are any child rows for parent keys of T1 in T3.
If table T1 is a parent of table T3 with a delete rule of NO ACTION, the child rows may be deleted by the delete rule of CASCADE when deleting rows from T2 before the NO ACTION delete rule is enforced for the deletes from T1. If deletes from T2 did not result in deleting all child rows for parent keys of T1 in T3, then a constraint violation will be raised (SQLSTATE 23504).
Note that the SQLSTATE returned is different depending on whether the delete or update rule is RESTRICT or NO ACTION.
When a remote base table is created using transparent DDL, a nickname is automatically created for that remote base table.
Page Size | Row Size Limit | Column Count Limit |
---|---|---|
4K | 4 005 | 500 |
8K | 8 101 | 1 012 |
16K | 16 293 | 1 012 |
32K | 32 677 | 1 012 |
Total Columns * 8 + Number of LOB Columns * 12 <=
Row Size Limit for Page Size
If a table is based on a structured type, an additional 4 bytes of overhead is reserved to identify rows of subtables, regardless of whether or not subtables are defined. Additional subtable columns must be considered nullable for byte count purposes, even if defined as not nullable.
Data type | VALUE COMPRESSION is active1 | VALUE COMPRESSION is not active | |
---|---|---|---|
Column is nullable | Column is not nullable | ||
SMALLINT | 4 | 3 | 2 |
INTEGER | 6 | 5 | 4 |
BIGINT | 10 | 9 | 8 |
REAL | 6 | 5 | 4 |
DOUBLE | 10 | 9 | 8 |
DECIMAL | The integral part of (p/2)+3, where p is the precision | The integral part of (p/2)+2, where p is the precision | The integral part of (p/2)+1, where p is the precision |
DECFLOAT(16) | 10 | 9 | 8 |
DECFLOAT(34) | 18 | 17 | 16 |
CHAR(n) | n+2 | n+1 | n |
VARCHAR(n) | n+2 | n+5 (within a table) | n+4 (within a table) |
LONG VARCHAR2 | 22 | 25 | 24 |
GRAPHIC(n) | n*2+2 | n*2+1 | n*2 |
VARGRAPHIC(n) | n*2+2 | n*2+5 (within a table) | n*2+4 (within a table) |
LONG VARGRAPHIC2 | 22 | 25 | 24 |
DATE | 6 | 5 | 4 |
TIME | 5 | 4 | 3 |
TIMESTAMP(p) | The integral part of (p+1)/2+9, where p is the precision of fractional seconds | The integral part of (p+1)/2+8, where pis the precision of fractional seconds | The integral part of (p+1)/2+7, where pis the precision of fractional seconds |
XML (without INLINE LENGTH specified) | 82 | 85 | 84 |
XML (with INLINE LENGTH specified) | INLINE LENGTH +2 | INLINE LENGTH +4 | INLINE LENGTH +3 |
Maximum LOB3 length 1024 (without INLINE LENGTH specified) | 70 | 73 | 72 |
Maximum LOB length 8192 (without INLINE LENGTH specified) | 94 | 97 | 96 |
Maximum LOB length 65 536 (without INLINE LENGTH specified) | 118 | 121 | 120 |
Maximum LOB length 524 000 (without INLINE LENGTH specified) | 142 | 145 | 144 |
Maximum LOB length 4 190 000 (without INLINE LENGTH specified) | 166 | 169 | 168 |
Maximum LOB length 134 000 000 (without INLINE LENGTH specified) | 198 | 201 | 200 |
Maximum LOB length 536 000 000 (without INLINE LENGTH specified) | 222 | 225 | 224 |
Maximum LOB length 1 070 000 000 (without INLINE LENGTH specified) | 254 | 257 | 256 |
Maximum LOB length 1 470 000 000 (without INLINE LENGTH specified) | 278 | 281 | 280 |
Maximum LOB length 2 147 483 647 (without INLINE LENGTH specified) | 314 | 317 | 316 |
LOB with INLINE LENGTH specified | INLINE LENGTH + 2 | INLINE LENGTH + 5 | INLINE LENGTH + 4 |
1 There is an additional 2 bytes of storage used by each row when VALUE COMPRESSION is active for that row. 2The LONG VARCHAR and LONG VARGRAPHIC data types are supported but are deprecated and might be removed in a future release. 3 Each LOB value has a LOB descriptor in the base record that points to the location of the actual value. The size of the descriptor varies according to the maximum length defined for the column. When INLINE LENGTH is not specified for a LOB column, the size of the descriptor is used as the default inline length value. When determining the byte counts for LOB columns, there are extra bytes to consider when a LOB column is part of a system temporary table that might get generated for insensitive cursors, scrollable cursors, and other queries that require temporary space or sorting of data. The number of extra bytes required might go as high as 70 bytes, depending on the specific query. If the base table is close to the maximum row length for the pagesize, an error might be returned when processing a query if the system temporary table cannot fit in the largest available system temporary table space. For a distinct type, the byte count is equivalent to the length of the source type of the distinct type. For a reference type, the byte count is equivalent to the length of the built-in data type on which the reference type is based. For a structured type, the byte count is equivalent to the INLINE LENGTH + 4. The INLINE LENGTH is the value specified (or implicitly calculated) for the column in the column-options clause. |
DEPARTMENT 63 (0 + 3 + 33 + 7 + 3 + 17)
ORG 57 (0 + 3 + 19 + 2 + 15 + 18)
If VALUE COMPRESSION
were to be specified, the row sizes would change to: DEPARTMENT 69 (2 + 5 + 31 + 8 + 5 + 18)
ORG 53 (2 + 4 + 16 + 4 + 12 + 15)
Data value → | NULL | NULL | zero-length | system default2 | all other data values | all other data values | all other data values |
---|---|---|---|---|---|---|---|
VALUE COMPRES- SION → | not active | active1 | active1 | active1 | not active | not active | active1 |
Column nullability → | nullable | nullable | n/a | n/a | nullable | not nullable | n/a |
Data type | |||||||
SMALLINT | 3 | 3 | - | 3 | 3 | 2 | 4 |
INTEGER | 5 | 3 | - | 3 | 5 | 4 | 6 |
BIGINT | 9 | 3 | - | 3 | 9 | 8 | 10 |
REAL | 5 | 3 | - | 3 | 5 | 4 | 6 |
DOUBLE | 9 | 3 | - | 3 | 9 | 8 | 10 |
DECIMAL | The integral part of (p/2)+2, where p is the precision | 3 | - | 3 | The integral part of (p/2)+2, where p is the precision | The integral part of (p/2)+1, where p is the precision | The integral part of (p/2)+3, where p is the precision |
DECFLOAT(16) | 9 | 3 | - | 3 | 9 | 8 | 10 |
DECFLOAT(34) | 17 | 3 | - | 3 | 17 | 16 | 18 |
CHAR(n) | n+1 | 3 | - | 3 | n+1 | n | n+2 |
VARCHAR(n) | 5 | 3 | 2 | 2 | N+5, where N is the number of bytes in the data | N+4, where N is the number of bytes in the data | N+2, where N is the number of bytes in the data |
LONG VARCHAR3 | 5 | 3 | 2 | 2 | 25 | 24 | 22 |
GRAPHIC(n) | n*2+1 | 3 | - | 3 | n*2+1 | n*2 | n*2+2 |
VARGRAPHIC(n) | 5 | 3 | 2 | 2 | N*2+5, where N is the number of bytes in the data | N*2+4, where N is the number of bytes in the data | N*2+2, where N is the number of bytes in the data |
LONG VARGRAPHIC3 | 5 | 3 | 2 | 2 | 25 | 24 | 22 |
DATE | 5 | 3 | - | - | 5 | 4 | 6 |
TIME | 4 | 3 | - | - | 4 | 3 | 5 |
TIMESTAMP(p) | The integral part of (p+1)/2+8, where p is the precision of fractional seconds | 3 | - | - | The integral part of (p+1)/2+8, where p is the precision of fractional seconds | The integral part of (p+1)/2+7, where p is the precision of fractional seconds | The integral part of (p+1)/2+9, where p is the precision of fractional seconds |
Maximum LOB2 length 1024 | 5 | 3 | 2 | 2 | (60 to 68)+5 | (60 to 68)+4 | (60 to 68)+2 |
Maximum LOB length 8192 | 5 | 3 | 2 | 2 | (60 to 92)+5 | (60 to 92)+4 | (60 to 92)+2 |
Maximum LOB length 65 536 | 5 | 3 | 2 | 2 | (60 to 116)+5 | (60 to 116)+4 | (60 to 116)+2 |
Maximum LOB length 524 000 | 5 | 3 | 2 | 2 | (60 to 140)+5 | (60 to 140)+4 | (60 to 140)+2 |
Maximum LOB length 4 190 000 | 5 | 3 | 2 | 2 | (60 to 164)+5 | (60 to 164)+4 | (60 to 164)+2 |
Maximum LOB length 134 000 000 | 5 | 3 | 2 | 2 | (60 to 196)+5 | (60 to 196)+4 | (60 to 196)+2 |
Maximum LOB length 536 000 000 | 5 | 3 | 2 | 2 | (60 to 220)+5 | (60 to 220)+4 | (60 to 220)+2 |
Maximum LOB length 1 070 000 000 | 5 | 3 | 2 | 2 | (60 to 252)+5 | (60 to 252)+4 | (60 to 252)+2 |
Maximum LOB length 1 470 000 000 | 5 | 3 | 2 | 2 | (60 to 276)+5 | (60 to 276)+4 | (60 to 276)+2 |
Maximum LOB length 2 147 483 647 | 5 | 3 | 2 | 2 | (60 to 312)+5 | (60 to 312)+4 | (60 to 312)+2 |
XML | 5 | 3 | - | - | 85 | 84 | 82 |
1 There is an additional 2 bytes of storage used by each row when VALUE COMPRESSION is active for that row. 2 When COMPRESS SYSTEM DEFAULT is specified for the column. 3The LONG VARCHAR and LONG VARGRAPHIC data types are supported but are deprecated and might be removed in a future release. |
Dimensions involving generated columns whose expressions are non-monotonic, or whose monotonicity cannot be determined, can still be created, but range queries along slice or cell boundaries of these dimensions are not supported. Equality and IN predicates can be processed by slices or cells.
A generated column is monotonic if the following is true with respect to the generating function, fn:
SALARY - 10000
-SALARY
SALARY/1000
-SALARY/1000
The expression "PRICE*DISCOUNT" is not monotonic, because it involves more than one column of the table.
CREATE TABLE TDEPT
(DEPTNO CHAR(3) NOT NULL,
DEPTNAME VARCHAR(36) NOT NULL,
MGRNO CHAR(6),
ADMRDEPT CHAR(3) NOT NULL,
PRIMARY KEY(DEPTNO))
IN DEPARTX
CREATE TABLE PROJ
(PROJNO CHAR(6) NOT NULL,
PROJNAME VARCHAR(24) NOT NULL,
DEPTNO CHAR(3) NOT NULL,
RESPEMP CHAR(6) NOT NULL,
PRSTAFF DECIMAL(5,2) ,
PRSTDATE DATE ,
PRENDATE DATE ,
MAJPROJ CHAR(6) NOT NULL)
IN SCHED
CREATE TABLE EMPLOYEE_SALARY
(DEPTNO CHAR(3) NOT NULL,
DEPTNAME VARCHAR(36) NOT NULL,
EMPNO CHAR(6) NOT NULL,
SALARY DECIMAL(9,2) NOT NULL WITH DEFAULT)
Example 4: Create distinct types for total salary and miles and use them for columns of a table created in the default table space. In a dynamic SQL statement assume the CURRENT SCHEMA special register is JOHNDOE and the CURRENT PATH is the default ("SYSIBM","SYSFUN","JOHNDOE").
CREATE TYPE JOHNDOE.T_SALARY AS INTEGER WITH COMPARISONS
CREATE TYPE JOHNDOE.MILES AS FLOAT WITH COMPARISONS
CREATE TABLE EMPLOYEE
(ID INTEGER NOT NULL,
NAME CHAR (30),
SALARY T_SALARY NOT NULL WITH DEFAULT,
LIVING_DIST MILES DEFAULT MILES(1) )
CREATE TYPE IMAGE AS BLOB (10M)
CREATE TYPE AUDIO AS BLOB (1G)
CREATE TABLE PERSON
(SSN INTEGER NOT NULL,
NAME CHAR (30),
VOICE AUDIO,
PHOTO IMAGE)
CREATE TABLE EMPLOYEE
(ID SMALLINT NOT NULL,
NAME VARCHAR(9),
DEPT SMALLINT CHECK (DEPT BETWEEN 10 AND 100),
JOB CHAR(5) CHECK (JOB IN ('Sales','Mgr','Clerk')),
HIREDATE DATE,
SALARY DECIMAL(7,2),
COMM DECIMAL(7,2),
PRIMARY KEY (ID),
CONSTRAINT YEARSAL CHECK (YEAR(HIREDATE) > 1986
OR SALARY > 40500)
)
IN HUMRES
CREATE TABLE EMPLOYEE .....
IN PAYROLL
CREATE TABLE SALARY.....
IN ACCOUNTING INDEX IN ACCOUNT_IDX
CREATE TABLE SALARY1 .....
or
CREATE TABLE SALARY1 .....
DATA CAPTURE NONE
CREATE TABLE SALARY2 .....
DATA CAPTURE CHANGES
CREATE TABLE EMP_ACT
(EMPNO CHAR(6) NOT NULL,
PROJNO CHAR(6) NOT NULL,
ACTNO SMALLINT NOT NULL,
EMPTIME DECIMAL(5,2),
EMSTDATE DATE,
EMENDATE DATE,
CONSTRAINT EMP_ACT_UNIQ UNIQUE (EMPNO,PROJNO,ACTNO),
CONSTRAINT FK_ACT_PROJ FOREIGN KEY (PROJNO)
REFERENCES PROJECT (PROJNO) ON DELETE CASCADE
)
IN SCHED
A unique index called EMP_ACT_UNIQ is automatically created in the same schema to enforce the unique constraint.
CREATE TABLE HOCKEY_GOALS
( BY_PLAYER VARCHAR(30) NOT NULL,
BY_TEAM VARCHAR(30) NOT NULL,
AGAINST_PLAYER VARCHAR(30) NOT NULL,
AGAINST_TEAM VARCHAR(30) NOT NULL,
DATE_OF_GOAL DATE NOT NULL,
DESCRIPTION CLOB(5000) )
CREATE TABLE EXCEPTION_EMPLOYEE AS
(SELECT EMPLOYEE.*,
CURRENT TIMESTAMP AS TIMESTAMP,
CAST ('' AS CLOB(32K)) AS MSG
FROM EMPLOYEE
) WITH NO DATA
TBSPACE PAGESIZE USER USERAUTH
------------------ ----------- ------ --------
DEPT4K 4096 BOBBY Y
PUBLIC4K 4096 PUBLIC Y
DEPT8K 8192 BOBBY Y
DEPT8K 8192 RICK Y
PUBLIC8K 8192 PUBLIC Y
CREATE TABLE DOCUMENTS
(SUMMARY VARCHAR(1000),
REPORT VARCHAR(2000))
CREATE TABLE CURRICULUM
(SUMMARY VARCHAR(1000),
REPORT VARCHAR(2000),
EXERCISES VARCHAR(1500))
CREATE TABLE PROJECTS (PID INTEGER,
LEAD EMP INLINE LENGTH 300,
STARTDATE DATE,
...)
CREATE TABLE DEPT
(DEPTNO SMALLINT NOT NULL
GENERATED ALWAYS AS IDENTITY
(START WITH 500, INCREMENT BY 1),
DEPTNAME VARCHAR(36) NOT NULL,
MGRNO CHAR(6),
ADMRDEPT SMALLINT NOT NULL,
LOCATION CHAR(30))
CREATE TABLE SALES
(CUSTOMER VARCHAR(80),
REGION CHAR(5),
YEAR INTEGER)
DISTRIBUTE BY HASH (YEAR)
ORGANIZE BY DIMENSIONS (REGION, YEAR)
CREATE TABLE SALES
(CUSTOMER VARCHAR(80),
REGION CHAR(5),
PURCHASEDATE DATE,
PURCHASEYEARMONTH INTEGER
GENERATED ALWAYS AS (INTEGER(PURCHASEDATE)/100))
DISTRIBUTE BY HASH (REGION)
ORGANIZE BY DIMENSIONS (PURCHASEYEARMONTH)
CREATE TABLE CUSTOMER
(CUSTOMERNUM INTEGER,
CUSTOMERNAME VARCHAR(80),
ADDRESS VARCHAR(200),
CITY VARCHAR(50),
COUNTRY VARCHAR(50),
CODE VARCHAR(15),
CUSTOMERNUMDIM INTEGER
GENERATED ALWAYS AS (CUSTOMERNUM/50))
ORGANIZE BY DIMENSIONS (CUSTOMERNUMDIM)
CREATE TABLE EMPLOYEE
(EMP_NO CHAR(6) NOT NULL,
FIRST_NAME VARCHAR(12) NOT NULL,
MID_INT CHAR(1) NOT NULL,
LAST_NAME VARCHAR(15) NOT NULL,
HIRE_DATE DATE,
JOB CHAR(8),
SALARY DECIMAL(9,2),
PRIMARY KEY (EMP_NO))
OPTIONS
(REMOTE_SERVER 'ORASERVER',
REMOTE_SCHEMA 'J15USER1',
REMOTE_TABNAME 'EMPLOYEE')
The following CREATE TABLE statements show how to specify the table name, or the table name and the explicit remote base table name, to get the desired case. The lowercase identifier, employee, is used to illustrate the implicit folding of identifiers.
CREATE TABLE employee
(EMP_NO CHAR(6) NOT NULL,
...)
OPTIONS
(REMOTE_SERVER 'INFX_SERVER')
If the REMOTE_TABNAME
option is not specified, and table-name is
not delimited, the remote base table name will be in uppercase characters,
even if the remote data source normally stores names in lowercase
characters. CREATE TABLE employee
(EMP_NO CHAR(6) NOT NULL,
...)
OPTIONS
(REMOTE_SERVER 'INFX_SERVER',
REMOTE_TABNAME 'employee')
When creating
a table at a remote data source that supports delimited identifiers,
use the REMOTE_TABNAME option and a character string constant that
specifies the table name in the desired case. CREATE TABLE "employee"
(EMP_NO CHAR(6) NOT NULL,
...)
OPTIONS
(REMOTE_SERVER 'INFX_SERVER')
If the REMOTE_TABNAME
option is not specified, and table-name is
delimited, the remote base table name will be identical to table-name. CREATE TABLE STUDENTS
(SCHOOL_ID INTEGER NOT NULL,
PROGRAM_ID INTEGER NOT NULL,
STUDENT_NUM INTEGER NOT NULL,
STUDENT_ID INTEGER NOT NULL,
FIRST_NAME CHAR(30),
LAST_NAME CHAR(30),
GPA DOUBLE)
ORGANIZE BY KEY SEQUENCE
(STUDENT_ID
STARTING FROM 1
ENDING AT 1000000)
DISALLOW OVERFLOW
The size of each record
is the sum of the columns, plus alignment, plus the range-clustered
table row header. In this case, the row size is 98 bytes: 4 + 4 +
4 + 4 + 30 + 30 + 8 + 3 (for nullable columns) + 1 (for alignment)
+ 10 (for the header). With a 4-KB page size (or 4096 bytes), after
accounting for page overhead, there are 4038 bytes available, enough
room for 41 records per page. Allowing for 1 million student records,
there is a need for (1 million divided by 41 records per page) 24 391
pages. With two additional pages for table overhead, the final number
of 4-KB pages that are allocated when the table is created is 24 393. CREATE TABLE DEPARTMENT
(DEPTNO SMALLINT NOT NULL,
DEPTNAME VARCHAR(36) NOT NULL,
MGRNO CHAR(6),
ADMRDEPT SMALLINT NOT NULL,
LOCATION CHAR(30),
CHECK (DEPTNAME DETERMINED BY DEPTNO) NOT ENFORCED)
CREATE TABLE TOASTMASTERS
(PERFORMANCE DB2SECURITYLABEL,
POINTS INTEGER,
NAME VARCHAR(50))
SECURITY POLICY CONTRIBUTIONS
CREATE TABLE TOASTMASTERS
(PERFORMANCE CHAR(8),
POINTS INTEGER COLUMN SECURED WITH CLUBPOSITION,
NAME VARCHAR(50))
SECURITY POLICY CONTRIBUTIONS
CREATE TABLE TOASTMASTERS
(PERFORMANCE DB2SECURITYLABEL,
POINTS INTEGER COLUMN SECURED WITH CLUBPOSITION,
NAME VARCHAR(50))
SECURITY POLICY CONTRIBUTIONS
CREATE TABLE DOCUMENTS
(ID INTEGER,
CONTENTS CLOB)
LONG IN TBSP1, TBSP2
PARTITION BY RANGE (ID)
(STARTING 1 ENDING 1000
EVERY 100)
Alternatively, use the long
form of the syntax to explicitly identify a large table space for
each data partition. In this example, the CLOB data for the first
data partition is placed in LARGE_TBSP3, and the CLOB data for the
remaining data partitions is spread across LARGE_TBSP1 and LARGE_TBSP2
in a round-robin fashion. CREATE TABLE DOCUMENTS
(ID INTEGER,
CONTENTS CLOB)
LONG IN LARGE_TBSP1, LARGE_TBSP2
PARTITION BY RANGE (ID)
(STARTING 1 ENDING 100
IN TBSP1 LONG IN LARGE_TBSP3,
STARTING 101 ENDING 1000
EVERY 100)
CREATE TABLE ACCESSNUMBERS
(AREA INTEGER,
EXCHANGE INTEGER)
PARTITION BY RANGE (AREA NULLS LAST, EXCHANGE NULLS FIRST)
(STARTING (1,1) ENDING (10,100),
STARTING (11,1) ENDING (MAXVALUE,MAXVALUE))
Because
null values in the second column are sorted first, the row (11, NULL)
would sort below the low boundary of the last data partition (11,
1); attempting to insert this row returns an error. The row (12, NULL)
would fall within the last data partition. CREATE TABLE RATIO
(PERCENT INTEGER)
PARTITION BY RANGE (PERCENT)
(STARTING (MINVALUE) ENDING (MAXVALUE))
This
table definition allows any integer value for column PERCENT to be
inserted. The following definition for the RATIO table allows any
integer value between 1 and 100 inclusive to be inserted into column
PERCENT. CREATE TABLE RATIO
(PERCENT INTEGER)
PARTITION BY RANGE (PERCENT)
(STARTING 0 EXCLUSIVE ENDING 100 INCLUSIVE)
CREATE TABLE MYDOCS
(ID INTEGER,
DOC XML)
IN HLTBSPACE
CREATE TABLE NOTES
(ID INTEGER,
DESCRIPTION VARCHAR(255),
CREATED TIMESTAMP,
NOTE XML)
CREATE TABLE EMP_INFO
(EMPNO CHAR(6) NOT NULL,
EMP_INFOCHANGE TIMESTAMP NOT NULL GENERATED ALWAYS
FOR EACH ROW ON UPDATE
AS ROW CHANGE TIMESTAMP,
EMP_ADDRESS VARCHAR(300),
EMP_PHONENO CHAR(4),
PRIMARY KEY (EMPNO) )
CREATE TABLE DOCUMENTS
(ID INTEGER,
CONTENTS XML) INDEX IN TBSPX
PARTITION BY (ID NULLS LAST)
(STARTING FROM 1 INCLUSIVE ENDING AT 100 INCLUSIVE
IN TBSP11 INDEX IN TBSP21 LONG IN TBSP31,
STARTING FROM 101 INCLUSIVE ENDING AT 200 INCLUSIVE
IN TBSP21 INDEX IN TBSP22 LONG IN TBSP32)
CREATE TABLE SALES
(SID INTEGER,
AMOUNT INTEGER) INDEX IN TBSPX
PARTITION BY RANGE (SID NULLS LAST)
(STARTING FROM 1 INCLUSIVE ENDING AT 100 INCLUSIVE
IN TBSP11 INDEX IN TBSP21,
STARTING FROM 101 INCLUSIVE ENDING AT 200 INCLUSIVE
IN TBSP12 INDEX IN TBSP22)
CREATE TABLE BOOKS
(ISBN_NUM INTEGER,
TITLE VARCHAR(255),
AUTHOR VARCHAR(255),
DATE_ADDED TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP)
CREATE TABLE STUDENTS (
STUDENTID INT NOT NULL,
FAMILY_NAME VARCHAR(36) NOT NULL,
GIVEN_NAME VARCHAR(36) NOT NULL,
PRIMARY KEY(STUDENTID))
CCSID UNICODE