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 and its table space.
Invocation for CREATE TABLE
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES RUN behavior is in effect. For more information, see Authorization IDs and dynamic SQL.
Authorization for CREATE TABLE
The privilege set that is defined below must include at least one of the following:
- The CREATETAB privilege for the database explicitly specified by the IN clause.
If the IN clause is not specified, the CREATETAB privilege on database DSNDB04 is required.
- DBADM, DBCTRL, or DBMAINT authority for the database explicitly specified by the IN clause. If the IN clause is not specified, DBADM, DBCTRL, or DBMAINT authority for database DSNDB04 is required.
- SYSADM or SYSCTRL authority
- System DBADM
Installation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)
If the table space is created implicitly, the privilege set that is defined below must include at least one of the following:
- The CREATETS privilege for the database explicitly specified by the IN clause.
If the IN clause is not specified, the CREATETS privilege on database DSNDB04 is required.
- DBADM, DBCTRL, or DBMAINT authority for the database explicitly specified by the IN clause. If the IN clause is not specified, DBADM, DBCTRL, or DBMAINT authority for database DSNDB04 is required.
- SYSADM or SYSCTRL authority
Installation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)
- For the table space if one is specified in the IN clause
- For the default buffer pool and default storage group of the database if a database is specified in the IN clause
If you specify a table space name, you must also have the SYSADM or SYSCTRL authority or the DBADM authority for the database.
For tables that are created in an implicit database, the database authority must be held on DSNDB04.
Additional privileges might be required in the following conditions:
- The clause IN, LIKE or FOREIGN KEY is specified.
- The data type of a column is a distinct type.
- The table space is implicitly created.
- A fullselect is specified.
- A column is defined as a security label column.
Privilege set: See the description of the appropriate clauses for details about these privileges.
If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the package.
If the application is bound in a trusted context with the ROLE AS OBJECT OWNER clause specified:
- A role is the owner of the table that is being created
- The privilege set is the set of privileges that are held by that role
- The schema qualifier (implicit or explicit) must be the same as the role, unless the role has the CREATEIN privilege on the schema, or SYSADM, SYSCTRL, or system DBADM authority
Otherwise, an authorization ID is the owner of the package, and the following rules apply:
- If the privilege set lacks the CREATEIN privilege on the schema, SYSADM authority, SYSCTRL authority, and System DBADM authority, the schema qualifier (implicit or explicit) must be the same as the authorization ID of the owner of the package.
- If the privilege set lacks SYSADM authority, SYSCTRL authority, and System DBADM authority, and the table is explicitly qualified, the authorization ID that is the same as the schema name must have all the necessary privileges to create the table, and that authorization ID is the owner of the table. Otherwise, the authorization ID of the owner of the package must have all the necessary privileges to create the table, and that authorization ID is the owner of the table.
- If the privilege set includes SYSADM authority, SYSCTRL authority, or system DBADM authority, the schema qualifier (implicit or explicit) can be any schema name. However, if the table is explicitly qualified, the authorization ID that is the same as the schema name is the owner of the table. Otherwise, the authorization ID of the owner of the package is the owner of the table.
- If the privilege set includes DBADM authority and DBCTRL authority for the database, the schema qualifier (implicit or explicit) can be any schema name. However, if the table is explicitly qualified, the authorization ID that is the same as the schema name is the owner of the table. Otherwise, the authorization ID of the owner of the package is the owner of the table.
If the statement is dynamically prepared, the privilege set is the privileges that are held by the SQL authorization ID of the process unless the process is within a trusted context and the ROLE AS OBJECT OWNER clause is in effect. When ROLE AS OBJECT OWNER is in effect, the privileges set is the privileges that are held by the role that is associated with the primary authorization ID of the process, and the owner of the table is that role. The schema qualifier (implicit or explicit) must be the same as that role, unless the role has CREATEIN privilege on the schema, or SYSADM authority, SYSCTRL authority, or System DBADM authority.
For the case where the SQL authorization ID of the process holds the privileges, the following rules apply:
- If the privilege set lacks CREATEIN privilege on the schema, SYSADM authority, SYSCTRL authority, and System DBADM authority, the schema qualifier must be the same as one of the authorization IDs of the process.
- If the privilege set lacks SYSADM authority, SYSCTRL authority, and System DBADM authority, and the table is explicitly qualified, then the authorization ID that is the same as the schema name must have all the necessary privileges to create the table, and that authorization ID is the owner of the table. Otherwise, the SQL authorization ID of the process must include all privileges that are needed to create the table, and that authorization ID is the owner of the table.
- If the privilege set includes SYSADM authority, SYSCTRL authority, or System DBADM authority, the schema qualifier can be any schema name. However, if the table is explicitly qualified, then the authorization ID that is the same as the schema name is the owner of the table. Otherwise, the SQL authorization ID of the process is the owner of the table.
Syntax for CREATE TABLE
- 1 The same clause must not be specified more than once.
- 2 FL 504 Hash-organized tables are deprecated. Beginning in Db2 12, packages bound with APPLCOMPAT( V12R1M504) or higher cannot create hash-organized tables or alter existing tables to use hash-organization. Existing hash organized tables remain supported, but they are likely to be unsupported in the future.
- 3 The IMPTSCMP subsystem parameter specifies the default value. See USE DATA COMPRESSION field (IMPTSCMP subsystem parameter).
- 4 The IMPDSSIZE subsystem parameter specifies the default value. See IMPDSSIZE in macro DSN6SYSP.
- 5 The TBSBPOOL, TBSBP8K, TBSBP16K, or TBSBP32K subsystem parameter determines the default value. See DSNTIP2: Buffer pool sizes panel 2.
- 6 The IMPTKMOD subsystem parameter specifies the default value. See IMPTKMOD in macro DSN6SYSP.
- 7 See PAGE SET PAGE NUMBERING field (PAGESET_PAGENUM subsystem parameter).
- 8 PAGENUM RELATIVE is allowed only if a partitioning clause is specified.
column-definition:
- 1 Data type is optional if as-row-change-timestamp-clause is specified
- 2 The same clause must not be specified more than one time.
- 3 The cast-function-name form of the DEFAULT value can only be used with a column that is defined as a distinct type.
- 4 AS SECURITY LABEL can be specified only for a CHAR(8) data type and requires that the NOT NULL and WITH DEFAULT clauses be specified.
- 5 INLINE LENGTH only applies to a column with a LOB data type or a distinct type that is based on a LOB data type.
data-type:
built-in-type:
- 1 The CCSID clause must only be specified for a character string or graphic string column in an EBCDIC table. The CCSID clause must not be specified with non-deterministic-expression.
XML-type-modifier:
XML-schema-specification:
generated-clause:
as-identity-clause:
as-row-change-timestamp-clause:
as-row-transaction-start-id-clause:
as-row-transaction-timestamp-clause:
as-generated-expression-clause:
non-deterministic-expression:
special-register:
- 1 This definition of special register is specific to this context, as part of non-deterministic-expression.
- 2 USER can be specified as a synonym for SESSION_USER.
session-variable:
- 1 This definition of session variable is specific to this context, as part of non-deterministic-expression.
column-constraint:
period-definition:
unique-constraint:
- 1 If BUSINESS_TIME WITHOUT OVERLAPS is specified, the BUSINESS_TIME period will not overlap in time periods for the same column-name values.
referential-constraint:
references-clause:
check-constraint:
as-result-table:
copy-options:
- 1 These clauses can be specified in any order and must not be specified more than one time.
- 2 EXCLUDING COLUMN DEFAULTS, INCLUDING COLUMN DEFAULTS, and USING TYPE DEFAULTS must not be specified with the LIKE clause.
- 3 EXCLUDING XML TYPE MODIFIERS must be specified with the LIKE clause if the identified table has an XML type modifier and none of the XML columns of the new table has an XML type modifier. EXCLUDING XML TYPE MODIFIERS is not supported when a view is identified in a LIKE clause and the view contains XML columns.
materialized-query-definition
refreshable-table-options:
partitioning-clause:
partition-expression:
partition-element:
- 1 FL 504 Hash-organized tables are deprecated. Beginning in Db2 12, packages bound with APPLCOMPAT( V12R1M504) or higher cannot create hash-organized tables or alter existing tables to use hash-organization. Existing hash organized tables remain supported, but they are likely to be unsupported in the future.
Description for CREATE TABLE
- table-name
- Names the table. The name, including the implicit or explicit qualifier, must not identify a table, view, alias, or synonym that exists at the current server or a table that exists in the SYSIBM.SYSPENDINGOBJECTS catalog table. The unqualified name must not be the same as an existing synonym.
If the name is qualified, the name can be a two-part or three-part name. If a three-part name is used, the first part must match the value of field Db2LOCATION NAME on installation panel DSNTIPR at the current server. (If the current server is not the local Db2, this name is not necessarily the name in the CURRENT SERVER special register.)
For more information, see Guidelines for table names.
FL 502 KEY LABEL key-label-name or NO KEY LABEL
Specifies whether key label is specified at the table level for encryption. The table-name must identify a table that resides in a universal table space, or a partitioned (non-UTS) table space. If you specify a table-space-name using the IN clause, a subsequent REORG of the table space is required for the key label value to take effect.
- KEY LABEL key-label-name
- Specifies the default key label that is used to encrypt all the table spaces and index spaces associated with the table. This includes base table spaces, auxiliary table spaces, XML table spaces, index spaces, and clone table spaces, regardless of whether they are explicitly or implicitly created. Users must set the key label for archive or history tables independently.
The data set must be Db2-managed for all the table spaces and index spaces associated with the table.
The table-name must not identify one of the following:- An accelerator-only table.
- An auxiliary table.
The key label must be defined in ICSF. Db2 address space RACF® user ID or group must be permitted access to the key label in RACF.
The key label can be inherited or overridden when the data set is allocated. For details about the order of precedence, see Notes®.
- NO KEY LABEL
- Indicates that there is no key label specified at the table level for encryption.
column-definition
- column-name
- Names a column of the table. For a dependent table, up to 749 columns can be named. For a table that is not a dependent, this number is 750. Do not qualify column-name and do not use the same name for more than one column of the table.
- built-in-type
- Specifies the data type of the column as one of the following built-in data types, and for character string data types, specifies the subtype. For more information about defining a table with a LOB column (CLOB, BLOB, or DBCLOB), see Creating a table with LOB columns.
If IN ACCELERATOR is specified, not all data types are supported. For example, DECFLOAT, LOB, ROWID, TIMESTAMP WITH TIME ZONE, and XML are not supported. The IBM® Db2 Analytics Accelerator for z/OS®: Stored Procedures Reference has a complete list of supported data types.
- SMALLINT
- For a small integer.
- INTEGER or INT
- For a large integer.
- BIGINT
- For a big integer.
- DECIMAL(integer,integer) or DEC(integer,integer)
- DECIMAL(integer) or DEC(integer)
- DECIMAL or DEC
- For a decimal number. The first integer is the precision of the number. That is, the total number of digits, which can range 1–31. The second integer is the scale of the number. That is, the number of digits to the right of the decimal point, which can range from 0 to the precision of the number.
You can use DECIMAL(p) for DECIMAL(p,0) and DECIMAL for DECIMAL(5,0).
You can also use the word NUMERIC instead of DECIMAL. For example, NUMERIC(8) is equivalent to DECIMAL(8). Unlike DECIMAL, NUMERIC has no allowable abbreviation.
- DECFLOAT( integer)
- For a decimal floating-point number. The value of integer must be either 16 or 34 and represents the number of significant digits that can be stored. If integer is omitted, the DECFLOAT column will be capable of representing 34 significant digits.
- FLOAT(integer)
- FLOAT
- For a floating-point number. If integer is in the range 1–21 inclusive, the format is single precision floating-point. If the integer is in the range 22–53 inclusive, the format is double precision floating-point.
You can use DOUBLE PRECISION or FLOAT for FLOAT(53).
- REAL
- For single precision floating-point.
- DOUBLE or DOUBLE PRECISION
- For double precision floating-point
- CHARACTER(integer) or CHAR(integer)
- CHARACTER or CHAR
- For a fixed-length character string of length integer, which can range 1–255. If the length specification is omitted, a length of 1 character is assumed.
CCSID 1208
FL 500Specifies that the column is a Unicode column encoded in UTF-8. This clause must not be specified for an ASCII or Unicode table.
- VARCHAR(integer), CHAR VARYING(integer), or CHARACTER VARYING(integer)
- For a varying-length character string of maximum length integer, which can range from 1 to the maximum record size minus 10 bytes. See Table 5 to determine the maximum record size.
- CCSID 1208
- Specifies that the column is a Unicode column encoded in UTF-8. This clause must not be specified for an ASCII or Unicode table.
- FOR subtype DATA
- Specifies a subtype for a character string column, which is a column with a data type of CHAR, VARCHAR, or CLOB. Do not use the FOR subtype DATA clause with columns of any other data type (including any distinct type). subtype can be one of the following:
- SBCS
- Column holds single-byte data.
- MIXED
- Column holds mixed data. Do not specify MIXED if the value of field MIXED DATA on installation panel DSNTIPF is NO unless the CCSID UNICODE clause is also specified, or the table is being created in a Unicode table space or database.
- BIT
- Column holds BIT data. Do not specify BIT for a CLOB column.
Only character strings are valid when subtype is BIT.
If you do not specify the FOR subtype DATA clause, the column is defined with a default subtype. For ASCII or EBCDIC data:
- The default is SBCS when the value of field MIXED DATA on installation panel DSNTIPF is NO.
- The default is MIXED when the value is YES.
For Unicode data, the default subtype is MIXED.
A security label column is always considered SBCS data, regardless of the encoding scheme of the table.
- CLOB(integer [K|M|G]), CHAR LARGE OBJECT(integer [K|M|G]), or CHARACTER LARGE OBJECT(integer [K|M|G])
- CLOB, CHAR LARGE OBJECT, or CHARACTER LARGE OBJECT
- For a character large object (CLOB) string of the specified maximum length in bytes. The maximum length must be in the range 1–2147483647. A CLOB column has a varying-length. It cannot be referenced in certain contexts regardless of its maximum length. For more information, see Restrictions using LOBs. When integer is not specified, the default length is 1M. The maximum value that can be specified for integer depends on whether a units indicator is also specified as shown in the following list.
- integer
- The maximum value for integer is 2147483647. The maximum length of the string is integer.
- integer K
- The maximum value for integer is 2097152. The maximum length is 1024 times integer.
- integer M
- The maximum value for integer is 2048. The maximum length is 1,048,576 times integer.
- integer G
- The maximum value for integer is 2. The maximum length is 1,073,741,824 times integer.
integer can be separated from K, M, or G by 0 or more spaces.
If you specify a value that evaluates to 2 gigabytes (2,147,483,648), Db2 uses a value that is one byte less, or 2147483647.
CCSID 1208
FL 500Specifies that the column is a Unicode column encoded in UTF-8. This clause must not be specified for an ASCII or Unicode table.
- GRAPHIC(integer)
- GRAPHIC
- For a fixed-length graphic string of length integer, which can range 1–127. If the length specification is omitted, a length of 1 character is assumed.
- CCSID 1200
- FL 500Specifies that the column is a Unicode column encoded in UTF-16. This clause must not be specified for an ASCII or Unicode table.
- VARGRAPHIC(integer)
- For a varying-length graphic string of maximum length integer, which must range from 1 to n/2, where n is the maximum row size minus 2 bytes.
- CCSID 1200
- Specifies that the column is a Unicode column encoded in UTF-16. This clause must not be specified for an ASCII or Unicode table.
- DBCLOB(integer [K|M|G])
- DBCLOB
- For a double-byte character large object (DBCLOB) string of the specified maximum length in double-byte characters. The maximum length must be in the range of 1–1,073,741,823. A DBCLOB column has a varying-length. It cannot be referenced in certain contexts regardless of its maximum length. For more information, see Restrictions using LOBs.
When integer is not specified, the default length is 1M. The meaning of integer K|M|G is similar to CLOB. The difference is that the number specified is the number of double-byte characters.
integer can be separated from K, M, or G by 0 or more spaces.
- CCSID 1200
- Specifies that the column is a Unicode column encoded in UTF-16. This clause must not be specified for an ASCII or Unicode table.
- BINARY(integer)
- A fixed-length binary string of length integer. The integer can range 1–255. If the length specification is omitted, a length of 1 byte is assumed.
- BINARY VARYING(integer) or VARBINARY(integer)
- A varying-length binary string of maximum length integer, which can range 1–32704. The length is limited by the page size of the table space.
- BLOB (integer [K|M|G] or BINARY LARGE OBJECT(integer [K|M|G])
- BLOB or BINARY LARGE OBJECT
- For a binary large object (BLOB) string of the specified maximum length in bytes. The maximum length must be in the range of 1–2147483647. A BLOB column has a varying-length. It cannot be referenced in certain contexts regardless of its maximum length. For more information, see Restrictions using LOBs.
When integer is not specified, the default length is 1M. The meaning of integer K|M|G is the same as for CLOB.
integer can be separated from K, M, or G by 0 or more spaces.
- DATE
- For a date.
- TIME
- For a time.
- TIMESTAMP(integer) WITHOUT TIME ZONE
- For a timestamp. integer specifies the optional timestamp precision attribute and must be in the range 0–12. The timestamp precision denotes the number of fractional second digits that are included in the timestamp. The default is 6.
- TIMESTAMP(integer) WITH TIME ZONE
- For a timestamp with time zone. integer specifies the optional timestamp precision attribute and must be in the range 0–12. The timestamp precision denotes the number of fractional second digits that are included in the timestamp. The default is 6.
- ROWID
- For a row ID type.
A table can contain at most two ROWID columns. If it contains two, one column is implicitly generated by Db2 and the other column is explicitly defined as a ROWID without the IMPLICITLY HIDDEN attribute.
The values in a ROWID column are unique for every row in the table and cannot be updated. You must specify NOT NULL with ROWID.
- XML
- For an XML document. Only well-formed XML documents can be inserted into an XML column.
If the XML column is the first XML column that you create for the table, a BIGINT DOCID column is implicitly created and is used to store a unique document identifier for the XML columns of a row.
- XMLSCHEMA
- Specifies one or more XML schemas that are used to validate the XML value. The same XML schema can not be specified more than one time.
If the XML value has already been validated, for example, the XML value is the result of the DSN_XMLVALIDATE function or from an XML column with a type modifier, and the XML schema against which the XML value is validated is one of the schemas specified in the XML-type-modifier, Db2 accepts the XML value without revalidation.
- XML-schema-specification
- Specifies one XML schema. The XML schema can be identified by using either the registered-XML-schema-name or the schema's target namespace followed by an optional schema location. Any XML schema that is referenced in this clause must be registered in the XML schema repository prior to use.
- ID registered-XML-schema-name
- Identifies an XML schema by using its registered-XML-schema-name. The name must uniquely identify an existing XML schema in the XML schema repository at the current server. If no XML schema by this name exists, an error is returned.
The schema qualifier must be SYSXSR.
- URI target-namespace
- Specifies the target namespace URI of the XML schema. The value for the target-namespace URI is a character string constant which is not empty. The URI must be the target namespace of a registered XML schema and, if no LOCATION clause is specified, it must uniquely identify the registered XML schema.
- NO NAMESPACE
- Specifies that the XML schema has no target namespace. There must be a registered XML schema that has no target namespace. If no LOCATION clause is specified, there must be only one such registered XML schema.
- LOCATION schema-location
- Specifies the XML schema location URI of the XML schema. The value of schema-location is a character string constant that is not empty. The schema location URI, combined with the target namespace URI, must identify a registered XML schema.
- ELEMENT element-name
- Specifies the name of the global element declaration. element-name must match the local name of the root element node in the instance XML document. The namespace name of the root element node must be the same as the target namespace URI.
- distinct-type-name
- Specifies
the data type of the column is a distinct type (a user-defined data
type). The length, precision, and scale of the column are respectively
the length, precision, and scale of the source type of the distinct
type. The privilege set must implicitly or explicitly include the
USAGE privilege on the distinct type.
The encoding scheme of the distinct type must be the same as the encoding scheme of the table. The subtype for the distinct type, if it has the attribute, is the subtype with which the distinct type was created.
If the column is to be used in the definition of the foreign key of a referential constraint, the data type of the corresponding column of the parent key must have the same distinct type.
- NOT NULL
- Prevents the column from containing null values. Omission of NOT NULL implies that the column can contain null values.
- column-constraint
- The column-constraint of a column-definition provides
a shorthand method of defining a constraint composed of a single column.
Thus, if a column-constraint is specified
in the definition of column C, the effect is the same as if that constraint
were specified as a unique-constraint, referential-constraint,
or check-constraint in which C is the only
identified column.
- CONSTRAINT constraint-name
- Names the constraint. If a constraint name is not specified, a unique constraint name is generated. If the name is specified, it must be different from the names of any referential, check, primary key, or unique key constraints previously specified on the table.
- PRIMARY KEY
- Provides a shorthand method of defining a primary key composed of a single column. Thus, if PRIMARY KEY is specified in the definition of column C, the effect is the same as if the PRIMARY KEY(C) clause is specified as a separate clause. The NOT NULL clause must be specified with this clause. PRIMARY KEY cannot be specified more than one time in a column definition, and must not be specified if the UNIQUE clause is specified in the definition. This clause must also not be specified if the definition is for one of the following types of columns:
- a LOB column
- a ROWID column
a distinct type column that is based on a LOB or ROWID data type
- an XML column
- a row change timestamp column
- a column in an accelerator-only table
The table is marked as unavailable until its primary index is explicitly created unless the CREATE TABLE statement is processed by the schema processor or the table space that contains the table is implicitly created. In that case, Db2 implicitly creates an index to enforce the uniqueness of the primary key and the table definition is considered complete. (For more information about implicitly created indexes, see Implicitly created indexes.)
- UNIQUE
- Provides a shorthand method of defining a unique key composed of a single column. Thus, if UNIQUE is specified in the definition of column C, the effect is the same as if the UNIQUE(C) clause is specified as a separate clause. The NOT NULL clause must be specified with this clause. UNIQUE cannot be specified more than one time in a column definition and must not be specified if the PRIMARY KEY clause is specified in the column definition or if the definition is for one of the following types of columns:
- a LOB column
- a ROWID column
a distinct type column that is based on a LOB or ROWID data type
- an XML column
- a row change timestamp column
- a column in an accelerator-only table
The table is marked as unavailable until all the required indexes are explicitly created unless the CREATE TABLE statement is processed by the schema processor or the table space that contains the table is implicitly created. In that case, Db2 implicitly creates the indexes that are required for the unique keys and the table definition is considered complete. (For more information about implicitly created indexes, see Implicitly created indexes.)
- references-clause
- The references-clause of a column-definition provides
a shorthand method of defining a foreign key composed of a single
column. Thus, if references-clause is specified
in the definition of column C, the effect is the same as if the references-clause were
specified as part of a FOREIGN KEY clause in which C is the only identified
column. Do not specify references-clause in the definition of the following types of columns because these types of columns cannot be a foreign key:
- a LOB column
- a ROWID column
- a DECFLOAT column
- a distinct type column that is based on a LOB, ROWID, or DECFLOAT data type
- an XML column
- a row change timestamp column
- a security label column
- CHECK (check-condition)
- CHECK (check-condition) provides a shorthand method of defining a check constraint that applies to a single column. For conformance with the SQL standard, if CHECK is specified in the column definition of column C, no columns other than C should be referenced in the check condition of the check constraint. The effect is the same as if the check condition were specified as a separate clause.
- DEFAULT
- Specifies the default value that is assigned to the column in the absence of a value specified on an insert or update operation or LOAD. DEFAULT must not be specified more than one time in the same column-definition. Do not specify DEFAULT for the following types of columns because Db2 generates default values:
- An identity column (a column that is defined AS IDENTITY)
- A ROWID column (or a distinct type that is based on a ROWID)
- A row change timestamp column
- A row-begin column
- A row-end column
- A transaction-start-id column
- An XML column
If IN ACCELERATOR is specified, do not specify DEFAULT for a column.
Do not specify a value after the DEFAULT keyword for a security label column. Db2 provides the default value for a security label column.
If a value is not specified after DEFAULT, the default value depends on the data type of the column, as follows:- Data Type
- Default Value
- Numeric
- 0
- Big integer
- 0
- Fixed-length character string
- Blanks
- Fixed-length graphic string
- Blanks
- Fixed-length binary string
- Hexadecimal zeros
- Varying-length string
- A string of length 0
- Inline BLOB
- Hexadecimal zeros
- Inline CLOB
- Blanks
- Inline DBCLOB
- Blanks
- Date
- CURRENT DATE
- Time
- CURRENT TIME
- TIMESTAMP(integer) WITHOUT TIME ZONE
- CURRENT TIMESTAMP(p) WITHOUT TIME ZONE where p is the corresponding timestamp precision.
- TIMESTAMP(integer) WITH TIME ZONE
- CURRENT TIMESTAMP(p) WITH TIME ZONE where p is the corresponding timestamp precision.
If the column is defined as timestamp with time zone the default value must include a time zone.
- Distinct type
- The default of the source data type
A default value other than the one that is listed above can be specified in one of the following forms:
- WITH DEFAULT for a default value of an empty string
- DEFAULT NULL for a default value of null
Omission of NOT NULL and DEFAULT from a column-definition, for a column other than an identity column, is an implicit specification of DEFAULT NULL. For an identity column, it is an implicit specification of NOT NULL, and Db2 generates default values.
- constant
- Specifies a constant as the default value for the column. The value of the constant must conform to the rules for assigning that value to the column.
A character or graphic string constant must be short enough so that its UTF-8 representation requires no more than 1536. A hexadecimal graphic string constant (GX) cannot be specified.
In addition, the length of the constant value cannot be greater than the INLINE LENGTH attribute for LOB columns.
- SESSION_USER or USER
- Specifies the value of the SESSION_USER (USER) special register at the time of an SQL data change statement or LOAD as the default value for the column. If SESSION_USER is specified, the data type of the column must be a character string with a length attribute greater than or equal to 8 characters when the value is expressed in CCSID 37. If the data type of the column is an inline CLOB, the INLINE LENGTH attribute must be greater than or equal to 8 characters when the value is expressed as CCSID 37.
- CURRENT SQLID
- Specifies the value of the SQL authorization ID of the process at the time of an insert or update operation or LOAD as the default value for the column. If CURRENT SQLID is specified, the data type of the column must be a character string with a length attribute greater than or equal to the length attribute of the CURRENT SQLID special register. If the data type of the column is an inline CLOB, the INLINE LENGTH attribute must be greater than or equal to the length attribute of the CURRENT SQLID special register.
- NULL
- Specifies null as the default value for the column. If NOT NULL is specified, DEFAULT NULL must not be specified with the same column-definition.
- cast-function-name
- The name of the cast function that matches the name of the distinct type for the column. A cast function can only be specified if the data type of the column is a distinct type.
The schema name of the cast function, whether it is explicitly specified or implicitly resolved through function resolution, must be the same as the explicitly or implicitly specified schema name of the distinct type.
- 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. The length of the constant cannot be greater than the INLINE LENGTH attribute for LOB columns.
- SESSION_USER or USER
- Specifies the value of the SESSION_USER (USER) special register at the time a row is inserted as the default for the column. The source type of the distinct type of the column must be a CHAR, VARCHAR, or inline CLOB with a length attribute (inline length attribute for CLOB) that is greater than or equal to the length attribute of the SESSION_USER special register.
- CURRENT SQLID
- Specifies the value of the CURRENT SQLID special register at the time a row is inserted as the default for the column. The source type of the distinct type of the column must be a CHAR, VARCHAR, or inline CLOB with a length attribute (or inline length attribute for CLOB) that is greater than or equal to the length attribute of the CURRENT SQLID special register.
- NULL
- Specifies the NULL value as the argument.
In a given column definition:
- DEFAULT and FIELDPROC cannot both be specified.
- NOT NULL and DEFAULT NULL cannot both be specified.
Table 1 summarizes the effect of specifying the various combinations of the NOT NULL and DEFAULT clauses on the CREATE TABLE statement column-description clause.Table 1. Effect of specifying combinations of the NOT NULL and DEFAULT clauses If NOT NULL is: And DEFAULT is: The effect is: Specified1 Omitted An error occurs if a value is not provided for the column on an insert or update operation or LOAD. Specified without an operand The system defined nonnull default value is used. constant The specified constant is used as the default value. SESSION_USER The value of the SESSION_USER special register at the time of an insert or update operation or LOAD is used as the default value. CURRENT SQLID The SQL authorization ID of the process at the time of an insert or update operation or LOAD is used as the default value. NULL An error occurs during the execution of CREATE TABLE. Omitted Omitted Equivalent to an implicit specification of DEFAULT NULL. Specified without an operand The system defined nonnull default value is used. constant The specified constant is used as the default value. SESSION_USER The value of the SESSION_USER special register at execution time is used as the default value. CURRENT SQLID The SQL authorization ID of the process is used as the default value. NULL Null is used as the default value. Note: The table does not apply to a column with a ROWID data type or to an identity column. - GENERATED
- Specifies that Db2 generates values for the column. GENERATED must be specified if the column is to be considered one of the following types of columns:
- An identity column
- A row change timestamp column.
- A ROWID column
- A row-begin column
- A row-end column
- A transaction-start-id column
- A generated expression column
GENERATED must only be specified for these types of columns. GENERATED must not be specified with default-clause in a column definition.
GENERATED must not be specified if the column definition references global variables.
- ALWAYS
- Specifies that Db2 always generates a value for the column when a row is inserted or updated and a default value must be generated. ALWAYS is the default and recommended value.
- BY DEFAULT
- Specifies that Db2 will generate a value for the column when a row is inserted or updated and a default value must be generated, unless an explicit value is specified.
For a row change timestamp column, Db2 inserts or updates a specified value but does not verify that the value is unique for the column unless the row change timestamp column has a unique constraint or a unique index that specifies only the row change timestamp column.
For a ROWID column, Db2 uses a specified value only if it is a valid row ID value that was previously generated by Db2 and the column has a unique, single-column index. Until this index is created on the ROWID column, the SQL insert or update operation and the LOAD utility cannot be used to add rows to the table. If the table space is explicitly created and the value of the CURRENT RULES special register is 'STD' when the CREATE TABLE statement is processed, or if the table space is implicitly created, Db2 implicitly creates the index on the ROWID column. The name of this index is 'I' followed by the first ten characters of the column name followed by seven randomly generated characters. If the column name is less than ten characters, Db2 adds underscore characters to the end of the name until it has ten characters. The implicitly created index has the COPY NO attribute.
For an identity column, Db2 inserts a specified value but does not verify that it a unique value for the column unless the identity column has a unique, single-column index.
BY DEFAULT is the recommended value only when you are using data propagation.
- FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
- Specifies that the column is a timestamp column for the table. Db2 generates a value for the column for each row as the row is inserted, and for any row in which any column is updated. The value that is generated for a row change timestamp column is a timestamp that corresponds to the insert or update time of the row. If multiple rows are inserted or updated with a single statement, the value for the row change timestamp column might be different for each row.
A table can only have one row change timestamp column.
If data-type is specified, it must be TIMESTAMP WITHOUT TIME ZONE with a precision of 6.
A row change timestamp column cannot have a DEFAULT clause. NOT NULL must be specified for a row change timestamp column.
- AS TRANSACTION START ID
- Specifies that the value is assigned by Db2 whenever a row is inserted into the table or any column in the row is updated. Db2 assigns a unique timestamp value per transaction or the null value. The null value is assigned to the transaction-start-ID column if the column is nullable. Otherwise, the value is generated using 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 when 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.
A table can have only one transaction-start-ID column. If a data type is not specified, the column is defined as TIMESTAMP(12) WITHOUT TIME ZONE. If a data type is specified, it must be TIMESTAMP(12) WITHOUT TIME ZONE or TIMESTAMP(12) WITH TIME ZONE. If the column is defined as TIMESTAMP WITH TIME ZONE, the values are stored in UTC, with a time zone of +00:00. A transaction-start-ID column cannot have a DEFAULT clause. A transaction-start-ID column is not updatable.
A value for a transaction-start-ID column is composed of a TIMESTAMP(9) value that is unique per transaction per data sharing member followed by 000 or three digits that indicate the data sharing member ID.
- AS ROW BEGIN
Specifies that a timestamp value is assigned to the column whenever a row is inserted or any column in the row is updated. If the value of the SYSIBM.TEMPORAL_LOGICAL_TRANSACTION_TIME built-in global variable at the time of the insert or update is null, the value is generated using a reading of the time-of-day clock during execution of the first data change statement in the unit of work that requires a value to be assigned to a row-begin column or transaction-start-ID column in a table, or a row in a system-period temporal table is deleted. Otherwise, the row-begin column is assigned the value of the SYSIBM.TEMPORAL_LOGICAL_TRANSACTION_TIME built-in global variable at the time of the insert or update.
A row-begin column is intended to be used for a system-period temporal table.
A table can have only one column defined as AS ROW BEGIN. If a data type is specified, it must be TIMESTAMP(12) WITHOUT TIME ZONE or TIMESTAMP(12) WITH TIME ZONE. If the column is defined as TIMESTAMP(12) WITH TIME ZONE, the values are stored in UTC, with a time zone of +00:00. If no data type is specified, the column is defined as TIMESTAMP(12) WITHOUT TIME ZONE. A column defined as a row-begin column cannot have a DEFAULT clause, and must be defined as NOT NULL.
A row-begin column is not updatable.
A value for a row-begin column is composed of a TIMESTAMP(9) value that is unique per transaction per data sharing member followed by 000 or three digits that indicate the data sharing member ID.
- AS ROW END
- Specifies that a value for the data type of the column is assigned by Db2 whenever a row is inserted or any column in the row is updated. The value that is assigned for a TIMESTAMP WITHOUT TIME ZONE column is the TIMESTAMP value '9999-12-30-00.00.00.000000000000'. The value that is assigned for a TIMESTAMP WITH TIME ZONE column is the TIMESTAMP value '9999-12-30-00.00.00.000000000000 +00:00'.
A row-end column is required as the second column of a SYSTEM_TIME period.
A table can have only one row-end column. If a data type is not specified, the column is defined as TIMESTAMP(12) WITHOUT TIME ZONE. If a data type is specified, it must be TIMESTAMP(12) WITHOUT TIME ZONE or TIMESTAMP(12) WITH TIME ZONE. If the column is defined as TIMESTAMP WITH TIME ZONE, the values are stored in UTC, with a time zone of +00:00. A row-end column cannot have a DEFAULT clause and must be defined as NOT NULL. A row-end column is not updatable.
- as-generated-expression-clause
- Specifies that values for the column are generated by Db2. The generated value is assigned to the column whenever a row is inserted, or any column in the row is updated.
- DATA CHANGE OPERATION
- Specifies that the database manager generates one of the following values, depending on the specified expression:
- I
- Insert operation.
- U
- Update operation.
- D
- Delete operation.
A table can have only one DATA CHANGE OPERATION column. The column must be defined as CHAR(1). The column cannot have a DEFAULT clause and must not be defined as NOT NULL.
The column is a non-deterministic generated expression column.
Do not specify any of the following clauses for the column:
- CCSID 1200
- CCSID 1208
- FIELDPROC
- special-register
- Specifies the value of the special register. The column is to contain the value of the special register at the time of the data change statement that assigns the value to the column. If multiple rows are inserted or updated with a single SQL statement, the value for the column is the same for all of the rows.
special-register must be one of the following special registers, and the column must use the required data type.
Table 2. Possible special register values for non-deterministic generated expression columns Special register Data type for the column CURRENT CLIENT_ACCTNG VARCHAR(255) CURRENT CLIENT_APPLNAME VARCHAR(255) CURRENT CLIENT_CORR_TOKEN VARCHAR(255) CURRENT CLIENT_USERID VARCHAR(255) CURRENT CLIENT_WRKSTNNAME VARCHAR(255) CURRENT SERVER CHAR(16) CURRENT SQLID VARCHAR(n) where n ≥ 8 SESSION_USER or USER VARCHAR(128) The column cannot have a DEFAULT clause and must not be defined as NOT NULL.
The column is a non-deterministic generated expression column.
Do not specify any of the following clauses for the column:
- CCSID 1200
- CCSID 1208
- FIELDPROC
For more information, see Special registers.
- session-variable
- Specifies the value of a built-in session variable. The fully qualified name of the session variable must be specified. The value of the session variable is obtained from the GETVARIABLE function at the time of the data change operation that assigns the value to the column. If multiple rows are changed with a single SQL statement, the value for the column is the same for all of the rows.
session-variable must be one of the following session variables, and the column must use the required data type.
Table 3. Possible session variable values for non-deterministic generated expression columns Session variable Data type for the column SYSIBM.PACKAGE_NAME VARCHAR(128) SYSIBM.PACKAGE_SCHEMA VARCHAR(128) SYSIBM.PACKAGE_VERSION VARCHAR(122) The column cannot have a DEFAULT clause and must not be defined as NOT NULL.
The column is a non-deterministic generated expression column.
Do not specify any of the following clauses for the column:
- CCSID 1200
- CCSID 1208
- FIELDPROC
For more information, see Built-in session variables.
- AS IDENTITY
- Specifies that the column is an identity column for the table. A table can have only one identity column. AS IDENTITY can be specified only if the data type for the column is an exact numeric type with a scale of zero (SMALLINT, INTEGER, BIGINT, DECIMAL with a scale of zero, or a distinct type based on one of these types).
An identity column is implicitly NOT NULL. An identity column cannot have a WITH DEFAULT clause.
Defining a column AS IDENTITY does not necessarily ensure the uniqueness of the values. To ensure uniqueness of the values, define a unique, single-column index on the identity column.
If IN ACCELERATOR is specified, AS IDENTITY must not be specified.
- START WITH numeric-constant
- Specifies the first value that is generated for the identity column. The value can be any positive or negative value that could be assigned to the column without non-zero digits existing to the right of the decimal point.
If a value is not explicitly specified when the identity column is defined, the default is the MINVALUE for an ascending identity column and the MAXVALUE for a descending identity column. This value is not necessarily the value that would be cycled to after reaching the maximum or minimum value for the identity column. The range used for cycles is defined by MINVALUE and MAXVALUE. MAXVALUE and MINVALUE do not constrain the numeric-constant value. That is, the START WITH clause can be used to start the generation of values outside the range that is used for cycles. However, the next generated value after the specified START WITH value is MINVALUE for an ascending identity column or MAXVALUE for a descending identity column.
- INCREMENT BY numeric-constant
- Specifies the interval between consecutive values of the identity column. The value can be any positive or negative value (including 0) that does not exceed the value of a large integer constant, and could be assigned to the column without any non-zero digits existing to the right of the decimal point.
If this value is negative, the values for the identity column descend. If this value is 0 or positive, the values for the identity column ascend. The default is 1.
- MINVALUE or NO 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
- Specifies that the minimum end point of the range of values for the identity column has not be set. In such a case, the default value for MINVALUE becomes one of the following:
- For an ascending identity column, the value is the START WITH value or 1 if START WITH is not specified.
- For a descending identity column, the value is the minimum value of the data type of the column.
The default is NO MINVALUE.
- MINVALUE numeric-constant
- Specifies the numeric constant that is the minimum value that is generated for this identity column. This value can be any positive or negative value that could be assigned to this column without non-zero digits existing to the right of the decimal point. The value must be less than or equal to the maximum value.
- MAXVALUE or NO 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
- Specifies that the minimum end point of the range of values for the identity column has not be set. In such a case, the default value for MAXVALUE becomes one of the following:
- For an ascending identity column, the value is the maximum value of the data type associated with the column.
- For a descending identity column, the value is the START WITH value -1 if START WITH is not specified.
The default is NO MAXVALUE.
- MAXVALUE numeric-constant
- Specifies the numeric constant that is the maximum value that is generated for this identity column. This value can be any positive or negative value that could be assigned to this column without non-zero digits existing to the right of the decimal point. The value must be greater than or equal to the minimum value.
- CYCLE or NO CYCLE
- Specifies whether this identity column should continue to generate values after reaching either its maximum or minimum value. The default is NO CYCLE.
- NO CYCLE
- Specifies that values will not be generated for the identity column after the maximum or minimum value has been reached.
- CYCLE
- Specifies that values continue to be generated for the identity column after the maximum or minimum value has been reached. If this option is used, after an ascending identity column reaches the maximum value, it generates its minimum value. After a descending identity column reaches its 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 can be generated by Db2 for an identity column. However, if a unique index exists on the identity column and a non-unique value is generated for it, an error occurs.
- CACHE integer-constant or NO CACHE
- Specifies whether to keep some preallocated values in memory. Preallocating and storing values in the cache improves the performance of inserting rows into a table. The default is CACHE 20.
In a non-data sharing environment, if the system is shut down (either normally or through a system failure), all cached sequence values that have not been used in committed statements are lost (that is, they will never be used). The value specified for the CACHE option is the maximum number of sequence values that could be lost when the system is shut down.
In a data sharing environment, you can use the CACHE and NO ORDER options to allow multiple Db2 members to cache sequence values simultaneously.
- NO CACHE
- Specifies that values for the identity column and sequences are not preallocated and stored in the cache, ensuring that values will not be lost in the case of a system failure. In this case, every request for a new value for the identity column or sequence results in synchronous I/O.
In a data sharing environment, use NO CACHE if you need to guarantee that the identity column and sequence values are generated in the order in which they are requested.
- CACHE integer-constant
- Specifies the maximum number of values of the identity column sequence that Db2 can preallocate and keep in memory.
During a Db2 shutdown, all cached identity column values and sequence values that are yet to be assigned will be lost and will not be used. Therefore, the value that is specified for CACHE also represents the maximum number of identity column values and sequence values that will be lost during a Db2 shutdown.
The minimum value is 2.
In a data sharing environment, you can use the CACHE and NO ORDER options to allow multiple Db2 members to cache sequence values simultaneously.
- ORDER or NO ORDER
- Specifies whether the identity column values must be generated in order of request. The default is NO ORDER.
In a non-data sharing environment, there is no guarantee that values are assigned in order across the entire server unless NO CACHE is also specified. ORDER applies only to a single-application process.
In a data sharing environment, if ORDER is specified, NO CACHE is implicitly set, even if CACHE integer-constant is specified.
- NO ORDER
- Specifies that the values do not need to be generated in order of request.
- ORDER
- Specifies that the values are generated in order of request. Specifying ORDER might disable the caching of values. ORDER applies only to a single-application process.
In a data sharing environment, if the CACHE and NO ORDER options are in effect, multiple caches can be active simultaneously, and the requests for identity values from different Db2 members might not result in the assignment of values in strict numeric order. For example, if members DB2A and DB2B are using the identity column, and DB2A gets the cache values 1 to 20 and DB2B gets the cache values 21 to 40, the actual order of values assigned would be 1,21,2 if DB2A requested a value first, then DB2B requested, and then DB2A again requested. Therefore, to guarantee that identity values are generated in strict numeric order among multiple Db2 members using the same identity column, specify the ORDER option.
- FIELDPROC program-name
- Designates program-name as the field procedure exit routine for the column. A field procedure can be specified only for a column with a length attribute that is not greater than 255 bytes. FIELDPROC can only be specified for columns that are a built-in character string or graphic string data types. The column must not be one of the following:
- a LOB column
- a security label column
- a row change timestamp column
- a column with the TIMESTAMP WITH TIME ZONE data type
- a Unicode column in an EBCDIC table
- a column in an accelerator-only table
The field procedure encodes and decodes column values: before a value is inserted in the column, it is passed to the field procedure for encoding. Before a value from the column is used by a program, it is passed to the field procedure for decoding. A field procedure could be used, for example, to alter the sorting sequence of values entered in the column.
The field procedure is also invoked during the processing of the CREATE TABLE statement. When so invoked, the procedure provides Db2 with the column's field description. The field description defines the data characteristics of the encoded values. By contrast, the information you supply for the column in the CREATE TABLE statement defines the data characteristics of the decoded values.
For more information, see:
- constant
- Is a parameter that is passed to the field procedure when it is invoked. A parameter list is optional. The nth parameter specified in the FIELDPROC clause on CREATE TABLE corresponds to the nth parameter of the specified field procedure. The maximum length of the parameter list is 254 bytes, including commas but excluding insignificant blanks and the delimiting parentheses.
If you omit FIELDPROC, the column has no field procedure.
- AS SECURITY LABEL
- Specifies that the column will contain security label values. This also indicates that the table is defined with multilevel security with row level granularity. A table can have only one security label column. A security label column cannot be defined for an accelerator-only table. To define a table with a security label column, the primary authorization ID of the statement must have a valid security label, and the RACF SECLABEL class must be active. In addition, the following conditions are also required:
- The data type of the column must be CHAR(8).
- The subtype of the column must be SBCS.
- The column must be defined with the NOT NULL and WITH DEFAULT clauses.
- The column must be an EBCDIC column.
- The WITH DEFAULT clause must not specify a default value (Db2 determines the default value)
- No field procedures, check constraints, or referential constraints are defined on the column.
- No edit procedure for the table can be defined with row attribute sensitivity.
For information about using multilevel security, see Multilevel security.
- IMPLICITLY HIDDEN
- Specifies that the column is not visible in the result for SQL
statements unless you explicitly refer to the column by name. For example, assuming that the table
T1 includes a column that is defined with the IMPLICITLY HIDDEN clause, the result of a SELECT *
would not include the implicitly hidden column. However, the result of a SELECT statement that
explicitly refers to the name of the implicitly hidden column would include that column in the
result table.
IMPLICITLY HIDDEN must not be specified for a column that is defined as a ROWID, or a distinct type that is based on a ROWID. IMPLICITLY HIDDEN must not be specified for all columns of a table. If IN ACCELERATOR is specified, IMPLICITLY HIDDEN must not be specified.
- INLINE LENGTH integer
Specifies the maximum length of the inline portion of a LOB column value. The inline portion is the portion that is stored in the base table space. INLINE LENGTH cannot be specified if the column is not a LOB column (or a distinct type that is based on a LOB), if the table is not in a universal table space, or if the table is an accelerator-only table.
For BLOB and CLOB columns, integer specifies the maximum number of bytes that are stored in the base table space for the column. integer must be in the range 0–32680 (inclusive) for a BLOB or CLOB column.
For a DBCLOB column, integer specifies the maximum number of double-byte characters that are stored in the table space for the column. integer must be in the range 0–16340 (inclusive) for a DBCLOB column.
If INLINE LENGTH is specified, the value of integer cannot be greater than the maximum length of the LOB column.
If the INLINE LENGTH clause is not specified, the maximum length of the LOB column depends on the following conditions:
- If a distinct type is not used or the distinct type that is used has been created without the INLINE LENGTH attribute, the LOB column will use the value of the LOB INLINE LENGTH parameter on installation panel DSNTIPD as the default inline length when the value of LOB INLINE LENGTH does not exceed the maximum length of the LOB column. If the value of LOB INLINE LENGTH exceeds the maximum length of the LOB column, the maximum length is the inline length of this LOB column.
- If a distinct type that has been created with the INLINE LENGTH attribute is used, the LOB column inherits the inline length from the distinct type.
Regardless of how the length is determined, the inline length of the LOB cannot be greater than its maximum length.
period-definition
PERIOD FOR
- FL 500Defines a period for the table. begin-column-name must not be the same as end-column-name. The data type, length, precision, and scale for begin-column-name must be the same as for end-column-name.
If IN ACCELERATOR is specified, PERIOD must not be specified.
- 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. A table can have only one SYSTEM_TIME period. begin-column-name must be defined as AS ROW BEGIN and end-column-name must be defined as AS ROW END.
- 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. A table can have only one BUSINESS_TIME period. begin-column-name and end-column-name must be defined as DATE or TIMESTAMP(6) WITHOUT TIME ZONE, and the columns must be defined as NOT NULL. begin-column-name and end-column-name must not identify a column that is defined with a GENERATED clause.
FL 500An implicit check constraint is generated to ensure the relationship of the value of end-column-name to the value of begin-column-name as follows:
- For an inclusive-exclusive BUSINESS_TIME period, the value of end-column-name is greater than the value of begin-column-name.
- For an inclusive-inclusive BUSINESS_TIME period, the value of end-column-name is greater than or equal to the value of begin-column-name.
FL 500The name of the implicitly created check constraint is DB2_GENERATED_CHECK_CONSTRAINT_FOR_BUSINESS_TIME, and that name must not be defined as the name of an existing check constraint.
- begin-column-name
- Identifies the column that records the beginning of the period of time in which a row is valid. The name must identify a column that exists in the table and must not be the same as a column that is used in the definition of another period for the table. begin-column-name must not be the same as end-column-name. The data type and precision for begin-column-name must be the same as for end-column-name.
For a SYSTEM_TIME period, begin-column-name must be defined as AS ROW BEGIN.
For a BUSINESS_TIME period, the column must not be defined with a GENERATED clause.
- end-column-name
- Identifies the column that records the end of the period of time in which a row is valid. In the history table that is associated with a system-period temporal table, the history table column that corresponds to end-column-name in the system-period temporal table is set to reflect the deletion of the row. The name must identify a column that exists in the table and must not be the same as a column that is used in the definition of another period for the table.
For a SYSTEM_TIME period, end-column-name must be defined as AS ROW END.
For a BUSINESS_TIME period, the column must not be defined with a GENERATED clause.
EXCLUSIVE
FL 500Specifies that the value of the end column is not included in the period. The BUSINESS_TIME period is defined as inclusive-exclusive.
INCLUSIVE
FL 500Specifies that the value of the end column is included in the period. The BUSINESS_TIME period is defined as inclusive-inclusive.
unique-constraint
- CONSTRAINT constraint-name
- Names the constraint. If a constraint name is not specified, a unique constraint name is generated. If a name is specified, it must be different from the names of any referential, check, primary key, or unique key constraints previously specified on the table.
- PRIMARY KEY(column-name,...)
- Defines a primary key composed of the identified columns. The clause must not be specified more than one time and the same column must not be identified more than one time. The identified columns must be defined as NOT NULL. Each column-name must be an unqualified name that identifies a column of the table except for the following types of columns:
- a LOB column
- a ROWID column
a distinct type column that is based on a LOB or ROWID data type
- an XML column
- a row change timestamp column
- a column in an accelerator-only table
FL 500All character and graphic string columns in the key must use the same encoding scheme.
The number of identified columns must not exceed 64. In addition, the sum of the length attributes of the columns must not be greater than 2000 - n - 2m - 3d, where m is the number of varying-length columns and d is the number of DECFLOAT columns in the key.
The table is marked as unavailable until its primary index is explicitly created unless the table space is explicitly created and the CREATE TABLE statement is processed by the schema processor, or the table space is implicitly created. In that case, Db2 implicitly creates an index to enforce the uniqueness of the primary key and the table definition is considered complete. (For more information about implicitly created indexes, see Implicitly created indexes.)
BUSINESS_TIME WITHOUT OVERLAPS can be specified as the last item in the list. If BUSINESS_TIME WITHOUT OVERLAPS is specified, the list must include at least one column-name or key-expression. When WITHOUT OVERLAPS is specified, the values for the rest of the specified keys are unique with respect to the 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. The specification of BUSINESS_TIME WITHOUT OVERLAPS adds the following 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
- UNIQUE(column-name,…)
- Defines a unique key composed of the identified columns. Each column-name must be an unqualified name that identifies a column of the table. Each identified column must be defined as NOT NULL. The same column must not be identified more than one time. The following types of columns cannot be identified:
- a LOB column
- a ROWID column
a distinct type column that is based on a LOB or ROWID data type
- an XML column
- a row change timestamp column
- a column in an accelerator-only table
The number of identified columns must not exceed 64. In addition, the sum of the length attributes of the columns must not be greater than 2000 - n - 2m - 3d, where m is the number of varying-length columns and d is the number of DECFLOAT columns in the key.
All character and graphic string columns in the key must use the same encoding scheme.
A unique key is a duplicate if it is the same as the primary key or a previously defined unique key. The specification of a duplicate unique key is ignored with a warning.
The table is marked as unavailable until all the required indexes are explicitly created unless the table space is explicitly created and the CREATE TABLE statement is processed by the schema processor, or the table space is implicitly created. In these cases, Db2 implicitly creates the indexes that are required for the unique keys and the table definition is considered complete. (For more information about implicitly created indexes, see Implicitly created indexes.)
BUSINESS_TIME WITHOUT OVERLAPS can be specified as the last item in the list. If BUSINESS_TIME WITHOUT OVERLAPS is specified, the list must include at least one column-name or key-expression. When WITHOUT OVERLAPS is specified, the values for the rest of the specified keys are unique with respect to the 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. The specification of BUSINESS_TIME WITHOUT OVERLAPS adds the following 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
referential-constraint
- CONSTRAINT constraint-name
- Names the referential constraint. If a constraint name is not specified, a unique constraint name is generated. If a name is specified, it must be different from the names of any referential, check, primary key, or unique key constraints previously specified on the table.
- FOREIGN KEY (column-name,...) references-clause
- Each
specification of the FOREIGN KEY clause defines a referential constraint.
The table being created is the child table for the referential constraint. The foreign key of the referential constraint is composed of the identified columns, and the columns of the BUSINESS_TIME period if the clause PERIOD BUSINESS_TIME is specified. Each column-name must be an unqualified name that identifies a column of the table. The same column must not be identified more than one time.
If PERIOD BUSINESS_TIME is specified, the columns of the BUSINESS_TIME period must not be specified as part of the constraint.
The column cannot be any of the following types of columns:
- a LOB column
- a ROWID column
- a DECFLOAT column
- an XML column
- a row change timestamp column
- a security label column
- a column in an accelerator-only table
The number of identified columns, and the columns of the BUSINESS_TIME period if the clause PERIOD BUSINESS_TIME is specified, must not exceed 64, including columns of the BUSINESS_TIME period if PERIOD BUSINESS_TIME is specified. The sum of the column length attributes must not exceed 255 minus the number of columns that allow null values. The referential constraint is a duplicate if the FOREIGN KEY and parent table are the same as the FOREIGN KEY and parent table of a previously defined referential constraint. The specification of a duplicate referential constraint is ignored with a warning. An exception is that a duplicate referential constraint is not allowed if the definition of the constraint includes the PERIOD BUSINESS_TIME clause.
- REFERENCES parent-table-name (column-name,...)
- The table name that is specified after REFERENCES is the parent table for the referential constraint. parent-table-name must identify a table that exists at the current server1. The table name must not identify one of the following tables:
- A catalog table
A directory table
- A declared global temporary table
- A history table
- An archive table
T2 must have a unique index. The privilege set must include the ALTER or REFERENCES privilege on the parent table, or the REFERENCES privilege on the columns of the nominated parent key, including the columns of the BUSINESS_TIME period if the PERIOD BUSINESS_TIME clause is specified..
The parent key of the referential constraint is composed of the identified columns, or columns of the BUSINESS_TIME period if PERIOD BUSINESS_TIME is specified. Each column-name must be an unqualified name that identifies a column of T2. The same column must not be identified more than one time. If PERIOD BUSINESS_TIME is specified, the columns of the BUSINESS_TIME period must not be specified as part of the constraint. The identified column cannot be any of the following types of columns:- a LOB column
- a ROWID column
- a DECFLOAT column
- an XML column
- a row change timestamp column
- a security label column
The list of column names in the parent key must match the list of column names in a primary key or unique key in the parent table T2. The column names must be specified in the same order as in the primary key or unique key. If PERIOD BUSINESS_TIME was specified for the primary key or unique key of the parent table T2, then PERIOD BUSINESS_TIME must also be specified for the foreign key clause for T1. If any of the referenced columns in T2 has a non-numeric data type, T2 and T1 must use the same encoding scheme, unless T2 is a Unicode table, and T1 is an EBCDIC table with Unicode key columns. In that case, for each character or graphic string column in T1, the CCSID must be the same as the corresponding column in T2.
If a list of column names is not specified, T2 must have a primary key. Omission of a list of column names is an implicit specification of the columns of the primary key for T2.
The specified foreign key must have the same number of columns as the parent key of T2 and, except for their names, default values, null attributes and check constraints, the description of the nth column of the foreign key must be identical to the description of the nth column of the nominated parent key. If the foreign key includes a column defined as a distinct type, the corresponding column of the nominated parent key must be the same distinct type. If a column of the foreign key has a field procedure, the corresponding column of the nominated parent key must have the same field procedure and an identical field description. A field description is a description of the encoded value as it is stored in the database for a column that has been defined to have an associated field procedure.
If PERIOD BUSINESS_TIME is specified in the FOREIGN KEY clause, then PERIOD BUSINESS_TIME must also be specified in the REFERENCES clause. If PERIOD BUSINESS_TIME is not specified in the FOREIGN KEY clause, then PERIOD BUSINESS_TIME must also not be specified in the REFERENCES clause.
If the PERIOD BUSINESS_TIME clause is specified, T2 must not be defined as part of a referential cycle. T1 and T2 must not be the same table, and T1 must not be a descendent, directly or indirectly, of another table that is a descendent of T2.
The table space that contains T1 must be available to Db2. If T1 is populated, its table space is placed in a check pending status. A table in a segmented table space is populated if the table is not empty. A table in a table space that is not segmented is considered populated if the table space has ever contained any records.
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. A description of the referential constraint is recorded in the catalog.
PERIOD BUSINESS_TIME
Specifies that the BUSINESS_TIME period is considered part of the referential constraint. When PERIOD BUSINESS_TIME is specified, the values for the rest of the specified columns are unique with respect to the specified point of time.
PERIOD BUSINESS_TIME can be specified as the last key expression. If PERIOD BUSINESS_TIME is not the last key expression, an error is returned. If PERIOD BUSINESS_TIME is specified, the columns of the BUSINESS_TIME period must not be specified as part of the constraint.
When PERIOD BUSINESS_TIME is specified, the following columns are implicitly added to the end of the constraint:- The end column of the BUSINESS_TIME period.
- The start column of the BUSINESS_TIME period.
The PERIOD BUSINESS_TIME clause specifies that there must not be a row in the child table for which the period of time represented by the BUSINESS_TIME period values for that row is not contained in the BUSINESS_TIME period of a corresponding row in the parent table. Furthermore, it is not necessary that there be exactly one corresponding row in the parent table where the BUSINESS_TIME period contains the BUSINESS_TIME period of the child row. As long as the BUSINESS_TIME period of a row in the child table is contained in the union of the BUSINESS_TIME periods of two or more contiguous matching rows in the parent table, the referential constraint is considered satisfied.
When the FOREIGN KEY clause specifies the PERIOD BUSINESS_TIME clause, the following conditions apply:- The corresponding REFERENCES clause must also specify the PERIOD BUSINESS_TIME clause.
- A unique index with the BUSINESS_TIME WITHOUT OVERLAPS clause must be defined on the table. The table is marked as unavailable until the index is created.
- A unique index must be defined on the parent table with the BUSINESS_TIME WITHOUT OVERLAPS clause.
ON DELETE RESTRICT must be, implicitly or explicitly, specified when PERIOD BUSINESS_TIME is also specified.
- ON DELETE
- The delete rule of the relationship is determined by the ON DELETE clause. For more on the concepts used here, see Referential constraints.
SET NULL must not be specified unless some column of the foreign key allows null values. The default value for the rule depends on the value of the CURRENT RULES special register when the CREATE TABLE statement is processed. If the value of the register is 'Db2', the delete rule defaults to RESTRICT; if the value is 'STD', the delete rule defaults to NO ACTION.
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. Then:
- 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.
Let T3 denote a table identified in another FOREIGN KEY clause (if any) of the CREATE TABLE statement. The delete rules of the relationships involving T2 and T3 must be the same and must not be SET NULL if:
- T2 and T3 are the same table.
- T2 is a descendent of T3 and the deletion of rows from T3 cascades to T2.
- T2 and T3 are both descendents of the same table and the deletion of rows from that table cascades to both T2 and T3.
- ENFORCED or NOT ENFORCED
- Indicates whether or not the referential constraint is enforced by Db2 during normal operations, such as insert, update, or delete.
- ENFORCED
- Specifies that the referential constraint is enforced by the Db2 during normal operations (such as insert, update, or delete) and that it is guaranteed to be correct. This is the default.
- NOT ENFORCED
- Specifies that the referential constraint is not enforced by Db2 during normal operations, such as insert, update, or delete. This option should only be used when the data that is stored in the table is verified to conform to the constraint by some other method than relying on the database manager.
- ENABLE QUERY OPTIMIZATION
- Specifies that the constraint can be used for query optimization. Db2 uses the information in query optimization using materialized query tables with the assumption that the constraint is correct. This is the default.
check-constraint
- CONSTRAINT constraint-name
- Names the check constraint.
The constraint name must be different from the names of any referential,
check, primary key, or unique key constraints previously specified
on the table.
If constraint-name is not specified, a unique constraint name is derived from the name of the first column in the check-condition specified in the definition of the check constraint.
- CHECK (check-condition)
- Defines
a check constraint. At any time, the check-condition must
be true or unknown for every row of the table. A check-condition can
evaluate to unknown if a column that is an operand of the predicate
is null. A check-condition that evaluates
to unknown does not violate the check constraint. A check-condition is
a search condition, with the following restrictions:
- It can refer only to columns of table table-name.
- The columns cannot be the following types of columns:
- LOB columns
- ROWID columns
- DECFLOAT columns
- distinct type columns that are based on LOB, ROWID, and DECFLOAT data types
- XML columns
- security label columns
- columns in an accelerator-only table
- It can be up to 3800 bytes long, not including redundant blanks.
- It must not contain any of the following:
- Subselects
- Built-in or user-defined functions
- CAST specifications
- Cast functions other than those created when the distinct type was created
- Host variables
- Parameter markers
- Special registers
- Global variables
- Columns that include a field procedure
- CASE expressions
- ROW CHANGE expressions
Row-value expressions
- DISTINCT predicates
- GX constants (hexadecimal graphic string constants)
- Sequence references
- OLAP specifications
It must not result in CCSID conversion.
- If a check-condition refers to a LOB column (including a distinct type that is based on a LOB), the reference must occur within a LIKE predicate.
- The AND and OR logical operators can be used between predicates.
The NOT logical operator cannot be used with the following predicates: NOT BETWEEN, NOT IN, NOT LIKE, or IS NOT NULL.
- The first operand of every predicate must be the column name of a column in the table.
- The second operand in the check-condition must be either a constant or
the name of a column in the table.
- If the second operand of a predicate is a constant, and if the constant is:
- A floating-point number, then the column data type must be floating point.
- A decimal number, then the column data type must be either floating point or decimal.
- An integer number, then the column data type must not be a small integer.
- A small integer number, then the column data type must be small integer.
- A decimal constant, then its precision must not be larger than the precision of the column.
- If the second operand of a predicate is a column, then both columns of the predicate must have:
- The same data type.
- Identical descriptions with the exception that the specification of the NOT NULL and DEFAULT clauses for the columns can be different, and that string columns with the same data type can have different length attributes.
- If the second operand of a predicate is a constant, and if the constant is:
LIKE
- table-name or view-name
- Specifies
that the columns of the table have exactly the same name and description
as the columns of the identified table or view.
The name that is specified after LIKE must identify a table or view that exists at the current server or a declared temporary table. A view cannot contain columns of length 0.
LIKE must not reference an accelerator-only table or be used with the IN ACCELERATOR clause.
The privilege set must implicitly or explicitly include the SELECT privilege on the identified table or view. If the identified table or view contains a column with a distinct type, the USAGE privilege on the distinct type is also needed. An identified table must not be an auxiliary table or a clone table. An identified view must not include a column that is an explicitly defined ROWID column (including a distinct type that is based on a ROWID), an identity column, or a row change timestamp column.
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) or view. 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 includes all attributes of the n columns as they are described in SYSCOLUMNS with the following exceptions:
- When a table is identified in the LIKE clause and a column in the table has a field procedure, the corresponding column of the new table has the same field procedure and the field description. However, the field procedure is not invoked during the execution of the CREATE TABLE statement. When a view is identified in the LIKE clause, none of the columns of the new table will have a field procedure. This is true even in the case that a column of a base table underlying the view has a field procedure defined.
- When a table is identified in the LIKE clause and a column in the table is an identity column, the corresponding column of the new table inherits only the data type of the identity column; none of the identity attributes of the column are inherited unless the INCLUDING IDENTITY clause is specified.
- When a table is identified in the LIKE clause and a column in the table is a security label column, the corresponding column of the new table inherits only the data type of the security label column; none of the security label attributes of the column are inherited.
- When a table that contains a ROWID column is identified in the LIKE clause, the corresponding column of the new table inherits the ROWID column, regardless of whether the column has the IMPLICITLY HIDDEN attribute.
- When a table is identified in the LIKE clause and the table contains a row change timestamp column, a transaction-start-ID column, a row-begin column, or a row-end column, the corresponding column of the new table inherits only the data type of the original column. The new column is not considered a generated column.
- When a table is identified in the LIKE clause and a column in the table is a generated expression column, the corresponding column of the new table inherits only the data type of the original column. The new column is not considered a generated column.
- When a table is identified in the LIKE clause and the table contains an inline LOB column, the corresponding columns of the new table will inherit the inline attribute if the table is in an universal table space. Otherwise, the inline attribute of the table identified in the LIKE clause is ignored.
- When a view is identified in the LIKE clause, the default value
that is associated with the corresponding column of the new table
depends on the column of the underlying base table for the view. If
the column of the base table does not have a default, the new column
does not have a default. If the column of the base table has a default,
the default of the new column is:
- Null if the column of the underlying base table allows nulls.
- The default for the data type of the underlying base table if the underlying base table does not allow nulls.
- When a table that uses table-controlled partitioning is identified in the LIKE clause, the new table does not inherit partitioning scheme of that table. You can add these partition boundaries by specifying ALTER TABLE with the ADD PARTITION BY RANGE clause.
- The CCSID of the column is determined by the implicit or explicit
CCSID clause. For more information, see the CCSID clause.
An exception is a Unicode column in an EBCDIC table, which inherits the CCSID of the column in the existing table.
When a table is identified in the LIKE clause and the table includes a period definition, the new table does not inherit the period. definition.
- When the table that is identified in the LIKE clause is a system-period temporal table, the new table is not a system-period temporal table.
- When the table that is identified in the LIKE clause has row access controls or column access controls activated, the new table does not inherit the row access controls or the column access controls.
The implicit definition does not include any other attributes of the identified table or view. For example, the new table does not have a primary key or foreign key. 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.
copy-options
- copy-options
- Specifies whether identity column attributes, row change timestamp
attributes, and column defaults are inherited from the definition
of the source of the result table.
- EXCLUDING IDENTITY COLUMN ATTRIBUTES or INCLUDING IDENTITY COLUMN ATTRIBUTES
- Specifies whether identity column attributes are inherited from
the definition of the source of the result table.
- EXCLUDING IDENTITY COLUMN ATTRIBUTES
- Specifies that identity column attributes are not inherited from the definition of the source of the result table. This is the default.
- INCLUDING IDENTITY COLUMN ATTRIBUTES
- Specifies that, if available,
identity column attributes (such as START WITH, INCREMENT BY, and
CACHE values) are inherited from the definition of the source table.
These attributes can be inherited if the element of the corresponding
column in the table, view, or fullselect is
the name of a column of a table or the name of a column of a view
that directly or indirectly maps to the column name of a base table
with the identity attribute. In other cases, the columns of the new
temporary table do not inherit the identity attributes. The columns
of the new table do not inherit the identity attributes in the following
cases:
- The select list of the fullselect includes multiple instances of an identity column name (that is, selecting the same column more than one time).
- 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.
- EXCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES or INCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES
- Specifies
whether row change timestamp column attributes are inherited from
the definition of the source of the result table.
- EXCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES
- Specifies that row change timestamp column attributes are not inherited from the source result table definition. This is the default.
- INCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES
- Specifies that, if available, row change timestamp column attributes
are inherited from the definition of the source table. These attributes
can be inherited if the element of the corresponding column in the
table, view, or fullselect is the name of
a column of a table or the name of a column of a view that directly
or indirectly maps to the column name of a base table defined as a
row change timestamp column. In other cases, the columns of the new
temporary table do not inherit the row change timestamp column attributes.
The columns of the new table do not inherit the row change timestamp
attributes in the following cases:
- The select list of the fullselect includes multiple instances of a row change timestamp column name (that is, selecting the same column more than one time).
- The select list of the fullselect includes multiple row change timestamp column names (that is, it involves a join).
- The row change timestamp column is included in an expression in the select list.
- The fullselect includes a set operation (such as union).
- EXCLUDING COLUMN DEFAULTS, INCLUDING COLUMN DEFAULTS, or USING TYPE DEFAULTS
- Specifies whether column defaults are inherited from the source
result table definition. EXCLUDING COLUMN DEFAULTS,
INCLUDING COLUMN DEFAULTS, and USING TYPE DEFAULTS must not be specified
if the LIKE clause is specified.
- EXCLUDING COLUMN DEFAULTS
- Specifies that the column defaults are not inherited from the definition of the source table. The default values of the column of the new table are either null or there are no default values. If the column can be null, the default is the null value. If the column cannot be null, there is no default value, and an error occurs if a value is not provided for a column on an insert or update operation, or LOAD for the new table.
- INCLUDING COLUMN DEFAULTS
- Specifies that column defaults for each updatable column of the definition of the source table are inherited. Columns that are not updatable do not have a default defined in the corresponding column of the created table. The existence of an INSTEAD OF trigger for a view does not affect the inheritance of default values.
- USING TYPE DEFAULTS
- Specifies
that the default values for the table depend on data type of the columns
that result from fullselect, as follows:
- Data type
- Default value
- Numeric
- 0
- Fixed-length character string
- Blanks
- Fixed-length graphic string
- Blanks
- Fixed-length binary string
- Hexadecimal zeros
- Varying-length string
- A string of length 0
- Fixed-length char or fixed-length graphic
- A string of blanks
- Fixed-length binary
- Hexadecimal zeros
- Date
- CURRENT DATE
- Time
- CURRENT TIME
- Timestamp(integer) without time zone
- CURRENT TIMESTAMP(p) WITHOUT TIME ZONE where p is the corresponding timestamp precision.
- Timestamp(integer) with time zone
- CURRENT TIMESTAMP(p) WITH TIME ZONE where p is the corresponding timestamp precision.
as-result-table
- as-result-table
- Specifies that the column definitions of the table are based
on the result 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 or an unnamed column. An unnamed column is a column derived from a constant, function, expression, or set operation that is not named using the AS clause.
- AS (fullselect)
- Specifies that the table definition is based on the column definitions from the result of the
fullselect. The use of AS (fullselect) is an
implicit definition of n columns for the table, where n is the
number of columns that would result from the fullselect. The columns of
the new table are defined by the columns that result from the fullselect.
Every select list element must have a unique name. The AS clause can be used in the
select-clause to provide unique names.
The implicit definition includes the column name, data type, length, precision, scale, and nullability characteristic of each of the result columns of fullselect. The length of each column must not be 0. Other column attributes, such as DEFAULT and IDENTITY, are not inherited from the fullselect. A column of the new table that corresponds to an implicitly hidden column of a base table referenced in the fullselect is not considered hidden in the new table. The generated column attributes are not inherited from the fullselect. That is, a new column of the table is not considered as a generated column. A FIELDPROC is inherited for a column if the corresponding select item of the fullselect is a column that can be mapped to a column of a base table or a view. The new table contains a security label column if only one table in the fullselect contains a security label column and the primary authorization ID of the statement has a valid security label.
The outermost SELECT list of the fullselect must not reference data that is encoded with different encoding schemes. An exception is that the outermost SELECT list can contain a mixture of EBCDIC and Unicode columns. In this case, the new table is an EBCDIC table with one or more Unicode columns.
The implicit definition does not include any other attributes of the identified table or view. For example, the new table does not have a primary key or foreign key. 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.
If IN ACCELERATOR is specified, AS (fullselect) cannot be specified.
The owner of the table being created must have the SELECT privilege on the tables or views referenced in the fullselect, or the privilege set must include SYSADM or DBADM authority for the database in which the tables of the fullselect reside. Having SELECT privilege means that the owner has at least one of the following authorizations.
- Ownership of the tables or views referenced in the fullselect
- The SELECT privilege on the tables and views referenced in the fullselect
- SYSADM authority
- DBADM authority for the database in which the tables of the fullselect reside
Additional privileges might be necessary for accessing other objects that are referenced in the fullselect.
The fullselect must not:
- Result in a column having a ROWID, BLOB, CLOB, DBCLOB, or XML data type or a distinct type based on these data types.
- Include multiple security label columns.
- Include a PREVIOUS VALUE or a NEXT VALUE expression.
- Refer to host variables or include parameter markers.
- Include an SQL data change statement in the FROM clause.
In the outermost SELECT, reference a combination of ASCII and EBCDIC data, or a combination of ASCII and Unicode data.
- Result in a column that is an array.
- Reference a remote object.
- Reference an accelerator-only table.
- WITH NO DATA
- Specifies that the query is used only to define the attributes of the new table.
The table is not populated using the results of the fullselect and the
REFRESH TABLE statement cannot be used.
If the tables that are specified in the fullselect use row access controls or column access controls, the row access controls and the column access controls are not defined for the new table.
materialized-query-definition
- materialized-query-definition
- Specifies that the column definitions
of the materialized query table are based on the result of a fullselect.
If materialized-query-table-options are
specified, the REFRESH TABLE statement can be used to populate the
table with the results 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 or an unnamed column. An unnamed column is a column derived from a constant, function, expression, or set operation that is not named using the AS clause of the select list.
- AS (fullselect)
- Specifies that the table definition is based on the column definitions from the result of the
fullselect. The use of AS (fullselect) is an
implicit definition of n columns for the table, where n is the
number of columns that would result from the fullselect. The columns of
the new table are defined by the columns that result from the fullselect.
Every select list element must have a unique name. The AS clause can be used in the
select-clause to provide unique names.
The implicit definition includes the column name, data type, length, precision, scale, and nullability characteristic of each of the result columns of fullselect. The length of each column must not be a 0. Other column attributes, such as DEFAULT, IDENTITY, and unique constraints, are not inherited from the fullselect. A column of the new table that corresponds to an implicitly hidden column of a base table referenced in the fullselect is not considered hidden in the new table. The generated column attributes are not inherited from the fullselect. That is, the new column of the materialized query table is not considered as a generated column. A FIELDPROC is inherited for a column if the corresponding select item of the fullselect is a column that can be directly mapped to a column of a base table or a view in the FROM clause of the fullselect. The materialized query table contains a security label column if only one table in the fullselect contains a security label column and the primary authorization ID of the statement has a valid security label.
The outermost SELECT list of the fullselect can include result columns that are defined as EBCDIC columns and result columns that are defined as Unicode columns. In this case, the materialized query table is an EBCDIC table with one or more Unicode columns.
- Authorization for creating materialized query tables
- The owner of the table being created must have the SELECT privilege on the tables or views
referenced in the fullselect, or the privilege set must include SYSADM or DBADM authority for the
database in which the tables of the fullselect reside. Having SELECT privilege means that the owner
has at least one of the following authorizations:
- Ownership of the tables or views referenced in the fullselect
- The SELECT privilege on the tables and views referenced in the fullselect
- SYSADM authority
- DBADM authority for the database in which the tables of the fullselect reside
Additional privileges might be necessary for accessing other objects that are referenced in the fullselect.
The rules for establishing the qualifiers for names used in the fullselect are the same as the rules used to establish the qualifiers for table-name.
The following restrictions apply when creating materialized query tables. When fullselect does not satisfy the restrictions, an error occurs:
- The length of each result column of the fullselect must not be 0.
- The fullselect cannot contain a column of a LOB or XML data type.
- No more than one table in the fullselect can contain a security label column.
- The fullselect must not contain a period specification.
The outermost SELECT list must not reference data that is encoded with a combination of ASCII and EBCDIC CCSID sets, or a combination of ASCII and Unicode CCSID sets.
- The object that is specified in the FROM clause of the fullselect cannot be a view with columns of length 0.
The fullselect cannot contain a reference to a created global temporary table, a declared global temporary table, an accelerator-only table, a directory table, or another materialized query table.
- If IN ACCELERATOR is specified, materialized-query-definition cannot be specified.
- The fullselect cannot directly or indirectly reference a base table that has been activated for the row or column access control or a base table for which a row permission or a column mask has been defined.
- The fullselect must not refer to host variables or include parameter markers.
- The fullselect must not refer to global variables.
The fullselect must not include the following built-in functions: LISTAGG, PERCENTILE_CONT, or PERCENTILE_DISC.
Additional restrictions when ENABLE QUERY OPTIMIZATION is in effect:- The fullselect must be a subselect.
- The subselect cannot include the following:
- A special register
- A scalar fullselect
- A row change timestamp column
- A ROW CHANGE expression
- An expression for which implicit time zone values apply (for example, cast a timestamp to a timestamp with time zone)
- The RAND built-in function
- The RID built-in function
- A user-defined scalar or table function that is not deterministic or that has external actions
- Any predicates that include a subquery
A row-value-expression in a predicate
- A join using the INNER JOIN syntax, or an outer join
- A lateral correlation
- A nested table expression or view that requires temporary materialization
- A direct or indirect reference to a table that uses activated row or column access controls, or a table for which row or column access controls have been defined.
- A FETCH FIRST clause
- A reference to a global variable
- A collection-derived table (UNNEST)
- A GROUPING SETS or super-groups clause
- If a table with a security label is referenced, the security label column must be referenced in the outer select list of the subselect.
- If the subselect references a view, the fullselect in the view definition must satisfy all other restrictions.
- refreshable-table-options
- Specifies the options for a refreshable materialized query table. The
ORDER BY clause is allowed, but it is used only by REFRESH. The ORDER
BY clause can improve the locality of reference of data in the materialized
query table.
- DATA INITIALLY DEFERRED
- Specifies that the data is not inserted into the materialized query table when it is created. Use the REFRESH TABLE statement to populate the materialized query table, or use the INSERT statement to insert data into a user-maintained materialized query table.
- REFRESH DEFERRED
- Specifies that the data in the table can be refreshed at any time using the REFRESH TABLE statement. The data in the table only reflects the result of the query as a snapshot at the time when the REFRESH TABLE statement is processed or when it was last updated for a user-maintained materialized query table.
- MAINTAINED BY SYSTEM or MAINTAINED BY USER
- Specifies how the data in the materialized query table is maintained.
- MAINTAINED BY SYSTEM
- Specifies that the materialized query table is maintained by the system. Only the REFRESH statement is allowed on the table. This is the default.
- MAINTAINED BY USER
- Specifies that the materialized query table is maintained by the user, who can use the LOAD utility, an SQL data change statement, a SELECT from data change statement, or REFRESH TABLE SQL statements on the table.
- ENABLE QUERY OPTIMIZATION or DISABLE QUERY OPTIMIZATION
- Specifies whether this materialized query table can be used for
optimization.
- ENABLE QUERY OPTIMIZATION
- Specifies that the materialized query table can be used for query
optimization. If the fullselect specified does not satisfy the restrictions
for query optimization, an error occurs.
ENABLE QUERY OPTIMIZATION is the default.
The fullselect must not contain a period specification.
- DISABLE QUERY OPTIMIZATION
- Specifies that the materialized query table cannot be used for query optimization. The table can still be queried directly.
IN
- IN database-name.table-space-name or IN DATABASE database-name
- Identifies the database and table space in which the table is created. Both forms are optional.
If you specify database-name and table-space-name, the database must be described in the catalog on the current server. The database must not be DSNDB06 or a work file database. The table space must belong to the database that you specify.
If you specify database-name but not table-space-name, a table space is implicitly created in database-name. The name of the table space is derived from the name of the table. The buffer pool that is used is the default buffer pool for user data that is specified on installation panel DSNTIP1.
If you specify a table space but not a database, the database that contains the table space is used.
If you do not specify the IN clause, a database is implicitly created with the name DSNxxxxx, where xxxxx is a five-digit number. A table space is also implicitly created.
If you specify table-space-name, the table space cannot be one of the following table spaces:
- A table space that was created implicitly
- A partitioned table space that already contains a table
- A LOB table space
- An XML table space
FL 504 A non-UTS table space
If you specify a partitioned table space, you cannot load or use the table until its partitioned scheme is created.
You cannot specify a name in the format of an implicitly created database name, which is DSNxxxxx, where xxxxx is a five-digit number..
If you specify table-space-name, but you do not specify database-name, or you do not specify the IN clause, users who have the authority to create table spaces or tables in database DSNDB04 have authority to create tables and table spaces in the implicitly created database.
If you do not specify table-space-name, the privilege set must have: SYSADM or SYSCTRL authority; DBADM, DBCTRL, or DBMAINT authority for the database; or the CREATETS privilege for the database. You must also have the USE privilege for the default buffer pool in the database and default storage group.
For implicitly created table spaces, Db2 selects the buffer pool as described in Implicitly defined table spaces.
- IN ACCELERATOR accelerator-name
- Specifies that the table is an accelerator-only table. accelerator-name identifies the accelerator in which the table will be defined.
FL 509 You can specify an alias (logical name) for accelerator-name. For more information, see Using an alias for an accelerator. To create a high availability accelerator-only table, specify a location alias that represents multiple accelerators to define the table in all accelerators that are associated with the location alias.
If you specify an accelerator-only table, the table and the data of the table exists only in the accelerator, not in Db2. However, the table and column definition of the accelerator-only table are contained in Db2 catalog tables.
partitioning-clause block
- PARTITION BY SIZE or PARTITION BY RANGE
- Specifies the partitioning scheme for the table. For more information, see Partitioning data in Db2 tables.
- PARTITION BY SIZE
- Specifies that the table is created in a partition-by-growth table space. If the IN clause specifies a table-space-name, it must identify a partition-by-growth table space. If the IN clause does not specify an existing table space name and the PARTITION BY clause is not specified, PARTITION BY SIZE is the default.
If IN ACCELERATOR is specified, PARTITION BY SIZE must not be specified.
- EVERY integer G
- Specifies that the table is to be partitioned by growth, every integer G bytes. integer must not be greater than 256. If the IN clause identifies a table space, integer must be the same as the DSSIZE value that is in effect for the table space that will contain the table.
integer can be separated from G by 0 or more spaces.
- PARTITION BY RANGE
- Specifies the range partitioning scheme for the table (the columns that are used to partition the data). When this clause is specified, the table space is complete, and it is not necessary to create a partitioned index on the table. If this clause is used, the ENDING AT clause cannot be used on a subsequent CREATE INDEX statement for this table.
PARTITION BY RANGE must not be specified for a table that is created in a partition-by-growth table space. If IN ACCELERATOR is specified, PARTITION BY RANGE must not be specified.
- partition-expression
- Specifies the key data over which the range is defined to determine the target data partition of the data.
- column-name
- Specifies the columns of the key. Each column-name must identify a column of the table. Do not specify more than 64 columns or the same column more than one time. The sum of length attributes of the columns must not be greater than 255 - n, where n is the number of columns that can contain null values. Do not specify a qualified column name.
A timestamp with time zone column (or a column with a distinct type that is based on the timestamp with time zone data type) can only be specified as the last column in a partitioning key.
Do not specify a column for column-name if the column is defined as follows:
- a LOB column (or a column with a distinct type that is based on a LOB data type)
- a BINARY column (or a column with a distinct type that is based on a BINARY data type)
- a VARBINARY column (or a column with a distinct type that is based on a VARBINARY data type)
- a DECFLOAT column (or a column with a distinct type that is based on a DECFLOAT data type)
- an XML column
All character and graphic string columns in the key must be defined with the same encoding scheme.
- NULLS LAST
- Specifies that null values are treated as positive infinity for purposes of comparison.
- ASC
- Puts the entries in ascending order by the column. ASC is the default.
- DESC
- Puts the entries in descending order by the column.
- 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 integer
- integer is the physical number of a partition in the table space. A PARTITION clause must be specified for every partition of the table space. In this context, highest means highest in the sorting sequences of the columns. In a column defined as ascending (ASC), highest and lowest have their usual meanings. In a column defined as descending (DESC), the lowest actual value is highest in the sorting sequence.
- ENDING AT (constant, MAXVALUE, or MINVALUE, ...)
- Defines the limit key for a partition boundary. Specify at least one value (constant, MAXVALUE, or MINVALUE) after ENDING AT in each PARTITION clause. You can use as many values as there are columns in the key. The concatenation of all values is the highest value of the key for ascending and the lowest for descending.
- constant
- Specifies a constant value with a data type that must conform to the rules for assigning that value to the column. If a string constant is longer or shorter than required by the length attribute of its column, the constant is either truncated or padded on the right to the required length. If the column is ascending, the padding character is X'FF'. If the column is descending, the padding character is X'00'. The precision and scale of a decimal constant must not be greater than the precision and scale of its corresponding column. A hexadecimal string constant (GX) cannot be specified.
- MAXVALUE
- Specifies a value greater than the maximum value for the limit key of a partition boundary (that is, all X'FF' regardless of whether the column is ascending or descending). If all of the columns in the partitioning key are ascending, a constant or the MINVALUE clause cannot be specified following MAXVALUE. After MAXVALUE is specified, all subsequent columns must be MAXVALUE.
- MINVALUE
- Specifies a value that is smaller than the minimum value for the limit key of a partition boundary (that is, all X'00' regardless of whether the column is ascending or descending). If all of the columns in the partitioning key are descending, a constant or the MAXVALUE clause cannot be specified following MINVALUE. After MINVALUE is specified, all subsequent columns must be MINVALUE.
- The first value corresponds to the first column of the key, the second value to the second column, and so on. Using fewer values than there are columns in the key has the same effect as using the highest or lowest values for the omitted columns, depending on whether they are ascending or descending.
- The highest value of the key in any partition must be lower than the highest value of the key in the next partition for ascending cases.
- The values specified for the last partition are enforced. The value specified for the last partition is the highest value of the key that can be placed in the table. Any key values greater than the value specified for the last partition are out of range.
- If the concatenation of all the values exceeds 255 bytes, only the first 255 bytes are considered.
- If a key includes a ROWID column or a column with a distinct type that is based on a ROWID data type, 17 bytes of the constant that is specified for the corresponding ROWID column are considered.
- If a null value is specified for the partitioning key and the key is ascending, an error is returned unless MAXVALUE is specified. If the key is descending, an error is returned unless MINVALUE is specified.
- partition-hash-space
- See partition-hash-space.
- INCLUSIVE
- Specifies that the specified range values are included in the data partition.
organization-clause
See organization-clause.
Other options
- EDITPROC program-name
- Identifies the user-written
code that implements the edit procedure for the table. The edit procedure
must exist at the current server. The procedure is invoked during
the execution of an SQL data change statement or LOAD and all row
retrieval operations on the table.
An edit routine receives an entire table row, and can transform that row in any way. Also, it receives a transformed row and must change the row back to its original form.
For information on writing an EDITPROC exit routine, see Edit procedures.
- WITH ROW ATTRIBUTES
- Specifies that the edit procedure parameter list contains an address for the description of a row. WITH ROW ATTRIBUTES must not be specified for a table with an identity, LOB, XML, ROWID, or SECURITY LABEL column. WITH ROW ATTRIBUTES is the default. When WITH ROW ATTRIBUTES is specified, the column names in the table must not be longer than 18 EBCDIC SBCS characters in length.
- WITHOUT ROW ATTRIBUTES
- Specifies that the description of the row is not provided to the edit procedure. On entry to the edit procedure, the address for the row description in the parameter list contains a value of zero.
- VALIDPROC program-name
- Designates program-name as
the validation exit routine for the table. Writing a validation exit
routine is described in Validation routines.
The validation routine can inhibit a load, insert, update, or delete operation on any row of the table: before the operation takes place, the procedure is passed the row. The values that are represented by any LOB or XML columns in the table are not passed to the validation routine. On an insert or update operation, if the table has a security label column and the user does not have write-down privilege, the user's security label value is passed to the validation routine as the value of the column. After examining the row, the procedure returns a value that indicates whether the operation should proceed. A typical use is to impose restrictions on the values that can appear in various columns. If IN ACCELERATOR is specified, VALIDPROC must not be specified.
A table can have only one validation procedure at a time. In an ALTER TABLE statement, you can designate a replacement procedure or discontinue the use of a validation procedure.
If you omit VALIDPROC, the table has no validation routine.
- AUDIT
- Identifies the types of access to this table that causes auditing to be performed. For information about audit trace classes, see Types of Db2 traces and -START TRACE command (Db2).
If a materialized query table is refreshed with the REFRESH TABLE statement, the auditing also occurs during the REFRESH TABLE operation. AUDIT works as usual for LOAD and SQL data change operations on a user-maintained materialized query table.
- NONE
- Specifies that no auditing is to be done when this table is accessed. This is the default.
- CHANGES
- Specifies that auditing is to be done when the table is accessed during the first insert, update, or delete operation. However, the auditing is done only if the appropriate audit trace class is active.
- ALL
- Specifies that auditing is to be done when the table is accessed during the first operation of any kind performed by a utility or application process.
However, the auditing is done only if the appropriate audit trace class is active and the access is not performed with COPY, RECOVER, REPAIR, LOAD with a dummy input data set, or any stand-alone utility.
If the table is subsequently altered with an ALTER TABLE statement, the ALTER TABLE statement is audited for successful and failed attempts in the following cases, if the appropriate audit trace class is active:
- AUDIT attribute is changed to NONE, CHANGES, or ALL on an audited or non-audited table.
- AUDIT CHANGES or AUDIT ALL is in effect.
If IN ACCELERATOR is specified, AUDIT NONE, CHANGES, and ALL must not be specified.
- OBID integer
- Identifies the OBID to be used for this table. An OBID is the identifier for an object's internal descriptor. The integer must be greater than 1 and must not identify an existing or previously used OBID of the database. If you omit OBID, Db2 generates a value. The following statement retrieves the value of OBID:
SELECT OBID FROM SYSIBM.SYSTABLES WHERE CREATOR = 'ccc' AND NAME = 'nnn';
Here, nnn is the table name and ccc is the creator of the table.
- DATA CAPTURE
- Specifies whether the logging of the following actions on the table includes additional information to support data replication processing:
- SQL data change operations
- Adding columns (using the ADD COLUMN clause)
- Changing columns (using the ALTER COLUMN clause)
For more information, see Altering a table to capture changed data.
If a materialized query table is refreshed with the REFRESH TABLE statement, the logging of the augmented information occurs during the REFRESH TABLE operation. DATA CAPTURE works as usual for insert, update, and delete operations on a user-maintained materialized query table.
A table with data that is stored only in an accelerator-only table cannot be defined with this attribute.
- NONE
- Do not record additional information to the log. This is the default.
- CHANGES
- Write additional data about SQL updates to the log. Information about the values that are represented by any LOB or XML columns is not available. Do not specify DATA CAPTURE CHANGES for tables that reside in table spaces that specify NOT LOGGED.
- WITH RESTRICT ON DROP
- Indicates that the table can be dropped only by using REPAIR DBD
DROP. In addition, the database and table space that contain the table
can be dropped only by using REPAIR DBD DROP.
The WITH RESTRICT ON DROP clause can be removed using the ALTER TABLE statement with the DROP RESTRICT ON DROP clause. After the WITH RESTRICT ON DROP clause is removed from the definition of the table, the table, the database, and the containing table space can be dropped using the DROP statement.
- CCSID encoding-scheme
- Specifies the encoding scheme for string data stored in the table. If the IN clause is specified with a table space, the value must agree with the encoding scheme that is already in use for the specified table space. The specific CCSIDs for SBCS, mixed, and graphic data are determined by the table space or database specified in the IN clause. If the IN clause is not specified, the value specified is used for the table being created as well as for the table space that Db2 implicitly creates. The specific CCSIDs for SBCS, mixed, and graphic data are determined by the default CCSIDs for the server for the specified encoding scheme. The valid values are ASCII, EBCDIC, and UNICODE.
If IN ACCELERATOR is specified, a Unicode column cannot be defined in an EBCDIC table and a column cannot be defined as ASCII mixed or graphic. IBM Db2 Analytics Accelerator for z/OS: Stored Procedures Reference contains a complete description of encoding schemes allowed in an accelerator.
If the CCSID clause is not specified, the encoding scheme for the table depends on the IN clause:
- If the IN clause is specified, the encoding scheme already in use for the table space or database specified in the IN clause is used.
- If the IN clause is not specified, the encoding scheme of the new table is the same as the scheme for the table that is specified in the LIKE clause.
FL 500If CCSID EBCDIC is explicitly or implicitly specified, and any columns in the table are defined with the CCSID 1208 or CCSID 1200 clause, CCSID EBCDIC represents the default encoding scheme for character or graphic columns that do not include the CCSID 1208 or CCSID 1200 clause.
FL 500If the CCSID clause is specified for a materialized query table:
- If the encoding scheme in the CCSID clause is ASCII or Unicode, or if the encoding scheme in the CCSID clause is EBCDIC and the result table of the fullselect contains no Unicode columns, the encoding scheme specified in the clause must be the same as the scheme for the result CCSID of the fullselect. The CCSID must also be the same as the CCSID of the table space for the table being created.
- If the encoding scheme in the CCSID clause is EBCDIC, and the result table of the fullselect contains Unicode columns, the encoding scheme of the table space for the table must be EBCDIC.
- VOLATILE or NOT VOLATILE
- Specifies how Db2 chooses to access the table.
- VOLATILE
- Specifies that Db2 uses index access to the table whenever possible for SQL operations. However, be aware that list prefetch and certain other optimization techniques might be disabled when VOLATILE is used.
One instance in which you might want to use VOLATILE is for a table whose size can vary greatly. If statistics are taken when the table is empty or has only a few rows, those statistics might not be appropriate when the table has many rows.
Another instance in which you might want to use VOLATILE is for a table that contains groups of rows, as defined by the primary key on the table. All but the last column of the primary key of such a table indicate the group to which a given row belongs. The last column of the primary key is the sequence number indicating the order in which the rows are to be read from the group. VOLATILE maximizes concurrency of operations on rows within each group, since rows are usually accessed in the same order for each operation. If IN ACCELERATOR is specified, VOLATILE must not be specified. For this usage, the primary index must be the only index that is defined on the table, and list prefetch is disabled to ensure the sequence in which the rows are locked.
- NOT VOLATILE
- Specifies that SQL access to this table should be based on the current statistics. NOT VOLATILE is the default.
- CARDINALITY
- An optional keyword that currently has no effect, but that is provided for Db2 family compatibility.
- LOGGED or NOT LOGGED
- Specifies whether changes that are made to the data in an implicitly created table space are recorded in the log. This parameter applies to an implicitly created table space and to all indexes of this table. XML table spaces and indexes associated with the XML table spaces inherit the logging attribute from the associated base table space. Auxiliary indexes also inherit the logging attribute from the associated base table space.
Do not specify LOGGED or NOT LOGGED if the table space name is specified by using the IN table-space-name clause or if the IN ACCELERATOR clause is specified.
- LOGGED
- Specifies that changes that are made to the data in an implicitly created table space are recorded in the log.
LOGGED is the default.
- NOT LOGGED
- Specifies that changes that are made to data in an implicitly created table space are not recorded in the log.
NOT LOGGED prevents undo and redo information from being recorded in the log. However, control information for an implicitly created table space will continue to be recorded in the log.
COMPRESS YES or COMPRESS NO
Specifies whether data compression applies to the rows of any implicitly created table space. The IMPTSCMP subsystem parameter specifies the default value. See USE DATA COMPRESSION field (IMPTSCMP subsystem parameter).
If the IN table-space-name clause or the IN ACCELERATOR clause is specified, COMPRESS must not be specified.
- YES
- FL 509Specifies that data compression applies to the rows of the implicitly created table space. The rows are not compressed until the LOAD or REORG utility is run on the table in the implicitly created table space, or the total row data size reaches the compression data threshold while an insert operation is performed.
If a keyword for the compression algorithm is not specified, the default compression algorithm is used. The data compression algorithm is determined by the TS_COMPRESSION_TYPE subsystem parameter.
If a keyword for the compression algorithm is specified:- LOB table spaces that are implicitly created for LOB columns in this table are defined as if COMPRESS YES is specified without a compression algorithm. LOB compression is managed by zEnterprise® data compression (zEDC) hardware if available.
- XML table spaces that are implicitly created for XML columns in this table inherit the COMPRESS attribute.
- FIXEDLENGTH
- FL 509 Specified the fixed-length data compression algorithm.
- HUFFMAN
- FL 509 Specifies the Huffman data compression algorithm. See Using Huffman compression to compress your data for requirements to enable Huffman compression.
- NO
- Specifies that data compression is not used for the rows of the implicitly created table space. Inserted and updated rows are not subject to data compression.
- APPEND NO or APPEND YES
- Specifies
whether append processing is used for the table. The APPEND clause
must not be specified for a table that is created in a work file table
space.
- NO
- Specifies that append processing is not used for the table. For insert and LOAD operations, Db2 will attempt to place data rows in a well clustered manner with respect to the value in the row's cluster key column.
NO is the default.
- YES
- Specifies that data rows are to be placed into the table by disregarding the clustering during insert and LOAD operations.
- DSSIZE integer G
- Specifies the maximum size for an implicitly created partition-by-growth or partition-by-range table space. This value is only applied to an implicitly created base table space, not to any associated implicitly created XML or LOB table spaces.
Do not specify DSSIZE integer G if any of the following conditions are true:
- The table space name is specified by using the IN table-space-name clause.
- The PARTITION BY clause includes the EVERY integer-constant G clause.
- The statement contains an accelerator-only table.
The IMPDSSIZE subsystem parameter specifies the default value. See IMPDSSIZE in macro DSN6SYSP.
For more detailed information about the DSSIZE clause, refer to CREATE TABLESPACE statement.
integer can be separated from G by 0 or more spaces.
- BUFFERPOOL bpname
- Specifies the buffer pool be use for an implicitly created table space and determines the page size of the table space. For 4KB, 8KB, 16KB and 32KB page buffer pools, the page sizes are 4 KB, 8 KB, 16 KB, and 32 KB, respectively.
bpname must identify an activated buffer pool. The privilege set must include SYSADM authority, SYSCTRL authority, or the USE privilege on the buffer pool.
Do not specify BUFFERPOOL bpname if the table space name is specified by using the IN table-space-name clause or the IN ACCELERATOR clause is specified.
If you do not specify the BUFFERPOOL clause, Db2 selects the buffer pool as described in Implicitly defined table spaces.
Refer to Naming conventions in SQL for more information about bpname.
- MEMBER CLUSTER
- Specifies that data that is inserted by an insert operation is not clustered by the implicit clustering index (the first index) or the explicit clustering index. Db2 places the data in an implicitly created table space based on available space.
Do not specify MEMBER CLUSTER if the table space name is specified by using the IN table-space-name clause or if IN ACCELERATOR clause is specified.
- TRACKMOD YES or TRACKMOD NO
- Specifies whether Db2 tracks modified pages in the space map pages of an implicitly created table space. The IMPTKMOD subsystem parameter specifies the default value. See IMPTKMOD in macro DSN6SYSP.
Do not specify TRACKMOD YES or TRACKMOD NO if the table space name is specified by using the IN table-space-name clause or if using the IN ACCELERATOR clause.
- TRACKMOD YES
- Changed pages are tracked in the space map pages to help improve performance of incremental image copies.
- TRACKMOD NO
- Changed pages are not tracked in the space map pages. Db2 uses the LRSN value in each page to determine whether a page has been changed.
PAGENUM
FL 500Identifies the type of page numbering that is used when you create a partition-by-range table space. This value is applied to an implicitly created base table space. The PAGESET_PAGENUM subsystem parameter specifies the default PAGENUM value. See PAGE SET PAGE NUMBERING field (PAGESET_PAGENUM subsystem parameter).
- RELATIVE
- Indicates that internal page numbering is kept as a 4-byte value without a partition number. The page number is a relative page from the start of the partition, and the partition number is kept only in the header page.
- ABSOLUTE
- Indicates that internal page numbering is kept as a 4-byte value that includes a partition number and page number. Distinguishing which bits represent the partition and which represent the page number requires a shift value. The shift value is LOG base 2 (DSSIZE/(page size)).
Notes for CREATE TABLE
- Owner privileges
- The owner of the table has all table privileges (see GRANT statement (table or view privileges)) with the ability to grant these privileges to others. For more information about ownership of the object, see Authorization, privileges, permissions, masks, and object ownership.
- Table design
- Designing tables is part of the process of database design. For more information, see Db2 database design .
- If the IN DATABASE clause is specified without a table space name
- If you specify IN DATABASE (either explicitly or by default), but do not specify a table space, a table space is implicitly created in the specified database. The name of the table space is derived from the table name. The qualifier of the table space is the same as the qualifier of the table. The owner of the table space is SYSIBM.
For more information, see Implicitly defined table spaces.
- If the IN clause is not specified
- If you do not specify the IN clause, the Db2 implicitly creates a table space as described previously, but the Db2 also chooses a database. Db2 creates a name in the form of DSNnnnnn, where nnnnn is between 00001 and the maximum value of the sequence SYSIBM.DSNSEQ_IMPLICITDB, which has a default of 10000, inclusive. The owner of the database is SYSIBM.
- If DSNnnnnn already exists and is an implicitly created database, the Db2 subsystem creates the table in that database.
- If DSNnnnnn does not exist, the Db2 subsystem creates a database with the name DSNnnnnn.
If DSNnnnnn cannot be created because of a deadlock, timeout, or resource unavailable condition, the Db2 subsystem increments nnnnn by one and tries the resultant database name. If the Db2 subsystem reaches the maximum value of the sequence SYSIBM.DSNSEQ_IMPLICITDB, and the corresponding database name is not available, the Db2 subsystem sets nnnnn to 00001 and tries the resultant database name. If the Db2 subsystem attempts to create the table a number of times that is equal to the maximum value of the sequence SYSIBM.DSNSEQ_IMPLICITDB without success, an error occurs.
- System objects for implicitly created table spaces
- If a table space is implicitly created, all of the following required system objects are also implicitly created:
- The enforcing primary key index
- The enforcing unique key index
- Any necessary LOB table spaces, auxiliary table spaces, and auxiliary indexes
- The ROWID index (if the ROWID column is defined as GENERATED BY DEFAULT)
When Db2 implicitly creates a base table space for a table with LOB columns that can have inline LOBs, Db2 creates the base table space in reordered row format, regardless of the value of the RRF subsystem parameter.
The attributes of an implicitly created table space can be changed by using the ALTER TABLESPACE statement statement.
- Creating a table in a segmented (non-UTS) table space (deprecated)
- A table cannot be created in a segmented table space if any of the following conditions are true:
FL 504 The effective application compatibility of the CREATE TABLE statement is V12R1M504 or higher.
- The available space in the data set is less than the segment size specified for the table space, and
- The data set cannot be extended.
Deprecated function:FL 504 Non-UTS table spaces for base tables are deprecated. CREATE TABLESPACE statements that run at application compatibility level V12R1M504 or higher always create a partition-by-growth or partition-by-range table space, and CREATE TABLE statements that specify a non-UTS table space (including existing multi-table segmented table spaces) return an error. However, you can use a lower application compatibility level to create table spaces of the deprecated types if needed, such as for recovery situations. For instructions, see Creating non-UTS table spaces (deprecated).
- Creating a table with graphic and mixed data columns
You cannot create an ASCII or EBCDIC table with a GRAPHIC, VARGRAPHIC, or DBCLOB column or a CHAR, VARCHAR, or CLOB column defined as FOR MIXED DATA when the setting for installation option MIXED DATA is NO, unless the table is EBCDIC, and the columns are Unicode.
- Creating a table with distinct type columns based on LOB, ROWID, and DECFLOAT columns
- Because a distinct type is subject to the same restrictions as its source type, all the syntactic rules that apply to LOB columns (CLOB, DBCLOB, and BLOB), ROWID columns, and DECFLOAT columns apply to distinct type columns that are based on LOBs, row IDs, and DECFLOATs. For example, a table cannot have both an explicitly defined ROWID column and a column with a distinct type that is based on a row ID.
- Tables with inline LOB columns
If the page size is exceeded for a table in a universal table space, Db2 recalculates the record size using 0 as the inline length for LOB columns that do not specify the INLINE LENGTH clause. A record size of 0 is used in the recalculation even if the LOB_INLINE_LENGTH subsystem parameter value is greater than 0. After the recalculation, if the page size is still exceeded, the CREATE TABLE statement returns an error.
You cannot create a table with an inline LOB column in a table space that has basic row format.
- Creating a table with LOB columns
- A table with a LOB column (CLOB, DBCLOB, or BLOB) must also have a ROWID column, one or more auxiliary tables, and indexes on the auxiliary tables. In many situations, Db2 can implicitly create the required objects for you. For more information, see ROWID columns for tables with LOB columns and Auxiliary tables and indexes for LOB columns.
- ROWID columns for tables with LOB columns
-
When you create the table without explicitly defining a ROWID column, Db2 implicitly generates a ROWID column for you.
This column is called an implicitly hidden ROWID column. The implicitly hidden ROWID column has the following attributes:
- Db2 creates the column with a name of DB2_GENERATED_ROWID_FOR_LOBSnn.
Db2 appends nn only if the column name already exists in the table, replacing nn with 00 and incrementing by 1 until the name is unique within the row.
- Defines the column as GENERATED ALWAYS.
- Appends the implicitly hidden ROWID column to the end of the row after all the other explicitly defined columns.
For example, assume that Db2 generated an implicitly hidden ROWID column named DB2_GENERATED_ROWID_FOR_LOBS for table MYTABLE. The result table for a SELECT * statement for table MYTABLE would not contain that ROWID column. However, the result table for SELECT COL1, DB2_GENERATED_ROWID_FOR_LOBS would include the implicitly hidden ROWID column.
If the MIXED DATA subsystem parameter is set to YES, and a lowercase or mixed case hexadecimal constant is specified as the default value for a LOB column, the CREATE TABLE statement returns an error.
- Db2 creates the column with a name of DB2_GENERATED_ROWID_FOR_LOBSnn.
- Auxiliary tables and indexes for LOB columns
-
The definition of a table that contains a LOB column is marked incomplete until an auxiliary table is created in a LOB table space for each LOB column in the base table and an index is created on each auxiliary table. The auxiliary table stores the actual values of a LOB column. For each LOB column in a partitioned table space, one auxiliary table and related index must be defined for each partition of the base table space.
Db2 sometimes implicitly creates the LOB table space, auxiliary table, and index on the auxiliary table for each LOB column in a table or partition. For more information, see LOB table space implicit creation.
If Db2 does not implicitly create the LOB table spaces, auxiliary tables, and indexes on the auxiliary tables, you must create these objects by issuing CREATE TABLESPACE, CREATE AUXILIARY TABLE, and CREATE INDEX statements. For more information, see Creating LOB table spaces, auxiliary tables, and auxiliary indexes explicitly.
- Creating a table with an XML column
FL 500If the table has XML columns, the underlying XML table space is implicitly created with the same PAGENUM attribute as the base table space. The DSSIZE is inherited from the base table space for a base table in the partition-by-growth (PBG) table space.
The following table shows the DSSIZE for an implicitly created XML table space for a base table in a partition-by-range (PBR) or range-partitioned (non-UTS) table space. For partition-by-range (PBR) table spaces with relative page numbering, Db2 also rounds the DSSIZE up to the nearest power of two before using the following table.Table 4. Default DSSIZE for XML table spaces, given the base table space DSSIZE and buffer-pool page size Base table space DSSIZE 4KB base page size 8KB base page size 16KB base page size 32KB base page size 1–4 GB 4G B 4 GB 4 GB 4 GB 5–8 GB 32 GB 16 GB 16 GB 16 GB 9–16 GB 64 GB 32 GB 16 GB 16 GB 17–32 GB 64 GB 64 GB 32 GB 16 GB 33–64 GB 64 GB 64 GB 64 GB 32 GB 65–128 GB 256 GB 256 GB 128 GB 64 GB 129–256 GB 256 GB 256 GB 256 GB 128 GB 257–512 GB 512 GB 512 GB 512 GB 256 GB 513-1024 GB 1024 GB 1024 GB 1024 GB 512 GB For more information, see XML table space implicit creation.
- Naming convention for implicitly created XML objects
- Implicitly created XML table spaces names will be Xyyynnnn, where
yyy is derived from the first three bytes of the base table name (if the
name is shorter than 3, yyy is padded with X).
nnnn is a numeric string that will start at 0000 and be incremented by 1
until a unique number is found.
Implicitly created XML table names will be Xyyyyyyyyyyyyyyyyyynnn, where yyyyyyyyyyyyyyyyyy is the first 18 UTF-8 bytes of the base table name or of the entire name if it is less than 18. nnn will only be appended if the name already exists in the table. If the name already exists, nnn will be replaced with 000 and will be incremented by 1 until the name is unique.
Implicitly created document ID index names will be I_DOCIDyyyyyyyyyyyyyyyyyynnn, where yyyyyyyyyyyyyyyyyy is the first 18 UTF-8 bytes of the base table name or the entire name if it is less than 18. nnn will only be appended if the index already exists in the table. If the index already exists, nnn will be replaced with 000 and will be incremented by 1 until the name is unique.
Implicitly created node ID index names will be I_NODEIDyyyyyyyyyyyyyyyyyynnn, where yyyyyyyyyyyyyyyyyy is the first 18 UTF-8 bytes of the XML table name or the entire name if it is less than 18. nnn will only be appended if the index already exists in the table. If the index already exists, nnn will be replaced with 000 and will be incremented by 1 until the name is unique.
- Creating a table with an identity column
- When a table has an identity column, Db2 can automatically generate sequential numeric values for the column as rows are inserted into the table. Thus, identity columns are ideal for primary keys. Identity columns and ROWID columns are similar in that both types of columns contain values that Db2 generates. ROWID columns are used in large object (LOB) table spaces and can be useful in direct-row access. ROWID columns contain values of the ROWID data type, which returns a 40-byte VARCHAR value that is not regularly ascending or descending. ROWID data values are therefore not well suited to many application uses, such as generating employee numbers or product numbers. For data that is not LOB data and that does not require direct-row access, identity columns are usually a better approach, because identity columns contain existing numeric data types and can be used in a wide variety of uses for which ROWID values would not be suitable.
When a table is recovered to a point-in-time, it is possible that a large gap in the generated values for the identity column might result. For example, assume a table has an identity column that has an incremental value of 1 and that the last generated value at time T1 was 100 and Db2 subsequently generates values up to 1000. Now, assume that the table space is recovered back to time T1. The generated value of the identity column for the next row that is inserted after the recovery completes will be 1001, leaving a gap in the range 100–1001 in the values of the identity column.
If you want to ensure that an identity column has unique values, create a unique index on the column.
- Creating a table with a LONG VARCHAR or LONG VARGRAPHIC column
- Although the syntax LONG VARCHAR and LONG VARGRAPHIC is allowed for compatibility with previous releases of Db2, its use is not encouraged. VARCHAR(integer) and VARGRAPHIC(integer) is the recommended syntax, because after the CREATE TABLE statement is processed, Db2 considers a LONG VARCHAR column to be VARCHAR and a LONG VARGRAPHIC column to be VARGRAPHIC.
When a column is defined using the LONG VARCHAR or LONG VARGRAPHIC syntax, Db2 determines the length attribute of the column. You can use the following information, which is provided for existing applications that require the use of the LONG VARCHAR or LONGVARGRAPHIC syntax, to calculate the byte count and the character count of the column.
To calculate the byte count, use this formula:2*(INTEGER((INTEGER((m-i-k)/j))/2))Where:- m
- Is the maximum row size (8 less than the maximum record size)
- i
- Is the sum of the byte counts of all columns in the table that are not LONG VARCHAR or LONG VARGRAPHIC
- j
- is the number of LONG VARCHAR and LONG VARGRAPHIC columns in the table
- k
- k is the number of LONG VARCHAR and LONG VARGRAPHIC columns that allow nulls
To find the character count:
- Find the byte count.
- Subtract 2.
- If the data type is LONG VARGRAPHIC, divide the result by 2. If the result is not an integer, drop the fractional part.
- Defining a system-period temporal table
- A system-period temporal table definition includes the following:
- A system period named SYSTEM_TIME which is defined using a row-begin column and a row-end column.
- A transaction-start-ID column.
- A system-period data versioning definition which includes the name of the associated history table, which is specified in a subsequent ALTER TABLE statement.
- Defining an application-period temporal table
- An application-period temporal table definition includes an application period named
BUSINESS_TIME. The application period is defined using a begin timestamp column and an end timestamp
column.
Data change operations on an application-period temporal table might result in an automatic insert of one or two additional rows when a row is updated or deleted. When an update or delete of a row in an application-period temporal table is specified for a portion of the period that is 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.
- Bitemporal tables
- A table that is defined for system-period data versioning and contains a BUSINESS_TIME period is referred to as a bitemporal table.
- Considerations for transaction-start-ID columns
- A transaction-start-ID column contains a null value if the column allows null values. A
row-begin column which is unique from other row-begin column values that are generated for other
transactions exists with the transaction-start-ID column. Given that the column might contain null
values, consider using 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
- Implicitly created indexes
- When the PRIMARY KEY or UNIQUE clause is used in the CREATE TABLE statement and the CREATE TABLE statement is processed by the schema processor or the table space that contains the table is implicitly created, Db2 implicitly creates the unique indexes used to enforce the uniqueness of the primary or unique keys.
When a ROWID column is defined as GENERATED BY DEFAULT in the CREATE TABLE statement, and the CREATE TABLE statement is processed by SET CURRENT RULES = 'STD' or the table space that contains the table is implicitly created, Db2 implicitly creates the unique indexes used to enforce the uniqueness of the ROWID column.
The privilege set must include the USE privilege of the buffer pool.
Each index is created as if the following CREATE INDEX statement were issued:
Where:CREATE UNIQUE INDEX xxx ON table-name (column1,...)
- xxx is the name of the index that Db2 generates.
- table-name is the name of the table specified in the CREATE TABLE statement.
- (column1,...) is the list of column names that were specified in the UNIQUE or PRIMARY KEY clause of the CREATE TABLE statement, or the column is a ROWID column that is defined as GENERATED BY DEFAULT.
For more information about the schema processor, see Creating a schema by using the schema processor.
In addition, if A table space that contains the table is implicitly created, Db2 will check the DEFINE DATA SET subsystem parameter to determine whether to define the underlying data set for the index space of the implicitly created index on the base table.
If DEFINE DATA SET is NO, the index is created as if the following CREATE INDEX statement is issued:CREATE UNIQUE INDEX xxx ON table-name (column1,...) DEFINE NO
- Maximum record size
- The maximum record size of a table depends on the page size of the table space and whether the EDITPROC clause is specified, as shown in Table 5..
The initial page size of the table space is the size of its buffer, which is determined by the BUFFERPOOL clause that was explicitly or implicitly specified when the table space was created. When the record size reaches 90 percent of the maximum record size for the page size of the table space, the next largest page size is automatically used.
Table 5. Maximum record size, in bytes Page Size
= 4KBPage Size
= 8KBPage Size
= 16KBPage Size
= 32KBTable without EDITPROC=YES 4056 8138 16330 32714 Table with EDITPROC=YES 4046 8128 16320 32704 The maximum record size corresponds to the maximum length of a VARCHAR column if that column is the only column in the table.
If the table space that contains the table is implicitly created, the proper buffer pool size is chosen according to the actual record size. If the record size reaches 90% of the maximum record size for the page size of the table space, the next largest page size will be used. Table 6 shows 90% of the maximum record size:Table 6. 90% of Maximum record size, in bytes Page Size
= 4KBPage Size
= 8KBPage Size
= 16KBPage Size
= 32KBTable without EDITPROC=YES 3650 7324 14697 29443 Table with EDITPROC=YES 3641 7315 14688 29434 A row in a table with PAGENUM RELATIVE or in a table space with PAGENUM RELATIVE must have a minimum data size of 3 bytes. Rows with data that compresses to less than 3 bytes, will not be compressed when stored in the table.
- Byte counts
- The sum of the byte counts of the columns must not exceed the maximum row size of the table. The maximum row size is eight less than the maximum record size.
For columns that do not allow null values, Table 7 gives the byte counts of columns by data type. For columns that allow null values, the byte count is one more than shown in the table.
Table 7. Byte counts of columns by data type Data Type Byte Count INTEGER 4 SMALLINT 2 BIGINT 8 FLOAT(n) If n is in the range 1–21, the byte count is 4. If n is in the range 22–53, the byte count is 8. DECIMAL INTEGER(p/2)+1, where p is the precision DECFLOAT(16) 9 DECFLOAT(34) 17 CHAR(n) n VARCHAR(n) n+2 CLOB 6 Inline CLOB 6 + inline byte count GRAPHIC(n) 2n VARGRAPHIC(n) 2n+2 DBCLOB 6 Inline DBCLOB 6 + (inline char count * 2) BINARY(n) n VARBINARY(n) n+2 BLOB 6 Inline BLOB 6 + inline byte count DATE 4 TIME 3 TIMESTAMP(p) WITHOUT TIME ZONE INTEGER((p+1)/2) + 7 where p is the precision TIMESTAMP(p) WITH TIME ZONE INTEGER((p+1)/2) + 9 where p is the precision ROWID 19 distinct type The length of the source data type upon which the distinct type was based XML 6 - If column cannot contain multiple versions of an XML document.
14 - If column can contain multiple versions of an XML document.
For more information, see How Db2 uses XML versions.
- Creating a materialized query table
- If the fullselect in the CREATE TABLE statement contains a SELECT *, the select list of the subselect is determined at the time the materialized query table is created. In addition, any references to user-defined functions are resolved at the same time. The isolation level at the time when the CREATE TABLE statement is executed is the isolation level for the materialized query table. After a materialized query table is created, the REFRESH_TIME column of the row for the table in the SYSIBM.SYSVIEWS catalog table contains the default timestamp.
The owner of a materialized query table has all the table privileges with the grant option on the table irrespective of whether the owner has the necessary privileges on the base tables, views, functions, and sequences.
No unique constraints or unique indexes can be created for materialized query tables. Thus, a materialized query table cannot be a parent table in a referential constraint.
When you are creating user-maintained materialized query tables, you should create the materialized query table with query optimization disabled and then enable the table for query optimization after it is populated. Otherwise, Db2 might rewrite queries to use the empty materialized query table, and you will not get accurate results.
- Considerations for implicitly hidden columns
- A column that is defined as implicitly hidden is not part of the result table of a query that
specifies * in a SELECT list. However, an implicitly hidden column can be explicitly referenced in a
query. For example, an implicitly hidden column can be referenced in the SELECT list or in a
predicate in a query. Additionally, an implicitly hidden column can be explicitly referenced in a
COMMENT, CREATE INDEX statement, ALTER TABLE statement, INSERT statement, MERGE statement, UPDATE
statement, or RENAME statement. An implicitly hidden column can be referenced in a referential
constraint. A REFERENCES clause that does not contain a column list refers implicitly to the primary
key of the parent table. It is possible that the primary key of the parent table includes a column
defined as implicitly hidden. Such a referential constraint is allowed.
If the SELECT list of the fullselect of a materialized query definition explicitly refers to an implicitly hidden column, that column will be part of the materialized query table.
If the SELECT list of the fullselect of a view definition (CREATE VIEW statement) explicitly refers to an implicitly hidden column, that column will be part of the view, however the view column is not considered 'hidden'.
- Restrictions on field procedures, edit procedures, and validation exit procedures
- Field procedures, edit procedures, and validation exit procedures cannot be used on tables that
have column names that are larger than 18 EBCDIC bytes. If you have tables that have field
procedures or validation exit procedures and you add a column where the column name is larger than
18 bytes, the field procedures and validation exit procedures for the table will be
invalidated.
Consider using triggers to replace the functionality on field procedures, edit procedures, and validation exit procedures on tables where the column names are larger than 18 EBCDIC bytes.
- Restrictions on certain SQL statements in the same unit of work as CREATE TABLE
-
- A CREATE TABLE statement that contains a PARTITION BY clause should not be followed in the same unit of work by SQL statements that change data.
- A CREATE TABLE statement that contains an IN ACCELERATOR clause should be issued in a separate unit of work from other SQL statements.
- Creating a table while a utility runs
- You cannot use CREATE TABLE while a Db2 utility has control of the table space implicitly or explicitly specified by the IN clause.
- Restrictions involving pending definition changes
- A CREATE TABLE statement is not allowed if there are pending changes to the definition of the table space, if the CREATE TABLE statement specifies a FOREIGN KEY clause that reference a column for which there are pending definition changes, or if the CREATE TABLE statement specifies a materialized query table definition that references a table for which there are pending definition changes.
- Key label requirement
- To use a key label for encryption, the VSAM data sets for the page sets need to be associated with an SMS Data Class that has extended format capability (EF enabled).
Determining a key label for base table space and associated objects
When a key label is specified at the table level, Db2 provides the key label to DFSMS to encrypt all the table spaces and index spaces associated with the table. This includes base table space, auxiliary table spaces, XML table spaces, index spaces, and clone table space, regardless of whether the base table space or associated objects are explicitly or implicitly created. Db2 does not enforce any key label relationship between the base table and an associated history or archive table. The key label for the archive and the history tables has to be set independent of the base table. If there is no key label specified at the table level, Db2 will provide the key label to DFSMS specified for the storage group.
When Db2 calls DFSMS to allocate the dataset for table space or index space, DFSMS uses its order of precedence to determine the key label and can override the key label specified by Db2.
DFSMS order of precedence:- RACF data set profile
- JCL, dynamic allocation, TSO ALLOCATE
- SMS data class construct
If the security administrator has specified a key label for the RACF data set profile, that key label takes precedence over the Db2 provided key label. The REPORT utility can be run to determine the key label used for encryption.
Description of key label in effect in DB2
Table 8. Example scenarios for a partition-by-growth table space, that describe the key label in effect in DB2. This is the key label provided to DFSMS during allocation of data set for table spaces and index spaces. Scenarios Catalog key label value Key label provided to DFSMS during data set allocation Create storage group, SG01 with key label, SGKL01. SYSSTOGROUP record - KEY LABEL: SGKL01 Create table space, TBSP01 using storage group, SG01 – Creates Partition 1 SGKL01 Create table, TB01 in table space, TBSP01 with key label, TBKL01 SYSTABLESPACE record for TBSP01 / SYSTABLES record for TBKL01 – KEY LABEL: TBKL01 REORG TABLESPACE TBSP01 – Reorgs Partition 1 TBKL01 Create index, IX01 on table, TB01 creates index space SYSINDEXES record for IX01 – KEY LABEL: TBKL01 TBKL01 Insert data into TB01 – Creates Partition 2 TBKL01 Alter table, TB01 to specify NO KEY LABEL SYSTABLESPACE record for TBSP01 / SYSTABLES record for TBKL01 / SYSINDEXES record for IX01 – KEY LABEL: Empty string Insert data into TB01 – Creates partition 3 SGKL01 REORG TABLESPACE TBSP01 with REUSE option – Resets and reuses DB2-managed data sets. No change to key label - Key label considerations
-
If the last table is dropped from a segmented table space, the table space and its underlying data set will remain. If key label is in effect, the KEYLABEL column for the table space's SYSTABLESPACE record will be cleared. If a new table is created in this table space, it will be encrypted with the previous key label. If the table has to be created as unencrypted, execute the REORG TABLESPACE utility for the table space.
If a table space is explicitly created with the DEFINE YES option and a table with a key label is defined in that table space, then the data sets associated with the table space will not be encrypted. A subsequent REORG is necessary to encrypt the data sets. Users that want immediate encryption of the data sets associated with the table space must to define table spaces with the DEFINE NO option.
- Syntax and descriptions for hash organization
(deprecated)
-
Deprecated function: FL 504 Hash-organized tables are deprecated. Beginning in Db2 12, packages bound with APPLCOMPAT( V12R1M504) or higher cannot create hash-organized tables or alter existing tables to use hash-organization. Existing hash organized tables remain supported, but they are likely to be unsupported in the future.
- organization-clause
-
- ORGANIZE BY HASH
- Specifies that a hash is to be used for the data organization of the table.
If PARTITION BY RANGE is specified, and the IN clause specifies a table space, the table space must be a partition by range universal table space, and cannot be a table space with PAGENUM RELATIVE.
If PARTITION BY RANGE is not specified, and an IN clause is specified, the IN clause must identify a partition-by-growth table space.
ORGANIZE BY HASH must not be specified if the table is defined with APPEND YES.
ORGANIZE BY HASH must not be specified if the table is using basic row format.
If IN ACCELERATOR is specified, ORGANIZE BY HASH must not be specified.
- UNIQUE
- Specifies that Db2 enforces uniqueness of the hash key columns, preventing the table from containing two or more rows with the same value of the hash key.
- (column-name,...)
-
The list of column names defines the hash key that is used to determine where a row will be placed. Each column-name must be an unqualified name that identifies a column of the table. The same column must not be specified more than once and the specified columns must be defined as NOT NULL. The number of specified columns must not exceed 64, and the sum of their length attributes must not exceed 255. A specified column cannot be any of the following types:
- a LOB column
- a DECFLOAT column
- a XML column
- a distinct type column that is based on one of the preceding data types
All character and graphic string columns in the key must use the same encoding scheme.
If PARTITION BY RANGE is also specified, the list of column names must specify all of the column names that are specified in partition-expression for the table, and must specify the column names in the same order as partition-expression. If the ORGANIZE BY clause contains more columns than partition-expression, partition-expression determines the partition number.
- HASH SPACE integerK|M|G
- Specifies the amount of fixed hash space to preallocate for the table. If the table is
partitioned by range, this is the space for each partition.
The default is 64M for a table in a partition-by-growth table space or 64M for each partition of a partition-by-range table space.
- K
- Indicates that the integer value is multiplied by 1024 to specify the hash space size in bytes. The integer value must be in the range 256–268,435,456.
- M
- Indicates that the integer value is multiplied by 1,048,576 to specify the hash space size in bytes. The integer value must be in the range 1–262,144.
- G
- Indicates that the integer value is to be multiplied by 1,073,741,824 to specify the hash space size in bytes. The integer value must be in the range 1–256 for a partition by range table and must be in the range 1–131,072 for a non-partitioned table.
- partition-hash-space
-
- HASH SPACE integerK|M|G
- Specifies the amount of fixed hash space to preallocate for the partition that is associated
with the partition-element. If HASH SPACE is omitted from the partition
element, the HASH SPACE value from the ORGANIZE
BY clause is used. If IN ACCELERATOR is specified,
HASH SPACE must not be specified.
If HASH SPACE is not specified, each partition will use the HASH SPACE value specified in organization-clause.
The HASH SPACE keyword in partition-element must only be specified if organization-clause is also specified.
- K
- Indicates that the integer value is multiplied by 1024 to specify the hash space size in bytes. The integer value must be in the range 256–268,435,456.
- M
- Indicates that the integer value is multiplied by 1,048,576 to specify the hash space size in bytes. The integer value must be in the range 1–262,144.
- G
- Indicates that the integer value is to be multiplied by 1,073,741,824 to specify the hash space size in bytes. The integer value must be in the range 1–256 for a partition by range table and must be in the range 1–131,072 for a non-partitioned table.
- Notes for hash organization
(deprecated)
-
Deprecated function: FL 504 Hash-organized tables are deprecated. Beginning in Db2 12, packages bound with APPLCOMPAT( V12R1M504) or higher cannot create hash-organized tables or alter existing tables to use hash-organization. Existing hash organized tables remain supported, but they are likely to be unsupported in the future.
- If the IN clause is not specified with ORGANIZE BY HASH
- If you do not specify IN DATABASE (either explicitly or by default), Db2 will use the default DSSIZE of 4G for each partition for a partition-by-range table space or use the value that is specified in the partitioning clause. The hash space value that is specified on CREATE TABLE will be validated, per part, to ensure that the specified DSSIZE is adequate. If the DSSIZE is not adequate, an error will be returned.
If the maximum number of partitions needed for the specified hash space is more than the maximum number of partitions allowed, Db2 to will return an error.
If the selected buffer pool is not available, a error will be returned.
- Creating a table with hash organization and LOB columns
- If the table space is a partition-by-growth table space, Db2 will preallocate as many partitions as needed depending on the value specified for HASH SPACE. If Db2 needs to implicitly create the LOB object in a new partition, the privilege set for the implicitly created LOB objects must include the USE privilege on the buffer pool for the LOB table space.
- Hash space and Db2 page size
- If the specified hash space is less than or equal to 64 MB (the Db2 default), Db2 will add extra space for Db2 system pages. If the specified hash space is greater than 64 MB, Db2 will use part of the hash space for Db2 system pages. The amount of space needed for Db2 system pages depends on SEGSIZE and PAGESIZE. The larger the SEGSIZE and/or PAGESIZE becomes, the larger the requirement for Db2 system pages. Db2 can reserve up to 5 MB for system pages for the highest SEGSIZE value (64) and PAGESIZE value (32K).
- Hash space and DSSIZE
- Depending on certain table space characteristics, Db2 needs to reserve space for the hash overflow area. Therefore, the amount of hash space cannot be equal to the DSSIZE value. The maximum amount of hash space that can be specified is approximately 20% less than the DSSIZE value. Db2 returns an error if the amount of hash space is too large. If the amount of hash space is too large, specify a larger value of DSSIZE, or decrease the amount of hash space.
- Specifying APPEND for hash-organized tables
- Append processing is not applicable to tables with hash organization since there is no key clustering in hash organization. For insert operations into tables with hash organization, Db2 will use the internal hash algorithm to determine the location of the row.
- Maximum record size for hash-organized tables
- For hash-organized tables, the maximum record size on whether the EDITPROC clause is specified, as shown in Table 9.
The initial page size of the table space is the size of its buffer, which is determined by the BUFFERPOOL clause that was explicitly or implicitly specified when the table space was created. When the record size reaches 90 percent of the maximum record size for the page size of the table space, the next largest page size is automatically used.
Table 9. Maximum record size, in bytes for hash organized tables Page Size
= 4KBPage Size
= 8KBPage Size
= 16KBPage Size
= 32KBHash table (hash home page) 3817 7899 16091 32475 Hash table with EDITPROC=YES (hash home page) 3807 7889 16081 32465 The maximum record size corresponds to the maximum length of a VARCHAR column if that column is the only column in the table.
If the table space that contains the table is implicitly created, the proper buffer pool size is chosen according to the actual record size.
FL 500A row in a table with PAGENUM RELATIVE or in a table space with PAGENUM RELATIVE must have a minimum data size of 3 bytes. Rows with data that compresses to less than 3 bytes, will not be compressed when stored in the table.
- Restrictions for tables with hash organization
- Tables that use hash organization are subject to the following restrictions:
- A table that is defined to use hash organization cannot be created in a LOB table space or XML table space.
- ORGANIZE BY HASH must not be specified if the table space is defined with the MEMBER CLUSTER clause.
- The MAXROWS clause is applicable only to the hash overflow area of the table space for tables with hash organization. The fixed hash area of each page will contain as many rows as it can hold, up to a maximum of 255.
- The ORGANIZE BY HASH UNIQUE (column-list) clause is required when specifying HASH SPACE integer K|M|G in the partition-element. The organization-clause applies to the entire table and the partition-element clause applies at the partition level.
- Db2 automatically creates a hash overflow index when a table is created with hash organization.
- Alternative syntax and synonyms
- To provide compatibility with previous releases of Db2 or other products in the Db2 family, Db2 supports the following clauses:
- NOCACHE (single clause) as a synonym for NO CACHE
- NOCYCLE (single clause) as a synonym for NO CYCLE
- NOMINVALUE (single clause) as a synonym for NO MINVALUE
- NOMAXVALUE (single clause) as a synonym for NO MAXVALUE
- NOORDER (single clause) as a synonym for NO ORDER
- PART integer VALUES can be specified as an alternative to PARTITION integer ENDING AT.
- VALUES as a synonym for ENDING AT
- DEFINITION ONLY as a synonym for WITH NO DATA
- SUMMARY between CREATE and TABLE
- TIMEZONE can be specified as an alternative to TIME ZONE.
Examples for CREATE TABLE
- Example 1
-
Create a table named DSN8C10.DEPT in the table space DSN8S12D of the database DSN8D12A. Name the five columns DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, and LOCATION, allowing only MGRNO and LOCATION to contain nulls, and designating DEPTNO as the only column in the primary key. All five columns hold character string data. Assuming a value of NO for the field MIXED DATA on installation panel DSNTIPF, all five columns have the subtype SBCS.
CREATE TABLE DSN8C10.DEPT (DEPTNO CHAR(3) NOT NULL, DEPTNAME VARCHAR(36) NOT NULL, MGRNO CHAR(6) , ADMRDEPT CHAR(3) NOT NULL, LOCATION CHAR(16) , PRIMARY KEY(DEPTNO) ) IN DSN8D12A.DSN8S12D;
- Example 2
- Create a table named DSN8C10.PROJ in an implicitly created table space of the
database DSN8D12A. Assign the table a
validation procedure named DSN8EAPR.
CREATE TABLE DSN8C10.PROJ (PROJNO CHAR(6) NOT NULL, PROJNAME VARCHAR(24) NOT NULL, DEPTNO CHAR(3) NOT NULL, RESPEMP CHAR(6) NOT NULL, PRSTAFF DECIMAL(5,2) , PRSTDATE DATE , PRENDATE DATE , MAJPROJ CHAR(6) NOT NULL) IN DATABASE DSN8D12A VALIDPROC DSN8EAPR;
- Example 3
-
Assume that table PROJECT has a non-primary unique key that consists of columns DEPTNO and RESPEMP (the department number and employee responsible for a project). Create a project activity table named ACTIVITY with a foreign key on that unique key.
CREATE TABLE ACTIVITY (PROJNO CHAR(6) NOT NULL, ACTNO SMALLINT NOT NULL, ACTDEPT CHAR(3) NOT NULL, ACTOWNER CHAR(6) NOT NULL, ACSTAFF DECIMAL(5,2) , ACSTDATE DATE NOT NULL, ACENDATE DATE , FOREIGN KEY (ACTDEPT,ACTOWNER) REFERENCES PROJECT (DEPTNO,RESPEMP) ON DELETE RESTRICT) IN DSN8D12A.DSN8S12D;
- Example 4
-
Create an employee photo and resume table EMP_PHOTO_RESUME that complements the sample employee table. The table contains a photo and resume for each employee. Put the table in table space DSN8D12A.DSN8S12E. Let Db2 always generate the values for the ROWID column.
CREATE TABLE DSN8C10.EMP_PHOTO_RESUME (EMPNO CHAR(6) NOT NULL, EMP_ROWID ROWID NOT NULL GENERATED ALWAYS, EMP_PHOTO BLOB(110K), RESUME CLOB(5K), PRIMARY KEY (EMPNO)) IN DSN8D12A.DSN8S12E CCSID EBCDIC;
- Example 5
- Create an EMPLOYEE table with an identity column named EMPNO. Define the identity column so that
Db2 will always generate the values for the
column. Use the default value, which is 1, for the first value that should be assigned and for the
incremental difference between the subsequently generated consecutive numbers.
CREATE TABLE EMPLOYEE (EMPNO INTEGER GENERATED ALWAYS AS IDENTITY, ID SMALLINT, NAME CHAR(30), SALARY DECIMAL(5,2), DEPTNO SMALLINT) IN DSN8D12A.DSN8S12D;
- Example 6
- Assume a very large transaction table named TRANS contains one row for each transaction
processed by a company. The table is defined with many columns. Create a materialized query table
for the TRANS table that contain daily summary data for the date and amount of a transaction.
CREATE TABLE STRANS AS (SELECT YEAR AS SYEAR, MONTH AS SMONTH, DAY AS SDAY, SUM(AMOUNT) AS SSUM FROM TRANS GROUP BY YEAR, MONTH, DAY) DATA INITIALLY DEFERRED REFRESH DEFERRED;
- Example 7
- The following example creates a table in a partition-by-growth table space and includes the
APPEND option:
CREATE TABLE TS01TB (C1 SMALLINT, C2 DECIMAL(9,2), C3 CHAR(4)) APPEND YES IN TS01DB.TS01TS;
- Example 8
- The following example creates a table in a partition-by-growth table space where the table space
is implicitly created:
CREATE TABLE TS02TB (C1 SMALLINT, C2 DECIMAL(9,2), C3 CHAR(4)) PARTITION BY SIZE EVERY 4G IN DATABASE DSNDB04;
- Example 9
- Create a table, EMP_INFO, that contains a phone number and address for each employee. Include a
row change timestamp column in the table to track the modification of employee information:
CREATE TABLE EMP_INFO (EMPNO CHAR(6) NOT NULL, EMP_INFOCHANGE NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP, EMP_ADDRESS VARCHAR(300), EMP_PHONENO CHAR(4), PRIMARY KEY (EMPNO));
- Example 10
- Create a table, TB01, that uses a range partitioning scheme with a segment size of 4 and 4
partitions.
CREATE TABLE TB01 ( ACCT_NUM INTEGER, CUST_LAST_NM CHAR(15), LAST_ACTIVITY_DT VARCHAR(25), COL2 CHAR(10), COL3 CHAR(25), COL4 CHAR(25), COL5 CHAR(25), COL6 CHAR(55), STATE CHAR(55)) IN DBB.TS01 PARTITION BY (ACCT_NUM) (PARTITION 1 ENDING AT (199), PARTITION 2 ENDING AT (299), PARTITION 3 ENDING AT (399), PARTITION 4 ENDING AT (MAXVALUE));
- Example 11
- Create a table, policy_info, that uses a SYSTEM_TIME period and create a history table,
hist_policy_info. Then issue an ALTER TABLE statement to associate the policy_info table with the
hist_policy_info table.
CREATE TABLE policy_info (policy_id CHAR(10) NOT NULL, coverage INT NOT NULL, sys_start TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN, sys_end TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END, create_id TIMESTAMP(12) GENERATED ALWAYS AS TRANSACTION START ID, PERIOD SYSTEM_TIME(sys_start,sys_end));
CREATE TABLE hist_policy_info (policy_id CHAR(10) NOT NULL, coverage INT NOT NULL, sys_start TIMESTAMP(12) NOT NULL, sys_end TIMESTAMP(12) NOT NULL, create_id TIMESTAMP(12));
ALTER TABLE policy_info ADD VERSIONING USE HISTORY TABLE hist_policy_info;
- Example 12
- Create a table, policy_info, that uses a BUSINESS_TIME
period.
CREATE TABLE policy_info (policy_id CHAR(4) NOT NULL, coverage INT NOT NULL, bus_start DATE NOT NULL, bus_end DATE NOT NULL, PERIOD BUSINESS_TIME(bus_start, bus_end));
- Example 13
-
Create a table, policy_info, that uses both a SYSTEM_TIME period and a BUSINESS_TIME period to keep historical rows and track a user-specified time period. A table that specifies both a SYSTEM_TIME period and a BUSINESS_TIME period is sometimes referred to as a bitemporal table. To enable retention of historical rows, a history table, hist_policy_info, also needs to be created and associated (using the ALTER TABLE statement) with the policy_info table.
CREATE TABLE policy_info (policy_id CHAR(4) NOT NULL, coverage INT NOT NULL, bus_start DATE NOT NULL, bus_end DATE NOT NULL, sys_start TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN, sys_end TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END, create_id TIMESTAMP(12) GENERATED ALWAYS AS TRANSACTION START ID, PERIOD BUSINESS_TIME(bus_start, bus_end), PERIOD SYSTEM_TIME(sys_start, sys_end));
CREATE TABLE hist_policy_info (policy_id CHAR(4) NOT NULL, coverage INT NOT NULL, bus_start DATE NOT NULL, bus_end DATE NOT NULL, sys_start TIMESTAMP(12) NOT NULL, sys_end TIMESTAMP(12) NOT NULL), create_id TIMESTAMP(12);
ALTER TABLE policy_info ADD VERSIONING USE HISTORY TABLE hist_policy_info;
Example 14: Create table EMPLOYEE.PERSONAL with key label EMPKEYLABEL.
CREATE TABLE EMPLOYEE.PERSONAL (DEPTNO CHAR(3) NOT NULL, DEPTNAME VARCHAR(36) NOT NULL, MGRNO CHAR(6) , ADMRDEPT CHAR(3) NOT NULL, LOCATION CHAR(16) , PRIMARY KEY(DEPTNO) ) IN DSN8D12A.DSN8S12D KEY LABEL EMPKEYLABEL;