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 by using 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

The authorization ID of the statement must have either DBADM authority, or must have CREATETAB authority in combination with the following additional authorization:
  • One of the following privileges or authorities:
    • USE privilege on the table space
    • SYSADM authority
    • SYSCTRL authority
  • Plus one of these privileges or 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.
    • SCHEMAADM authority on the schema, if the schema name of the table refers to an existing schema.
If a subtable is being defined, at least one of the following conditions must be met:
  • The authorization ID must be the same as the owner of the root table of the table hierarchy.
  • The privileges that are held by the authorization ID must include SCHEMAADM authority on the schema that contains the root table of the table hierarchy.
  • The privileges that are held by the authorization ID must include DBADM authority.
To define a foreign key, the authorization ID of the statement must have one of the following privileges for the parent table:
  • REFERENCES privilege on the table
  • REFERENCES privilege on each column of the specified parent key
  • CONTROL privilege on the table
  • SCHEMAADM authority on the schema, if the schema name of the parent table refers to an existing schema.
  • DBADM authority
To define a materialized query table, the following conditions must be met:
  • The authorization ID of the statement must have at least one of the following privileges on each table or view that is identified in the fullselect (privileges that are held through groups are not considered):
    • SELECT privilege on the table or view
    • CONTROL privilege on the table or view
    • SELECTIN privilege on the schema that contains the table or view
    • DATAACCESS authority on the schema that contains the table or view
    • DATAACCESS authority
  • The authorization ID of the statement must have at least one of the following privileges on each table that is identified in the fullselect (this is required for altering the base table to associate it with the materialized query table):
    • ALTER privilege on the table or view
    • CONTROL privilege on the table or view
    • SCHEMAADM authority on the schema that contains the table or view
    • DBADM authority
To define a staging table that is associated with a materialized query table, the authorization ID of the statement must hold the following privileges:
  • At least one of the following privileges for the materialized query table:
    • ALTER privilege on the materialized query table
    • CONTROL privilege on the materialized query table
    • SCHEMAADM authority on the schema that contains the materialized query table
    • DBADM authority
  • At least one of the following privileges for each table or view that is identified in the fullselect of the materialized query table:
    • SELECT privilege on the table or view
    • CONTROL privilege on the table or view
    • SELECTIN privilege on the schema that contains the table or view
    • DATAACCESS authority on the schema that contains the table or view
    • DATAACCESS authority on the database
    In addition, at least one of the following privileges for each table or view identified in the fullselect of the materialized query table:
    • ALTER privilege on the table or view
    • CONTROL privilege on the table or view
    • SCHEMAADM authority on the schema that contains the table or view
    • DBADM authority

Syntax

Read syntax diagramSkip visual syntax diagram CREATE TABLE IF NOT EXISTS table-name Element-listOFtype-name1typed-table-optionsLIKEtable-name1view-namenicknamecopy-optionsas-result-tablecopy-optionsmaterialized-query-definitionstaging-table-definition ORGANIZE BYROWCOLUMNROW USING1Dimensions-clauseKEY SEQUENCEsequence-key-specINSERT TIME DATA CAPTURENONECHANGES tablespace-clauses distribution-clause partitioning-clause COMPRESS YESADAPTIVECOMPRESS YES STATICCOMPRESS NO VALUE COMPRESSION WITH RESTRICT ON DROP NOT LOGGED INITIALLY CCSIDASCIIUNICODE SECURITY POLICYpolicy name OPTIONS(,table-option-namestring-constant)
Element-list
Read syntax diagramSkip visual syntax diagram ( ,column-definitionperiod-definitionunique-constraintreferential-constraintcheck-constraint )
Column-definition
Read syntax diagramSkip visual syntax diagram column-name Data-type2Column-options
Data-type
Read syntax diagramSkip visual syntax diagramBuilt-in-typedistinct-type-name3structured-type-nameREF( type-name2)
Built-in-type
Read syntax diagramSkip visual syntax diagramSMALLINTINTEGERINTBIGINTDECIMALDECNUMERICNUM(5,0)( precision-integer,0, scale-integer)FLOAT(53)( integer)REALDOUBLEPRECISIONDECFLOAT(34)(16)CHARACTERCHAR(1)( integerOCTETSCODEUNITS32)VARCHARCHARACTERCHARVARYING( integerOCTETSCODEUNITS32)FOR BIT DATA4CCSID ASCIICCSID UNICODECCSID 1208CLOBCHARACTERCHARLARGE OBJECT(1M)( integerKMGOCTETSCODEUNITS32)CCSID ASCIICCSID UNICODECCSID 1208GRAPHIC(1)( integerCODEUNITS16CODEUNITS32)CCSID ASCIICCSID UNICODECCSID 1208VARGRAPHIC( integerCODEUNITS16CODEUNITS32)DBCLOB(1M)( integerKMGCODEUNITS16CODEUNITS32)NCHARNATIONALCHARCHARACTER(1)( integer)NVARCHARNCHAR VARYINGNATIONALCHARCHARACTERVARYING( integer)NCLOBNCHAR LARGE OBJECTNATIONAL CHARACTER LARGE OBJECT(1M)( integerKMG)BINARY(1)( integer)VARBINARYBINARY VARYING(integer)BLOBBINARY LARGE OBJECT(1M)( integerKMG)DATETIMETIMESTAMP(6)(integer)XMLBOOLEANSYSPROC.DB2SECURITYLABEL56
Column-options
Read syntax diagramSkip visual syntax diagramNOT NULLLob-options7SCOPEtyped-table-nametyped-view-name8CONSTRAINTconstraint-namePRIMARY KEYUNIQUEReferences-clauseCHECK(check-condition)constraint-attributes9Default-clausegenerated-clauseINLINE LENGTHinteger10COMPRESS SYSTEM DEFAULTCOLUMNSECURED WITHsecurity-label-nameNOT HIDDENIMPLICITLY HIDDEN
Lob-options
Read syntax diagramSkip visual syntax diagram LOGGEDNOT LOGGED NOT COMPACTCOMPACT
References-clause
Read syntax diagramSkip visual syntax diagram REFERENCES table-namenickname (,column-name)rule-clauseconstraint-attributes
Rule-clause
Read syntax diagramSkip visual syntax diagram ON DELETE NO ACTIONON DELETERESTRICTCASCADESET NULL ON UPDATE NO ACTIONON UPDATE RESTRICT
Constraint-attributes
Read syntax diagramSkip visual syntax diagram ENFORCEDNOT ENFORCEDTRUSTEDNOT TRUSTED ENABLE QUERY OPTIMIZATIONDISABLE QUERY OPTIMIZATION
Default-clause
Read syntax diagramSkip visual syntax diagram WITH DEFAULT Default-values
Default-values
Read syntax diagramSkip visual syntax diagramconstantdatetime-special-registeruser-special-registerCURRENT SCHEMACURRENT MEMBERNULLcast-function(constantdatetime-special-registeruser-special-registerCURRENT SCHEMA)EMPTY_CLOB()EMPTY_DBCLOB()EMPTY_NCLOB()EMPTY_BLOB()
Generated-clause
Read syntax diagramSkip visual syntax diagramGENERATEDALWAYSBY DEFAULTIdentity-optionsas-row-change-timestamp-clauseGENERATEDALWAYSas-generated-expression-clauseas-row-transaction-timestamp-clauseas-row-transaction-start-id-clause
Identity-options
Read syntax diagramSkip visual syntax diagram AS IDENTITY (11START WITH1numeric-constantINCREMENT BY1numeric-constantNO MINVALUEMINVALUEnumeric-constantNO MAXVALUEMAXVALUEnumeric-constantNO CYCLECYCLECACHE 20NO CACHECACHEinteger-constantNO ORDERORDER)
As-row-change-timestamp-clause
Read syntax diagramSkip visual syntax diagram12 FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
As-generated-expression-clause
Read syntax diagramSkip visual syntax diagram AS ( generation-expression )
As-row-transaction-timestamp-clause
Read syntax diagramSkip visual syntax diagram13 AS ROW BEGINEND
As-row-transaction-start-id-clause
Read syntax diagramSkip visual syntax diagram14 AS TRANSACTION START ID
Period-definition
Read syntax diagramSkip visual syntax diagram PERIOD SYSTEM_TIMEBUSINESS_TIME ( begin-column-name , end-column-name )
Unique-constraint
Read syntax diagramSkip visual syntax diagramCONSTRAINTconstraint-nameUNIQUEPRIMARY KEY ( ,column-name ,BUSINESS_TIMEWITHOUT OVERLAPS ) constraint-attributes
Referential-constraint
Read syntax diagramSkip visual syntax diagramCONSTRAINTconstraint-name FOREIGN KEY ( ,column-name ) references-clause
Check-constraint
Read syntax diagramSkip visual syntax diagramCONSTRAINTconstraint-name CHECK ( Check-condition ) constraint-attributes
Check-condition
Read syntax diagramSkip visual syntax diagramsearch-conditionFunctional-dependency
Functional-dependency
Read syntax diagramSkip visual syntax diagram column-name(,column-name) DETERMINED BY column-name(,column-name)
Typed-table-options
Read syntax diagramSkip visual syntax diagram HIERARCHYhierarchy-nameUnder-clause typed-element-list
Under-clause
Read syntax diagramSkip visual syntax diagram UNDER supertable-name INHERIT SELECT PRIVILEGES
Typed-element-list
Read syntax diagramSkip visual syntax diagram ( ,OID-column-definitionwith-optionsunique-constraintcheck-constraint )
OID-column-definition
Read syntax diagramSkip visual syntax diagram REF IS OID-column-name USER GENERATED
With-options
Read syntax diagramSkip visual syntax diagram column-name WITH OPTIONS Column-options
As-result-table
Read syntax diagramSkip visual syntax diagram(,column-name) AS ( fullselect ) WITH NO DATAWITH DATA
Materialized-query-definition
Read syntax diagramSkip visual syntax diagram(,column-name) AS ( fullselect ) refreshable-table-options
Copy-options
Read syntax diagramSkip visual syntax diagram INCLUDINGEXCLUDINGCOLUMNDEFAULTS EXCLUDING IDENTITYCOLUMN ATTRIBUTESINCLUDING IDENTITYCOLUMN ATTRIBUTES
Refreshable-table-options
Read syntax diagramSkip visual syntax diagram DATA INITIALLY DEFERRED REFRESH DEFERREDIMMEDIATE ENABLE QUERY OPTIMIZATIONDISABLE QUERY OPTIMIZATION MAINTAINED BY SYSTEMMAINTAINED BYUSERREPLICATIONFEDERATED_TOOL
Staging-table-definition
Read syntax diagramSkip visual syntax diagram(,staging-column-name) FOR table-name2 PROPAGATE IMMEDIATE
Dimensions-clause
Read syntax diagramSkip visual syntax diagram DIMENSIONS (,column-name(,column-name))
Sequence-key-spec
Read syntax diagramSkip visual syntax diagram ( ,column-nameSTARTINGFROMconstantENDINGATconstant ) ALLOW OVERFLOWDISALLOW OVERFLOWPCTFREEinteger
Tablespace-clauses
Read syntax diagramSkip visual syntax diagramIN,tablespace-nameCYCLENO CYCLE INDEX INtablespace-name15LONG IN,tablespace-name
Distribution-clause
Read syntax diagramSkip visual syntax diagram DISTRIBUTE BY HASH(,column-name)REPLICATIONRANDOM
Partitioning-clause
Read syntax diagramSkip visual syntax diagram PARTITION BY RANGE Range-partition-spec
Range-partition-spec
Read syntax diagramSkip visual syntax diagram ( ,partition-expression ) ( ,partition-element )
Partition-expression
Read syntax diagramSkip visual syntax diagram column-name NULLS LASTNULLS FIRST
Partition-element
Read syntax diagramSkip visual syntax diagramPARTITIONpartition-nameBoundary-specpartition-tablespace-optionsboundary-specEVERY(constantduration-label16)constantduration-label16
Boundary-spec
Read syntax diagramSkip visual syntax diagramStarting-clause17Ending-clause
Starting-clause
Read syntax diagramSkip visual syntax diagram STARTING FROM (,constantMINVALUEMAXVALUE)constantMINVALUEMAXVALUEINCLUSIVEEXCLUSIVE
Ending-clause
Read syntax diagramSkip visual syntax diagram ENDING AT (,constantMINVALUEMAXVALUE)constantMINVALUEMAXVALUEINCLUSIVEEXCLUSIVE
Partition-tablespace-options
Read syntax diagramSkip visual syntax diagramINtablespace-nameINDEX INtablespace-nameLONG INtablespace-name
Duration-label
Read syntax diagramSkip visual syntax diagramYEARYEARSMONTHMONTHSDAYDAYSHOURHOURSMINUTEMINUTESSECONDSECONDSMICROSECONDMICROSECONDS
Notes:
  • 1 If you specify a dimensions clause, key sequence, or insert time, specifying ROW USING is optional unless the default table organization for the database is COLUMN, in which case specifying ROW USING is mandatory.
  • 2 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.
  • 3 The specified distinct type cannot have any data type constraints and the source type cannot be an anchored data type.
  • 4 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).
  • 5 DB2SECURITYLABEL is the built-in distinct type that must be used to define the row security label column of a protected table.
  • 6 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.
  • 7 The lob-options clause only applies to large object types (BLOB, CLOB, and DBCLOB) and distinct types based on large object types.
  • 8 The SCOPE clause only applies to the REF type.
  • 9 The default-clause and generated-clause cannot both be specified for the same column definition (SQLSTATE 42614).
  • 10 INLINE LENGTH applies only to columns defined as structured, XML, or LOB types.
  • 11 The same clause must not be specified more than once.
  • 12 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).
  • 13 Data type is optional for row-begin and row-end timestamp columns if the first column-option specified is a generated-clause. The data type default is TIMESTAMP(12).
  • 14 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).
  • 15 Specifying which table space contains 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.
  • 16 This syntax for a partition-element is valid if only one partition-expression exists with a numeric or datetime data type.
  • 17 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 a need exists to identify each one separately.

IF NOT EXISTS
Specifies that no error message is shown when the table cannot be created because a table with the specified name already exists in the current database and schema. Typically, you use this option for scripted applications that are running SQL commands. When you suppress the Table not found error message, the scripted application is not impacted or halted.
The following conditions apply when you use this option:
  • You cannot use the IF NOT EXISTS option with the AS SELECT clause. Using the IF NOT EXISTS option with the AS SELECT clause causes a syntax error.
  • Unless other errors prevent the creation of the table, a CREATE TABLE message is returned although no table is created. The reason is that the failure is ignored if a table with the specified name already exists.
  • The existing table and the specified table in the command are not compared, that is, the tables might have different sizes. The exisiting table remains as is with its current size. The content of the rows is not changed. The application must ensure that the target table and rows are as expected.
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, including 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 information, see Row Size Limit.

data-type
Specifies the data type of the column.
built-in-type
One of the following built-in data types:
SMALLINT
A small integer.
[INTEGER | INT]
A large integer.
BIGINT
A big integer.
[DECIMAL | DEC | NUMERIC | NUM](precision-integer, scale-integer)
A decimal number.
  • The precision integer specifies the total number of digits. It must be in the range 1 - 31. The default is 5.
  • The scale integer specifies the number of digits to the right of the decimal point. It cannot be negative and cannot exceed the precision. The default is 0.
FLOAT(integer)
A single or double-precision floating-point number. If the specified length is in the range:
  • 1 - 24, the number uses single precision.
  • 25 - 53, the number uses double-precision.
Instead of FLOAT, you can 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)
A decimal floating-point number. The precision integer specifies the total number of digits, which can be either 16 or 34. The default is 34.
[CHARACTER | CHAR](integer [OCTETS | CODEUNITS32])
A fixed-length character string of the specified number of code units. This number can range from 1 - 255 OCTETS or from 1 - 63 CODEUNITS32. The default is 1.
[VARCHAR | CHARACTER VARYING | CHAR VARYING](integer [OCTETS | CODEUNITS32])
A varying-length character string with a maximum length of the specified number of code units. This number can range from 1 - 32672 OCTETS or from 1 - 8168 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.
CCSID
Specifies the encoding scheme for string data that is stored in the column. If the CCSID clause is not specified, the default is the CCSID of the table.
ASCII
Specifies that string data is encoded in the database code page. If the table is a Unicode table, CCSID ASCII cannot be specified (SQLSTATE 56031).
UNICODE, 1208, 1200
Specifies that string data is encoded in Unicode. Character data is in UTF-8; graphic data is in UTF-16 BE. CCSID 1208 and 1200 are synonyms for CCSID UNICODE. CCSID UNICODE cannot be specified for an SBCS database (SQLSTATE 560AA).
If the table is not a Unicode table, columns can be created with CCSID UNICODE, but the following rules apply:
  • The alternative collating sequence must be specified in the database configuration before creating the table (SQLSTATE 56031). CCSID UNICODE columns collate with the alternative collating sequence that is specified in the database configuration.

    The only supported alternative collating sequence is IDENTITY_16BIT.

  • The column cannot be a graphic data type.
  • Anchored data types cannot anchor to a column that is created with CCSID UNICODE (SQLSTATE 428HS).
  • Tables cannot have both the CCSID UNICODE clause and the DATA CAPTURE CHANGES clause specified (SQLSTATE 42613).
  • Created temporary tables and declared temporary tables cannot have columns declared with CCSID UNICODE (SQLSTATE 56031).
  • CCSID UNICODE columns cannot be specified in a CREATE SCHEMA statement (SQLSTATE 53090).
  • A column of the exception table for a load operation must have the same CCSID as the corresponding target table column for the operation (SQLSTATE 428A5).
  • A column of the exception table for a SET INTEGRITY statement must have the same CCSID as the corresponding target table column for the statement (SQLSTATE 53090).
  • Columns of the target table for event monitor data must not be declared as CCSID UNICODE (SQLSTATE 55049).
[CLOB | CHARACTER LARGE OBJECT | CHAR LARGE OBJECT](integer [K | M | G] [OCTETS | CODEUNITS32])
A character large object string with a maximum length of the specified number of code units. The default is 1,048,576 (1M) code units.
If you want to multiply the length integer by 1024 (kilo), 1,048,576 (mega), or 1,073,741,824 (giga), specify a K (kilo), M (mega), or G (giga) multiplier.
  • Regardless of which multiplier, if any, you use, the resulting length is limited by the maximum length of a CLOB column, which is 2,147,483,646 (for OCTETS) or 536,870,911 (for CODEUNITS32). If a multiple of K, M, or G slightly exceeds this maximum length (for example, 2G = 2,147,483,648), the maximum length is used instead.
  • Any number of spaces (including zero spaces) is allowed between data type and the length specification or between the length integer and the K, M, or G multiplier. For example, the following specifications are all equivalent and valid:
    CLOB(50K)
    CLOB(50 K)
    CLOB (50   K)
  • The K, M, or G multiplier can be specified in either uppercase or lowercase.

In a Unicode database, the default string units for a character string data type are determined by the value of the NLS_STRING_UNITS global variable or string_units database configuration parameter. In a non-Unicode database, the default string units for character string data types are OCTETS.

OCTETS
Specifies that the units of the length attribute are bytes.
CODEUNITS32
Specifies that the units of the length attribute are Unicode UTF-32 code units, which approximate 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 were converted to UTF-32. CODEUNITS32 can be specified only in a Unicode database (SQLSTATE 560AA).
GRAPHIC(integer [CODEUNITS16 | CODEUNITS32])
A fixed-length graphic string of the specified length, which can range from 1 - 127 double bytes, 1 - 127 CODEUNITS16, or 1 - 63 CODEUNITS32. The default length is 1.
VARGRAPHIC(integer [CODEUNITS16 | CODEUNITS32])
A varying-length graphic string of the specified maximum length, which can range from 1 - 16336 double bytes, 1 - 16336 CODEUNITS16, or 1 - 8168 CODEUNITS32.
DBCLOB(integer [K | M | G] [CODEUNITS16 | CODEUNITS32])
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 default is 1,048,576 (1M) code units.
If you want to multiply the length integer by 1024 (kilo), 1,048,576 (mega), or 1,073,741,824 (giga), specify a K (kilo), M (mega), or G (giga) multiplier.
  • Regardless of which multiplier, if any, you use, the resulting length is limited by the maximum length of a DBCLOB column, which is which is 1,073,741,823 (for double bytes or CODEUNITS16) or 536,870,911 (for CODEUNITS32). If a multiple of K, M, or G slightly exceeds this maximum length (for example, 1G = 1,073,741,824), the maximum length is used instead.
  • Any number of spaces (including zero spaces) is allowed between data type and the length specification or between the length integer and the K, M, or G multiplier. For example, the following specifications are all equivalent and valid:
    DBCLOB(50K)
    DBCLOB(50 K)
    DBCLOB (50   K)
  • The K, M, or G multiplier can be specified in either uppercase or lowercase.

In a Unicode database, the default string units for a character string data type are determined by the value of the NLS_STRING_UNITS global variable or string_units database configuration parameter. In a non-Unicode database, the default string units for character string data types is CODEUNITS16.

CODEUNITS16
Specifies that the units of the length attribute are Unicode UTF-16 code units, which are 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 are Unicode UTF-32 code units. 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 were converted to UTF-32. CODEUNITS32 can be specified only in a Unicode database (SQLSTATE 560AA).
[NATIONAL CHARACTER | NATIONAL CHAR | NCHAR](integer)
A fixed-length string of the specified length. The default length is 1.

The NATIONAL CHARACTER type maps to either a fixed-length character or a fixed-length graphic string, depending on the value of the nchar_mapping database configuration parameter, which also defines the string units.

[NATIONAL CHARACTER VARYING | NATIONAL CHAR VARYING | NCHAR VARYING | NVARCHAR](integer)
A varying-length string of the specified maximum length.

The NATIONAL CHARACTER VARYING type maps to either a varying-length character or a varying-length graphic string, depending on the value of the nchar_mapping database configuration parameter, which also defines the string units.

[NATIONAL CHARACTER LARGE OBJECT | NCHAR LARGE OBJECT | NCLOB](integer [K | M | G])
A large object string of the specified maximum length.

This data type maps to either a character large object (CLOB) or a double-byte character large object (DBCLOB), depending on the current value of the nchar_mapping database configuration parameter, which also defines the string units. See the description of the CLOB or DBCLOB parameter (whichever applies) for information about possible values for the length integer and how to use a K (kilo), M (mega), or G (giga) multiplier.

BINARY(integer)
A fixed-length binary string of the specified length, which must be in the range 1 - 255 bytes. The default length is 1.
[VARBINARY | BINARY VARYING](integer)
A varying-length binary string of the specified maximum length, which must be in the range 1 - 32672 bytes.
[BLOB | BINARY LARGE OBJECT](integer [K | M | G])
A binary large object string of the specified maximum length. The default is 1,048,576 (1M) bytes.
If you want to multiply the length integer by 1024 (kilo), 1,048,576 (mega), or 1,073,741,824 (giga), specify a K (kilo), M (mega), or G (giga) multiplier.
  • Regardless of which multiplier, if any, you use, the resulting length is limited by the maximum length of a BLOB column, which is 2,147,483,647 bytes. If a multiple of K, M, or G slightly exceeds this maximum length (for example, 2G = 2,147,483,648), the maximum length is used instead.
  • Any number of spaces (including zero spaces) is allowed between data type and the length specification or between the length integer and the K, M, or G multiplier. For example, the following specifications are all equivalent and valid:
    BLOB(50K)
    BLOB(50 K)
    BLOB (50   K)
  • The K, M, or G multiplier can be specified in either uppercase or lowercase.
DATE
A date.
TIME
A time.
TIMESTAMP(integer) or TIMESTAMP
A time stamp. The integer specifies the precision of fractional seconds from 0 (seconds) to 12 (picoseconds). The default is 6 (microseconds).
XML
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 time stamp (yymmddhhmmssxxx). The schema name is SYSIBM.

BOOLEAN
A Boolean value.
SYSPROC.DB2SECURITYLABEL
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 by using a distinct type, then the data type of the column is the distinct type. The length and the scale of the column are 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 by 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 by using a structured type, then the static data type of the column is the structured type. The column can include values with a dynamic type that is a subtype of structured-type-name.

A column that is defined by 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 by 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 that are 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 that is 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 that is 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 includes 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 that are 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 that are 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 the LOB value is logged. The implication of not logging is that during a rollforward 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 rollforward. During crash recovery, all committed changes and changes rolled back 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 that is 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. Storing data in this way might reduce the performance of 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 can be deferred to a subsequent ALTER TABLE statement to allow the target table to be defined, usually when 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 that is 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 that are 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 that are 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 that are generated by a timestamp-based function).

When used with a PRIMARY KEY or UNIQUE constraint, the constraint-name can 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 that is 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 that is 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 that is 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 if 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 by 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 by 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 might 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 are rounded by 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 quotation mark 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. 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 can be specified within the same column definition but results 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, except for 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 can 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 time stamp 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 that is defined as GENERATED (SQLSTATE 42623).

ALWAYS
Specifies that a value is always 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 generates 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 might 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 that is 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 might 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 might 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 might 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 are not generated for the identity column after the maximum or minimum value is reached. This is the default.
CYCLE
Specifies that values continue to be generated for this column after the maximum or minimum value is 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 none is 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 an unused value exists 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 no unused values are 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 an unused value exists in the cache, the allocation of that identity value can happen more quickly by avoiding the I/O to the log.

The minimum value is 2 (SQLSTATE 42815). The default value is CACHE 20.

Use the CACHE and NO ORDER options to allow multiple caches of identity values simultaneously. In a multi-partition or Db2® pureScale® environment, multiple members can cache them.

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 time stamp 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 time stamp 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 that use 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 considered to be nullable unless the NOT NULL column option is specified. 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 by 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 time stamp value might be adjusted to ensure that rows that are inserted into an associated history table have the end time stamp value greater than the begin time stamp 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 time stamp 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 that are 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 time stamp 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 by 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 that are 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 that is 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 might 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.

Generally, you are not allowed to protect data in such a way that your current LBAC credentials do not allow you to write to that data. To protect a column with a particular security label, you must have LBAC credentials that allow you to write to data protected by that security label. You do not have to have SECADM authority.

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 in the range 0 - 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 the name of any other check constraint that is 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 that is 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, is automatically created for the columns in the sequence that are specified with ascending order for each column. The name of the index is 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 is SQL, followed by a character time stamp (yymmddhhmmssxxx), with SYSIBM as the schema name.

PRIMARY KEY (column-name,...)
Defines a primary key that is 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 is 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 is SQL, followed by a character time stamp (yymmddhhmmssxxx), with SYSIBM as the schema name.

When explicitly defining distribution keys using the DISTRIBUTE BY HASH clause, the columns of a unique-constraint must be a superset of the distribution key columns; column order is unimportant. When distribution keys are implicitly defined, they are selected based on the definition of the unique constraint. Implicit selection of distribution keys occurs in the following cases:
  • Omit DISTRIBUTE BY HASH clause and the table is defined in a database partition group with multiple partitions.
  • DISTRIBUTE BY RANDOM clause is used.
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 that is specified in a REFERENCES clause must identify a base table or 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 that is being created (or altered). (T1 and T2 can 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 that is 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:
  1. RESTRICT
  2. SET NULL OR CASCADE
  3. 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 that are 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 considered.

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 that is 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 that are defined to have an external action
    • User-defined functions that are 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 that are specified as part of a table definition can have column references identifying columns that are 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 is enforced directly, by 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 that is 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 that are 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 determined by the setting of the ddl_constraint_def configuration parameter. You can override the default behavior by specifying either ENFORCED or NOT ENFORCED explicitly.
ENFORCED
The constraint is enforced by the database manager. ENFORCED cannot be specified in the following situations:
  • For a functional dependency (SQLSTATE 42621)
  • 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 a dependent ENFORCED referential constraint exists.
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 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 no orphan rows exist 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 that is 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 that is 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 that is 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 that is 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 by 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 that is identified by supertable-name must not have any existing subtable already defined that uses 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 is 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 the same name and description as the columns of the specified table (table-name1), view (view-name), or nickname (nickname). The specified table, view, or nickname must either exist in the catalog or must be 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 specified after LIKE:
  • If a table is specified, then the implicit definition includes the column name, data type, hidden attribute, and nullability characteristic of each of the columns of that table. If EXCLUDING COLUMN DEFAULTS is not specified, then the column default is also included.
  • If a view is specified, then the implicit definition includes the column name, data type, and nullability characteristic of each of the result columns of the fullselect defined in that view. The data types of the view columns must be data types that are valid for columns of a table.
  • If a nickname is specified, then the implicit definition includes the column name, data type, and nullability characteristic of each column of that nickname.
  • If a protected table is specified, the new table inherits the same security policy and protected columns as the identified table.
  • If a table is specified and if that 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.
  • If a table that includes a period is specified, the new table does not inherit the period definition.
  • If a system-period temporal table is specified, the new table is not a system-period temporal table.
  • If a random distribution table that uses the random by generation method is specified, and if the new table that is being created does not share the same table distribution, the RANDOM_DISTRIBUTION_KEY column that is used to generate the random distribution values is not included.

Column default and identity column attributes can 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. Consequently, the new table does not have any primary key, unique constraints, foreign key constraints, referential integrity 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 a table is specified, and if row or column level access control is activated for that table, it is not inherited by the new table.

copy-options
These options specify whether 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 that 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 the name of an identity column (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 that is derived from a constant, function, expression, or set operation that is not named using the AS clause of the select list.

AS (fullselect)
Specifies that, for each column in the derived result table of the fullselect, a corresponding column is to be defined for the table. Each defined column adopts the following attributes from its corresponding column of the result table (if applicable to the data type):
  • Column name
  • Column description
  • Data type, length, precision, and scale
  • Nullability
The following attributes are not included (although the default value and identity attributes can be included by using the copy-options):
  • Default value
  • Identity attributes
  • Hidden attribute
  • ROW CHANGE TIMESTAMP
  • Any other optional attributes of the tables or views that are referenced in the fullselect
The following restrictions apply:
  • Every select list element must have a unique name (SQLSTATE 42711). The AS clause can be used in the select clause to provide unique names.
  • The fullselect cannot refer to host variables or include parameter markers.
  • The data types of the result columns of the fullselect must be data types that are valid for columns of a table.
  • If row or column level access control (RCAC) is activated for any table that is specified in the fullselect, RCAC is not cascaded to the new 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.
WITH NO DATA | WITH DATA
Determines whether to fill the columns of the table with data:
WITH NO DATA
Do not run the fullselect. It is used only to define the table, which is not populated with the results of the query.
WITH DATA
Run the fullselect and populate the table with the results of the query.
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 that is 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 that is defined with the same query. A column of the new table that corresponds to an implicitly hidden column of a base table that is 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 that is chosen also defines the contents of the fullselect as follows:

The fullselect cannot include a data-change-table-reference clause (SQLSTATE 428FL), the fetch-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 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 that is defined on them, and the columns of these keys must appear in the select list of the materialized query table definition.
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 that are 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 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. 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 by 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 that are defined with this attribute do not allow INSERT, UPDATE, or DELETE statements (SQLSTATE 42807). User-maintained materialized query tables that are defined with this attribute do allow INSERT, UPDATE, or DELETE statements.
IMMEDIATE
The changes that are 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 (MQTs) defined with this attribute do not allow INSERT, UPDATE, or DELETE statements (SQLSTATE 42807). Column-organized MQTs using the REFRESH IMMEDIATE option are not supported when the MAINTAINED BY SYSTEM clause is specified (SQL20058N).
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. A system-maintained materialized query table that is defined as ORGANIZE BY COLUMN must use the REFRESH DEFERRED and DISTRIBUTE BY REPLICATION options.
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, which is 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. MAINTAINED BY REPLICATION cannot be specified in a partitioned database environment or in a Db2 pureScale environment (SQLSTATE 56038). The REFRESH TABLE statement, which is 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, which is 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 that is 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 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. Row and column level access control on the staging table does not affect the internal process of applying the changes that are 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 exist 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 time stamp 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 duplicate 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 that are 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.
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, even if the dft_table_org database configuration parameter is set to COLUMN.
  • For a column-organized MQT, the following types of tables can be used:
    • Shadow tables
    • User-maintained MQTs
    • System-maintained MQTs that are defined with the REFRESH DEFERRED and DISTRIBUTE BY REPLICATION clauses.

The default organization is determined by the value of the dft_table_org database configuration parameter. If dft_table_org is not specified, the default is ROW.

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. Each 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 is further organized by using a dimensions clause, key sequence, or insert time. If you specify a dimensions clause, key sequence, or insert time, specifying ROW USING is optional unless the default table organization for the database is COLUMN, in which case specifying ROW USING is mandatory.
DIMENSIONS (column-name,...)
Specifies a dimension for each column or group of columns used to cluster the table data. A table whose definition specifies this clause is known as a multidimensional clustering (MDC) table. Use parentheses within the dimension list to specify that a group of columns is to be treated as a single dimension. The DIMENSIONS keyword is optional.

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 include them all. The set of columns that are used in the ORGANIZE BY clause must follow the rules for the CREATE INDEX statement that specifies CLUSTER.

Each column name that is 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. 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 that were created using Db2 V9.7 or earlier, 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.

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 that is 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 that is 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 time stamp (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 is RCT, and the check constraint is enforced. If the ALLOW OVERFLOW clause is specified, the name of the check constraint is 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.
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 that 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 by 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 uses 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).

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 that are 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:
  1. The authorization ID
  2. A role to which the authorization ID is granted
  3. A group to which the authorization ID belongs
  4. A role to which a group the authorization ID belongs is granted
  5. PUBLIC
  6. A role to which PUBLIC is granted
If more than one table space still qualifies, the final choice is made by the database manager.
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 more information on table and table space limits, see SQL limits.
  • The table can support more than 255 rows per data page, which can improve space usage on data pages.
  • Indexes that are defined on the table will require an extra 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 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 that is 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
For more information about partitioned indexes on partitioned tables, see the description of the partition-element INDEX IN clause.
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 that are 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.
Note: An automatic storage table space is also 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 as the distribution method across database partitions. The specified columns are called a distribution key.
  • Each 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).
  • A column cannot be used as part of a distribution key if its data type is BLOB, CLOB, DBCLOB, XML, a distinct type based on any of these types, or a structured type (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, because the distribution key is inherited from the root table in the table hierarchy (SQLSTATE 42613).
  • A distribution key cannot contain row begin, row end, or transaction start ID columns.
  • If a DISTRIBUTE BY HASH clause is not specified, and if the table resides in a multiple partition database partition group with multiple database partitions, a default distribution key is automatically defined.
  • The columns of the distribution key must be a subset of the columns that make up any enforced unique constraints.

If none of the columns satisfy 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 RANDOM
Specifies that the database manager will select a distribution key to spread data evenly across all database partitions of the database partitioning group. There are two methods that the database manager uses to achieve this:
  • Random by unique: If the table includes a unique or primary key, it uses the unique characteristics of the key columns to create a random spread of the data. The columns of the unique or primary key are used as the distribution keys.
  • Random by generation: If the table does not have a unique or primary key, the database manager will include a column in the table to generate and store a generated value to use in the hashing function. The column will be created with the IMPLICITLY HIDDEN clause so that it does not appear in queries unless explicitly included. The value of the column is automatically generated as new rows are added to the table. By default, the column name is RANDOM_DISTRIBUTION_KEY. If it collides with the existing column, a non-conflicting name is generated by the database manager.
DISTRIBUTE BY REPLICATION
Specifies that the data that is 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 that are 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 fewer values are 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 fewer values are 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 that are specified for the table. If a table space was not specified for large objects by 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.
Note: An automatic storage table space is also 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 that is 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 that is 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 that is 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 that is 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 row compression is to be used for the table.The ddl_compression_def configuration parameter determines the default value of the COMPRESS keyword.
NO
Row compression is disabled.
YES
Row compression is enabled. Insert and update operations on the table use row 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 that is 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. The functionality of COMPRESS YES ADAPTIVE is a superset of the functionality of COMPRESS YES STATIC. This is the default when COMPRESS YES is explicitly specified.
STATIC
Enables classic row compression using a table-level compression dictionary. This is the same row compression functionality that existed in previous Db2 versions. This is the default when row compression is used by default but COMPRESS YES is not explicitly specified.
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 3 bytes. This is the same or less space than when VALUE COMPRESSION is not active for columns of all data types, except for CHAR(1). Whether a column is defined as nullable has no effect on the row size calculation. The zero-length data values for columns whose data type is VARCHAR, VARGRAPHIC, LONG VARCHAR, LONG VARGRAPHIC, CLOB, DBCLOB, VARBINARY, BLOB, or XML are to be stored using 2 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 3 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 that are 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 that is 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. Character data is in UTF-8. Graphic data is not allowed.
If the database is not a Unicode database, tables can be created with CCSID UNICODE, but the following rules apply:
  • The alternative collating sequence must be specified in the database configuration before creating the table (SQLSTATE 56031). CCSID UNICODE tables collate with the alternative collating sequence that is specified in the database configuration.

    The only supported alternative collating sequence is IDENTITY_16BIT.

  • 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 that is 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). This clause does not activate row or column protection by itself. For more information, refer to Protection of data using LBAC.
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 is 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 (extra 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. For random distribution tables using the random by generation method, the number of columns cannot exceed 1,011 because of the inclusion of the RANDOM_DISTRIBUTION_KEY column.
  • An object identifier column of a typed table cannot be updated (SQLSTATE 42808).
  • Any enforced unique or primary key constraint that is 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 that are 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 range-clustered table cannot be specified in a Db2 pureScale environment (SQLSTATE 42997).
    • 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.
    • Cannot be created as a random distribution table.
  • The following restrictions apply to random distribution tables:
    • Cannot be defined as a typed table
    • Cannot be defined as a range-clustered table
    • Cannot be defined as a materialized-query-table
    • Cannot be defined as a staging table
    • For random distribution tables that use the "random by" generation method (this happens when a random distribution table is created without a unique or primary key), the following additional restrictions apply:
      • 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
      • Cannot be used as an explain table
  • 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 that is 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 that are protected by label-based access control, all base tables must have the same security policy object (SQLSTATE, 428FG). The materialized query table is automatically protected with that security policy object.
    • The security label that is 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 that are 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 that are 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.
  • The isolation-clause cannot be specified in the full-select of the materialized-query-definition (SQLSTATE 42601).
  • Subselect statements that contain a lock-request-clause are not eligible for MQT routing.
  • National character data types can be specified only in an MBCS 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 that are 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:
    RESTRICT
    The delete or update rule is enforced before all other constraints, including those referential constraints with modifying rules such as CASCADE or SET NULL.
    NO ACTION
    The delete or update rule 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 V1
    If table T1 is a parent of table T3:
    • With a delete rule of RESTRICT, a restrict violation (SQLSTATE 23001) is raised if t3 contains any child rows for parent keys of T1.
    • With a delete rule of NO ACTION, the child rows might be deleted by the delete rule of CASCADE when deleting rows from T2 before the NO ACTION delete rule is enforced for the deletions from T1. If deletions from T2 did not result in the deletion of all child rows for parent keys of T1 in T3, then a constraint violation is 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 that are defined on multiple partition database partition groups, consider table collocation when choosing the distribution keys:
    • The tables must be in the same database partition group for collocation. The table spaces can 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, consider the joining columns 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 alternative 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 that is permitted is to drop the table.
    • Once such a table is backed up as part of a database or table space backup, 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 that is 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 that is 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.
      For a description of the nonzero duration values that can be specified, see SET CURRENT REFRESH AGE statement.
    • A REFRESH IMMEDIATE materialized query table that is 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.
      If the query that is specified when creating a materialized query table does not conform to these rules, a warning is returned (SQLSTATE 01633).
  • 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.
  • 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 that is 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 that are generated for other transactions. Because the column might contain null values, it is recommended that you use one of the following methods when retrieving a value from the column:
    COALESCE ( transaction_start_id_col,  row_begin_col)
     
    CASE WHEN transaction_start_id_col IS NOT NULL
              THEN transaction_start_id_col 
         ELSE row_begin_col END
  • Defining a system-period temporal table: A system-period temporal table definition includes the following:
    • A system period that is named SYSTEM_TIME, which is defined by 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 that is 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.
    To ensure that the history table cannot be implicitly dropped when a system-period temporal table is dropped, use the WITH RESTRICT ON DROP clause in the definition of the history table. A history table can manually be dropped only when the RESTRICT ON DROP attribute is removed by an ALTER TABLE statement.
  • 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 time stamp column and an end column. See the description of period-definition.

    Data change operations on an application-period temporal table might result in an automatic insert of one or two extra 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 that are 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 database column data types are mapped
      • The primary key option in the CREATE TABLE statement
      Depending on how the data source responds to requests it does not support, an error might be returned or the request might be ignored.

    When a remote base table is created using transparent DDL, a nickname is automatically created for that remote base table.

  • A referential constraint can 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 situation, the effect of the referential constraint depends on the type of statement:
    1. For an INSERT, UPDATE, or DELETE statement, the constraint ensures that, for each row of the dependent table (or any of its subtables) that has a non-null foreign key, a row exists in the parent table (or one of its subtables) with a matching parent key. This rule is enforced against any action that affects a row of either table, regardless of how that action is initiated.
    2. For a DROP TABLE statement:
      • If the dropped table is the parent table or dependent table, the constraint is dropped.
      • If 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.
      • If 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.
  • 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 Size

    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.

  • 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 VALUE COMPRESSION is active. When VALUE COMPRESSION is not active, the byte counts also depend on whether 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 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
    BINARY n+2 n+1 n
    VARBINARY n+2 n+5 (within a table) n+4 (within a table)
    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
    BOOLEAN 3 2 1
    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 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:
    DEPARTMENT 63 (0 + 3 + 33 + 7 +  3 + 17)
    ORG        57 (0 + 3 + 19 + 2 + 15 + 18)
    If VALUE COMPRESSION were to be specified, the row sizes would change to:
    DEPARTMENT 69 (2 + 5 + 31 + 8 +  5 + 18)
    ORG        53 (2 + 4 + 16 + 4 + 12 + 15)
    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
    The value is computed using the Byte Counts of Columns by Data Type table. The computed result is then used to find the lower bound of the page size where the table with extended row size can be created.
  • 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
    BINARY n+1 3 - 3 n+1 n n+2
    VARBINARY 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
    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
    BOOLEAN 2 2 - 2 2 1 3
    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 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 might be desirable, such as INTEGER(ORDER_DATE)/100. In this case, a generated column can be defined for the table, and this generated column can 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.

  • 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 might not be appropriate.
  • 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:
    • If the CACHE and NO ORDER options are in effect, multiple caches can be active simultaneously. This can happen at each member in a multi-partition or Db2 pureScale environment. The requests for next value assignments from different members might not result in the assignment of values in strict numeric order. Assume, for example, in a multi-partition or Db2 pureScale environment, that members DB1A and DB1B are using the same sequence, and DB1A gets the cache values in the range 1 - 20 and DB1B gets the cache values in the range 21 - 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 (underlined options are defaults). 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 | KEY SEQUENCE | INSERT TIME}
    • DATA CAPTURE CHANGES
    • VALUE COMPRESSION
    • COMPRESS YES [ADAPTIVE | STATIC]
    • COMPRESS NO
    • PARTITION BY RANGE
    • FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
    • CREATE TABLE OF <type-name1> (to create a typed table)
    • PROPAGATE IMMEDIATE
    • CHECK
    • DETERMINED BY

    Structured type columns are not supported.

    The columns of a column-organized table must have one of the following data types:
    • SMALLINT
    • INTEGER
    • BIGINT
    • DECIMAL
    • REAL
    • DOUBLE
    • DECFLOAT
    • CHAR (including FOR BIT DATA)
    • VARCHAR (including FOR BIT DATA)
    • BINARY
    • VARBINARY
    • GRAPHIC
    • VARGRAPHIC
    • DATE
    • TIME
    • TIMESTAMP (n)
    • BOOLEAN
    • CLOB
    • BLOB
    • DBCLOB
    • NCLOB
    • Distinct types of a supported data type

Syntax alternatives

The following alternatives are non-standard. They are supported for compatibility with earlier product versions or with other database products.
  • 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.
  • PARTITIONING KEY can be specified in place of DISTRIBUTE BY.
  • DISTRIBUTE ON can be specified in place of DISTRIBUTE BY when it is followed by the HASH option, but not when it is followed by the REPLICATION option.
  • 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.
  • When specifying the value of the datetime special register, NOW() can be specified in place of CURRENT_TIMESTAMP.

Examples

  1. 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
  2. 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
  3. 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)
  4. Create distinct types for total salary and miles and use them for columns of a table created in the default table space. In a dynamic SQL statement assume the CURRENT SCHEMA special register is JOHNDOE and the CURRENT PATH is the default (SYSIBM, SYSFUN, JOHNDOE).
    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) )
  5. 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)
  6. 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
  7. Create a table that is wholly contained in the PAYROLL table space.
       CREATE TABLE EMPLOYEE .....
         IN PAYROLL
  8. 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
  9. Create a table and log SQL changes in the default format.
       CREATE TABLE SALARY1 .....
    or
       CREATE TABLE SALARY1 .....
         DATA CAPTURE NONE
  10. Create a table and log SQL changes in an expanded format.
       CREATE TABLE SALARY2 .....
         DATA CAPTURE CHANGES
  11. 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.
       CREATE TABLE EMP_ACT
         (EMPNO       CHAR(6) NOT NULL,
          PROJNO      CHAR(6) NOT NULL,
          ACTNO       SMALLINT NOT NULL,
          EMPTIME     DECIMAL(5,2),
          EMSTDATE    DATE,
          EMENDATE    DATE,
          CONSTRAINT EMP_ACT_UNIQ UNIQUE (EMPNO,PROJNO,ACTNO),
          CONSTRAINT FK_ACT_PROJ FOREIGN KEY (PROJNO)
                                 REFERENCES PROJECT (PROJNO) ON DELETE CASCADE
         )
         IN SCHED
    A unique index called EMP_ACT_UNIQ is automatically created in the same schema to enforce the unique constraint.
  12. 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) )
  13. 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
  14. 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))
  15. 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,
                   ...)
  16. 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))
  17. 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)
  18. 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)
  19. 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)
  20. 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:
       CREATE TABLE employee
         (EMP_NO CHAR(6) NOT NULL,
          ...)
       OPTIONS
         (REMOTE_SERVER 'INFX_SERVER')
    If the REMOTE_TABNAME option is not specified, and table-name is not delimited, the remote base table name will be in uppercase characters, even if the remote data source normally stores names in lowercase characters.
    Create a remote base table called employee (lowercase characters) on an Informix server, and create a nickname named EMPLOYEE (uppercase characters) on that table:
       CREATE TABLE employee
         (EMP_NO CHAR(6) NOT NULL,
          ...)
       OPTIONS
         (REMOTE_SERVER 'INFX_SERVER',
          REMOTE_TABNAME 'employee')
    When creating a table at a remote data source that supports delimited identifiers, use the REMOTE_TABNAME option and a character string constant that specifies the table name in the required case.
    Create a remote base table called employee (lowercase characters) on an Informix server, and create a nickname named employee (lowercase characters) on that table:
       CREATE TABLE "employee"
         (EMP_NO CHAR(6) NOT NULL,
          ...)
       OPTIONS
         (REMOTE_SERVER 'INFX_SERVER')
    If the REMOTE_TABNAME option is not specified, and table-name is delimited, the remote base table name will be identical to table-name.
  21. 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).
       CREATE TABLE STUDENTS
         (SCHOOL_ID     INTEGER   NOT NULL,
          PROGRAM_ID    INTEGER   NOT NULL,
          STUDENT_NUM   INTEGER   NOT NULL,
          STUDENT_ID    INTEGER   NOT NULL,
          FIRST_NAME    CHAR(30),
          LAST_NAME     CHAR(30),
          GPA           DOUBLE)
       ORGANIZE BY KEY SEQUENCE
         (STUDENT_ID
           STARTING FROM 1
           ENDING AT 1000000)
         DISALLOW OVERFLOW
    The size of each record is the sum of the columns, plus alignment, plus the range-clustered table row header. In this case, the row size is 98 bytes: 4 + 4 + 4 + 4 + 30 + 30 + 8 + 3 (for nullable columns) + 1 (for alignment) + 10 (for the header). With a 4-KB page size (or 4096 bytes), after accounting for page overhead, there are 4038 bytes available, enough room for 41 records per page. Allowing for 1 million student records, there is a need for (1 million divided by 41 records per page) 24,391 pages. With two additional pages for table overhead, the final number of 4-KB pages that are allocated when the table is created is 24,393.
  22. 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)
  23. Create a table with protected rows.
       CREATE TABLE TOASTMASTERS
         (PERFORMANCE DB2SECURITYLABEL,
          POINTS      INTEGER,
          NAME        VARCHAR(50))
         SECURITY POLICY CONTRIBUTIONS
  24. Create a table with protected columns.
       CREATE TABLE TOASTMASTERS
         (PERFORMANCE CHAR(8),
          POINTS      INTEGER COLUMN SECURED WITH CLUBPOSITION,
          NAME        VARCHAR(50))
         SECURITY POLICY CONTRIBUTIONS
  25. 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
  26. 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.
       CREATE TABLE DOCUMENTS
         (ID INTEGER,
          CONTENTS CLOB)
         LONG IN TBSP1, TBSP2
         PARTITION BY RANGE (ID)
           (STARTING 1 ENDING 1000
            EVERY 100)
    Alternatively, use the long form of the syntax to explicitly identify a large table space for each data partition. In this example, the CLOB data for the first data partition is placed in LARGE_TBSP3, and the CLOB data for the remaining data partitions is spread across LARGE_TBSP1 and LARGE_TBSP2 in a round-robin fashion.
       CREATE TABLE DOCUMENTS
         (ID INTEGER,
          CONTENTS CLOB)
         LONG IN LARGE_TBSP1, LARGE_TBSP2
         PARTITION BY RANGE (ID)
           (STARTING 1 ENDING 100
            IN TBSP1 LONG IN LARGE_TBSP3,
            STARTING 101 ENDING 1000
            EVERY 100)
  27. 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.
       CREATE TABLE ACCESSNUMBERS
         (AREA INTEGER,
          EXCHANGE INTEGER)
         PARTITION BY RANGE (AREA NULLS LAST, EXCHANGE NULLS FIRST)
           (STARTING (1,1) ENDING (10,100),
            STARTING (11,1) ENDING (MAXVALUE,MAXVALUE))
    Because null values in the second column are sorted first, the row (11, NULL) would sort below the low boundary of the last data partition (11, 1); attempting to insert this row returns an error. The row (12, NULL) would fall within the last data partition.
  28. Create a table named RATIO having a single data partition and partitioning column PERCENT.
       CREATE TABLE RATIO
         (PERCENT INTEGER)
         PARTITION BY RANGE (PERCENT)
           (STARTING (MINVALUE) ENDING (MAXVALUE))
    This table definition allows any integer value for column PERCENT to be inserted. The following definition for the RATIO table allows any integer value between 1 and 100 inclusive to be inserted into column PERCENT.
       CREATE TABLE RATIO
         (PERCENT INTEGER)
         PARTITION BY RANGE (PERCENT)
           (STARTING 0 EXCLUSIVE ENDING 100 INCLUSIVE)
  29. 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
  30. 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)
  31. 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) )
  32. 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.
    The table level INDEX IN clause has no impact on table space selection for partitioned indexes.
       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)
  33. 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.
    The table level INDEX IN clause has no impact on table space selection for partitioned indexes.
       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)
    
  34. 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)
  35. 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
  36. Create a table called TDEPT_TEMP, based on the TDEPT table that is created in Example 1.
       CREATE TABLE TDEPT_TEMP LIKE TDEPT
    The TDEPT_TEMP table will have the same definition as TDEPT except that the primary key will not be defined and a default table space will be implicitly chosen.
  37. 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
  38. Column security labels inherited by a materialized query table.
       CREATE SECURITY LABEL COMPONENT level_array ARRAY ['A', 'B', 'C']
    
       CREATE SECURITY POLICY P COMPONENTS level_array WITH DB2LBACRULES
    
       CREATE SECURITY LABEL P.A COMPONENT level_array 'A'
    
       CREATE SECURITY LABEL P.B COMPONENT level_array 'B'
    
       CREATE SECURITY LABEL P.C COMPONENT level_array 'C'
    
       CREATE TABLE t1 (c1 INT, c2 INT SECURED WITH B, c3 REAL SECURED WITH A)
          SECURITY POLICY P
    
       CREATE TABLE t2 (c4 REAL, c5 INT SECURED WITH C, c6 DB2SECURITYLABEL)
          SECURITY POLICY P
    Generate a materialized query table
    CREATE TABLE m1 AS
       (SELECT  c1, c3, c5, c6 FROM  t1,t2 WHERE c2 !=100)
       DATA INITALLY DEFERRED REFRESH DEFERRED
    The security label of t1.c2 is used to compute security labels of all columns of m1 because it appears in the predicates of the query. The label-based access control properties of the materialized query table m1 are:
    • 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.
    A staging table for a materialized query table is protected with label-based access control. Staging table st1 is defined as:
       CREATE TABLE st1 FOR m1 PROPAGATE IMMEDIATE
    The label-based access control properties of the staging table st1 are:
    • 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.
  39. The following example shows you how to create a shadow table called T1_SHADOW that is based on the row-organized table T1.

    1. 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);
    2. 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);
  40. Create a table that is named STRING_UNITS, which demonstrates each possible string unit specification.
    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))
    The columns have the following string units:
    • 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
    Environment string units can be set with the NLS_STRING_UNITS session level global variable. If the NLS_STRING_UNITS session level global variable is not set or is null, the environment string units are determined by the value of the string_units database configuration parameter.
  41. Create a random distribution table using the random by unique method. The distribution keys are automatically set to both keys of the index: ID and NAME.
    CREATE TABLE RAND_BY_UNIQUE (ID BIGINT NOT NULL,
                                 NAME CHAR(25) NOT NULL,
                                 DESCRIPTION VARCHAR(1000),
                                 PRIMARY KEY(ID, NAME)) DISTRIBUTE BY RANDOM
  42. Create a random distribution table using the random by generation method. The distribution key is set to an internal column RANDOM_DISTRIBUTION_KEY, which is hidden from SQL unless explicitly specified.
    CREATE TABLE RAND_BY_GENERATION (C1 BIGINT) DISTRIBUTE BY RANDOM