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 |-----------------+ '-INSERT TIME-----------------------------------------' >--●--+---------------------------+--●--------------------------> '-DATA CAPTURE--+-NONE----+-' '-CHANGES-' >--+------------------------+--●--+-------------------------+---> '-| tablespace-clauses |-' '-| distribution-clause |-' >--●--+-------------------------+-------------------------------> '-| partitioning-clause |-' .-COMPRESS NO---------------. >--●--+---------------------------+--●--+-------------------+---> | .-ADAPTIVE-. | '-VALUE COMPRESSION-' '-COMPRESS YES-+----------+-' '-STATIC---' >--●--+-----------------------+--●--+----------------------+----> '-WITH RESTRICT ON DROP-' '-NOT LOGGED INITIALLY-' >--●--+--------------------+------------------------------------> '-CCSID--+-ASCII---+-' '-UNICODE-' >--●--+------------------------------+--●-----------------------> '-SECURITY POLICY--policy name-' >--+-------------------------------------------------------+--->< | .-,----------------------------------. | | V | | '-OPTIONS--(----table-option-name--string-constant-+--)-' element-list .-,------------------------------. V | |--(----+-| column-definition |------+-+--)---------------------| +-| period-definition |------+ +-| unique-constraint |------+ +-| referential-constraint |-+ '-| check-constraint |-------' column-definition |--column-name--+-------------------+--+--------------------+---| | (1) | '-| column-options |-' '-| data-type |-----' data-type |--+-| built-in-type |------+-----------------------------------| | (2) | +-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-. | | | (3) | | | | | | '-(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.-. (4) (5) | '-+----------+--DB2SECURITYLABEL--------------------------------------------------' column-options .--------------------------------------------------------------------------------------------------. V | |----+----------------------------------------------------------------------------------------------+-+--| +-NOT NULL-------------------------------------------------------------------------------------+ | (6) | +-| lob-options |------------------------------------------------------------------------------+ | (7) | +-SCOPE--+-typed-table-name-+------------------------------------------------------------------+ | '-typed-view-name--' | +-+-----------------------------+--+-+-PRIMARY KEY-+-----------------------------------------+-+ | '-CONSTRAINT--constraint-name-' | '-UNIQUE------' | | | +-| references-clause |-----------------------------------+ | | '-CHECK--(--check-condition--)--| constraint-attributes |-' | | (8) | +-----+-| default-clause |---+-----------------------------------------------------------------+ | '-| generated-clause |-' | | (9) | +-INLINE LENGTH--integer-----------------------------------------------------------------------+ +-COMPRESS SYSTEM DEFAULT----------------------------------------------------------------------+ | .-COLUMN-. | +-+--------+--SECURED WITH--security-label-name------------------------------------------------+ | .-NOT HIDDEN--------. | '-+-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----------------------. |--●--+-------------------------------+--●----------------------> | .-TRUSTED-----. | '-NOT ENFORCED--+-------------+-' '-NOT TRUSTED-' .-ENABLE QUERY OPTIMIZATION--. >--+----------------------------+--●----------------------------| '-DISABLE QUERY OPTIMIZATION-' default-clause .-WITH-. |--+------+--DEFAULT--+--------------------+--------------------| '-| default-values |-' default-values |--+-constant-------------------------------------------+-------| +-datetime-special-register--------------------------+ +-user-special-register------------------------------+ +-CURRENT SCHEMA-------------------------------------+ +-CURRENT MEMBER-------------------------------------+ +-NULL-----------------------------------------------+ +-cast-function--(--+-constant------------------+--)-+ | +-datetime-special-register-+ | | +-user-special-register-----+ | | '-CURRENT SCHEMA------------' | +-EMPTY_CLOB()---------------------------------------+ +-EMPTY_DBCLOB()-------------------------------------+ +-EMPTY_NCLOB()--------------------------------------+ '-EMPTY_BLOB()---------------------------------------' generated-clause .-ALWAYS-----. |--+-GENERATED--+------------+--+-| identity-options |---------------+--+--| | '-BY DEFAULT-' '-| as-row-change-timestamp-clause |-' | | .-ALWAYS-. | '-GENERATED--+--------+--+-| as-generated-expression-clause |------+-' +-| as-row-transaction-timestamp-clause |-+ '-| as-row-transaction-start-id-clause |--' identity-options |--AS IDENTITY--+-----------------------------------------------------------+--| | .---------------------------------------------. | | V (10) .-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 (11) |---------FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP--------| as-generated-expression-clause |--AS--(--generation-expression--)------------------------------| as-row-transaction-timestamp-clause (12) |---------AS--ROW--+-BEGIN-+------------------------------------| '-END---' as-row-transaction-start-id-clause (13) |---------AS--TRANSACTION START ID------------------------------| period-definition |--PERIOD--+-SYSTEM_TIME---+--(--begin-column-name--,--end-column-name--)--| '-BUSINESS_TIME-' unique-constraint |--+-----------------------------+--+-UNIQUE------+-------------> '-CONSTRAINT--constraint-name-' '-PRIMARY KEY-' .-,---------------. V | >--(------column-name---+--+------------------------------------+--)--| '-,--BUSINESS_TIME--WITHOUT OVERLAPS-' 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--------------. >--+-----------------------------------+--●---------------------| '-MAINTAINED BY--+-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-' >--+--------------------------------+---------------------------> | (14) | '-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--+-------------------------+--)-+----------' | | (15) | | | '-| duration-label |------' | '-constant--+-------------------------+-------' | (15) | '-| duration-label |------' boundary-spec (16) |--+-| 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. The specified distinct type cannot have any data type constraints and the source type cannot be an anchored data type (SQLSTATE 428H2).
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 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).
Row-begin, row-end, and transaction-start-ID columns cannot be used as part of a primary key (SQLSTATE 429BV).
See PRIMARY KEY within the unique-constraint description.
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 unique-constraint description.
See references-clause under referential-constraint description.
Provides a default value in the event a value is not supplied on insert or is specified as DEFAULT on INSERT or UPDATE. If a default value is not specified following the DEFAULT keyword, the default value depends on the data type of the column as shown in "ALTER TABLE". This clause must not be specified with generated-clause in a column definition (SQLSTATE 42614).
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).
Specifies that DB2® generates values for the column. GENERATED must be specified if the column is to be considered an identity column or a row change timestamp column, row-begin column, row-end column, transaction-start-ID column, or generated expression column. A default clause must not be specified for a column defined as GENERATED (SQLSTATE 42623).
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 required. 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.
In a multi-partition or DB2 pureScale® environment, use the CACHE and NO ORDER options to allow multiple DB2 members to cache sequence values simultaneously.
In a DB2 pureScale environment, if both CACHE and ORDER are specified, the specification of ORDER overrides the specification of CACHE and instead NO CACHE will be in effect.
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).
Specifies that the generated value is assigned by the database manager whenever a row is inserted into the table or any column in the row is updated. The value is generated using a reading from the time-of-day clock during execution of the first data change statement in the transaction that requires a value to be assigned to the row-begin column or transaction-start-ID column in the table, or a row in a system-period temporal table is deleted.
For a system-period temporal table, the database manager ensures uniqueness of the generated values for a row-begin column across transactions. The timestamp value might be adjusted to ensure that rows inserted into an associated history table have the end timestamp value greater than the begin timestamp value. This can happen when a conflicting transaction is updating the same row in the system-period temporal table. The database configuration parameter systime_period_adj must be set to Yes for this adjustment to the timestamp value to occur. If multiple rows are inserted or updated within a single SQL transaction and an adjustment is not needed, the values for the row-begin column are the same for all the rows and are unique from the values generated for the column for another transaction. A row-begin column is required as the begin column of a SYSTEM_TIME period, which is the intended use for this type of generated column.
A table can have only one row-begin column (SQLSTATE 428C1). If data-type is not specified the column is defined as a TIMESTAMP(12). If data-type is specified, it must be TIMESTAMP(12) (SQLSTATE 42842). The column cannot have a DEFAULT clause (SQLSTATE 42623), and must be defined as NOT NULL (SQLSTATE 42831). A row-begin column is not updatable.
Specifies that a value for the data type of the column is assigned by the database manager whenever a row is inserted or any column in the row is updated. The assigned value is TIMESTAMP ’9999-12-30-00.00.00.000000000000’.
A row-end column is required as the second column of a SYSTEM_TIME period, which is the intended use for this type of generated column.
A table can have only one row-end column (SQLSTATE 428C1). If data-type is not specified, the column is defined as TIMESTAMP(12). If data-type is specified, it must be TIMESTAMP(12) (SQLSTATE 42842). The column cannot have a DEFAULT clause (SQLSTATE 42623), and must be defined as NOT NULL (SQLSTATE 42831). A row-end column is not updatable.
Specifies that the value is assigned by the database manager whenever a row is inserted into the table or any column in the row is updated. The database manager assigns a unique timestamp value per transaction or the null value. The null value is assigned to the transaction-start-ID column if the column is nullable and if there is a row-begin column in the table for which the value did not need to be adjusted. Otherwise the value is generated using a reading of the time-of-day clock during execution of the first data change statement in the transaction that requires a value to be assigned to a row-begin column or transaction-start-ID column in the table, or a row in a system-period temporal table is deleted. If multiple rows are inserted or updated within a single SQL transaction, the values for the transaction-start-ID column are the same for all the rows and are unique from the values generated for the column for another transaction.
A transaction-start-ID column is required for a system-period temporal table, which is the intended use for this type of generated column.
A table can have only one transaction-start-ID column (SQLSTATE 428C1). If data-type is not specified, the column is defined as TIMESTAMP(12). If data-type is specified it must be TIMESTAMP(12). A transaction-start-ID column cannot have a DEFAULT clause (SQLSTATE 42623). A transaction-start-ID column is not updatable.
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.
Defines a system period with the name SYSTEM_TIME. There must not be a column in the table with the name SYSTEM_TIME (SQLSTATE 42711). A table can have only one SYSTEM_TIME period (SQLSTATE 42711). begin-column-name must be defined as ROW BEGIN and end-column-name must be defined as ROW END (SQLSTATE 428HN).
Defines an application period with the name BUSINESS_TIME. There must not be a column in the table with the name BUSINESS_TIME (SQLSTATE 42711). A table can have only one BUSINESS_TIME period (SQLSTATE 42711). begin-column-name and end-column-name must both be defined as DATE or TIMESTAMP(p) where p is from 0 to 12 (SQLSTATE 42842), and the columns must be defined as NOT NULL (SQLSTATE 42831). begin-column-name and end-column-name must not identify a column that is defined with a GENERATED clause (SQLSTATE 428HZ).
An implicit check constraint is generated to ensure that the value of end-column-name is greater than the value of begin-column-name. The name of the implicitly created check constraint is DB2_GENERATED_CHECK_CONSTRAINT_FOR_BUSINESS_TIME and must not be name of any other check constraint specified in the statement (SQLSTATE 42710).
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". No LOB, 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, 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 previously mentioned 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, indexes, ORGANIZE BY specification, or PARTITIONING KEY specification. 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.
If row or column level access control is activated for table-name-1, it is not inherited by the new table.
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 data types of the result columns must be data types that are valid for columns of a 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.
If row or column level access control is activated for tables that are specified in fullselect, it is not cascaded to the new table.
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.
If the fullselect references a table or a view that depends on a table for which row or column level access control has been activated, those row or column level access controls are ignored when populating the materialized query table. The materialized query table is automatically created with row level access control activated. Direct access by end users to this table does not see any content unless appropriate permissions are created or a user with SECADM authority chooses to deactivate row level access control on this materialized query table. Note that row and column level access control on the materialized query table does not affect internal routing by the DB2 SQL compiler to the 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).
If the fullselect references a table or a view that depends on a table for which row or column level access control has been activated, those row or column level access controls are ignored when populating the staging table. However, the staging table is automatically created with row level access control activated. Direct access by end users to this staging table does not see any content unless appropriate permissions are created or a user with SECADM authority chooses to deactivate row level access control on this staging table. Note that row and column level access control on the staging table does not affect the internal process of applying the changes captured by the staging table to the associated materialized query table.
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, row-begin column, row-end column, transaction-start-ID 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).
Data is clustered using an implicitly created virtual dimension. A clustering block index is automatically maintained for this virtual dimension. The virtual dimension cannot be manipulated and it consumes no space for each row that exists in the table. Pages of the table are arranged in blocks of equal size, which is the extent size of the table space.
The ORGANIZE BY INSERT TIME clause cannot be specified if the table is a typed table (SQLSTATE 428DH).
If the clause is not specified and that table is not a typed table, then the default is determined by the DATA CAPTURE setting of the schema at the time the table is created.
If the table is a typed table that is not a subtable, then this option is not supported (SQLSTATE 428DH).
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 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).
For adaptive row compression, page-level compression dictionaries are created or updated as soon as data is inserted or changed in the table.
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.
COALESCE ( transaction_start_id_col, row_begin_col)
CASE WHEN transaction_start_id_col IS NOT NULL
THEN transaction_start_id_col
ELSE row_begin_col END
Data change operations on an application-period temporal table may result in an automatic insert of one or two additional rows when a row is updated or deleted. When an update or delete of a row in an application-period temporal table is specified for a portion of the period represented by that row, the row is updated or deleted and one or two rows are automatically inserted to represent the portion of the row that is not changed. New values are generated for each generated column in an application-period temporal table for each row that is automatically inserted as a result of an update or delete operation on the table. If a generated column is defined as part of a unique or primary key, parent key in a referential constraint, or unique index, it is possible that an automatic insert will violate a constraint or index in which case an error is returned.
Data movement utilities must use the DB2_DMU_DEFAULT registry variable, or the implicitlyhiddeninclude or implicitlyhiddenmissing file type modifiers when working with tables that contain implicitly hidden columns.
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)
The first table defines the sets of attributes. Those attributes are referenced in the second table, which contains the details for the byte counts for each data type.
The byte counts depend on whether VALUE COMPRESSION is active. When VALUE COMPRESSION is not active, the byte counts also depend on whether the column is nullable. The values in the table represent the amount of storage (in bytes) that is used to store the value. The byte counts shown apply when row compression is not enabled. If row compression is active, the total number of bytes used by a row will generally be smaller than for an uncompressed version of the row; it will never be larger.
Case | Data value | VALUE COMPRESSION | Column nullability |
---|---|---|---|
A | NULL | Not active | Nullable |
B | NULL | Active 2 | Nullable |
C | Zero-length | Active 2 | Not applicable |
D | System default1 | Active 2 | Not applicable |
E | All other data values | Not active | Nullable |
F | All other data values | Not active | Not nullable |
G | All other data values | Active 2 | Not applicable |
1 When COMPRESS SYSTEM DEFAULT is specified for the column. 2 There is an additional 2 bytes of storage used by each row when VALUE COMPRESSION is active for that row. |
Data type | Case A | Case B | Case C | Case D | Case E | Case F | Case G |
---|---|---|---|---|---|---|---|
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 VARCHAR2 | 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 VARGRAPHIC2 | 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 LOB1 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 When COMPRESS SYSTEM DEFAULT is specified for the column. 2 The 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)
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 required 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 required 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
CREATE TABLE TDEPT_TEMP LIKE TDEPT
The
TDEPT_TEMP table will have the same definition as TDEPT except that
the primary key will not be defined and a default table space will
be implicitly chosen. CREATE SECURITY LABEL COMPONENT level_array ARRAY ['A', 'B', 'C']
CREATE SECURITY POLICY P COMPONENTS level_array WITH DB2LBACRULES
CREATE SECURITY LABEL P.A COMPONENT level_array 'A'
CREATE SECURITY LABEL P.B COMPONENT level_array 'B'
CREATE SECURITY LABEL P.C COMPONENT level_array 'C'
CREATE TABLE t1 (c1 INT, c2 INT SECURED WITH B, c3 REAL SECURED WITH A)
SECURITY POLICY P
CREATE TABLE t2 (c4 REAL, c5 INT SECURED WITH C, c6 DB2SECURITYLABEL)
SECURITY POLICY P
Generate a materialized
query tableCREATE TABLE m1 AS(SELECT c1, c3, c5, c6 FROM t1,t2 WHERE c2 !=100)
DATA INITALLY DEFERRED REFRESH DEFERRED
The security label
of t1.c2 is used to compute security labels of all columns of m1 because
it appears in the predicates of the query. The label-based access
control properties of the materialized query table m1 are: CREATE TABLE st1 FOR m1 PROPAGATE IMMEDIATE
The
label-based access control properties of the staging table st1 are: