Limited support BMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see ALTER and BMC AMI Change Manager for Db2 13.1.

CREATE TABLE statement

The CREATE TABLE statement defines a new persistent table or global temporary table to the Db2 system.

Note

All of the parameters apply only to persistent tables unless otherwise noted.



 CREATE TABLE statement (page 1)

createTable_bqu0665.png

CREATE TABLE tableOwner 1.tableName 2

This option specifies the fully qualified name of the table to be created.

SQLID SQLID

This option specifies the current SQLID.

COLUMNS (columnDefinition, ...)

This option specifies the column definitions for the table according to columnDefinition (see columnDefinition).

PERIOD BUSINESS_TIME(columnName 1, columnName 2)

This option specifies the BUSINESS_TIME period for the table. columnName 1 identifies the column that begins the time period. columnName 2 identifies the column that ends the time period.

BUSINESS_TIME parameter values (valid for Db2 Version 12.1 and later)

Value

Description

EXCLUSIVE

The beginning value is included in the period, and the ending value is excluded.

INCLUSIVE

The beginning and ending values are both included in the period.

PERIOD SYSTEM_TIME(columnName 3, columnName 4)

This option specifies the SYSTEM_TIME period for the table. columnName 3 identifies the column that begins the time period. columnName 4 identifies the column that ends the time period.

VERSIONING_TABLE tableOwner 2.tableName 2

This option specifies the name of the history table.

ARCHIVING_TABLE tableOwner 3.tableName 4

This option specifies the name of the archive table.

CLONE

CLONE parameter values

Value

Description

NO

Specifies that the table should not be defined as a clone table

YES

Specifies that the table should be defined as a clone table

REFERENCETB tableOwner 5.tableName 6

This option specifies the name of the table to which the clone table references.

MQT

This option specifies the materialized query table (MQT).

MQT parameter values

Value

Description

NO

Specifies that the table should not be defined as a materialized query table

YES

Specifies that the table should be defined as a materialized query table

MQTParameters

This option specifies the parameters for the MQT (see MQTParameters).

DATABASE databaseName

This option specifies the database for the table.

TABLESPACE

This option specifies the new table space for the table.

TABLESPACE parameter values

Value

Description

tableSpaceName

Specifies the name of the table space

<DEFLT>

Specifies a default value for an implicit table space

<DEFLT> is a reserved keyword.

TYPE

This option specifies the type of table to create.

TYPE parameter values

Value

Description

TEMPORARY

Specifies that the table will be a global temporary table

PERSISTENT

Denotes that a standard Db2 table will be created

EDITPROC procedureName

This option specifies an edit procedure for the table.

EDITPROC parameter values

Value

Description

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 procedureName

This option specifies a validation procedure for the table.

AUDIT

This option specifies the audit procedure for the table.

AUDIT parameter values

Value

Description

NONE

Specifies not to perform auditing

CHANGES

Specifies to perform auditing when the table is updated

ALL

Specifies to perform auditing when any action is performed

OBID integer

This option specifies the value of the internal identifier of a table (OBID) in a read-only shared database.

NUMPARTS number (PART number tablePartitionParameters)

This option defines the new number of partitions for the table. For a partitioned table, NUMPARTS defines the parameters for PART number according to tablePartitionParameters (see tablePartitionParameters).

 CREATE TABLE statement (page 2)

createTable2_bqu1030.png

DATA CAPTURE

This option specifies whether additional information is added to the logging of SQL commands.

DATA CAPTURE parameter values

Value

Description

NONE

Specifies not to include additional information

CHANGES

Specifies to write additional information to the log

VOLATILE

This option specifies whether Db2 should use index access to the table whenever possible.

VOLATILE parameter values

Value

Description

NO

Specifies that SQL access to the table should be based on current statistics

YES

Specifies that Db2 should use index access whenever possible for SQL operations

DROPRESTRICT

This option indicates whether to restrict a table from being dropped.

DROPRESTRICT parameter values

Value

Description

NO

Specifies not to restrict the table

YES

Specifies to restrict the tableSpecifies to restrict the table


APPEND

This option specifies whether to use append processing for the table. With append processing, Db2 attempts to place table rows in accordance with the value in the rows’ cluster key columns when inserting or loading data.

APPEND parameter values

Value

Description

NO

Specifies not to use append processing

YES

Specifies to use append processing

CCSID

(global temporary tables) This option specifies the new encoding scheme of the table.

CCSID parameter values

Value

Description

ASCII

Specifies ASCII encoding

EBCDIC

Specifies EBCDIC encoding

UNICODE

Specifies Unicode encoding

KEY LABEL keyLabelName

This option specifies the name of the encryption key for the table.

PRIMARYKEY (columnName, ...)

This option specifies the primary key definition for the table.

KEYCOLUMNS (columnName order, ...)

This option specifies the names of the partitioning key columns for the table.

order values

Value

Description

ASC (default value)

Places the columns in ascending order

DESC

Places the columns in descending order

COMMENT 'string'

This option defines a comment for the table.

LABEL 'string'

This option defines a label for the table.

COLCOMMENT columnName 'string'

This option defines a comment 'string' on columnName.

COLLABEL columnName 'string'

This option defines a label 'string' on columnName.

columnDefinition

columnDefinition uses the following parameters.

GUID-5616AFB2-868C-48D7-9216-D5EE49385120-low.png

columnName

(persistent and global temporary tables) This option specifies the type of column.

columnName values

Value

Description

builtInDataType

Defines the column as the specified Db2 data type (see builtInDataType)

TYPESCHEMAdataTypeSchema

Specifies the schema type of the column

TYPENAME dataTypeName

Specifies the distinct type of the column

FOR subtype DATA

(persistent and global temporary tables) For character (CHAR), varying-length character (VARCHAR), and character large object (CLOB) columns, defines the character subtype.

Note

If the parent table space or database is defined as Unicode, and the column is created with an implicit subtype, the FOR subtype DATA parameter is omitted from the column definition.

subtype values

Value

Description

BIT

Defines the subtype as a binary character set

SBCS

Defines the subtype as a single-byte character set

MIXED

Defines the subtype as a mixed double-byte and single-byte character set

FIELDPROC procedureName (constant, ...)

This option defines a field procedure for the defined column.

INLINE LENGTH integer

This option specifies the length for an inline LOB column. A value of 0 indicates that the column is not a LOB column, or that the LOB column does not have a length.

nullParameters

Provides additional options for null parameters (see nullParameters).

IDENTITY

Indicates whether the table includes an identity column. Each table can have one identity column.

IDENTITY parameter values

Value

Description

NO

Indicates that the table does not include an identity column.

YES

Indicates that the table includes an identity column.

If the table includes an identity column, the parameters in Identity column parameter values describe the column.

Identity column parameter values

Value

Description

START decimal

Specifies the starting value for the identity column

The default value is the minimum value for a column in ascending sequence or the maximum value for a column in descending sequence.

INCREMENT integer

Specifies the amount of change in the values for the identity column

The value can be any positive or negative integer, including 0 (zero). The default value is 1.

CACHE integer

Specifies the number of a set of values that are stored for the identity column

A value of 0 (zero) is the equivalent of NO CACHE.

CYCLE

Indicates whether values should be generated after the maximum or minimum value is reached

  • NO (default) indicates that values should not be generated after the maximum or minimum value is reached
  • YES indicates that values should be generated after the maximum or minimum value is reached

ORDER

Indicates whether values must be generated in the order in which they are requested

  • NO (default) indicates that values should not be generated in the order in which they are requested
  • YES indicates that values must be generated in the order in which they are requested

MAXVALUE decimal

Specifies the maximum value for an identity column

The maximum value can be equal to or greater than the minimum value.

MINVALUE decimal

Specifies the minimum value for an identity column

The minimum value can be equal to or less than the maximum value.

ROWCHANGE

Indicates whether to generate the time stamp of the most recent change to a row in a column. The data type must be TIMESTAMP, and the null parameter must be NOT NULL.

ROWCHANGE parameter values

Value

Description

NO

Indicates that a value should not be generated for the time stamp

YES

Indicates that a value should be generated for the time stamp.

HIDDEN

Indicates whether the column can be 'hidden' or excluded from the results of a SELECT * SQL statement.

HIDDEN parameter values

Value

Description

NO

Indicates that the column is visible in the results of the statement

YES

Indicates that the column is hidden from the results of the statement

You cannot specify HIDDEN YES for a ROWID column unless you have PTF BQU0665 applied.


builtInDataType (page 1)

GUID-E62DDDB3-4A92-4BBA-8934-9163AF44008A-low.png

builtInDataType (page 2)

GUID-4775589C-95AD-456B-B47B-510D8DC4AFF4-low.png

For information about valid data types, see the IBM documentation.

nullParameters

nullParameters uses the following parameters:

createTableNullNotNull_bqu1029.png

NOT NULL

This option specifies that the column cannot contain any null values.

NOT NULL parameter values

Value

Description

WITH DEFAULT

Specifies that the column has a default value

If a default value is not specified, BMC AMI Change Manager for Db2 uses a value assigned by the system.

DEFAULTVALUE

Specifies the default value of the column:

  • constant specifies a numeric or non-numeric constant as the default value
  • USER specifies the value of the CURRENT USER special register
  • CURRENT SQLID specifies the value of the CURRENT SQLID special register

GENERATED

For an identity column or a ROWID column, indicates when the value for the column is generated by Db2:

  • ALWAYS indicates that the value for the column is generated by Db2
  • DEFAULT indicates that the value for the column is generated by Db2 when a value is not specified

NULL

This option specifies that the column can contain any null values. Any column added to a global temporary table must have a default value of null.

NULL parameter values

Value

Description

DEFAULT

Specifies that the column has a default value

If a default value is not specified, Change Manager uses a value assigned by the system.

DEFAULTVALUE

Specifies the default value of the column:

  • constant specifies a numeric or non-numeric constant as the default value
  • USER specifies the value of the CURRENT USER special register
  • CURRENT SQLID specifies the value of the CURRENT SQLID special register

GENERATED AS

Choose one of the following options:

  • DATA CHANGE OPERATION
  • special-register
    • CURRENT CLIENT_ACCTNG
    • CURRENT CLIENT_APPLNAME
    • CURRENT CLIENT_CORR_TOKEN
    • CURRENT CLIENT_USERID
    • CURRENT CLIENT_WRKSTNNAME
    • CURRENT SERVER
    • CURRENT SQLID
    • SESSION_USER
    • USER
  • session-variable
    • SYSIBM.PACKAGE_NAME
    • SYSIBM.PACKAGE_SCHEMA
    • SYSIBM.PACKAGE_VERSION

MQTParameters

MQTParameters uses the following parameters for a materialized query table:


    • MQTParameters_SPE2501.png

PATH

This option specifies the path for the materialized query table.

PATH parameter values

Value

Description

<NONE>

Indicates no path is specified

schemaList

Specifies the path for the materialized query table

TEXT( AS SELECT( fullSelect) )

This option defines the fullselect component of the SQL statement that is used to create the materialized query table.

MAINTAINEDBY

This option specifies whether the system or the user maintains data in the materialized query table.

MAINTAINEDBY parameter values

Value

Description

SYSTEM

Specifies that the system maintains the materialized query table

USER

Specifies that the user maintains the materialized query table

QUERYOPT

This option specifies whether the materialized query table can be used to optimize queries.

QUERYOPT parameter values

Value

Description

YES

Specifies that the table can be used to optimize queries (in other words, enables optimization)

NO

Specifies that the table cannot be used to optimize queries (in other words, disables optimization)

tablePartitionParameters

tablePartitionParameters uses the following parameter:

GUID-CE118D4E-84DE-42E2-A852-F8CDF9E6B5EF-low.png

VALUES (constant, ...)

This option specifies the limiting constants for this partition of the table.


Related topics

CDL-statements


 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*