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
- 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.
- 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.
- 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
- 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
- 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
- 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
- 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. 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).
- 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.
- [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
- One of the following built-in data types:
- 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.
- 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.
- Specifies that the column is included in implicit references to the table, and that the column can be explicitly referenced.
- 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).
- Specifies the constant as the default value for the column. The specified constant must:
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
- 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).
-
- 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 parent-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
- 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.
- Specifies what action to take on dependent tables.
- ON DELETE
- Specifies what action is to take place on the dependent tables when a row of the parent table is
deleted. There are four possible actions:
- NO ACTION (default)
- RESTRICT
- CASCADE
- SET NULL
The delete rule applies when a row of T2 is the object of a DELETE or propagated delete operation and that row has dependents in T1. Let p denote such a row of T2.- If RESTRICT or NO ACTION is specified, an error occurs and no rows are deleted.
- If CASCADE is specified, the delete operation is propagated to the dependents of p in T1.
- If SET NULL is specified, each nullable column of the foreign key of each dependent of p in T1 is set to null.
SET NULL must not be specified unless some column of the foreign key allows null values. Omission of the clause is an implicit specification of ON DELETE NO ACTION.
If T1 is delete-connected to T2 through multiple paths, defining two SET NULL rules with overlapping foreign key definitions is not allowed. For example: T1 (i1, i2, i3). Rule1 with foreign key (i1, i2) and Rule2 with foreign key (i2, i3) is not allowed.
The firing order of the rules is:- RESTRICT
- SET NULL OR CASCADE
- NO ACTION
If any row in T1 is affected by two different rules, error occurs and no rows are deleted.
A referential constraint cannot be defined if it would cause a table to be delete-connected to itself by a cycle involving two or more tables, and where one of the delete rules is RESTRICT or SET NULL (SQLSTATE 42915).
A referential constraint that would cause a table to be delete-connected to either itself or another table by multiple paths can be defined, except in the following cases (SQLSTATE 42915):
- A table must not be both a dependent table in a CASCADE relationship (self-referencing, or referencing another table), and have a self-referencing relationship in which the delete rule is RESTRICT or SET NULL.
- A key overlaps another key when at least one column in one key is the same as a column in the other key. When a table is delete-connected to another table through multiple relationships with overlapping foreign keys, those relationships must have the same delete rule, and none of the delete rules can be SET NULL.
- When a table is delete-connected to another table through multiple relationships, and at least one of those relationships is specified with a delete rule of SET NULL, the foreign key definitions of these relationships must not contain any distribution key or multidimensional clustering (MDC) key column.
- When two tables are delete-connected to the same table through CASCADE relationships, the two tables must not be delete-connected to each other if the delete rule of the last relationship in each delete-connected path is RESTRICT or SET NULL.
If any row in T1 is affected by different delete rules, the result would be the effect of all the actions 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.
- The search-condition has the following restrictions:
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.
- 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).
- 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).
- Defines the object identifier column for the typed table.
- 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
- 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.
-
-
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 toGROUPING 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.
- No grouping sets can be repeated. For example,
- 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 a GROUP BY clause is specified, the following uniqueness-related restrictions apply:
When REFRESH DEFERRED is specified:- If the materialized query table is created with the intention of providing it with an associated staging table in a later statement, the fullselect of the materialized query table must follow the same restrictions and rules as a fullselect used to create a materialized query table with the REFRESH IMMEDIATE option.
- If the query is recursive, the materialized query table is not used to optimize the processing of queries.
- The materialized query table is not used to optimize the processing of static queries.
A materialized query table whose fullselect contains a GROUP BY clause is summarizing data from the tables 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
- 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.
- 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.
- 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.
- 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
andDISTRIBUTE BY REPLICATION
clauses.
The default organization is determined by the value of the dft_table_org database configuration parameter.
- 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.
- 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).
- 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:- The authorization ID
- A role to which the authorization ID is granted
- A group to which the authorization ID belongs
- A role to which a group the authorization ID belongs is granted
- PUBLIC
- A role to which PUBLIC is granted
Table space determination can change if:- Table spaces are dropped or created
- USE privileges are granted or revoked
Partitioned tables can have their data partitions spread across multiple table spaces. When multiple table spaces are specified, all of the table spaces must exist, and they must all be either SMS or regular DMS or large DMS table spaces (SQLSTATE 42838). The authorization ID of the statement must hold the USE privilege on all of the specified table spaces.
The sufficient page size of a table is determined by either the byte count of the row or the number of columns. For more information, see Row Size Limits.
When a table is placed in a large table space:- The table can be larger than a table in a regular table space. For 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
- 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.
- 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).
- 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. If the database is a Unicode database,
character data is in UTF-8, and graphic data is in UCS-2. If the database is not a Unicode database,
character data is in UTF-8, and 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.
- 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.
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.
- Row level security
- 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.
- 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.
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).
- 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.
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.
- 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:
- 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.
- 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
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:
- 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.
- 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:
If VALUE COMPRESSION were to be specified, the row sizes would change to:DEPARTMENT 63 (0 + 3 + 33 + 7 + 3 + 17) ORG 57 (0 + 3 + 19 + 2 + 15 + 18)
DEPARTMENT 69 (2 + 5 + 31 + 8 + 5 + 18) ORG 53 (2 + 4 + 16 + 4 + 12 + 15)
Minimum page size requirements for a table with extended row size : When a data row is inserted or updated in a table with extended row size support and the physical data row length exceeds the maximum record length for the table space, a subset of the varying length string columns (VARCHAR or VARGRAPHIC) is stored as large object (LOB) data outside of the data row. The table column in the base row is replaced by a descriptor that is 24 bytes in size. In order to accommodate the extreme case where all VARCHAR or VARGRAPHIC data is stored outside of the data row, the database manager computes the minimum row size using the following method:- Handles every VARCHAR(n) column where n > 24 as if it were VARCHAR(24)
- Handles every VARGRAPHIC(m) column where m > 12 as if it were VARGRAPHIC(12
- Storage byte counts: The following tables describe the storage
byte counts of columns by data type for data values.
The first table defines the sets of attributes. Those attributes are referenced in the second table, which contains the details for the byte counts for each data type.
The byte counts depend on whether VALUE COMPRESSION is active. When VALUE COMPRESSION is not active, the byte counts also depend on whether the column is nullable. The values in the table represent the amount of storage (in bytes) that is used to store the value. The byte counts shown apply when row compression is not enabled. If row compression is active, the total number of bytes used by a row will generally be smaller than for an uncompressed version of the row; it will never be larger.
Table 5. Definitions of the criteria referenced in the related table Case Data value VALUE COMPRESSION Column nullability A NULL Not active Nullable B NULL Active 2 Nullable C Zero-length Active 2 Not applicable D System default1 Active 2 Not applicable E All other data values Not active Nullable F All other data values Not active Not nullable G All other data values Active 2 Not applicable 1 When COMPRESS SYSTEM DEFAULT is specified for the column.
2 There is an additional 2 bytes of storage used by each row when VALUE COMPRESSION is active for that row.
Table 6. Storage Byte Counts Based on Row Format, Data Type, and Data Value Data type Case A Case B Case C Case D Case E Case F Case G SMALLINT 3 3 - 3 3 2 4 INTEGER 5 3 - 3 5 4 6 BIGINT 9 3 - 3 9 8 10 REAL 5 3 - 3 5 4 6 DOUBLE 9 3 - 3 9 8 10 DECIMAL The integral part of (p/2)+2, where p is the precision 3 - 3 The integral part of (p/2)+2, where p is the precision The integral part of (p/2)+1, where p is the precision The integral part of (p/2)+3, where p is the precision DECFLOAT(16) 9 3 - 3 9 8 10 DECFLOAT(34) 17 3 - 3 17 16 18 CHAR(n) n+1 3 - 3 n+1 n n+2 VARCHAR(n) 5 3 2 2