DB2 10.5 for Linux, UNIX, and Windows
CREATE TABLE statement
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.
Invocation
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).
Authorization
- One of the following privileges and authorities:
- USE privilege on the table space
- SYSADM
- SYSCTRL
- Plus one of these privileges and authorities:
- IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the table does not exist
- CREATEIN privilege on the schema, if the schema name of the table refers to an existing schema
If a subtable is being defined, the authorization ID must be the same as the owner of the root table of the table hierarchy.
- REFERENCES privilege on the table
- REFERENCES privilege on each column of the specified parent key
- CONTROL privilege on the table
- DBADM authority
- The privileges that are held by the authorization ID of the statement
must include at least one of the following on each table or view that
is identified in the fullselect (excluding group privileges):
- SELECT privilege on the table or view
- CONTROL privilege on the table or view
- DATAACCESS authority
- At least one of the following privileges on each table or view
that is identified in the fullselect:
- ALTER privilege on the table or view
- CONTROL privilege on the table or view
- DBADM authority
- ALTER privilege on the materialized query table
- CONTROL privilege on the materialized query table
- DBADM authority
- SELECT privilege or DATAACCESS authority on the table or view,
and at least one of the following:
- ALTER privilege on the table or view
- DBADM authority
- CONTROL privilege on the table or view
Syntax
>>-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 |----------------' >--+-----------------------------------------------------------------------------+--> '-ORGANIZE BY--+-ROW--------------------------------------------------------+-' +-COLUMN-----------------------------------------------------+ '-+---------------+--+-| dimensions-clause |---------------+-' | (1) | +-KEY SEQUENCE--| sequence-key-spec |-+ '-ROW USING-----' '-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--+-------------------+--+--------------------+---| | (2) | '-| column-options |-' '-| data-type |-----' data-type |--+-| built-in-type |------+-----------------------------------| | (3) | +-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)------------------------. | +-+-+-+-CHARACTER-+--+----------------------------+----------+--+------------------+-+-+ | | | '-CHAR------' '-(integer-+-------------+-)-' | | (4) | | | | | | +-OCTETS------+ | '-FOR BIT DATA-----' | | | | | '-CODEUNITS32-' | | | | | '-+-VARCHAR----------------+--(integer-+-------------+-)-' | | | | '-+-CHARACTER-+--VARYING-' +-OCTETS------+ | | | | '-CHAR------' '-CODEUNITS32-' | | | | .-(1M)-----------------------------. | | | '-+-CLOB------------------------+--+----------------------------------+------------' | | '-+-CHARACTER-+--LARGE OBJECT-' '-(integer-+---+-+-------------+-)-' | | '-CHAR------' +-K-+ +-OCTETS------+ | | +-M-+ '-CODEUNITS32-' | | '-G-' | | .-(1)------------------------. | +-+-GRAPHIC--+----------------------------+------+-------------------------------------+ | | '-(integer-+-------------+-)-' | | | | +-CODEUNITS16-+ | | | | '-CODEUNITS32-' | | | +-VARGRAPHIC--(integer-+-------------+-)-------+ | | | +-CODEUNITS16-+ | | | | '-CODEUNITS32-' | | | | .-(1M)-----------------------------. | | | '-DBCLOB--+----------------------------------+-' | | '-(integer-+---+-+-------------+-)-' | | +-K-+ +-CODEUNITS16-+ | | +-M-+ '-CODEUNITS32-' | | '-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.-. (5) (6) | '-+----------+--DB2SECURITYLABEL-------------------------------------------------------' column-options .--------------------------------------------------------------------------------------------------. V | |----+----------------------------------------------------------------------------------------------+-+--| +-NOT NULL-------------------------------------------------------------------------------------+ | (7) | +-| lob-options |------------------------------------------------------------------------------+ | (8) | +-SCOPE--+-typed-table-name-+------------------------------------------------------------------+ | '-typed-view-name--' | +-+-----------------------------+--+-+-PRIMARY KEY-+--------------+--| constraint-attributes |-+ | '-CONSTRAINT--constraint-name-' | '-UNIQUE------' | | | +-| references-clause |--------+ | | '-CHECK--(--check-condition--)-' | | (9) | +-----+-| default-clause |---+-----------------------------------------------------------------+ | '-| generated-clause |-' | | (10) | +-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 (11) .-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 (12) |---------FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP--------| as-generated-expression-clause |--AS--(--generation-expression--)------------------------------| as-row-transaction-timestamp-clause (13) |---------AS--ROW--+-BEGIN-+------------------------------------| '-END---' as-row-transaction-start-id-clause (14) |---------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-' >--| constraint-attributes |------------------------------------| 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-----------+-' +-REPLICATION----+ '-FEDERATED_TOOL-' staging-table-definition |--+-------------------------------+--FOR--table-name2----------> | .-,-------------------. | | V | | '-(----staging-column-name-+--)-' >--PROPAGATE IMMEDIATE------------------------------------------| dimensions-clause .-,-------------------------. .-DIMENSIONS-. V | |--+------------+--(----+-column-name-----------+-+--)----------| | .-,-----------. | | V | | '-(----column-name-+--)-' 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-' >--+--------------------------------+---------------------------> | (15) | '-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--+-------------------------+--)-+----------' | | (16) | | | '-| duration-label |------' | '-constant--+-------------------------+-------' | (16) | '-| duration-label |------' boundary-spec (17) |--+-| 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-'
- If the default table organization for the database is COLUMN, ROW USING must be specified.
- If the first column-option chosen is a generated-clause with a generation-expression, then the data-type can be omitted. It will be determined from the resulting data type of the generation-expression.
- The specified distinct type cannot have any data type constraints and the source type cannot be an anchored data type.
- The FOR BIT DATA clause can be specified in any order with the other column constraints that follow. The FOR BIT DATA clause cannot be specified with string units CODEUNITS32 (SQLSTATE 42613).
- DB2SECURITYLABEL is the built-in distinct type that must be used to define the row security label column of a protected table.
- For a column of type DB2SECURITYLABEL, NOT NULL WITH DEFAULT is implicit and cannot be explicitly specified (SQLSTATE 42842). The default value for a column of type DB2SECURITYLABEL is the session authorization ID's security label for write access.
- The lob-options clause only applies to large object types (BLOB, CLOB and DBCLOB) and distinct types based on large object types.
- The SCOPE clause only applies to the REF type.
- The default-clause and generated-clause cannot both be specified for the same column definition (SQLSTATE 42614).
- INLINE LENGTH applies only to columns defined as structured, XML, or LOB types.
- The same clause must not be specified more than once.
- Data type is optional for a row change timestamp column if the first column-option specified is a generated-clause. The data type default is TIMESTAMP(6).
- Data type is optional for a row-begin and row-end timestamp columns if the first column-option specified is a generated-clause. The data type default is TIMESTAMP(12).
- Data type is optional for a transaction-start-ID timestamp columns if the first column-option specified is a generated-clause. The data type default is TIMESTAMP(12).
- Specifying which table space will contain a table's indexes can be done when the table is created. If the table is a partitioned table, the index table space for a nonpartitioned index can be specified with the IN clause of the CREATE INDEX statement.
- This syntax for a partition-element is valid if there is only one partition-expression with a numeric or datetime data type.
- The first partition-element must include a starting-clause and the last partition-element must include an ending-clause.
Description
System-maintained, user-maintained, federated_tool-maintained, and replication-maintained materialized query tables (shadow tables) are referred to by the common term materialized query table, unless there is a need to identify each one separately.
- table-name
- Names the table. The name, including the implicit or explicit qualifier, must not identify a table, view, nickname, or alias described in the catalog. The schema name must not be SYSIBM, SYSCAT, SYSFUN, or SYSSTAT (SQLSTATE 42939).
- element-list
- Defines the elements of a table. This includes the definition of columns and constraints on the table.
- column-definition
- Defines the attributes of a column.
- column-name
- Names a column of the table. The name cannot be qualified, and the same name cannot be used for
more than one column of the table (SQLSTATE 42711).
A row-organized table can have the following:
- A 4K page size with a maximum of 500 columns, where the row size must not be greater than 4005
- An 8K page size with a maximum of 1012 columns, where the row size must not be greater than 8101
- A 16K page size with a maximum of 1012 columns, where the row size must not be greater than 16,293
- A 32K page size with a maximum of 1012 columns, where the row size must not be greater than 32,677
A column-organized table can have a maximum of 1012 columns, regardless of page size, where the byte counts of the columns must not be greater than 32,677. Extended row size support does not apply to column-organized tables.
For more details, see Row Size Limit.
- data-type
- Specifies the data type of the column.
- built-in-type
- For built-in types, use one of the following types.
- SMALLINT
- For a small integer.
- INTEGER or INT
- For a large integer.
- BIGINT
- For a big integer.
- DECIMAL(precision-integer, scale-integer) or DEC(precision-integer, scale-integer)
- For a decimal number. The first integer is the precision of the
number; that is, the total number of digits; it may range from 1 to
31. The second integer is the scale of the number; that is, the number
of digits to the right of the decimal point; it may range from 0 to
the precision of the number.
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.
- FLOAT(integer)
- For a single or double-precision floating-point number, depending
on the value of the integer. The value
of the integer must be in the range 1 through 53. The values 1 through
24 indicate single precision and the values 25 through 53 indicate
double-precision. You can also specify:
- REAL
- For single precision floating-point.
- DOUBLE
- For double-precision floating-point.
- DOUBLE PRECISION
- For double-precision floating-point.
- FLOAT
- For double-precision floating-point.
- DECFLOAT(precision-integer)
- For a decimal floating-point number. The value of precision-integer is
the precision of the number; that is, the total number of digits,
which can be 16 or 34.
If the precision is not specified, a default value of 34 is used.
- CHARACTER(integer [OCTETS | CODEUNITS32]) or CHAR(integer [OCTETS | CODEUNITS32]) or CHARACTER or CHAR
- For a fixed-length character string of length integer code units, which may range from 1 to 254 OCTETS or from 1 to 63 CODEUNITS32. If the length specification is omitted, a length of 1 is assumed.
- VARCHAR(integer [OCTETS | CODEUNITS32]), or CHARACTER VARYING(integer [OCTETS | CODEUNITS32]), or CHAR VARYING(integer [OCTETS | CODEUNITS32])
- For a varying-length character string of maximum length integer code units, which may range from 1 to 32,672 OCTETS or from 1 to 8,168 CODEUNITS32.
- FOR BIT DATA
- Specifies that the contents of the column are to be treated as bit (binary) data. During data exchange with other systems, code page conversions are not performed. Comparisons are done in binary, irrespective of the database collating sequence.
- CLOB or CHARACTER (CHAR) LARGE OBJECT(integer [K | M | G] [OCTETS | CODEUNITS32])
- For a character large object string of the specified maximum length
in code units.
The meaning of the integer K | M | G is the same as for BLOB when the string unit is OCTETS. When the string unit is CODEUNITS32, and a multiple of K, M or G that calculates out to 536,870,912 is specified, the actual value used is 536,870,911 (or 2 gigabytes minus 1 string unit divided 4), which is the maximum length for a LOB column.
If the length specification is omitted, a length of 1,048,576 (1 megabyte) code units 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.
- OCTETS
- Specifies that the units of the length attribute is bytes.
- CODEUNITS32
- Specifies that the units of the length attribute is Unicode UTF-32
code units which approximates counting in characters. This does not
affect the underlying code page of the data type. The actual length
of a data value is determined by counting the UTF-32 code units as
if the data was converted to UTF-32. CODEUNITS32 can be specified
only in a Unicode database (SQLSTATE 560AA).
When no string units are specified for a character string data type in a Unicode database, the string units are implicit and determined by the value of the NLS_STRING_UNITS global variable or string_units database configuration parameter. In a non-Unicode database, the string units for character string data types are OCTETS.
- GRAPHIC(integer CODEUNITS16 | CODEUNITS32])
- For a fixed-length graphic string of length integer which may range from 1 to 127 double bytes, 1 to 127 CODEUNITS16, or 1 to 63 CODEUNITS32. If the length specification is omitted, a length of 1 is assumed.
- VARGRAPHIC(integer CODEUNITS16 | CODEUNITS32])
- For a varying-length graphic string of maximum length integer, which may range from 1 to 16,336 double bytes, 1 to 16,336 CODEUNITS16, or 1 to 8,168 CODEUNITS32 .
- DBCLOB(integer [K | M | G] CODEUNITS16 | CODEUNITS32])
- For a character large object string of the specified maximum length
in double bytes, Unicode UTF-16 code units, or Unicode UTF-32 code
units.
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 bytes, Unicode UTF-16 code units, or Unicode UTF-32 code units with a maximum size of 2,147,483,646 total bytes, 1,073,741,823 double bytes, 1,073,741,823 CODEUNITS16, or 536,870,911 CODEUNITS32. When the string unit is CODEUNITS32 and a multiple of K, M or G that calculates out to 536,870,912 is specified, the actual value used is 536,870,911 (or 2 gigabytes minus 1 string unit divided 4), which is the maximum length for a LOB column.
If the length specification is omitted, a length of 1,048,576 code units is assumed.
- CODEUNITS16
- Specifies that the units of the length attribute is Unicode UTF-16 code units which is the same as counting in double bytes. CODEUNITS16 can be specified only in a Unicode database (SQLSTATE 560AA).
- CODEUNITS32
- Specifies that the units of the length attribute is Unicode UTF-32
code units which approximates counting in characters. This does not
affect the underlying code page of the data type. The actual length
of a data value is determined by counting the UTF-32 code units as
if the data was converted to UTF-32. CODEUNITS32 can be specified
only in a Unicode database (SQLSTATE 560AA).
When no string units are specified for a graphic string data type in a Unicode database, the string units are implicit and determined by the value of the NLS_STRING_UNITS global variable or string_units database configuration parameter. In a non-Unicode database, the string units for character string data types are double-bytes.
- NATIONAL CHARACTER (integer) or NATIONAL CHAR (integer) or NCHAR (integer)
- For a fixed-length string of length integer . If the length
specification is omitted, a length of 1 is assumed.
The NATIONAL CHARACTER type maps to either a fixed-length character or a fixed-length graphic string based on the current value of the nchar_mapping database configuration parameter which also defines the string units.
- NATIONAL CHARACTER VARYING (integer) or NATIONAL CHAR VARYING (integer) or NCHAR VARYING (integer) or NVARCHAR (integer)
- For a varying-length string of maximum length integer.
The NATIONAL CHARACTER VARYING type maps to either a varying-length character or a varying-length graphic string based on the current value of the nchar_mapping database configuration parameter which also defines the string units.
- NATIONAL CHARACTER LARGE OBJECT (integer [K|M|G]) or NCHAR LARGE OBJECT (integer [K|M|G]) or NCLOB(integer [K|M|G])
- For
a large object string of the specified maximum length.
The NATIONAL CHARACTER LARGE OBJECT type maps to either a character large object or a double-byte character large object string based on the current value of the nchar_mapping database configuration parameter which also defines the string units
The meaning of the integer K | M | G is described under the mapped data type based on the string units of the mapping.
If the length specification is omitted, a length of 1,048,576 code units is assumed.
- BLOB or BINARY LARGE OBJECT(integer [K | M | G])
- For a binary large object string of the specified maximum length
in bytes.
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.
Any number of spaces is allowed between the integer and K, M, or G, and a space is not required. For example, all of the following are valid:BLOB(50K) BLOB(50 K) BLOB (50 K) - DATE
- For a date.
- TIME
- For a time.
- TIMESTAMP(integer) or TIMESTAMP
- For a timestamp. The integer must be between 0 and 12 and specifies the precision of fractional seconds from 0 (seconds) to 12 (picoseconds). The default is 6 (microseconds).
- XML
- For an XML document. Only well-formed XML documents can be inserted
into an XML column. An XML column has the following restrictions:
- The column cannot be part of any index except an index over XML data. Therefore, it cannot be included as a column of a primary key or unique constraint (SQLSTATE 42962).
- The column cannot be a foreign key of a referential constraint (SQLSTATE 42962).
- A default value (WITH DEFAULT) cannot be specified for the column (SQLSTATE 42613). If the column is nullable, the default for the column is the null value.
- The column cannot be used as the distribution key (SQLSTATE 42997).
- The column cannot be used as a data partitioning key (SQLSTATE 42962).
- The column cannot be used to organize a multidimensional clustering (MDC) table (SQLSTATE 42962).
- The column cannot be used in a range-clustered table (SQLSTATE 429BG).
- The column cannot be referenced in a check constraint except in a VALIDATED predicate (SQLSTATE 42621).
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.
- SYSPROC.DB2SECURITYLABEL
- This is a built-in distinct type that must be used to define the row security label column of a protected table. The underlying data type of a column of the built-in distinct type DB2SECURITYLABEL is VARCHAR(128) FOR BIT DATA. A table can have at most one column of type DB2SECURITYLABEL (SQLSTATE 428C1).
- distinct-type-name
- For a user-defined type that is a distinct type. If a distinct
type name is specified without a schema name, the distinct type name
is resolved by searching the schemas on the SQL path (defined by the
FUNCPATH preprocessing option for static SQL and by the CURRENT PATH
register for dynamic SQL).
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.
- structured-type-name
- For a user-defined type that is a structured type. If a structured
type name is specified without a schema name, the structured type
name is resolved by searching the schemas on the SQL path (defined
by the FUNCPATH preprocessing option for static SQL, and by the CURRENT
PATH register for dynamic SQL).
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.
- REF (type-name2)
- For a reference to a typed table. If type-name2 is specified without a schema name, the type name is resolved by searching the schemas on the SQL path (defined by the FUNCPATH preprocessing option for static SQL and by the CURRENT PATH register for dynamic SQL). The underlying data type of the column is based on the representation data type specified in the REF USING clause of the CREATE TYPE statement for type-name2 or the root type of the data type hierarchy that includes type-name2.
- column-options
- Defines additional options related to columns of the table.
- NOT NULL
- Prevents the column from containing null values.
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.
- NOT HIDDEN or IMPLICITLY HIDDEN
- Specifies whether the column is to be defined as hidden. The hidden
attribute determines whether the column is included in an implicit
reference to the table, or whether it can be explicitly referenced
in SQL statements. The default is NOT HIDDEN.
- NOT HIDDEN
- Specifies that the column is included in implicit references to the table, and that the column can be explicitly referenced.
- IMPLICITLY HIDDEN
- Specifies that the column is not visible in SQL statements unless
the column is explicitly referenced by name. For example, assuming
that a table includes a column defined with the IMPLICITLY HIDDEN
clause, the result of a SELECT * does not include the implicitly hidden
column. However, the result of a SELECT that explicitly refers to
the name of an implicitly hidden column will include that column in
the result table.
IMPLICITLY HIDDEN must not be specified for all columns of the table (SQLSTATE 428GU).
- lob-options
- Specifies options for LOB data types.
- LOGGED
- Specifies that changes made to the column are to be written to the log. The data in such columns is then recoverable with database utilities (such as RESTORE DATABASE). LOGGED is the default.
- NOT LOGGED
- Specifies that changes made to the column are not to be logged. This only applies to LOB data that is not inlined.
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.
- COMPACT
- Specifies that the values in the LOB column should take up minimal disk space (free any extra disk pages in the last group used by the LOB value), rather than leave any leftover space at the end of the LOB storage area that might facilitate subsequent append operations. Note that storing data in this way may cause a performance penalty in any append (length-increasing) operations on the column.
- NOT COMPACT
- Specifies some space for insertions to assist in future changes to the LOB values in the column. This is the default.
- SCOPE
- Identifies the scope of the reference type column.
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.
- typed-table-name
- The name of a typed table. The table must already exist or be the same as the name of the table being created (SQLSTATE 42704). The data type of column-name must be REF(S), where S is the type of typed-table-name (SQLSTATE 428DM). No checking is done of values assigned to column-name to ensure that the values actually reference existing rows in typed-table-name.
- typed-view-name
- The name of a typed view. The view must already exist or be the same as the name of the view being created (SQLSTATE 42704). The data type of column-name must be REF(S), where S is the type of typed-view-name (SQLSTATE 428DM). No checking is done of values assigned to column-name to ensure that the values actually reference existing rows in typed-view-name.
- CONSTRAINT constraint-name
- Names the constraint. A constraint-name must
not identify a constraint that was already specified within the same
CREATE TABLE statement. (SQLSTATE 42710).
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.
- PRIMARY KEY
- This provides a shorthand method of defining a primary key composed
of a single column. Thus, if PRIMARY KEY is specified in the definition
of column C, the effect is the same as if the PRIMARY KEY(C) clause
is specified as a separate clause.
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.
- UNIQUE
- This provides a shorthand method of defining a unique key composed
of a single column. Thus, if UNIQUE is specified in the definition
of column C, the effect is the same as if the UNIQUE(C) clause is
specified as a separate clause.
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.
- references-clause
- This provides a shorthand method of defining a foreign key composed
of a single column. Thus, if a references-clause is specified in the
definition of column C, the effect is the same as if that references-clause
were specified as part of a FOREIGN KEY clause in which C is the only
identified column.
See references-clause under referential-constraint description.
- CHECK (check-condition)
- This provides a shorthand method of defining a check constraint that applies to a single column. See description for CHECK (check-condition).
- default-clause
- Specifies a default value for the column.
- WITH
- An optional keyword.
- DEFAULT
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.
- default-values
- Specific types of default values that can be specified are as
follows.
- constant
- Specifies the constant as the default value for the column. The
specified constant must:
- represent a value that could be assigned to the column in accordance with the rules of assignment
- not be a floating-point constant unless the column is defined with a floating-point data type
- be a numeric constant or a decimal floating-point special value if the data type of the column is a decimal floating-point. Floating-point constants are first interpreted as DOUBLE and then converted to decimal floating-point if the target column is DECFLOAT. For DECFLOAT(16) columns, decimal constants having precision greater than 16 digits will be rounded using the rounding modes specified by the CURRENT DECFLOAT ROUNDING MODE special register.
- not have nonzero digits beyond the scale of the column data type if the constant is a decimal constant (for example, 1.234 cannot be the default for a DECIMAL(5,2) column)
- be expressed with no more than 254 bytes including the quote characters, any introducer character such as the X for a hexadecimal constant, and characters from the fully qualified function name and parentheses when the constant is the argument of a cast-function
- datetime-special-register
- Specifies the value of the datetime special register (CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP) at the time of INSERT, UPDATE, or LOAD as the default for the column. The data type of the column must be the data type that corresponds to the special register specified (for example, data type must be DATE when CURRENT DATE is specified).
- user-special-register
- Specifies the value of the user special register (CURRENT USER, SESSION_USER, SYSTEM_USER) at the time of INSERT, UPDATE, or LOAD as the default for the column. The data type of the column must be a character string with a length not less than the length attribute of a user special register. Note that USER can be specified in place of SESSION_USER and CURRENT_USER can be specified in place of CURRENT USER.
- CURRENT SCHEMA
- Specifies the value of the CURRENT SCHEMA special register at the time of INSERT, UPDATE, or LOAD as the default for the column. If CURRENT SCHEMA is specified, the data type of the column must be a character string with a length greater than or equal to the length attribute of the CURRENT SCHEMA special register.
- CURRENT MEMBER
- Specifies the value of the CURRENT MEMBER special register at the time of INSERT, UPDATE, or LOAD as the default for the column. If CURRENT MEMBER is specified, the data type of the column must allow assignment from an integer.
- NULL
- Specifies NULL as the default for the column. If NOT NULL was specified, DEFAULT NULL may be specified within the same column definition but will result in an error on any attempt to set the column to the default value.
- cast-function
- This form of a default value can only be used with columns defined
as a distinct type, BLOB or datetime (DATE, TIME or TIMESTAMP) data
type. For distinct type, with the exception of distinct types based
on BLOB or datetime types, the name of the function must match the
name of the distinct type for the column. If qualified with a schema
name, it must be the same as the schema name for the distinct type.
If not qualified, the schema name from function resolution must be
the same as the schema name for the distinct type. For a distinct
type based on a datetime type, where the default value is a constant,
a function must be used and the name of the function must match the
name of the source type of the distinct type with an implicit or explicit
schema name of SYSIBM. For other datetime columns, the corresponding
datetime function may also be used. For a BLOB or a distinct type
based on BLOB, a function must be used and the name of the function
must be BLOB with an implicit or explicit schema name of SYSIBM.
- constant
- Specifies a constant as the argument. The constant must conform to the rules of a constant for the source type of the distinct type or for the data type if not a distinct type. If the cast-function is BLOB, the constant must be a string constant.
- datetime-special-register
- Specifies CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP. The source type of the distinct type of the column must be the data type that corresponds to the specified special register.
- user-special-register
- Specifies CURRENT USER, SESSION_USER, or SYSTEM_USER. The data type of the source type of the distinct type of the column must be a string data type with a length of at least 8 bytes. If the cast-function is BLOB, the length attribute must be at least 8 bytes.
- CURRENT SCHEMA
- Specifies the value of the CURRENT SCHEMA special register. The data type of the source type of the distinct type of the column must be a character string with a length greater than or equal to the length attribute of the CURRENT SCHEMA special register. If the cast-function is BLOB, the length attribute must be at least 8 bytes.
- EMPTY_CLOB(), EMPTY_DBCLOB(), or EMPTY_BLOB()
- Specifies a zero-length string as the default for the column. The column must have the data type that corresponds to the result data type of the function.
If the value specified is not valid, an error is returned (SQLSTATE 42894).
- generated-clause
- Specifies a generated value for the column.
- GENERATED
Specifies that the database 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).
- ALWAYS
- Specifies that a value will always be generated for the column when a row is inserted into the table, or whenever the result value of the generation-expression changes. The result of the expression is stored in the table. GENERATED ALWAYS is the recommended value unless data propagation or unload and reload operations are being done. GENERATED ALWAYS is the required value for generated columns.
- BY DEFAULT
- Specifies
that the database will generate a value for the column when a row
is inserted, or updated specifying the DEFAULT clause, unless an explicit
value is specified. BY DEFAULT is the recommended value when using
data propagation or performing an unload and reload operation.
Although not explicitly required, to ensure uniqueness of the values, define a unique single-column index on generated IDENTITY columns.
- AS IDENTITY
- Specifies that the column is to be the identity column for this
table. A table can only have a single identity column (SQLSTATE 428C1).
The IDENTITY keyword can only be specified if the data type associated
with the column is an exact numeric type with a scale of zero, or
a user-defined distinct type for which the source type is an exact
numeric type with a scale of zero (SQLSTATE 42815). SMALLINT, INTEGER,
BIGINT, or DECIMAL with a scale of zero, or a distinct type based
on one of these types, are considered exact numeric types. By contrast,
single- and double-precision floating points are considered approximate
numeric data types. Reference types, even if represented by an exact
numeric type, cannot be defined as identity columns.
An identity column is implicitly NOT NULL. An identity column cannot have a DEFAULT clause (SQLSTATE 42623).
- START WITH numeric-constant
- Specifies the first value for the identity column. This value can be any positive or negative value that could be assigned to this column (SQLSTATE 42815), without nonzero digits existing to the right of the decimal point (SQLSTATE 428FA). The default is MINVALUE for ascending sequences, and MAXVALUE for descending sequences. This value is not necessarily the value that would be cycled to after reaching the maximum or minimum value for the identity column. The START WITH clause can be used to start the generation of values outside the range that is used for cycles. The range used for cycles is defined by MINVALUE and MAXVALUE.
- INCREMENT BY numeric-constant
- Specifies the interval between consecutive values of the identity
column. This value can be any positive or negative value that could
be assigned to this column (SQLSTATE 42815), and does not exceed the
value of a large integer constant (SQLSTATE 42820), without nonzero
digits existing to the right of the decimal point (SQLSTATE 428FA).
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.
- NO MINVALUE or MINVALUE
- Specifies the minimum value at which a descending identity column
either cycles or stops generating values, or an ascending identity
column cycles to after reaching the maximum value.
- NO MINVALUE
- For an ascending sequence, the value is the START WITH value, or 1 if START WITH was not specified. For a descending sequence, the value is the minimum value of the data type of the column. This is the default.
- MINVALUE numeric-constant
- Specifies the numeric constant that is the minimum value. This value can be any positive or negative value that could be assigned to this column (SQLSTATE 42815), without nonzero digits existing to the right of the decimal point (SQLSTATE 428FA), but the value must be less than or equal to the maximum value (SQLSTATE 42815).
- NO MAXVALUE or MAXVALUE
- Specifies the maximum value at which an ascending identity column
either cycles or stops generating values, or a descending identity
column cycles to after reaching the minimum value.
- NO MAXVALUE
- For an ascending sequence, the value is the maximum value of the data type of the column. For a descending sequence, the value is the START WITH value, or -1 if START WITH was not specified. This is the default.
- MAXVALUE numeric-constant
- Specifies the numeric constant that is the maximum value. This value can be any positive or negative value that could be assigned to this column (SQLSTATE 42815), without nonzero digits existing to the right of the decimal point (SQLSTATE 428FA), but the value must be greater than or equal to the minimum value (SQLSTATE 42815).
- NO CYCLE or CYCLE
- Specifies whether this identity column should continue to generate
values after generating either its maximum or minimum value.
- NO CYCLE
- Specifies that values will not be generated for the identity column once the maximum or minimum value has been reached. This is the default.
- CYCLE
- Specifies that values continue to be generated for this column
after the maximum or minimum value has been reached. If this option
is used, after an ascending identity column reaches the maximum value,
it generates its minimum value; or after a descending sequence reaches
the minimum value, it generates its maximum value. The maximum and
minimum values for the identity column determine the range that is
used for cycling.
When CYCLE is in effect, duplicate values might be generated 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).
- NO CACHE or CACHE
- Specifies whether to keep some pre-allocated values in memory
for faster access. If a new value is needed for the identity column,
and there are none available in the cache, then the end of the new
cache block must be logged. However, when a new value is needed for
the identity column, and there is an unused value in the cache, then
the allocation of that identity value is faster, because no logging
is necessary. This is a performance and tuning option.
- NO CACHE
- Specifies that values for the identity column are not to be pre-allocated.
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.
- CACHE integer-constant
- Specifies how many values of the identity sequence are to be pre-allocated and kept in memory.
When values are generated for the identity column, pre-allocating and storing values in the cache
reduces 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 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.
- NO ORDER or ORDER
- Specifies whether the identity values must be generated in order
of request.
- NO ORDER
- Specifies that the values do not need to be generated in order of request. This is the default.
- ORDER
- Specifies that the values must be generated in order of request.
- FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
- Specifies that the column is a timestamp column for the table.
A value is generated for the column in each row that is inserted,
and for any row in which any column is updated. The value that is
generated for a ROW CHANGE TIMESTAMP column is a timestamp that corresponds
to the insert or update time for that row. If multiple rows are inserted
or updated with a single statement, the value of the ROW CHANGE TIMESTAMP
column might be different for each row.
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).
- AS (generation-expression)
- Specifies that the definition of the column is based on an expression.
(If the expression for a GENERATED ALWAYS column includes a user-defined
external function, changing the executable for the function (such
that the results change for given arguments) can result in inconsistent
data. This can be avoided by using the SET INTEGRITY statement to
force the generation of new values.) The generation-expression cannot
contain any of the following (SQLSTATE 42621):
- Subqueries
- XMLQUERY or XMLEXISTS expressions
- Column functions
- Dereference operations or DEREF functions
- User-defined or built-in functions that are non-deterministic
- User-defined functions using the EXTERNAL ACTION option
- User-defined functions that are not defined with NO SQL
- Host variables or parameter markers
- Special registers and built-in functions that depend on the value of a special register
- Global variables
- References to columns defined later in the column list
- References to other generated columns
- References to columns of type XML
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).
- AS ROW BEGIN
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.
- AS ROW END
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.
- AS TRANSACTION START ID
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.
- INLINE LENGTH integer
- This option is valid only for a column defined
using a structured type, XML or LOB data type (SQLSTATE 42842).
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.
For a column of data type LOB, the default inline length is set to be the maximum size of the LOB descriptor if the clause is not specified. Any explicit INLINE LENGTH must be at least the maximum LOB descriptor size. The following table summarizes the LOB descriptor sizes.Table 1. Sizes of the LOB descriptor for various LOB lengths 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 For a structured type column, integer indicates the maximum size in bytes of an instance of a structured type to store inline with the rest of the values in the row. Instances of structured types that cannot be stored inline are stored separately from the base table row, similar to the way that LOB values are stored. This takes place automatically. The default INLINE LENGTH for a structured-type column is the inline length of its type (specified explicitly or by default in the CREATE TYPE statement). If INLINE LENGTH of the structured type is less than 292, the value 292 is used for the INLINE LENGTH of the column.Note: The inline lengths of subtypes are not counted in the default inline length, meaning that instances of subtypes may not fit inline unless an explicit INLINE LENGTH is specified at CREATE TABLE time to account for existing and future subtypes.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).
- COMPRESS SYSTEM DEFAULT
- Specifies that system default values are to be stored using minimal
space. If the VALUE COMPRESSION clause is not specified, a warning
is returned (SQLSTATE 01648), and system default values are not stored
using minimal space.
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.
- COLUMN SECURED WITH security-label-name
- Identifies a security label that exists for the security policy that is associated with the table. The name must not be qualified (SQLSTATE 42601). The table must have a security policy associated with it (SQLSTATE 55064). The table must not be a system-period temporal table.
- period-definition
- PERIOD
- Defines a period for the table.
- SYSTEM_TIME (begin-column-name, end-column-name)
Defines a system period with the name SYSTEM_TIME. There must not be a column in the table with the name SYSTEM_TIME (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).
- BUSINESS_TIME (begin-column-name, end-column-name)
Defines an application period with the name BUSINESS_TIME. There must not be a column in the table with the name BUSINESS_TIME (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).
- unique-constraint
- Defines a unique or primary key constraint. If the table has a
distribution key, any unique or primary key must be a superset of
the distribution key. A unique or primary key constraint cannot be
specified for a table that is a subtable (SQLSTATE 429B3). Primary
or unique keys cannot be subsets of dimensions (SQLSTATE 429BE). If
the table is a root table, the constraint applies to the table and
all its subtables.
- CONSTRAINT constraint-name
- Names the primary key or unique constraint.
- UNIQUE (column-name, ...)
- Defines a unique key composed of the identified columns. The identified
columns must be defined as NOT NULL. Each column-name must
identify a column of the table and the same column must not be identified
more than once. If the table has a BUSINESS_TIME period defined, BUSINESS_TIME WITHOUT OVERLAPS can be specified as the last item in the key expression list. If BUSINESS_TIME WITHOUT OVERLAPS is specified, the list must include at least one column-name. WITHOUT OVERLAPS means that for the other specified keys, the values are unique with respect to time for the BUSINESS_TIME period. When BUSINESS_TIME WITHOUT OVERLAPS is specified, the columns of the BUSINESS_TIME period must not be specified as part of the constraint (SQLSTATE 428HW). The specification of BUSINESS_TIME WITHOUT OVERLAPS adds the following attributes to the constraint:
- The end column of the BUSINESS_TIME period in ascending order
- The begin column of the BUSINESS_TIME period in ascending order
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, if enforced, its unique index. If enforced, 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.
- PRIMARY KEY (column-name,...)
- Defines a primary key composed of the identified columns. The
clause must not be specified more than once, and the identified columns
must be defined as NOT NULL. Each column-name must
identify a column of the table, and the same column must not be identified
more than once. If the table has a BUSINESS_TIME period defined, BUSINESS_TIME WITHOUT OVERLAPS can be specified as the last item in the key expression list. If BUSINESS_TIME WITHOUT OVERLAPS is specified, the list must include at least one column-name. WITHOUT OVERLAPS means that for the rest of the specified keys, the values are unique with respect to time for the BUSINESS_TIME period. When BUSINESS_TIME WITHOUT OVERLAPS is specified, the columns of the BUSINESS_TIME period must not be specified as part of the constraint (SQLSTATE 428HW). The specification of BUSINESS_TIME WITHOUT OVERLAPS adds the following attributes to the constraint:
- The end column of the BUSINESS_TIME period in ascending order
- The begin column of the BUSINESS_TIME period in ascending order
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, if enforced, its primary index. If enforced, 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.
- referential-constraint
- Defines a referential constraint.
- CONSTRAINT constraint-name
- Names the referential constraint.
- FOREIGN KEY (column-name,...)
- Defines a referential constraint with the specified constraint-name.
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).
- references-clause
- Specifies the parent table or the parent nickname, and
the parent key for the referential constraint.
- REFERENCES table-name or nickname
- The table or nickname specified in a REFERENCES clause
must identify a base table or a nickname that is described
in the catalog, but must not identify a catalog table.
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 specified foreign key must have the same number of columns as the parent key of T2 and the description of the nth column of the foreign key must be comparable to the description of the nth column of that parent key. Datetime columns are not considered to be comparable to string columns for the purposes of this rule.- (column-name,...)
- The parent key of a referential constraint is composed of the
identified columns. Each column-name must
be an unqualified name that identifies a column of T2. The same column
must not be identified more than once.
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.
- rule-clause
- Specifies what action to take on dependent tables.
- ON DELETE
- Specifies what action is to take place on the dependent tables
when a row of the parent table is deleted. There are four possible
actions:
- NO ACTION (default)
- RESTRICT
- CASCADE
- SET NULL
The delete rule applies when a row of T2 is the object of a DELETE or propagated delete operation and that row has dependents in T1. Let p denote such a row of T2.- If RESTRICT or NO ACTION is specified, an error occurs and no rows are deleted.
- If CASCADE is specified, the delete operation is propagated to the dependents of p in T1.
- If SET NULL is specified, each nullable column of the foreign key of each dependent of p in T1 is set to null.
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.
The firing order of the rules is:- RESTRICT
- SET NULL OR CASCADE
- NO ACTION
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):
- A table must not be both a dependent table in a CASCADE relationship (self-referencing, or referencing another table), and have a self-referencing relationship in which the delete rule is RESTRICT or SET NULL.
- A key overlaps another key when at least one column in one key is the same as a column in the other key. When a table is delete-connected to another table through multiple relationships with overlapping foreign keys, those relationships must have the same delete rule, and none of the delete rules can be SET NULL.
- When a table is delete-connected to another table through multiple relationships, and at least one of those relationships is specified with a delete rule of SET NULL, the foreign key definitions of these relationships must not contain any distribution key or multidimensional clustering (MDC) key column.
- When two tables are delete-connected to the same table through CASCADE relationships, the two tables must not be delete-connected to each other if the delete rule of the last relationship in each delete-connected path is RESTRICT or SET NULL.
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.
- ON UPDATE
- Specifies what action is to take place on the dependent tables when a row of the parent table is updated. The clause is optional. ON UPDATE NO ACTION is the default and ON UPDATE RESTRICT is the only alternative.
The difference between NO ACTION and RESTRICT is described in the "Notes" section.
- check-constraint
- Defines a check constraint. A check-constraint is
a search-condition that must evaluate to
not false or a functional dependency that is defined between columns.
- CONSTRAINT constraint-name
- Names the check constraint.
- CHECK (check-condition)
- Defines a check constraint. The search-condition must
be true or unknown for every row of the table.
- search-condition
- The search-condition has the following
restrictions:
- A column reference must be to a column of the table being created.
- The search-condition cannot contain a TYPE predicate.
- The search-condition cannot contain
any of the following (SQLSTATE 42621):
- Subqueries
- XMLQUERY or XMLEXISTS expressions
- Dereference operations or DEREF functions where the scoped reference argument is other than the object identifier (OID) column
- CAST specifications with a SCOPE clause
- Column functions
- Functions that are not deterministic
- Functions defined to have an external action
- User-defined functions defined with either MODIFIES SQL or READS SQL DATA
- Host variables
- Parameter markers
- sequence-references
- OLAP specifications
- Special registers and built-in functions that depend on the value of a special register
- Global variables
- References to generated columns other than the identity column
- References to columns of type XML (except in a VALIDATED predicate)
- An error tolerant nested-table-expression
- functional-dependency
- Defines a functional dependency between columns.
- column-name DETERMINED BY column-name or (column-name,...) DETERMINED BY (column-name,...)
- The parent set of columns contains the identified columns that immediately precede the DETERMINED BY clause. The child set of columns contains the identified columns that immediately follow the DETERMINED BY clause. All of the restrictions on the search-condition apply to parent set and child set columns, and only simple column references are allowed in the set of columns (SQLSTATE 42621). The same column must not be identified more than once in the functional dependency (SQLSTATE 42709). The data type of the column must not be a LOB data type, a distinct type based on a LOB data type, an XML data type, or a structured type (SQLSTATE 42962). A ROW CHANGE TIMESTAMP column cannot be used as part of a primary key (SQLSTATE 429BV). No column in the child set of columns can be a nullable column (SQLSTATE 42621).
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.
- constraint-attributes
- Defines attributes associated with primary key, unique, referential
integrity, or check constraints.
- ENFORCED or NOT ENFORCED
- Specifies whether the constraint is enforced by the database manager
during normal operations such as insert, update, or delete. The default
is ENFORCED.
- ENFORCED
- The constraint is enforced by the database manager. ENFORCED cannot be specified for a functional dependency (SQLSTATE 42621). ENFORCED cannot be specified when a referential constraint refers to a nickname (SQLSTATE 428G7).
- NOT ENFORCED
- The constraint is not enforced by the database manager. A primary key constraint or unique constraint
cannot be NOT ENFORCED if there is a dependent ENFORCED referential
constraint.
- TRUSTED
- The data can be trusted to conform to the constraint. TRUSTED must be used only if the data in the table is independently known to conform to the constraint. Query results might be unpredictable if the data does not actually conform to the constraint. This is the default option.
- Informational constraints must not be violated at any time. Informational constraints are used in query optimization, as well as the incremental processing of REFRESH IMMEDIATE MQT and staging tables. These processes might produce unpredictable results or incorrect MQT and staging table content if the constraints are violated. For example, the order in which parent-child tables are maintained is important. When you want to add rows to a parent-child table, you must insert rows into the parent table first. To remove rows from a parent-child table, you must delete rows from the child table first. This ensures that there are no orphan rows in the child table at any time. If informational constraints are violated, the incremental maintenance of dependent MQT data and staging table data might be optimized based on the violated informational constraints, producing incorrect data.
- NOT TRUSTED
- The data cannot be trusted to conform to the constraint. NOT TRUSTED is intended for cases where the data conforms to the constraint for most rows, but it is not independently known that all the rows or future additions will conform to the constraint. If a constraint is NOT TRUSTED and enabled for query optimization, then it will not be used to perform optimizations that depend on the data conforming completely to the constraint. NOT TRUSTED can be specified only for referential integrity constraints (SQLSTATE 42613).
- ENABLE QUERY OPTIMIZATION or DISABLE QUERY OPTIMIZATION
- Specifies whether the constraint or functional dependency can
be used for query optimization under appropriate circumstances. The
default is ENABLE QUERY OPTIMIZATION.
- ENABLE QUERY OPTIMIZATION
- The constraint is assumed to be true and can be used for query optimization.
- DISABLE QUERY OPTIMIZATION
- The constraint cannot be used for query optimization. DISABLE QUERY OPTIMIZATION cannot be specified for primary key and unique constraints (SQLSTATE 42613).
- OF type-name1
- Specifies that the columns of the table are based on the attributes
of the structured type identified by type-name1.
If type-name1 is specified without a schema
name, the type name is resolved by searching the schemas on the SQL
path (defined by the FUNCPATH preprocessing option for static SQL
and by the CURRENT PATH register for dynamic SQL). The type name must
be the name of an existing user-defined type (SQLSTATE 42704) and
it must be an instantiable structured type (SQLSTATE 428DP) with
at least one attribute (SQLSTATE 42997).
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.
- HIERARCHY hierarchy-name
- Names the hierarchy table associated with the table hierarchy. It is created at the same time as the root table of the hierarchy. The data for all subtables in the typed table hierarchy is stored in the hierarchy table. A hierarchy table cannot be directly referenced in SQL statements. A hierarchy-name is a table-name. The hierarchy-name, including the implicit or explicit schema name, must not identify a table, nickname, view, or alias described in the catalog. If the schema name is specified, it must be the same as the schema name of the table being created (SQLSTATE 428DQ). If this clause is omitted when defining the root table, a name is generated by the system. This name consists of the name of the table being created, followed by a unique suffix, such that the identifier is unique among the identifiers of existing tables, views, and nicknames.
- UNDER supertable-name
- Indicates that the table is a subtable of supertable-name.
The supertable must be an existing table (SQLSTATE 42704) and the
table must be defined using a structured type that is the immediate
supertype of type-name1 (SQLSTATE 428DB).
The schema name of table-name and supertable-name must
be the same (SQLSTATE 428DQ). The table identified by supertable-name must
not have any existing subtable already defined using type-name1 (SQLSTATE
42742).
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).
- INHERIT SELECT PRIVILEGES
- Any user or group holding a SELECT privilege on the supertable will be granted an equivalent privilege on the newly created subtable. The subtable definer is considered to be the grantor of this privilege.
- typed-element-list
- Defines the additional elements of a typed table. This includes
the additional options for the columns, the addition of an object
identifier column (root table only), and constraints on the table.
- OID-column-definition
- Defines the object identifier column for the typed table.
- REF IS OID-column-name USER GENERATED
- Specifies that an object identifier (OID) column is defined in the table as the first column. An OID is required for the root table of a table hierarchy (SQLSTATE 428DX). The table must be a typed table (the OF clause must be present) that is not a subtable (SQLSTATE 42613). The name for the column is defined as OID-column-name and cannot be the same as the name of any attribute of the structured type type-name1 (SQLSTATE 42711). The column is defined with type REF(type-name1), NOT NULL and a system required unique index (with a default index name) is generated. This column is referred to as the object identifier column or OID column. The keywords USER GENERATED indicate that the initial value for the OID column must be provided by the user when inserting a row. Once a row is inserted, the OID column cannot be updated (SQLSTATE 42808).
- with-options
- Defines additional options that apply to columns of a typed table.
- column-name
- Specifies the name of the column for which additional options
are specified. The column-name must correspond
to the name of a column of the table that is not also a column of
a supertable (SQLSTATE 428DJ). A column name can only appear in one
WITH OPTIONS clause in the statement (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.
- WITH OPTIONS column-options
- Defines options for the specified column. See column-options described earlier. If the table is a subtable, primary key or unique constraints cannot be specified (SQLSTATE 429B3).
- LIKE table-name1 or view-name or nickname
- Specifies that the columns of the table have exactly the same
name and description as the columns of the identified table (table-name1),
view (view-name) or nickname (nickname).
The name specified after LIKE must identify a table, view or nickname
that exists in the catalog, or a declared temporary table. A typed
table or typed view cannot be specified (SQLSTATE 428EC). The use of LIKE is an implicit definition of n columns, where n is the number of columns in the identified table (including implicitly hidden columns), view, or nickname. A column of the new table that corresponds to an implicitly hidden column in the existing table will also be defined as implicitly hidden. The implicit definition depends on what is identified after LIKE:
- If a table is identified, then the implicit definition includes the column name, data type, hidden attribute, and nullability characteristic of each of the columns of table-name1. If EXCLUDING COLUMN DEFAULTS is not specified, then the column default is also included.
- If a view is identified, then the implicit definition includes the column name, data type, and nullability characteristic of each of the result columns of the fullselect defined in view-name. The data types of the view columns must be data types that are valid for columns of a table.
- If a nickname is identified, then the implicit definition includes the column name, data type, and nullability characteristic of each column of nickname.
- If a protected table is identified in the LIKE clause, the new table inherits the same security policy and protected columns as the identified table.
- If a table is identified in the LIKE clause and the table contains a row-begin column, row-end column, or transaction-start-ID column, the corresponding column of the new table inherits only the data type of the source column. The new column is not considered a generated column.
- When a table that includes a period is identified in the LIKE clause, the new table does not inherit the period definition.
- When a system-period temporal table is identified in the LIKE clause, the new table is not a system-period temporal table.
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.
- copy-options
- These options specify whether or not to copy additional attributes
of the source result table definition (table, view or fullselect).
- INCLUDING COLUMN DEFAULTS
- Column defaults for each updatable column of the source result
table definition are copied. Columns that are not updatable will not
have a default defined in the corresponding column of the created
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.
- EXCLUDING COLUMN DEFAULTS
- Columns defaults are not copied from the source result table definition.
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.
- INCLUDING IDENTITY COLUMN ATTRIBUTES
- Identity column attributes are copied from the source result table
definition, if possible. It is possible to copy the identity column
attributes, if the element of the corresponding column in the table,
view, or fullselect is the name of a table column, or the name of
a view column which directly or indirectly maps to the name of a base
table column with the identity property. In all other cases, the columns
of the new table will not get the identity property. For example:
- the select-list of the fullselect includes multiple instances of an identity column name (that is, selecting the same column more than once)
- the select list of the fullselect includes multiple identity columns (that is, it involves a join)
- the identity column is included in an expression in the select list
- the fullselect includes a set operation (union, except, or intersect).
- EXCLUDING IDENTITY COLUMN ATTRIBUTES
- Identity column attributes are not copied from the source result table definition.
- as-result-table
- column-name
- Names the columns in the table. If a list of column names is specified,
it must consist of as many names as there are columns in the result
table of the fullselect. Each column-name must
be unique and unqualified. If a list of column names is not specified,
the columns of the table inherit the names of the columns of the result
table of the fullselect.
A list of column names must be specified if the result table of the fullselect has duplicate column names 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.
- AS
- Introduces the query that is used for the definition of the table.
- fullselect
- Defines the query on which the table is based. The resulting column
definitions are the same as those for a view defined with the same
query. A column of the new table that corresponds to an implicitly
hidden column of a base table referenced in the fullselect is
not considered hidden in the new table.
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.
- WITH NO DATA
- The query is used only to define the table. The table is not populated
using the results of the query.
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.
- materialized-query-definition
- column-name
- Names the columns in the table. If a list of column names is specified, it must consist of as
many names as there are columns in the result table of the fullselect. Each
column-name must be unique and unqualified. If a list of column names is
not specified, the columns of the table inherit the names of the columns of the result table of the
fullselect.
A list of column names must be specified if the result table of the fullselect has duplicate column names 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.
If MAINTAINED BY REPLICATION is specified, the column names in the column list must match the names of the columns from the table that is specified in the fullselect.
- AS
- Introduces the query that is used for the definition of the table and that determines the data to be included in the table.
- fullselect
- Defines the query on which the table is based. The resulting column definitions are the same as
those for a view defined with the same query. A column of the new table that corresponds to an
implicitly hidden column of a base table referenced in the fullselect is not considered hidden in
the new table.
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), the FETCH FIRST clause, or the ORDER BY clause (SQLSTATE 428FJ).
When REFRESH DEFERRED or REFRESH IMMEDIATE is specified, the fullselect cannot include (SQLSTATE 428EC):- References to a materialized query table, created temporary table, declared temporary table, or typed table in any FROM clause
- References to a view where the fullselect of the view violates any of the listed restrictions on the fullselect of the materialized query table
- Expressions that are a reference type (or distinct type based on this type)
- Functions that have any of the following attributes:
- EXTERNAL ACTION
- LANGUAGE SQL
- CONTAINS SQL
- READS SQL DATA
- MODIFIES SQL DATA
- NOT SECURED functions if the functions reference a materialized query table which then references a table that has row or column access control activated.
- Functions that depend on physical characteristics (for example, DBPARTITIONNUM, HASHEDVALUE, RID_BIT, RID)
- A ROW CHANGE expression or reference to a ROW CHANGE TIMESTAMP column of the row
- Table or view references to system objects (Explain tables also should not be specified)
- Expressions that are a structured type, LOB type (or a distinct type based on a LOB type), or XML type
- References to a protected table or protected nickname
When DISTRIBUTE BY REPLICATION is specified, the following restrictions apply:- The GROUP BY clause is not allowed.
- The materialized query table must only reference a single table; that is, it cannot include a join.
When MAINTAINED BY REPLICATION is specified, the following restrictions apply:- The query must be a subselect consisting of only a SELECT clause and a FROM clause.
- The FROM clause must reference a single table that is organized by row and that is not specified in an existing shadow table definition.
- The referenced table cannot be a range-partitioned table, a multidimensional clustering table, a range-clustered table, a temporal table, or a table that contains a LONG VARCHAR or LONG VARGRAPHIC column.
- The referenced table cannot be protected by row and column access control (RCAC) or label-based access control (LBAC).
- The select list can include only direct references to the columns of the table whose data types are supported in a column-organized table. No expressions can be used.
- The columns that are specified in the select list cannot be renamed by using the column name list or the AS clause in the select list.
- The referenced table must have at least one enforced primary key constraint or unique constraint, and the columns that are specified in the select list must include all the key columns from at least one of these constraints.
When REFRESH IMMEDIATE is specified:- The query must be a subselect, with the exception that UNION ALL is supported in the input table expression of a GROUP BY.
- The query cannot be recursive.
- The query cannot include:
- References to a nickname
- Functions that are not deterministic
- Scalar fullselects
- Predicates with fullselects
- Special registers and built-in functions that depend on the value of a special register
- Global variables
- SELECT DISTINCT
- An error tolerant nested-table-expression
- If the FROM clause references more than one table or view, it can only define an inner join without using the explicit INNER JOIN syntax.
- When a GROUP BY clause is specified, the following considerations apply:
- The supported column functions are SUM, COUNT, COUNT_BIG and GROUPING (without DISTINCT). The select list must contain a COUNT(*) or COUNT_BIG(*) column. If the materialized query table select list contains SUM(X), where X is a nullable argument, the materialized query table must also have COUNT(X) in its select list. These column functions cannot be part of any expressions.
- A HAVING clause is not allowed.
- If in a multiple partition database partition group, the distribution key must be a subset of the GROUP BY items.
- The materialized query table must not contain duplicate rows, and the following restrictions
specific to this uniqueness requirement apply, depending upon whether or not a GROUP BY clause is
specified.
- When a GROUP BY clause is specified, the following uniqueness-related restrictions apply:
- All GROUP BY items must be included in the select list.
- When the GROUP BY contains GROUPING SETS, CUBE, or ROLLUP, the GROUP BY items and associated
GROUPING column functions in the select list must form a unique key of the result set. Thus, the
following restrictions must be satisfied:
- No grouping sets can be repeated. For example, ROLLUP(X,Y),X is not allowed, because it is equivalent to GROUPING SETS((X,Y),(X),(X)).
- If X is a nullable GROUP BY item that appears within GROUPING SETS, CUBE, or ROLLUP, then GROUPING(X) must appear in the select list.
- When a GROUP BY clause is not specified, the following uniqueness-related restrictions apply:
- The materialized query table's uniqueness requirement is achieved by deriving a unique key for the materialized view from one of the unique key constraints defined in each of the underlying tables. Therefore, the underlying tables must have at least one unique key constraint defined on them, and the columns of these keys must appear in the select list of the materialized query table definition.
- When a GROUP BY clause is specified, the following uniqueness-related restrictions apply:
When REFRESH DEFERRED is specified:- If the materialized query table is created with the intention of providing it with an associated staging table in a later statement, the fullselect of the materialized query table must follow the same restrictions and rules as a fullselect used to create a materialized query table with the REFRESH IMMEDIATE option.
- If the query is recursive, the materialized query table is not used to optimize the processing of queries.
- The materialized query table is not used to optimize the processing of static queries.
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 SQL compiler to the materialized query table.
- refreshable-table-options
- Define the refreshable options of the materialized query table
attributes.
- DATA INITIALLY DEFERRED
- Data is not inserted into the table as part of the CREATE TABLE statement. A REFRESH TABLE statement specifying the table-name is used to insert data into the table.
- REFRESH
- Indicates how the data in the table is maintained.
- DEFERRED
- The data in the table can be refreshed at any time using the REFRESH TABLE statement. The data in the table only reflects the result of the query as a snapshot at the time the REFRESH TABLE statement is processed. System-maintained materialized query tables defined with this attribute do not allow INSERT, UPDATE, or DELETE statements (SQLSTATE 42807). User-maintained materialized query tables defined with this attribute do allow INSERT, UPDATE, or DELETE statements.
- IMMEDIATE
- The changes made to the underlying tables as part of a DELETE, INSERT, or UPDATE are cascaded to the materialized query table. In this case, the content of the table, at any point-in-time, is the same as if the specified subselect is processed. Materialized query tables defined with this attribute do not allow INSERT, UPDATE, or DELETE statements (SQLSTATE 42807).
- ENABLE QUERY OPTIMIZATION
- The materialized query table can be used for query optimization under appropriate circumstances.
- DISABLE QUERY OPTIMIZATION
- The materialized query table will not be used for query optimization. The table can still be queried directly.
- MAINTAINED BY
- Specifies whether the data in the materialized query table is
maintained by the system, user, or replication tool. The default is
SYSTEM.
- SYSTEM
- Specifies that the data in the materialized query table is maintained by the system.
- USER
- Specifies that the data in the materialized query table is maintained by the user. The user is allowed to perform update, delete, or insert operations against user-maintained materialized query tables. The REFRESH TABLE statement, used for system-maintained materialized query tables, cannot be invoked against user-maintained materialized query tables. Only a REFRESH DEFERRED materialized query table can be defined as MAINTAINED BY USER.
- REPLICATION
- Specifies that the data in the materialized query table is maintained by an external replication technology. The REFRESH TABLE statement, used for system-maintained materialized query tables, cannot be issued against replication-maintained materialized query tables, which are referred to as shadow tables. Only a REFRESH DEFERRED materialized query table can be defined as MAINTAINED BY REPLICATION, and the definition must include ORGANIZE BY COLUMN.
- FEDERATED_TOOL
- Specifies that the data in the materialized query table is maintained
by a federated replication tool. The REFRESH TABLE statement, used
for system-maintained materialized query tables, cannot be invoked
against federated_tool-maintained materialized query tables. Only
a REFRESH DEFERRED materialized query table can be defined as MAINTAINED
BY FEDERATED_TOOL.
When specifying this option, the select clause in the CREATE TABLE statement cannot contain a reference to a base table (SQLSTATE 428EC).
- staging-table-definition
- Defines the query supported by the staging table indirectly through
an associated materialized query table. The underlying tables of the
materialized query table are also the underlying tables for its associated
staging table. The staging table collects changes that need to be
applied to the materialized query table to synchronize it with the
contents of the underlying tables.
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.
- staging-column-name
- Names the columns in the staging table. If a list of column names
is specified, it must consist of two more names than there
are columns in the materialized query table for which the staging
table is defined. If the materialized query table is a replicated
materialized query table, or the query defining the materialized
query table does not contain a GROUP BY clause, the list of column
names must consist of three more names than there are columns
in the materialized query table for which the staging table is defined.
Each column name must be unique and unqualified. If a list of column
names is not specified, the columns of the table inherit the names
of the columns of the associated materialized query table. The additional
columns are named GLOBALTRANSID and GLOBALTRANSTIME, and if a third
column is necessary, it is named OPERATIONTYPE.
Table 2. Extra Columns Appended in Staging Tables 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).
- FOR table-name2
- Specifies the materialized query table that is used for the definition
of the staging table. The name, including the implicit or explicit
schema, must identify a materialized query table that exists at the
current server defined with REFRESH DEFERRED. The fullselect of the
associated materialized query table must follow the same restrictions
and rules as a fullselect used to create a materialized query table
with the REFRESH IMMEDIATE option.
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.
- PROPAGATE IMMEDIATE
- The changes made to the underlying tables as part of a delete, insert, or update operation are cascaded to the staging table in the same delete, insert, or update operation. If the staging table is not marked inconsistent, its content, at any point-in-time, is the delta changes to the underlying table since the last refresh materialized query table.
- ORGANIZE BY
- Specifies how the data is organized in the data pages of the table.
If this clause is not specified, the default organization of ROW or
COLUMN is determined by the value of the dft_table_org database
configuration parameter, which defaults to ROW.The following restrictions apply to a column-organized MQT:
- MQTs other than shadow tables must reference tables with the same organization as the MQT.
- The ORGANIZE BY COLUMN clause must be specified when creating a column-organized MQT.
- For a column-organized MQT, only shadow tables and user-maintained MQTs are allowed.
- ROW
- The data is stored by row in the data pages of the table. A given data page stores the data for one or more rows of the table.
- COLUMN
- The data is stored by column in the data pages of the table. A given data page stores data for one column of the table.
- ROW USING
- The data is stored by row in the data pages of the table and further
organized using the specification in the clause that follows. If the
default table organization for the database is COLUMN, this clause
must be specified when organizing the data by dimensions or insert
time.
- DIMENSIONS (column-name,...)
- Specifies a dimension for each column or group of columns used to cluster the table data. The
use of parentheses within the dimension list specifies that a group of columns is to be treated as
one dimension. The DIMENSIONS keyword is optional. A table whose definition specifies this clause is
known as a multidimensional clustering (MDC) table.
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). If the table uses extended row size, each dimension column with a data type of VARCHAR or VARGRAPHIC cannot have a length attribute that is greater than 24 bytes (SQLSTATE 54010).
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.
- INSERT TIME
- Specifies that rows are clustered in the table relative to the
time they are inserted. Rows are inserted at the logical end of the
table object instead of searching for available space. A table which
is organized by insert time is known as an insert time clustering
(ITC) table. This type of table can use REORG TABLE RECLAIM EXTENTS
to reclaim free extents for immediate use by other objects in the
table space.
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).
- KEY SEQUENCE sequence-key-spec
- Specifies that the table is organized in ascending key sequence
with a fixed size based on the specified range of key sequence values.
A table organized in this way is referred to as a range-clustered
table. Each possible key value in the defined range has a predetermined
location in the physical table. The storage required for a range-clustered
table must be available when the table is created, and must be sufficient
to contain the number of rows in the specified range multiplied by
the row size (for details on determining the space requirement, see Row Size Limit and Byte Counts).
- column-name
- Specifies a column of the table that is included in the unique
key that determines the sequence of the range-clustered table. The
data type of the column must be SMALLINT, INTEGER, or BIGINT (SQLSTATE
42611), and the columns must be defined as NOT NULL (SQLSTATE 42831).
The same column must not be identified more than once in the sequence
key. The number of identified columns must not exceed 64 (SQLSTATE
54008).
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.
- STARTING FROM constant
- Specifies the constant value at the low end of the range for column-name. Values less than the specified constant are only allowed if the ALLOW OVERFLOW option is specified. If column-name is a SMALLINT or INTEGER column, the constant must be an INTEGER constant. If column-name is a BIGINT column, the constant must be an INTEGER or BIGINT constant (SQLSTATE 42821). If a starting constant is not specified, the default value is 1.
- ENDING AT constant
- Specifies the constant value at the high end of the range for column-name. Values greater than the specified constant are only allowed if the ALLOW OVERFLOW option is specified. The value of the ending constant must be greater than the starting constant. If column-name is a SMALLINT or INTEGER column, the constant must be an INTEGER constant. If column-name is a BIGINT column, the constant must be an INTEGER or BIGINT constant (SQLSTATE 42821).
- ALLOW OVERFLOW
- Specifies that the range-clustered table allows rows with key values that are outside of the defined range of values. When a range-clustered table is created to allow overflows, the rows with key values outside of the range are placed at the end of the defined range without any predetermined order. Operations involving these overflow rows are less efficient than operations on rows having key values within the defined range.
- DISALLOW OVERFLOW
- Specifies that the range-clustered table does not allow rows with
key values that are not within the defined range of values (SQLSTATE
23513). Range-clustered tables that disallow overflows will always
maintain all rows in ascending key sequence.
The DISALLOW OVERFLOW clause cannot be specified if the table is a range-clustered materialized query table (SQLSTATE 429BG).
- PCTFREE integer
- Specifies the percentage of each page that is to be left as free space. The first row on each page is added without restriction. When additional rows are added to a page, at least integer percent of the page is left as free space. The value of integer can range from 0 to 99. A PCTFREE value of -1 in the system catalog (SYSCAT.TABLES) is interpreted as the default value. The default PCTFREE value for a table page is 0.
- DATA CAPTURE
- Indicates
whether extra information for inter-database data replication is
to be written to the log. This clause cannot be specified when creating
a subtable (SQLSTATE 428DR).
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.
- NONE
- Indicates that no extra information will be logged.
- CHANGES
- Indicates that extra information regarding SQL changes
to this table will be written to the log. This option is required
if this table will be replicated and the Capture program is used to
capture changes for this table from the log.
If the table is a typed table that is not a subtable, then this option is not supported (SQLSTATE 428DH).
- IN tablespace-name,...
- Identifies the table spaces in which the table will be created.
The table spaces must exist, they must be in the same database partition
group, and they must be all regular DMS or all large DMS or all SMS
table spaces (SQLSTATE 42838) on which the authorization
ID of the statement holds the USE privilege.
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 and where the row size is within the row size limit of the page size on which the authorization ID of the statement has USE privilege.
If more than one table space qualifies, choose the table space in the following order of preference, depending how the authorization ID of the statement was granted USE privilege on the table space:- The authorization ID
- A role to which the authorization ID is granted
- A group to which the authorization ID belongs
- A role to which a group the authorization ID belongs is granted
- PUBLIC
- A role to which PUBLIC is granted
Table space determination can change if:- Table spaces are dropped or created
- USE privileges are granted or revoked
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.
When a table is placed in a large table space:- The table can be larger than a table in a regular table space. For details on table and table space limits, see "SQL limits".
- The table can support more than 255 rows per data page, which can improve space utilization on data pages.
- Indexes that are defined on the table will require an additional 2 bytes per row entry, compared to indexes defined on a table that resides in a regular table space.
- CYCLE or NO CYCLE
- Specifies whether or not the number of data partitions with no
explicit table space can exceed the number of specified table spaces.
- CYCLE
- Specifies that if the number of data partitions with no explicit table space exceeds the number of specified table spaces, the table spaces are assigned to data partitions in a round-robin fashion.
- NO CYCLE
- Specifies that the number of data partitions with no explicit table space must not exceed the number of specified tables spaces (SQLSTATE 428G1). This option prevents the round-robin assignment of table spaces to data partitions.
- tablespace-options
- Specifies the table space in which indexes or long column values
are to be stored. For details on types of table spaces, see "CREATE
TABLESPACE".
- INDEX IN tablespace-name
- Identifies the table space in which any indexes
on a nonpartitioned table or nonpartitioned indexes on a partitioned
table are to be created. The specified table space must exist; it
must be a DMS table space if the table has data in DMS table spaces,
or an SMS table space if the partitioned table has data in SMS table
spaces; it must be a table space on which the authorization ID of
the statement holds the USE privilege; and it must be in the same
database partition group as tablespace-name (SQLSTATE
42838).
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.
For a nonpartitioned index on a partitioned table, storage of the index is as follows:- The table space by the IN clause of the CREATE INDEX statement
- The table-level table space specified for the INDEX IN clause of the CREATE TABLE statement
- If neither of the preceding are specified, the index is stored in the table space of the first attached or visible data partition
- LONG IN tablespace-name
- Identifies the table spaces in which the values of any long columns
are to be stored. Long columns include those
with LOB data types, XML type, distinct types with any
of these as source types, or any columns defined with user-defined
structured types whose values cannot be stored inline. This option
is allowed only if the IN clause identifies a DMS table space.
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".
- distribution-clause
- Specifies the database partitioning or the way the data is distributed
across multiple database partitions.
- DISTRIBUTE BY HASH (column-name,...)
- Specifies
the use of the default hashing function on the specified columns,
called a distribution key, as the distribution method across
database partitions. The column-name must
be an unqualified name that identifies a column of the table (SQLSTATE
42703). The same column must not be identified more than once (SQLSTATE
42709). No column whose data type is BLOB,
CLOB, DBCLOB, XML, distinct type based on any of these types,
or structured type can be used as part of a distribution key (SQLSTATE
42962). The distribution key cannot contain a ROW CHANGE TIMESTAMP
column (SQLSTATE 429BV). A distribution key cannot be specified
for a table that is a subtable (SQLSTATE 42613), because the distribution
key is inherited from the root table in the table hierarchy or
a table with a column of data type XML (SQLSTATE 42997). A distribution
key cannot contain row begin/row end/transaction start id columns.
If this clause is not specified, and the table resides in a multiple
partition database partition group with multiple database partitions,
the distribution key is defined as follows:
- If the table is a typed table, the object identifier column is the distribution key.
- If a primary key is defined, the first column of the primary key is the distribution key.
- Otherwise, the first column whose data type is valid for a distribution key becomes the distribution key.
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.
- DISTRIBUTE BY REPLICATION
- Specifies that the data stored in the table is physically replicated on each database partition of the database partition group for the table spaces in which the table is defined. This means that a copy of all of the data in the table exists on each database partition. This option can only be specified for a materialized query table (SQLSTATE 42997).
- partitioning-clause
- Specifies how the data is partitioned within a database partition.
- PARTITION BY RANGE range-partition-spec
- Specifies
the table partitioning scheme for the table.
- partition-expression
- Specifies the key data over which the range is defined to determine
the target data partition of the data.
- column-name
- Identifies
a column of the table-partitioning key. The column-name must
be an unqualified name that identifies a column of the table (SQLSTATE
42703). The same column must not be identified more than once (SQLSTATE
42709). No column with a data type that is a BLOB,
CLOB, DBCLOB, XML, distinct type based
on any of these types, or structured type can be used as part of a
table-partitioning key (SQLSTATE 42962).
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).
- NULLS LAST or NULLS FIRST
- Indicates the partition placement of rows that have null values
in the table partitioning key columns. These clauses do not affect
the order of rows that are returned in an ORDER BY clause.
- NULLS LAST
- Indicates that null values are compared as the highest possible value, and are placed in a range ending at MAXVALUE.
- NULLS FIRST
- Indicates that null values are compared as the lowest possible value, and are placed in a range starting at MINVALUE.
- partition-element
- Specifies ranges for a data partitioning key and the table space
where rows of the table in the range will be stored.
- PARTITION partition-name
- Names the data partition. The name must not be the same as any other data partition for the table (SQLSTATE 42710). If this clause is not specified, the name will be 'PART' followed by the character form of an integer value to make the name unique for the table.
- boundary-spec
- Specifies the boundaries of a data partition. The lowest data
partition must include a starting-clause, and the highest data partition
must include an ending-clause (SQLSTATE 56016). Data partitions between
the lowest and the highest can include either a starting-clause, ending-clause,
or both clauses. If only the ending-clause is specified, the previous
data partition must also have included an ending-clause (SQLSTATE
56016).
- starting-clause
- Specifies the low end of the range for a data partition. There
must be at least one starting value specified and no more values than
the number of columns in the data partitioning key (SQLSTATE 53038).
If there are fewer values specified than the number of columns, the
remaining values are implicitly MINVALUE.
- STARTING FROM
- Introduces the starting-clause.
- constant
- Specifies a constant value with a data type that is assignable to the data type of the column-name to which it corresponds (SQLSTATE 53045). The value must not be in the range of any other boundary-spec for the table (SQLSTATE 56016).
- MINVALUE
- Specifies a value that is lower than the lowest possible value for the data type of the column-name to which it corresponds.
- MAXVALUE
- Specifies a value that is greater than the greatest possible value for the data type of the column-name to which it corresponds.
- INCLUSIVE
- Indicates that the specified range values are to be included in the data partition.
- EXCLUSIVE
- Indicates that the specified constant values are to be excluded from the data partition. This specification is ignored when MINVALUE or MAXVALUE is specified.
- ending-clause
- Specifies the high end of the range for a data partition. There
must be at least one starting value specified and no more values than
the number of columns in the data partitioning key (SQLSTATE 53038).
If there are fewer values specified than the number of columns, the
remaining values are implicitly MAXVALUE.
- ENDING AT
- Introduces the ending-clause.
- constant
- Specifies a constant value with a data type that is assignable to the data type of the column-name to which it corresponds (SQLSTATE 53045). The value must not be in the range of any other boundary-spec for the table (SQLSTATE 56016).
- MINVALUE
- Specifies a value that is lower than the lowest possible value for the data type of the column-name to which it corresponds.
- MAXVALUE
- Specifies a value that is greater than the greatest possible value for the data type of the column-name to which it corresponds.
- INCLUSIVE
- Indicates that the specified range values are to be included in the data partition.
- EXCLUSIVE
- Indicates that the specified constant values are to be excluded from the data partition. This specification is ignored when MINVALUE or MAXVALUE is specified.
- IN tablespace-name
- Specifies the table space where the data partition is to be stored.
The named table space must have the same page size, be in the same
database partition group, and manage space in the same way as the
other table spaces of the partitioned table (SQLSTATE 42838); it must
be a table space on which the authorization ID of the statement holds
the USE privilege. If this clause is not specified, a table space
is assigned by default in a round-robin fashion from the list of table
spaces specified for the table. If a table space was not specified
for large objects using the LONG IN clause, large objects are placed
in the same table space as are the rest of the rows for the data partition.
For partitioned tables, the LONG IN clause can be used to provide
a list of table spaces. This list is used in round robin-fashion to
place large objects for each data partition. For rules governing
the use of the LONG IN clause with partitioned tables, see "Large
object behavior in partitioned tables".
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.
- INDEX IN tablespace-name
- Specifies the table space where the partitioned index on the partitioned
table is to be stored.
The partition-element level INDEX IN clause only affects the storage of partitioned indexes. Storage of the index is as follows:
- If the INDEX IN clause is specified at the partition level when the table is created, the partitioned index is stored in the specified table space.
- If the INDEX IN clause is not specified at the partition level when the table is created, the partitioned index is stored in the table space of the corresponding data partition.
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).
- LONG IN tablespace-name
- Identifies the table spaces in which the values of any long columns
are to be stored. Long columns include those with LOB data types,
XML type, distinct types with any of these as source types, or any
columns defined with user-defined structured types whose values cannot
be stored inline. This option is allowed only if the IN clause identifies
a DMS table space.
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".
- EVERY (constant)
- Specifies the width of each data partition range when using the
automatically generated form of the syntax. Data partitions will be
created starting at the STARTING FROM value and containing this number
of values in the range. This form of the syntax is only supported
for tables that are partitioned by a single numeric or datetime column
(SQLSTATE 53038).
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).
- COMPRESS
- Specifies whether data compression applies to the rows of the
table
- NO
- Specifies that data row compression is disabled.
- YES
- Specifies that data row compression is enabled. Insert and update
operations on the table will be subject to compression. Any XML storage objects that exist are also compressed. For both adaptive and classic row compression,
a table-level compression dictionary is automatically created after
the table is sufficiently populated with data. This
also applies to the data in the XML storage object; if there is sufficient
data in the XML storage object, a compression dictionary is automatically
created and XML documents are subject to compression. Note: The compression applied to the XML storage object is the same, regardless of whether you use adaptive or classic row compression.
For adaptive row compression, page-level compression dictionaries are created or updated as soon as data is inserted or changed in the table.
- ADAPTIVE
- Enables adaptive compression, and records are subject to being compressed with a table-level and a page-level compression dictionary. This is the default option when COMPRESS YES is specified. The functionality of COMPRESS YES ADAPTIVE is a superset of the functionality of COMPRESS YES STATIC.
- STATIC
- Enables classic row compression using a table-level compression dictionary. This is the same row compression functionality that existed in previous DB2 versions.
- VALUE COMPRESSION
- This determines the row format that is to be used. Each data type
has a different byte count depending on the row format that is used.
For more information, see Byte Counts.
If the table is a typed table, this option is only supported on the
root table of the typed table hierarchy (SQLSTATE 428DR).
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.
- WITH RESTRICT ON DROP
- Indicates that the table cannot be dropped, and that the table space that contains the table cannot be dropped.
- NOT LOGGED INITIALLY
- Any changes made to the table by an Insert, Delete, Update, Create
Index, Drop Index, or Alter Table operation in the same unit of work
in which the table is created are not logged. For other considerations
when using this option, see the "Notes" section of this statement.
All catalog changes and storage related information are logged, as are all operations that are done on the table in subsequent units of work.
Note: If non-logged activity occurs against a table that has the NOT LOGGED INITIALLY attribute activated, and if a statement fails (causing a rollback), or a ROLLBACK TO SAVEPOINT is executed, the entire unit of work is rolled back (SQL1476N). Furthermore, the table for which the NOT LOGGED INITIALLY attribute was activated is marked inaccessible after the rollback has occurred, and can only be dropped. Therefore, the opportunity for errors within the unit of work in which the NOT LOGGED INITIALLY attribute is activated should be minimized. - CCSID
- Specifies the encoding scheme for string data stored in the table.
If the CCSID clause is not specified, the default is CCSID UNICODE
for Unicode databases, and CCSID ASCII for all other databases.
- ASCII
- Specifies that string data is encoded in the database code page. If the database is a Unicode database, CCSID ASCII cannot be specified (SQLSTATE 56031).
- UNICODE
- Specifies that string data is encoded in Unicode. If the database
is a Unicode database, character data is in UTF-8, and graphic data
is in UCS-2. If the database is not a Unicode database, character
data is in UTF-8. If the database is not a Unicode database, tables can be created with CCSID UNICODE, but the following rules apply:
- The alternate collating sequence must be specified in the database configuration before creating the table (SQLSTATE 56031). CCSID UNICODE tables collate with the alternate collating sequence specified in the database configuration.
- Tables or table functions created with CCSID ASCII, and tables or table functions created with CCSID UNICODE, cannot both be used in a single SQL statement (SQLSTATE 53090). This applies to tables and table functions referenced directly in the statement, as well as to tables and table functions referenced indirectly (such as, for example, through referential integrity constraints, triggers, materialized query tables, and tables in the body of views).
- Tables created with CCSID UNICODE cannot be referenced in SQL functions or SQL methods (SQLSTATE 560C0).
- An SQL statement that references a table created with CCSID UNICODE cannot invoke an SQL function or SQL method (SQLSTATE 53090).
- Graphic types, the XML type, and user-defined types cannot be used in CCSID UNICODE tables (SQLSTATE 560C1).
- Anchored data types cannot anchor to columns of a table created with CCSID UNICODE (SQLSTATE 428HS).
- Tables cannot have both the CCSID UNICODE clause and the DATA CAPTURE CHANGES clause specified (SQLSTATE 42613).
- The Explain tables cannot be created with CCSID UNICODE (SQLSTATE 55002).
- Created temporary tables and declared temporary tables cannot be created with CCSID UNICODE (SQLSTATE 56031).
- CCSID UNICODE tables cannot be created in a CREATE SCHEMA statement (SQLSTATE 53090).
- The exception table for a load operation must have the same CCSID as the target table for the operation (SQLSTATE 428A5).
- The exception table for a SET INTEGRITY statement must have the same CCSID as the target table for the statement (SQLSTATE 53090).
- The target table for event monitor data must not be declared as CCSID UNICODE (SQLSTATE 55049).
- SQL statements are always interpreted in the database code page. In particular, this means that every character in literals, hex literals, and delimited identifiers must have a representation in the database code page; otherwise, the character will be replaced with the substitution character.
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. The database manager 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.
- SECURITY POLICY
- Names the security policy to be associated with the table.
- policy-name
- Identifies a security policy that already exists at the current server (SQLSTATE 42704).
- OPTIONS (table-option-name string-constant, ...)
- Table options are used to identify the remote base table. The table-option-name is
the name of the option. The string-constant specifies
the setting for the table option. The string-constant must
be enclosed in single quotation marks.
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.
Rules
- The sum of the byte counts of the columns, including the inline lengths of all structured or XML type columns, must not be greater than the row size limit that is based on the page size of the table space (SQLSTATE 54010). For more information, see Byte Counts. For typed tables, the byte count is applied to the columns of the root table of the table hierarchy, and every additional column introduced by every subtable in the table hierarchy (additional subtable columns must be considered nullable for byte count purposes, even if defined as not nullable). There is also an additional 4 bytes of overhead to identify the subtable to which each row belongs.
- The number of columns in a table cannot exceed 1,012 (SQLSTATE 54011). For typed tables, the total number of attributes of the types of all of the subtables in the table hierarchy cannot exceed 1010.
- An object identifier column of a typed table cannot be updated (SQLSTATE 42808).
- Any unique or primary key constraint defined on the table must be a superset of the distribution key (SQLSTATE 42997).
- The following rules only apply to multiple database partition
databases.
- Tables composed only of columns with types LOB, XML, a distinct type based on one of these types, or a structured type can only be created in table spaces that are defined on single-partition database partition groups.
- The distribution key definition of a table in a table space that is defined on a multiple partition database partition group cannot be altered.
- The distribution key column of a typed table must be the OID column.
- Partitioned staging tables are not supported.
- For databases running in a DB2 pureScale environment, the ORGANIZE BY clause cannot be specified (SQLSTATE 42997).
- The following restrictions apply to range-clustered tables:
- A clustering index cannot be created.
- Altering the table to add a column is not supported.
- Altering the table to change the data type of a column is not supported.
- Altering the table to change PCTFREE is not supported.
- Altering the table to set APPEND ON is not supported.
- DETAILED statistics are not available.
- The load utility cannot be used to populate the table.
- Columns cannot be of type XML.
- A table is not protected unless it has a security policy associated with it and it includes either a column of type DB2SECURITYLABEL or a column defined with the SECURED WITH clause. The former indicates that the table is a protected table with row level granularity and the latter indicates that the table a protected table with column level granularity.
- Declaring a column of type DB2SECURITYLABEL fails if the table does not have a security policy associated with it (SQLSTATE 55064).
- A security policy cannot be added to a typed table (SQLSTATE 428DH), materialized query table, or staging table (SQLSTATE 428FG).
- An error tolerant nested-table-expression cannot be specified in the fullselect of a materialized-query-definition (SQLSTATE 428GG).
- When creating a materialized
query table and any of the base tables it depends upon are protected
with label-based access control, the following rules apply:
- Row level security
- Only one table in the materialized query table's fullselect can have a column type of DB2SECURITYLABEL (SQLSTATE 428FG).
- The row security label column must be selected and referenced as a stand alone column in the outermost SELECT list in the materialized query table definition (SQLSTATE 428FG). The corresponding column in the materialized query table will be marked as the row security label column.
- Column level security
- If a table involved in the materialized query table definition has a column protected with a security label, and that column appears in the materialized query table definition, that column's security label is inherited by the corresponding column in the materialized query table. See the examples in this topic for more details.
- When creating a materialized query table that depends on one or more tables protected by label-based access control, all base tables must have the same security policy object (SQLSTATE, 428FG). The materialized query table will be automatically protected with that security policy object.
- The security label associated with a materialized query table column is computed as the aggregate of one or more security labels. This aggregate consists of the security labels associated with the base tables' columns that participate in the definition of that materialized query table column. The aggregate also consists of the security labels associated with any base table columns that appear in other parts of the materialized query table definition, such as the WHERE, ORDER BY, and HAVING clauses. The ALTER SECURITY POLICY has a description of how two security labels are aggregated. See the examples in this topic for more details.
- When a staging table is created for a materialized query table that is protected with label-based access control, that staging table carries automatic protection like the materialized query table. See the examples in this topic for more details.
- Label-based access control is enforced for direct access to a materialized query table just as it is enforced for a regular table. There are no differences from this perspective. When the SQL compiler services a query through a materialized query table, the label-based access control defined on the materialized query table itself does not need to be enforced. The SQL compiler uses the materialized query table which factors in the label-based access control rules from the appropriate base tables.
- Row level security
- The isolation-clause cannot be specified in the full-select of the materialized-query-table-definition (SQLSTATE 42601).
- Subselect statements containing a lock-request-clause are not be eligible for MQT routing.
- National character spellings for the graphic data types can be specified only in a Unicode database (SQLSTATE 560AA).
- The
following restrictions apply to insert time clustering (ITC) tables:
- ITC tables are not supported in an SMS table space (SQLSTATE 42838).
- Indexes defined on ITC tables are not supported in an SMS table space (SQLSTATE 42838).
Notes
- Creating a table with a schema name that does not already exist will result in the implicit creation of that schema provided the authorization ID of the statement has IMPLICIT_SCHEMA authority. The schema owner is SYSIBM. The CREATEIN privilege on the schema is granted to PUBLIC.
- If a foreign key is specified:
- All packages with a delete usage on the parent table are invalidated.
- All packages with an update usage on at least one column in the parent key are invalidated.
- Creating a subtable causes invalidation of all packages that depend on any table in table hierarchy.
- VARCHAR and VARGRAPHIC columns that are greater than 4,000 and 2,000 respectively should not be used as input parameters in functions in SYSFUN schema. Errors will occur when the function is invoked with an argument value that exceeds these lengths (SQLSTATE 22001).
- The use of NO ACTION or RESTRICT as delete or update rules for
referential constraints determines when the constraint is enforced.
A delete or update rule of RESTRICT is enforced before all
other constraints, including those referential constraints with modifying
rules such as CASCADE or SET NULL. A delete or update rule of
NO ACTION is enforced after other referential constraints.
One example where different behavior is evident involves the deletion
of rows from a view that is defined as a UNION ALL of related tables.
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 V1If 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.
- For tables in table spaces defined on multiple partition database
partition groups, table collocation should be considered when choosing
the distribution keys. Following is a list of items to consider:
- The tables must be in the same database partition group for collocation. The table spaces may be different, but must be defined in the same database partition group.
- The distribution keys of the tables must have the same number of columns, and the corresponding key columns must be database partition-compatible for collocation.
- The choice of distribution key also has an impact on performance of joins. If a table is frequently joined with another table, you should consider the joining column(s) as a distribution key for both tables.
- The NOT LOGGED INITIALLY option is useful for situations where
a large result set needs to be created with data from an alternate
source (another table or a file) and recovery of the table is not
necessary. Using this option will save the overhead of logging the
data. The following considerations apply when this option is specified:
- When the unit of work is committed, all changes that were made to the table during the unit of work are flushed to disk.
- When you run the rollforward utility and it encounters a log record that indicates that a table in the database was either populated by the Load utility or created with the NOT LOGGED INITIALLY option, the table will be marked as unavailable. The table will be dropped by the rollforward utility if it later encounters a DROP TABLE log. Otherwise, after the database is recovered, an error will be issued if any attempt is made to access the table (SQLSTATE 55019). The only operation permitted is to drop the table.
- Once such a table is backed up as part of a database or table space back up, recovery of the table becomes possible.
- Use of materialized query tables
to optimize query processing: The various types of materialized
query tables use different controls to optimize the processing of
queries.
- A REFRESH DEFERRED materialized query table defined with ENABLE
QUERY OPTIMIZATION can be used to optimize the processing of queries
if each of the following conditions is true:
- CURRENT REFRESH AGE is set to ANY.
- CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION is set such that it includes the materialized query table type.
- CURRENT QUERY OPTIMIZATION is set to 2 or a value greater than or equal to 5.
Note: CURRENT REFRESH AGE does not affect query routing to MAINTAINED BY FEDERATED_TOOL materialized query tables. - A shadow table defined with ENABLE QUERY OPTIMIZATION can be used to optimize
the processing of queries based on a replication latency threshold if each of the following
conditions is true:
- CURRENT REFRESH AGE is set to a duration other than zero or ANY.
- CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION is set to contain only REPLICATION or ALL.
- CURRENT QUERY OPTIMIZATION is set to 2 or a value greater than or equal to 5.
- A REFRESH IMMEDIATE materialized query table defined with ENABLE QUERY OPTIMIZATION is always considered for optimization if CURRENT QUERY OPTIMIZATION is set to 2 or a value greater than or equal to 5.
- For this optimization to be able to use a REFRESH DEFERRED materialized
query table that is not maintained by replication or a REFRESH IMMEDIATE materialized query
table, the fullselect must conform to certain rules in addition to those already described:
- The fullselect must not include any special registers or built-in functions that depend on the value of a special register.
- The fullselect must not include any global variables.
- The fullselect must not include functions that are not deterministic.
- A REFRESH DEFERRED materialized query table defined with ENABLE
QUERY OPTIMIZATION can be used to optimize the processing of queries
if each of the following conditions is true:
- If a materialized query table is defined with REFRESH IMMEDIATE, or a staging table is defined with PROPAGATE IMMEDIATE, it is possible for an error to occur when attempting to apply the change resulting from an insert, update, or delete operation on an underlying table. The error will cause the failure of the insert, update, or delete operation on the underlying table.
- Deadlocks may occur when multiple transactions concurrently change an underlying table of a REFRESH IMMEDIATE MQT. Consider a REFRESH DEFERRED MQT with an associated staging table as an alternative.
- Materialized query tables or staging tables cannot be used as exception tables when constraints are checked in bulk, such as during load operations or during execution of the SET INTEGRITY statement.
- Certain operations cannot be performed on a table that is referenced
by a materialized query table defined with REFRESH IMMEDIATE, or defined
with REFRESH DEFERRED with an associated staging table:
- IMPORT REPLACE cannot be used.
- ALTER TABLE NOT LOGGED INITIALLY WITH EMPTY TABLE cannot be done.
- In a federated system, nicknames for relational data sources or local tables can be used as the underlying tables to create a materialized query table. Nicknames for non-relational data sources are not supported. When a nickname is one of the underlying tables, the REFRESH DEFERRED option must be used. System-maintained materialized query tables that reference nicknames are not supported in a partitioned database environment.
- Considerations for transaction-start-ID columns: A
transaction-start-ID column contains a null value if the column allows
null values, and there is a row-begin column and the value of the
column is unique from values for row-begin columns generated for other
transactions. Given that the column may contain null values, it is
recommended that one of the following methods be used when retrieving
a value from the column:
COALESCE ( transaction_start_id_col, row_begin_col) CASE WHEN transaction_start_id_col IS NOT NULL THEN transaction_start_id_col ELSE row_begin_col END - Defining a system-period temporal table: A system-period
temporal table definition includes the following:
- A system period named SYSTEM_TIME, which is defined using a row-begin column and a row-end column. See the descriptions of AS ROW BEGIN, AS ROW END, and period-definition.
- A transaction-start-ID column. See the description of AS TRANSACTION START ID.
- A system-period data versioning definition specified on a subsequent ALTER TABLE statement that specifies the ADD VERSIONING action, which includes the name of the associated history table. See the description of the ADD VERSIONING clause under ALTER TABLE.
- Defining
an application-period temporal table: An application-period
temporal table definition includes an application period named BUSINESS_TIME.
The application period is defined using a begin timestamp column and
an end column. See the description of period-definition.
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.
- Considerations for implicitly
hidden columns: Creating a table with implicitly hidden columns
can impact the behavior of data movement utilities that are working
with the table. When a table contains implicitly hidden columns, utilities
like IMPORT, INGEST, and LOAD require that you specify whether data
for the hidden columns is included in the operation. For example,
this might mean that a load operation runs successfully against a
table without any hidden columns, but fails when run against a table
that contains implicitly hidden columns (SQLCODE SQL2437N). Similarly,
EXPORT requires that you specify whether data for the hidden columns
is included in the operation.
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.
- Transparent DDL: In a
federated system, a remote base table can be created, altered, or dropped using DB2 SQL. This capability is known as
transparent DDL. Before a remote base table can be created on a data source, the federated
server must be configured to access that data source. This configuration includes creating the
wrapper for the data source, supplying the server definition for the server where the remote base
table will be located, and creating the user mappings between the federated server and the data
source. Transparent DDL does impose some limitations on what can be included in the CREATE TABLE statement:
- Only columns and a primary key can be created on the remote base table.
- Specific clauses supported by transparent DDL include:
- column-definition and unique-constraint in the element-list clause
- NOT NULL and PRIMARY KEY in the column-options clause
- OPTIONS
- The remote data source must support:
- The remote column data types to which the DB2 column data types are mapped
- The primary key option in the CREATE TABLE statement
When a remote base table is created using transparent DDL, a nickname is automatically created for that remote base table.
- A referential constraint may be defined in such a way that either
the parent table or the dependent table is a part of a table hierarchy.
In such a case, the effect of the referential constraint is as follows:
- Effects of INSERT, UPDATE, and DELETE statements:
- If a referential constraint exists, in which PT is a parent table and DT is a dependent table, the constraint ensures that for each row of DT (or any of its subtables) that has a non-null foreign key, a row exists in PT (or one of its subtables) with a matching parent key. This rule is enforced against any action that affects a row of PT or DT, regardless of how that action is initiated.
- Effects of DROP TABLE statements:
- for referential constraints in which the dropped table is the parent table or dependent table, the constraint is dropped
- for referential constraints in which a supertable of the dropped table is the parent table the rows of the dropped table are considered to be deleted from the supertable. The referential constraint is checked and its delete rule is invoked for each of the deleted rows.
- for referential constraints in which a supertable of the dropped table is the dependent table, the constraint is not checked. Deletion of a row from a dependent table cannot result in violation of a referential constraint.
- Effects of INSERT, UPDATE, and DELETE statements:
- Privileges: When any table is created, the definer of the table is granted CONTROL privilege. When a subtable is created, the SELECT privilege that each user or group has on the immediate supertable is automatically granted on the subtable with the table definer as the grantor.
- Row size limit: The maximum number of bytes allowed in the row of a row-organized table is
dependent on the page size of the table space in which the table is created
(tablspace-name1). The following table shows the row size limit and number
of columns limit associated with each table space page size.
Table 3. Limits for Number of Columns and Row Size in Each Table Space Page Size (row-organized tables) Page Size Row Size Limit Column Count Limit 4K 4005 500 8K 8101 1012 16K 16,293 1012 32K 32,677 1012 The actual number of columns for a row-organized table can be further limited by the following formula:Total Columns * 8 + Number of LOB Columns * 12 <= Row Size Limit for Page SizeA column-organized table can have a maximum of 1012 columns, regardless of page size, where the byte counts of the columns must not be greater than 32,677.
- Byte counts: The following table contains
the byte counts of columns by data type. This is used to calculate
the row size. The byte counts depend on whether or not VALUE COMPRESSION
is active. When VALUE COMPRESSION is not active, the byte counts also
depend on whether or not the column is nullable. 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.
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.
Table 4. Byte Counts of Columns by Data Type 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. If an existing system temporary table space is available that has a 32K page size, then extended row size support is used where possible.
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.
The row sizes for the following sample tables assume that VALUE COMPRESSION is not specified:
If VALUE COMPRESSION were to be specified, the row sizes would change to:DEPARTMENT 63 (0 + 3 + 33 + 7 + 3 + 17) ORG 57 (0 + 3 + 19 + 2 + 15 + 18)DEPARTMENT 69 (2 + 5 + 31 + 8 + 5 + 18) ORG 53 (2 + 4 + 16 + 4 + 12 + 15)Minimum page size requirements for a table with extended row size : When a data row is inserted or updated in a table with extended row size support and the physical data row length exceeds the maximum record length for the table space, a subset of the varying length string columns (VARCHAR or VARGRAPHIC) is stored as large object (LOB) data outside of the data row. The table column in the base row is replaced by a descriptor that is 24 bytes in size. In order to accommodate the extreme case where all VARCHAR or VARGRAPHIC data is stored outside of the data row, the database manager computes the minimum row size using the following method:- Handles every VARCHAR(n) column where n > 24 as if it were VARCHAR(24)
- Handles every VARGRAPHIC(m) column where m > 12 as if it were VARGRAPHIC(12
- Storage byte counts: The following
tables describe the storage byte counts of columns by data type for
data values.
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.
Table 5. Definitions of the criteria referenced in the related table 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.
Table 6. Storage Byte Counts Based on Row Format, Data Type, and Data Value 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.
- Dimension
columns: Because each distinct value of a dimension column
is assigned to a different block of the table, clustering on an expression
may be desirable, such as "INTEGER(ORDER_DATE)/100". In this case,
a generated column can be defined for the table, and this generated
column may then be used in the ORGANIZE BY DIMENSIONS clause. If the
expression is monotonic with respect to a column of the table, the
database might use the dimension index to satisfy range predicates
on that column. For example, if the expression is simply column-name + some-positive-constant,
it is monotonic increasing. User-defined functions, certain built-in
functions, and using more than one column in an expression, prevent
monotonicity or its detection.
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:
- Monotonic increasing. For every possible pair of values x1 and x2, if x2>x1, then fn(x2)>fn(x1). For example:
SALARY - 10000 - Monotonic decreasing. For every possible pair of values x1 and x2, if x2>x1, then fn(x2)<fn(x1). For example:
-SALARY - Monotonic non-decreasing. For every possible pair of values x1 and x2, if x2>x1, then fn(x2)>=fn(x1). For example:
SALARY/1000 - Monotonic non-increasing. For every possible pair of values x1 and x2, if x2>x1, then fn(x2)<=fn(x1). For example:
-SALARY/1000
The expression "PRICE*DISCOUNT" is not monotonic, because it involves more than one column of the table.
- Monotonic increasing.
- Range-clustered tables: Organizing a table by key sequence is effective for certain types of tables. The table should have an integer key that is tightly clustered (dense) over the range of possible values. The columns of this integer key must not be nullable, and the key should logically be the primary key of the table. The organization of a range-clustered table precludes the need for a separate unique index object, providing direct access to the row for a specified key value, or a range of rows for a specified range of key values. The allocation of all the space for the complete set of rows in the defined key sequence range is done during table creation, and must be considered when defining a range-clustered table. The storage space is not available for any other use, even though the rows are initially marked deleted. If the full key sequence range will be populated with data only over a long period of time, this table organization may not be an appropriate choice.
- A table can have at most one security policy.
- Referential integrity constraints that are defined on protected tables are enforced. Constraints violations in this case can be difficult to debug, because the database manager will not allow you to see what row has caused a violation if you do not have the appropriate security label or exemptions credentials.
- When defining the order of columns in a table, frequently updated columns should be placed at the end of the definition to minimize the amount of data logged for updates. This includes ROW CHANGE TIMESTAMP columns. ROW CHANGE TIMESTAMP columns are guaranteed to be updated on each row update.
- Security and replication: Replication can cause data rows from a protected table to be replicated outside of the database. Care must be taken when setting up replication for a protected table, because data that is outside of the database cannot be protected.
- Considerations for a multi-partition or DB2 pureScale environment:
- In a multi-partition or DB2 pureScale environment, if the CACHE and NO ORDER options are in effect, multiple caches can be active simultaneously and the requests for next value assignments from different members might not result in the assignment of values in strict numeric order. Assume, for example, that members DB1A and DB1B are using the same sequence, and DB1A gets the cache values 1 to 20 and DB1B gets the cache values 21 to 40. In this scenario, if DB1A requested the next value first, then DB1B requested, and then DB1A requested again, the actual order of values assigned would be 1,21,2. Therefore, to guarantee that sequence numbers are generated in strict numeric order among multiple members using the same sequence concurrently, specify the ORDER option.
- In a DB2 pureScale environment, using the ORDER or NO CACHE option ensures that the values assigned to a sequence which is shared by applications across multiple members are in strict numeric order. In a DB2 pureScale environment, if ORDER is specified, then NO CACHE is implied even if CACHE n is specified
- Considerations for row and column access control (RCAC): The ACTIVATE ROW ACCESS CONTROL, ACTIVATE COLUMN ACCESS CONTROL, DEACTIVATE ROW ACCESS CONTROL, and DEACTIVATE COLUMN ACCESS CONTROL clauses are not supported. Use the ALTER TABLE statement to activate or deactivate row or column level access control on a table.
- Considerations
for column-organized
tables: Create column-organized tables in
automatic storage table spaces only.The following options are not supported for column-organized tables. They can, however, be specified for row-organized tables that will be used in the same database and workloads as column-organized tables.
- ORGANIZE BY DIMENSIONS
- ORGANIZE BY KEY SEQUENCE
- ORGANIZE BY INSERT TIME
- DATA CAPTURE CHANGES
- VALUE COMPRESSION
- COMPRESS YES ADAPTIVE | STATIC
- COMPRESS NO
- NOT LOGGED INITIALLY
- PARTITION BY RANGE
- DISTRIBUTE BY REPLICATION
- PERIOD BUSINESS_TIME | SYSTEM_TIME
- CREATE TABLE OF (typed table)
- PROPAGATE IMMEDIATE
- CHECK
- DETERMINED BY
Generated columns (including GENERATED AS IDENTITY) and structured type columns are not supported.
The following data types are supported in a column-organized table:- SMALLINT
- INTEGER
- BIGINT
- DECIMAL
- REAL
- DOUBLE
- DECFLOAT
- CHAR (including FOR BIT DATA)1
- VARCHAR (including FOR BIT DATA)1
- GRAPHIC1
- VARGRAPHIC1
- DATE
- TIME
- TIMESTAMP (n)
- Distinct types of a supported type
- The length attribute for character and graphic strings can not be defined as CODEUNITS32 (SQLSTATE 42613). When the CODEUNITS32 string units are implicit, OCTETS must be specified when defining character strings and CODEUNITS16 must be specified when defining graphic strings.
- Syntax
alternatives: The following are supported for
compatibility with previous versions of DB2 and with other
database products. These alternatives are non-standard and should not be
used.
- The following syntax is accepted as the default behavior:
- IN database-name.tablespace-name
- IN DATABASE database-name
- FOR MIXED DATA
- FOR SBCS DATA
- PART can be specified in place of PARTITION
- PARTITION partition-number can be specified instead of PARTITION partition-name. A partition-number must not identify a partition that was previously specified in the CREATE TABLE statement. If a partition-number is not specified, a unique partition number is generated by the database manager.
- VALUES can be specified in place of ENDING AT
- The CONSTRAINT keyword can be omitted from a column-definition defining a references-clause
- constraint-name can be specified following FOREIGN KEY (without the CONSTRAINT keyword)
- SUMMARY can optionally be specified after CREATE
- DEFINITION ONLY can be specified in place of WITH NO DATA
- The PARTITIONING KEY clause can be specified in place of the DISTRIBUTE BY clause
- REPLICATED can be specified in place of DISTRIBUTE BY REPLICATION
- A comma can be used to separate multiple options in the identity-options clause
- NOMINVALUE, NOMAXVALUE, NOCYCLE, NOCACHE, and NOORDER can be specified in place of NO MINVALUE, NO MAXVALUE, NO CYCLE, NO CACHE, and NO ORDER, respectively
- ADD can be specified before table-option-name string-constant.
- The following syntax is accepted as the default behavior:
Examples
- Create
table TDEPT in the DEPARTX table space. DEPTNO, DEPTNAME, MGRNO, and
ADMRDEPT are column names. CHAR means the column will contain character
data. NOT NULL means that the column cannot contain a null value.
VARCHAR means the column will contain varying-length character data.
The primary key consists of the column DEPTNO.
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 in the SCHED table space. PROJNO, PROJNAME,
DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, and MAJPROJ are column
names. CHAR means the column will contain character data. DECIMAL
means the column will contain packed decimal data. 5,2 means the following:
5 indicates the number of decimal digits, and 2 indicates the number
of digits to the right of the decimal point. NOT NULL means that the
column cannot contain a null value. VARCHAR means the column will
contain varying-length character data. DATE means the column will
contain date information in a three-part format (year, month, and
day).
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 a table called EMPLOYEE_SALARY where any unknown salary
is considered 0. No table space is specified, so that the table will
be created in a table space selected by the system based on the rules
described for the IN tablespace-name clause.
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 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").If a value for SALARY is not specified it must be set to 0 and if a value for LIVING_DIST is not specified it must to set to 1 mile.
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 distinct types for image and audio and use them for columns
of a table. No table space is specified, so that the table will be
created in a table space selected by the system based on the rules
described for the IN tablespace-name clause.
Assume the CURRENT PATH is the default.
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 in the HUMRES table space. The constraints
defined on the table are the following:
- The values of department number must lie in the range 10 to 100.
- The job of an employee can only be either 'Sales', 'Mgr' or 'Clerk'.
- Every employee that has been with the company since 1986 must make more than $40,500.
Note: If the columns included in the check constraints are nullable they could also be NULL.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 a table that is wholly contained in the PAYROLL table space.
CREATE TABLE EMPLOYEE ..... IN PAYROLL - Create a table with its data part in ACCOUNTING and its index
part in ACCOUNT_IDX.
CREATE TABLE SALARY..... IN ACCOUNTING INDEX IN ACCOUNT_IDX - Create a table and log SQL changes in the default format.
orCREATE TABLE SALARY1 .....CREATE TABLE SALARY1 ..... DATA CAPTURE NONE - Create a table and log SQL changes in an expanded format.
CREATE TABLE SALARY2 ..... DATA CAPTURE CHANGES - Create a table EMP_ACT in the SCHED table space. EMPNO, PROJNO,
ACTNO, EMPTIME, EMSTDATE, and EMENDATE are column names. Constraints
defined on the table are:
- The value for the set of columns, EMPNO, PROJNO, and ACTNO, in any row must be unique.
- The value of PROJNO must match an existing value for the PROJNO column in the PROJECT table and if the project is deleted all rows referring to the project in EMP_ACT should also be deleted.
A unique index called EMP_ACT_UNIQ is automatically created in the same schema to enforce the unique constraint.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 - Create a table that is to hold information about famous goals
for the ice hockey hall of fame. The table will list information about
the player who scored the goal, the goaltender against who it was
scored, the date, and a description. The description column is nullable.
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) ) - Suppose an exception table is needed for the EMPLOYEE table. One
can be created using the following statement.
CREATE TABLE EXCEPTION_EMPLOYEE AS (SELECT EMPLOYEE.*, CURRENT TIMESTAMP AS TIMESTAMP, CAST ('' AS CLOB(32K)) AS MSG FROM EMPLOYEE ) WITH NO DATA - Given the following table spaces with the indicated attributes:
TBSPACE PAGESIZE USER USERAUTH ------------------ ----------- ------ -------- DEPT4K 4096 BOBBY Y PUBLIC4K 4096 PUBLIC Y DEPT8K 8192 BOBBY Y DEPT8K 8192 RICK Y PUBLIC8K 8192 PUBLIC Y- If RICK creates the following table, it is placed in table space
PUBLIC4K since the byte count is less than 4005; but if BOBBY creates
the same table, it is placed in table space DEPT4K, since BOBBY has
USE privilege because of an explicit grant:
CREATE TABLE DOCUMENTS (SUMMARY VARCHAR(1000), REPORT VARCHAR(2000)) - If BOBBY creates the following table, it is placed in table space
DEPT8K since the byte count is greater than 4005, and BOBBY has USE
privilege because of an explicit grant. However, if DUNCAN creates
the same table, it is placed in table space PUBLIC8K, since DUNCAN
has no specific privileges:
CREATE TABLE CURRICULUM (SUMMARY VARCHAR(1000), REPORT VARCHAR(2000), EXERCISES VARCHAR(1500))
- If RICK creates the following table, it is placed in table space
PUBLIC4K since the byte count is less than 4005; but if BOBBY creates
the same table, it is placed in table space DEPT4K, since BOBBY has
USE privilege because of an explicit grant:
- Create a table with a LEAD column defined with the structured
type EMP. Specify an INLINE LENGTH of 300 bytes for the LEAD column,
indicating that any instances of LEAD that cannot fit within the 300
bytes are stored outside the table (separately from the base table
row, similar to the way LOB values are handled).
CREATE TABLE PROJECTS (PID INTEGER, LEAD EMP INLINE LENGTH 300, STARTDATE DATE, ...) - Create
a table DEPT with five columns named DEPTNO, DEPTNAME, MGRNO, ADMRDEPT,
and LOCATION. Column DEPT is to be defined as an IDENTITY column so
that a value will always be generated for it. The values for the DEPT
column should begin with 500 and increment by 1.
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 a SALES table that is distributed on the YEAR column, and
that has dimensions on the REGION and YEAR columns. Data will be distributed
across database partitions according to hashed values of the YEAR
column. On each database partition, data will be organized into extents
based on unique combinations of values of the REGION and YEAR columns
on those database partitions.
CREATE TABLE SALES (CUSTOMER VARCHAR(80), REGION CHAR(5), YEAR INTEGER) DISTRIBUTE BY HASH (YEAR) ORGANIZE BY DIMENSIONS (REGION, YEAR) - Create a SALES table with a PURCHASEYEARMONTH column that is generated
from the PURCHASEDATE column. Use an expression to create a column
that is monotonic with respect to the original PURCHASEDATE column,
and is therefore suitable for use as a dimension. The table is distributed
on the REGION column, and organized within each database partition
into extents according to the PURCHASEYEARMONTH column; that is, different
regions will be on different database partitions, and different purchase
months will belong to different cells (or sets of extents) within
those database partitions.
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 a CUSTOMER table with a CUSTOMERNUMDIM column that is generated
from the CUSTOMERNUM column. Use an expression to create a column
that is monotonic with respect to the original CUSTOMERNUM column,
and is therefore suitable for use as a dimension. The table is organized
into cells according to the CUSTOMERNUMDIM column, so that there is
a different cell in the table for every 50 customers. If a unique
index were created on CUSTOMERNUM, customer numbers would be clustered
in such a way that each set of 50 values would be found in a particular
set of extents in the table.
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 a remote base table called EMPLOYEE on the Oracle server,
ORASERVER. A nickname, named EMPLOYEE, which refers to this newly
created remote base table, will also automatically be created.
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 a remote base table called EMPLOYEE (uppercase characters) on an Informix® server, and create a nickname named EMPLOYEE (uppercase characters) on that table:
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')Create a remote base table called employee (lowercase characters) on an Informix server, and create a nickname named EMPLOYEE (uppercase characters) on that table:
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', REMOTE_TABNAME 'employee')Create a remote base table called employee (lowercase characters) on an Informix server, and create a nickname named employee (lowercase characters) on that table:
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 "employee" (EMP_NO CHAR(6) NOT NULL, ...) OPTIONS (REMOTE_SERVER 'INFX_SERVER') - Create a range-clustered table that can be used to locate a student
using a student ID. For each student record, include the school ID,
program ID, student number, student ID, student first name, student
last name, and student grade point average (GPA).
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 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 - Create a table named DEPARTMENT with a functional dependency that
has no specified constraint name.
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 a table with protected rows.
CREATE TABLE TOASTMASTERS (PERFORMANCE DB2SECURITYLABEL, POINTS INTEGER, NAME VARCHAR(50)) SECURITY POLICY CONTRIBUTIONS - Create a table with protected columns.
CREATE TABLE TOASTMASTERS (PERFORMANCE CHAR(8), POINTS INTEGER COLUMN SECURED WITH CLUBPOSITION, NAME VARCHAR(50)) SECURITY POLICY CONTRIBUTIONS - Create a table with protected rows and columns.
CREATE TABLE TOASTMASTERS (PERFORMANCE DB2SECURITYLABEL, POINTS INTEGER COLUMN SECURED WITH CLUBPOSITION, NAME VARCHAR(50)) SECURITY POLICY CONTRIBUTIONS - Large objects for a partitioned table reside, by default, in the
same table space as the data. This default behavior can be overridden
by using the LONG IN clause to specify one or more table spaces for
the large objects. Create a table named DOCUMENTS whose large object
data is to be stored (in a round-robin fashion for each data partition)
in table spaces TBSP1 and TBSP2.
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 TBSP1, TBSP2 PARTITION BY RANGE (ID) (STARTING 1 ENDING 1000 EVERY 100)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 a partitioned table named ACCESSNUMBERS having two data
partitions. The row (10, NULL) is to be placed in the first partition,
and the row (NULL, 100) is to be placed in the second (last) data
partition.
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 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)) - Create a table named RATIO having a single data partition and
partitioning column PERCENT.
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 (MINVALUE) ENDING (MAXVALUE))CREATE TABLE RATIO (PERCENT INTEGER) PARTITION BY RANGE (PERCENT) (STARTING 0 EXCLUSIVE ENDING 100 INCLUSIVE) - Create a table named MYDOCS with two columns: one is an identifier,
and the other stores XML documents.
CREATE TABLE MYDOCS (ID INTEGER, DOC XML) IN HLTBSPACE - Create a table named NOTES with four columns, including one for
storing XML-based notes.
CREATE TABLE NOTES (ID INTEGER, DESCRIPTION VARCHAR(255), CREATED TIMESTAMP, NOTE XML) - Create a table, EMP_INFO, that contains a phone number and address
for each employee. Include a ROW CHANGE TIMESTAMP column in the table
to track the modification of employee information.
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 a partitioned table named DOCUMENTS having two data partitions:
- The data object in the first partition resides in table space TBSP11. The partitioned index partition on the partition resides in table space TBSP21. The XML data object resides in table space TBSP31.
- The data object in the second partition resides in table space TBSP12. The partitioned index partition on the partition resides in table space TBSP22. The XML data object resides in table space TBSP32.
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 a partitioned table named SALES having two data partitions:
- The data object in the first partition resides in table space TBSP11. The partitioned index partition on the partition resides in table space TBSP21.
- The data object in the second partition resides in table space TBSP12. The partitioned index object resides in table space TBSP22.
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 a table named BOOKS with four columns, including one named
DATE_ADDED, which inserts the current TIMESTAMP by default.
CREATE TABLE BOOKS (ISBN_NUM INTEGER, TITLE VARCHAR(255), AUTHOR VARCHAR(255), DATE_ADDED TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP) - Create a Unicode table called STUDENTS in a non-Unicode database.
Assume that the database was created using code set 1252 and territory
CA and the ALT_COLLATE database configuration parameter was updated
to IDENTITY_16BIT.
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
a table called TDEPT_TEMP, based on the TDEPT table that is created
in Example 1.
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 TABLE TDEPT_TEMP LIKE TDEPT - Create a
column-organized user-maintained materialized query table on column-organized table
CDE.TDEPT.
CREATE TABLE mqt_tdept AS (SELECT * FROM cde.tdept WHERE deptno BETWEEN 10 AND 20) DATA INITIALLY DEFERRED REFRESH DEFERRED MAINTAINED BY USER ORGANIZE BY COLUMN - Column
security labels inherited by a materialized query table.
Generate a materialized query tableCREATE 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
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 m1 AS(SELECT c1, c3, c5, c6 FROM t1, t2 WHERE c2 <> 100) DATA INITIALLY DEFERRED REFRESH DEFERRED- Security policy = P
- Security label of column m1.c1 = P.B
- Security label of column m1.c3 = P.A
- Security label of column m1.c5 = P.B
- Security label of column m1.c6 = P.B and it is also DB2SECURITYLABEL.
The label-based access control properties of the staging table st1 are:CREATE TABLE st1 FOR m1 PROPAGATE IMMEDIATE- Security policy = P
- Security label of column st1.c1 = P.B
- Security label of column st1.c3 = P.A
- Security label of column st1.c5 = P.B
- Security label of column st1.c6 = P.B and it is also DB2SECURITYLABEL.
The following example shows you how to create a shadow table called T1_SHADOW that is based on the row-organized table T1.
- Create the base table and define a primary key. The primary key on the base table must be
included in the select list of the shadow table. The primary key on the shadow table is required to
provide a one-to-one mapping for each row in the base table to the corresponding row in the shadow
table. The primary key also facilitates maintenance of the shadow
table.
CREATE TABLE t1 ( c1 INTEGER NOT NULL, c2 INTEGER ) ORGANIZE BY ROW; ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY(c1); - Create the shadow
table:
CREATE TABLE t1_shadow AS (SELECT c1, c2 FROM t1) DATA INITIALLY DEFERRED REFRESH DEFERRED MAINTAINED BY REPLICATION ORGANIZE BY COLUMN; SET INTEGRITY FOR t1_shadow ALL IMMEDIATE UNCHECKED; ALTER TABLE t1_shadow ADD CONSTRAINT t1_shadow_pk PRIMARY KEY (c1);
- Create the base table and define a primary key. The primary key on the base table must be
included in the select list of the shadow table. The primary key on the shadow table is required to
provide a one-to-one mapping for each row in the base table to the corresponding row in the shadow
table. The primary key also facilitates maintenance of the shadow
table.
- Create
a table that is named STRING_UNITS, which demonstrates each possible
string unit specification.
The columns have the following string units:CREATE TABLE string_units (c1 VARCHAR(10), c2 VARCHAR(10 OCTETS), c3 VARCHAR(10 CODEUNITS32), c4 VARGRAPHIC(10), c5 VARGRAPHIC(10 CODEUNITS16), c6 VARGRAPHIC(10 CODEUNITS32))- c1 = OCTETS, if the environment string units is SYSTEM; CODEUNITS32 if the environment string units is CODEUNITS32
- c2 = OCTETS
- c3 = CODEUNITS32
- c4 = CODEUNITS16, if the environment string units is SYSTEM; CODEUNITS32 if the environment string units is CODEUNITS32
- c5 = CODEUNITS16
- c6 = CODEUNITS32