CREATE TABLE

The CREATE TABLE statement defines a table at the current server. The definition must include its name and the names and attributes of its columns. The definition may include other attributes of the table such as primary key.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

The privilege to create in the schema. For more information, see Privileges necessary to create in a schema.
Database administrator authority

The privileges held by the authorization ID of the statement must include at least one of the following:

The following system authorities:
- *USE to the Create Physical File (CRTPF) command
- *CHANGE to the data dictionary if the library into which the table is created is an SQL schema with a data dictionary
Database administrator authority

If SQL names are specified and a user profile exists that has the same name as the library into which the table is created, and that name is different from the authorization ID of the statement, then the privileges held by the authorization ID of the statement must include at least one of the following:

The system authority *ADD to the user profile with that name
Database administrator authority

To define a foreign key, the privileges held by the authorization ID of the statement must include at least one of the following on the parent table:

The REFERENCES privilege or object management authority for the table
The REFERENCES privilege on each column of the specified parent key
Ownership of the table
Database administrator authority

If a field procedure is defined, the privileges held by the authorization ID of the statement must include at least one of the following:

The following system authorities:
- The system authority *EXECUTE on the program, and
- The system authority *EXECUTE on the library containing the program
Database administrator authority

If the LIKE clause or select-statement is specified, the privileges held by the authorization ID of the statement must include at least one of the following on the tables or views specified in these clauses:

The SELECT privilege for the table or view
Ownership of the table or view
Database administrator authority

If a distinct type is referenced, the privileges held by the authorization ID of the statement must include at least one of the following:

For each distinct type identified in the statement:
- The USAGE privilege on the distinct type, and
- The system authority *EXECUTE on the library containing the distinct type
Database administrator authority

Start of change To replace an existing table, the privileges held by the authorization ID of the statement must include at least one of the following: End of change

The following system authorities:
- The system authority of *OBJMGT on the table
- All authorities needed to DROP the table
Database administrator authority

For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View and Corresponding System Authorities When Checking Privileges to a Distinct Type.

Syntax

media-preference

memory-preference

Notes:

¹ The optional clauses can be specified in any order.

column-definition

data-type

Notes:

¹ data-type is optional for row change timestamp columns
² GENERATED can be specified only if the column has a ROWID data type (or a distinct type that is based on a ROWID data type), or the column is an identity column, or the column is a row change timestamp.
³ The datalink-options can only be specified for DATALINKs and distinct types sourced on DATALINKs.
⁴ The same clause must not be specified more than once.

built-in-type

allocate-clause

ccsid-clause

normalize-clause

default-clause

identity-options

Notes:

¹ The same clause must not be specified more than once.

as-row-change-timestamp-clause

column-constraint

datalink-options

file-link-options

Notes:

¹ The same clause must not be specified more than once.
² All five file-link-options must be specified, but they can be specified in any order.

as-result-table

copy-options

unique-constraint

referential-constraint

references-clause

check-constraint

distribution-clause

Notes:

¹ The ON DELETE and ON UPDATE clauses may be specified in either order.
² The same clause must not be specified more than once.

partitioning-clause

range-partition-spec

partition-expression

partition-element

boundary-spec

starting-clause

Notes:

¹ This syntax for a partition-element is valid if there is only one partition-expression with a numeric or datetime data type.

ending-clause

hash-partition-spec

materialized-query-definition

refreshable-table-options

Notes:

¹ The same clause must not be specified more than once. MAINTAINED BY USER must be specified.

Description

OR REPLACE

Specifies to replace the definition for the table if one exists at the current server. The existing definition is effectively altered before the new definition is replaced in the catalog.

A definition for the table exists if:

FOR SYSTEM NAME is specified and the system-object-identifier matches the system-object-identifier of an existing table.
FOR SYSTEM NAME is not specified and table-name is a system object name that matches the system-object-identifier of an existing table.

If a definition for the table exists and table-name is not a system object name, table-name can be changed to provide a new name for the table.

This option is ignored if a definition for the table does not exist at the current server.

table-name

Names the table. The name, including the implicit or explicit qualifier, must not identify an alias, file, index, table, or view that already exists at the current server.

If SQL names were specified, the table will be created in the schema specified by the implicit or explicit qualifier.

If system names were specified, the table will be created in the schema that is specified by the qualifier. If not qualified:

If the value of the CURRENT SCHEMA special register is *LIBL, the table will be created in the current library (*CURLIB).
Otherwise, the table will be created in the current schema.

FOR SYSTEM NAME system-object-identifier

Identifies the system-object-identifier of the table. system-object-identifier must not be the same as a table, view, alias, or index that already exists at the current server. The system-object-identifier must be an unqualified system identifier.

When system-object-identifier is specified, table-name must not be a valid system object name.

column-definition

Defines the attributes of a column. There must be at least one column definition and no more than 8000 column definitions.

The sum of the row buffer byte counts of the columns must not be greater than 32766 or, if a VARCHAR, VARGRAPHIC, or VARBINARY column is specified, 32740. Additionally, if a LOB or XML column is specified, the sum of the row data byte counts of the columns must not be greater than 3 758 096 383 at the time of insert or update. For information about the byte counts of columns according to data type, see Maximum row sizes.

column-name

Names a column of the table. Do not qualify column-name and do not use the same name for more than one column of the table or for a system-column-name of the table.

FOR COLUMN system-column-name

Provides an IBM® i name for the column. Do not use the same name for more than one column of the table or for a column-name of the table.

If the system-column-name is not specified, and the column-name is not a valid system-column-name, a system column name is generated. For more information about how system column names are generated, see Rules for Column Name Generation.

data-type

Specifies the data type of the column.

built-in-type

For built-in-types, use:

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 packed decimal number. The first integer is the precision of the number; that is, the total number of digits; it can range from 1 to 63. The second integer is the scale of the number (the number of digits to the right of the decimal point). It 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).

NUMERIC(integer,integer) or NUM(integer,integer)

NUMERIC(integer) or NUM(integer)

NUMERIC or NUM

For a zoned decimal number. The first integer is the precision of the number, that is, the total number of digits; it may range from 1 to 63. The second integer is the scale of the number, (the number of digits to the right of the decimal point). It may range from 0 to the precision of the number.

You can use NUMERIC(p) for NUMERIC(p,0), and NUMERIC for NUMERIC(5,0).

FLOAT

For a double-precision floating-point number.

FLOAT(integer)

For a single- or double-precision floating-point number, depending on the value of integer. The value of integer must be in the range 1 through 53. The values 1 through 24 indicate single-precision, the values 25 through 53 indicate double-precision. The default is 53.

REAL

For single-precision floating point.

DOUBLE PRECISION or DOUBLE

For double-precision floating point.

DECFLOAT(integer)

DECFLOAT

For a IEEE 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, then the DECFLOAT column will be capable of representing 34 significant digits.

CHARACTER(integer) or CHAR(integer)

CHARACTER or CHAR

For a fixed-length character string of length integer bytes. The integer can range from 1 through 32766 (32765 if null capable). If FOR MIXED DATA or a mixed data CCSID is specified, the range is 4 through 32766 (32765 if null capable). If the length specification is omitted, a length of 1 is assumed.

CHARACTER VARYING (integer) or CHAR VARYING (integer) or VARCHAR (integer)

For a varying-length character string of maximum length integer bytes, which can range from 1 through 32740 (32739 if null capable). If FOR MIXED DATA or a mixed data CCSID is specified, the range is 4 through 32740 (32739 if null capable).

CHARACTER LARGE OBJECT (integer[K|M|G]) or CHAR LARGE OBJECT (integer[K|M|G]) or CLOB (integer[K|M|G])

CHARACTER LARGE OBJECT or CHAR LARGE OBJECT or CLOB

For a character large object string of the specified maximum length in bytes. The maximum length must be in the range of 1 through 2 147 483 647. If FOR MIXED DATA or a mixed data CCSID is specified, the range is 4 through 2 147 483 647. If the length specification is omitted, a length of 1 megabyte is assumed. A CLOB is not allowed in a distributed table.

integer: The maximum value for integer is 2 147 483 647. The maximum length of the string is integer.
integer K: The maximum value for integer is 2 097 152. The maximum length of the string is 1024 times integer.
integer M: The maximum value for integer is 2 048. The maximum length of the string is 1 048 576 times integer.
integer G: The maximum value for integer is 2. The maximum length of the string is 1 073 741 824 times integer.

GRAPHIC(integer)

GRAPHIC

For a fixed-length graphic string of length integer, which can range from 1 through 16383 (16382 if null capable). If the length specification is omitted, a length of 1 is assumed.

VARGRAPHIC(integer) or GRAPHIC VARYING(integer)

For a varying-length graphic string of maximum length integer, which can range from 1 through 16370 (16369 if null capable).

DBCLOB(integer[K|M|G])

DBCLOB

For a double-byte character large object string of the specified maximum length.

The maximum length must be in the range of 1 through 1 073 741 823. If the length specification is omitted, a length of 1 megabyte is assumed. A DBCLOB is not allowed in a distributed table.

integer: The maximum value for integer is 1 073 741 823. The maximum length of the string is integer.
integer K: The maximum value for integer is 1 028 576. The maximum length of the string is 1024 times integer.
integer M: The maximum value for integer is 1 024. The maximum length of the string is 1 048 576 times integer.
integer G: The maximum value for integer is 1. The maximum length of the string is 1 073 741 824 times integer.

NATIONAL CHARACTER (integer) or NATIONAL CHAR (integer) or NCHAR (integer)

NATIONAL CHARACTER or NATIONAL CHAR or NCHAR

For a fixed-length Unicode graphic string of length integer, which can range from 1 through 16383 (16382 if null capable). If the length specification is omitted, a length of 1 is assumed. The CCSID is 1200.

NATIONAL CHARACTER VARYING (integer) or NATIONAL CHAR VARYING (integer) or NCHAR VARYING (integer) or NVARCHAR (integer)

For a varying-length Unicode graphic string of maximum length integer, which can range from 1 through 16370 (16369 if null capable). The CCSID is 1200.

NATIONAL CHARACTER LARGE OBJECT (integer[K|M|G]) or NCHAR LARGE OBJECT (integer[K|M|G]) or NCLOB(integer[K|M|G])

NATIONAL CHARACTER LARGE OBJECT or NCHAR LARGE OBJECT or NCLOB

For a Unicode double-byte character large object string of the specified maximum length.

The maximum length must be in the range of 1 through 1 073 741 823. If the length specification is omitted, a length of 1 megabyte is assumed. The CCSID is 1200. An NCLOB is not allowed in a distributed table.

integer: The maximum value for integer is 1 073 741 823. The maximum length of the string is integer.
integer K: The maximum value for integer is 1 028 576. The maximum length of the string is 1024 times integer.
integer M: The maximum value for integer is 1 024. The maximum length of the string is 1 048 576 times integer.
integer G: The maximum value for integer is 1. The maximum length of the string is 1 073 741 824 times integer.

BINARY(integer)

BINARY

For a fixed-length binary string of length integer. The integer can range from 1 through 32766 (32765 if null capable). If the length specification is omitted, a length of 1 is assumed.

BINARY VARYING (integer) or VARBINARY(integer)

For a varying-length binary string of maximum length integer, which can range from 1 through 32740 (32739 if null capable).

BLOB(integer[K|M|G]) or BINARY LARGE OBJECT(integer[K|M|G])

BLOB or BINARY LARGE OBJECT

For a binary large object string of the specified maximum length. The maximum length must be in the range of 1 through 2 147 483 647. If the length specification is omitted, a length of 1 megabyte is assumed. A BLOB is not allowed in a distributed table.

integer: The maximum value for integer is 2 147 483 647. The maximum length of the string is integer.
integer K: The maximum value for integer is 2 097 152. The maximum length of the string is 1024 times integer.
integer M: The maximum value for integer is 2 048. The maximum length of the string is 1 048 576 times integer.
integer G: The maximum value for integer is 2. The maximum length of the string is 1 073 741 824 times integer.

DATE

For a date.

TIME

For a time.

TIMESTAMP(integer) or TIMESTAMP

For a timestamp. Start of change

The integer must be between 0 and 12 and specifies the precision of fractional seconds from 0 (seconds) to 12 (picoseconds). The default is 6 (microseconds). End of change

DATALINK(integer ) or DATALINK

For a DataLink of the specified maximum length. The maximum length must be in the range of 1 through 32717. If FOR MIXED DATA or a mixed data CCSID is specified, the range is 4 through 32717. The specified length must be sufficient to contain both the largest expected URL and any DataLink comment. If the length specification is omitted, a length of 200 is assumed. A DATALINK is not allowed in a distributed table.

A DATALINK value is an encapsulated value with a set of built-in scalar functions. The DLVALUE function creates a DATALINK value. The following functions can be used to extract attributes from a DATALINK value.

DLCOMMENT
DLLINKTYPE
DLURLCOMPLETE
DLURLPATH
DLURLPATHONLY
DLURLSCHEME
DLURLSERVER

A DataLink cannot be part of any index. Therefore, it cannot be included as a column of a primary key, foreign key, or unique constraint.

ROWID

For a row ID. Only one ROWID column is allowed in a table. A ROWID is not allowed in a partitioned table.

XML

For an XML document. Only well-formed documents can be inserted into an XML column. The CCSID for the column cannot be 65535. The maximum length of the column is always 2 147 483 647 bytes.

An XML column has the following restrictions:

The column cannot be part of any index.
The column cannot be part of a primary, unique, or foreign key.
The column cannot be used in a check constraint.
A default value (WITH DEFAULT) cannot be specified for the column. If the column is nullable, the default for the column is the null value.
The column cannot be specified in the distribution clause of a distributed table.
The column cannot be specified in the partitioning clause of a partitioned table.

distinct-type-name

Specifies that 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. If a distinct type name is specified without a schema name, the distinct type name is resolved by searching the schemas on the SQL path.

ALLOCATE(integer)

Specifies for VARCHAR, VARGRAPHIC, VARBINARY, XML, and LOB types the space to be reserved for the column in each row. Column values with lengths less than or equal to the allocated value are stored in the fixed-length portion of the row. Column values with lengths greater than the allocated value are stored in the variable-length portion of the row and require additional input/output operations to retrieve. The allocated value may range from 1 to maximum length of the string, subject to the maximum row buffer size limit. For information about the maximum row buffer size, see Maximum row sizes. If FOR MIXED DATA or a mixed data CCSID is specified, the range is 4 to the maximum length of the string. If the allocated length specification is omitted, an allocated length of 0 is assumed. For VARGRAPHIC, the integer is the number of DBCS or Unicode graphic characters. If a constant is specified for the default value and the ALLOCATE length is less than the length of the default value, the ALLOCATE length is assumed to be the length of the default value.

FOR BIT DATA

Specifies that the values of the column are not associated with a coded character set and are never converted. FOR BIT DATA is only valid for CHARACTER or VARCHAR columns. The CCSID of a FOR BIT DATA column is 65535. FOR BIT DATA is not allowed for CLOB columns.

FOR SBCS DATA

Specifies that the values of the column contain SBCS (single-byte character set) data. FOR SBCS DATA is the default for CHAR, VARCHAR, and CLOB columns if the default CCSID at the current server at the time the table is created is not DBCS-capable or if the length of the column is less than 4. FOR SBCS DATA is only valid for CHARACTER, VARCHAR, or CLOB columns. The CCSID of FOR SBCS DATA is determined by the default CCSID at the current server at the time the table is created.

FOR MIXED DATA

Specifies that the values of the column contain both SBCS data and DBCS data. FOR MIXED DATA is the default for CHAR, VARCHAR, and CLOB columns if the default CCSID at the current server at the time the table is created is DBCS-capable and the length of the column is greater than 3. Every FOR MIXED DATA column is a DBCS-open application server field. FOR MIXED DATA is only valid for CHARACTER, VARCHAR, or CLOB columns. The CCSID of FOR MIXED DATA is determined by the default CCSID at the current server at the time the table is created.

CCSID integer

Specifies that the values of the column contain data of CCSID integer. If the integer is an SBCS CCSID, the column is SBCS data. If the integer is a mixed data CCSID, the column is mixed data and the length of the column must be greater than 3. For character columns, the CCSID must be an SBCS CCSID or a mixed data CCSID. For graphic columns, the CCSID must be a DBCS, UTF-16, or UCS-2 CCSID. If a CCSID is not specified for a graphic column, the CCSID is determined by the default CCSID at the current server at the time the table is created. For XML columns, the CCSID must not be 65535. If a CCSID is not specified for an XML column, the CCSID is established at the time the CREATE TABLE is executed according to the SQL_XML_DATA_CCSID QAQQINI option setting. The default CCSID is 1208. See XML Values for a description of this option. For a list of valid CCSIDs, see CCSID values.

CCSID 1208 (UTF-8) or 1200 (UTF-16) data can contain combining characters. Combining character support allows a resulting character to be comprised of more than one character. After the first character, up to 300 different non-spacing accent characters (umlauts, accent, etc.) can follow in the data string. If the resulting character is one that is already defined in the character set, that character has more than one representation. Normalization replaces the string of combining characters with the hex value of the defined character. This ensures that the same character is represented in a single consistent way. If normalization is not performed, two strings that look identical will not compare equal.

NOT NORMALIZED: The data should not be normalized when passed from the application.
NORMALIZED: The data should be normalized when passed from the application.

DEFAULT

Specifies a default value for the column. This clause cannot be specified more than once in a column-definition. DEFAULT cannot be specified for an XML column, a ROWID column, an identity column (a column that is defined AS IDENTITY), or a row change timestamp column. The database manager generates default values for ROWID columns, identity columns, and row change timestamp columns. For an XML column, the default is NULL unless NOT NULL is specified; in that case there is no default. If a value is not specified following the DEFAULT keyword, then:

if the column is nullable, the default value is the null value.

if the column is not nullable, the default depends on the data type of the column:

Data type	Default value
Numeric	0
Fixed-length character or graphic string	Blanks
Fixed-length binary string	Hexadecimal zeros
Varying-length string	A string length of 0
Date	The current date at the time of INSERT
Time	The current time at the time of INSERT
Timestamp	The current timestamp at the time of INSERT
Datalink	A value corresponding to DLVALUE('','URL','')
distinct-type	The default value of the corresponding source type of the distinct type.

Omission of NOT NULL and DEFAULT from a column-definition is an implicit specification of DEFAULT NULL.

constant

Specifies the constant as the default for the column. The specified constant must represent a value that could be assigned to the column in accordance with the rules of assignment as described in Assignments and comparisons. A floating-point constant or decimal floating-point constant must not be used for a SMALLINT, INTEGER, DECIMAL, or NUMERIC column. A decimal constant must not contain more digits to the right of the decimal point than the specified scale of the column.

USER

Specifies the value of the USER special register at the time of INSERT or UPDATE as the default value of the column. The data type of the column must be CHAR or VARCHAR with a length attribute that is greater than or equal to the length attribute of the USER special register.

NULL

Specifies null as the default for the column. If NOT NULL is specified, DEFAULT NULL must not be specified within the same column-definition.

NULL is the only default value allowed for a datalink column.

CURRENT_DATE

Specifies the current date as the default for the column. If CURRENT_DATE is specified, the data type of the column must be DATE or a distinct type based on a DATE.

CURRENT_TIME

Specifies the current time as the default for the column. If CURRENT_TIME is specified, the data type of the column must be TIME or a distinct type based on a TIME.

CURRENT_TIMESTAMP or CURRENT_TIMESTAMP(integer)

Specifies the current timestamp as the default for the column. If CURRENT_TIMESTAMP is specified, the data type of the column must be TIMESTAMP or a distinct type based on a TIMESTAMP. Start of change

The timestamp precision of the CURRENT_TIMESTAMP special register used as the default will always match the timestamp precision of the column, regardless of the precision specified for the special register. End of change

cast-function-name

This form of a default value can only be used with columns defined as a distinct type, BINARY, VARBINARY, BLOB, CLOB, DBCLOB, DATE, TIME or TIMESTAMP data types. The following table describes the allowed uses of these cast-functions.

Data Type	Cast Function Name
Distinct type N based on a BINARY, VARBINARY, BLOB, CLOB, or DBCLOB	BINARY, VARBINARY, BLOB, CLOB, or DBCLOB *
Distinct type N based on a DATE, TIME, or TIMESTAMP	N (the user-defined cast function that was generated when N was created) ** or DATE, TIME, or TIMESTAMP *
Distinct type N based on other data types	N (the user-defined cast function that was generated when N was created) **
BINARY, VARBINARY, BLOB, CLOB, or DBCLOB	BINARY, VARBINARY, BLOB, CLOB, or DBCLOB *
DATE, TIME, or TIMESTAMP	DATE, TIME, or TIMESTAMP *
Notes: * The name of the function must match the name of the data type (or the source type of the distinct type) with an implicit or explicit schema name of QSYS2. ** The name of the function must match the name of the distinct type for the column. If qualified with a schema name, it must be the same as the schema name for the distinct type. If not qualified, the schema name from function resolution must be the same as the schema name for the distinct type.

constant: Specifies a constant as the argument. The constant must conform to the rules of a constant for the source type of the distinct type or for the data type if not a distinct type. For BINARY, VARBINARY, BLOB, CLOB, DBCLOB, DATE, TIME, and TIMESTAMP functions, the constant must be a string constant.
USER: Specifies the value of the USER special register at the time of INSERT or UPDATE as the default value for the column. The data type of the source type of the distinct type of the column must be CHAR or VARCHAR with a length attribute greater than or equal to the length attribute of the USER special register.
CURRENT_DATE: Specifies the current date as the default for the column. If CURRENT_DATE is specified, the data type of the source type of the distinct type of the column must be DATE.
CURRENT_TIME: Specifies the current time as the default for the column. If CURRENT_TIME is specified, the data type of the source type of the distinct type of the column must be TIME.
CURRENT_TIMESTAMP or CURRENT_TIMESTAMP(integer): Specifies the current timestamp as the default for the column. If CURRENT_TIMESTAMP is specified, the data type of the source type of the distinct type of the column must be TIMESTAMP. The timestamp precision of the CURRENT_TIMESTAMP special register used as the default will always match the timestamp precision of the column, regardless of the precision specified for the special register.

If the value specified is not valid, an error is returned.

GENERATED

Specifies that the database manager generates values for the column. GENERATED may be specified if the column is to be considered an identity column (defined with the AS IDENTITY clause) or a row change timestamp column. It may also be specified if the data type of the column is a ROWID (or a distinct type that is based on a ROWID). Otherwise, it must not be specified.

ALWAYS

Specifies that the database manager will always generate a value for the column when a row is inserted or updated and a default value must be generated. ALWAYS is the recommended value.

BY DEFAULT

Specifies that the database manager 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 ROWID column, the database manager uses a specified value, but it must be a valid unique row ID value that was previously generated by the database manager or Db2® for i.

For an identity column or row change timestamp column, the database manager inserts or updates a specified value but does not verify that it is a unique value for the column unless the identity column or row change timestamp column has a unique constraint or a unique index that solely specifies the identity column or row change timestamp column.

AS IDENTITY

Specifies that the column is an identity column for the table. A table can have only one identity column. An identity column is not allowed in a distributed table. 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, or NUMERIC with a scale of zero, or a distinct type based on one of these data types). If a DECIMAL or NUMERIC data type is specified, the precision must not be greater than 31.

An identity column is implicitly NOT NULL.

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 sequence and the MAXVALUE for a descending sequence. This value is not necessarily the value that a sequence would cycle to after reaching the maximum or minimum value of the sequence. The START WITH clause can be used to start a sequence outside the range that is used for cycles. The range used for cycles is defined by MINVALUE and MAXVALUE.

INCREMENT BY numeric-constant

Specifies the interval between consecutive values of the identity column. The value must not exceed the value of a large integer constant without any non-zero digits existing to the right of the decimal point. The value must be assignable to the column. The default is 1.

If the value is zero or positive, the sequence of values for the identity column ascends. If the value is negative, the sequence of values descends.

MAXVALUE or MINVALUE

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.

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, but the value must be greater than the minimum value.
If a value is not explicitly specified when the identity column is defined, this is the maximum value of the data type for an ascending sequence; or the START WITH value, or -1 if START WITH was not specified, for a descending sequence.
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, but the value must be less than the maximum value.
If a value is not explicitly specified when the identity column is defined, this is the START WITH value, or 1 if START WITH was not specified, for an ascending sequence; or the minimum value of the data type (and precision, if DECIMAL) for a descending sequence.

CACHE 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.

CACHE integer: Specifies the number of values of the identity column sequence that the database manager preallocates and keeps in memory. The minimum value that can be specified is 2, and the maximum is the largest value that can be represented as an integer. The default is 20.
In certain situations, such as system failure, all cached identity column values that have not been used in committed statements are lost, and thus, will never be used. The value specified for the CACHE option is the maximum number of identity column values that could be lost in these situations.
NO CACHE: Specifies that values for the identity column are not preallocated.

CYCLE or NO CYCLE

Specifies whether this identity column should continue to generate values after reaching either the maximum or minimum value of the sequence.

CYCLE: Specifies that values continue to be generated for this column after the maximum or minimum value has been reached. If this option is used, after an ascending sequence reaches the maximum value of the sequence, it generates its minimum value. After a descending sequence reaches its minimum value of the sequence, it generates its maximum value. The maximum and minimum values for the column determine the range that is used for cycling.
When CYCLE is in effect, duplicate values can be generated by the database manager for an identity column. If a unique constraint or unique index exists on the identity column, and a non-unique value is generated for it, an error occurs.
NO CYCLE: Specifies that values will not be generated for the identity column once the maximum or minimum value for the sequence has been reached. This is the default.

ORDER or NO ORDER

Specifies whether the identity values must be generated in order of request.

ORDER: Specifies that the values are generated in order of request.
NO ORDER: Specifies that the values do not need to be generated in order of request. This is the default.

FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP

Specifies that the column is a timestamp and the values will be generated by the database manager. The database manager generates a value for the column for each row as a row is inserted, and for every row in which any column is updated. The value generated for a row change timestamp column is a timestamp corresponding to the time of the insert or update of the row. If multiple rows are inserted with a single SQL statement, the value for the row change timestamp column may be different for each row to reflect when each row was inserted. The generated value is not guaranteed to be unique.

A table can have only one row change timestamp column. If data-type is specified, it must be a TIMESTAMP Start of change

with a precision of 6 End of change

or a distinct type based on a TIMESTAMP Start of change

with a precision of 6 End of change

. A row change timestamp column cannot have a DEFAULT clause and must be NOT NULL.

NOT NULL

Prevents the column from containing null values. Omission of NOT NULL implies that the column can be null. NOT NULL is required for a row change timestamp column.

NOT HIDDEN

Indicates the column is included in implicit references to the table in SQL statements. This is the default.

IMPLICITLY HIDDEN

Indicates the column is not visible in SQL statements unless it is referred to explicitly by name. For example, SELECT * does not include any hidden columns in the result. A table must contain at least one column that is not IMPLICITLY HIDDEN.

column-constraint

CONSTRAINT constraint-name

Names the constraint. A constraint-name must not identify a constraint that was previously specified in the CREATE TABLE statement and must not identify a constraint that already exists at the current server.

If the clause is not specified, a unique constraint name is generated by the database manager.

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.

This clause must not be specified in more than one column definition and must not be specified at all if the UNIQUE clause is specified in the column definition. The column must not be a LOB, DATALINK, or XML column. If a sort sequence is specified, the column must not contain a field procedure.

When a primary key is added, a CHECK constraint is implicitly added to enforce the rule that the NULL value is not allowed in the column that makes up the primary key.

UNIQUE

Provides a shorthand method of defining a unique constraint 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.

This clause cannot be specified more than once in a column definition and must not be specified if PRIMARY KEY is specified in the column definition. The column must not be a LOB, DATALINK, or XML column. If a sort sequence is specified, the column must not contain a field procedure.

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 a references-clause is specified in the definition of column C, the effect is the same as if that references-clause were specified as part of a FOREIGN KEY clause in which C is the only identified column. The references-clause is not allowed if the table is a declared global temporary table or a distributed table. The column cannot be a row change timestamp column.

CHECK(check-condition)

The CHECK(check-condition) of a column-definition provides a shorthand method of defining a check constraint whose check-condition only references a single column. Thus, if CHECK is specified in the column definition of column C, no columns other than C can be referenced in the check-condition of the check constraint. The effect is the same as if the check constraint were specified as a separate clause.

Start of change If ON INSERT VIOLATION or ON UPDATE VIOLATION is specified, column C must be referenced in these clauses. End of change

ROWID, XML, and DATALINK with FILE LINK CONTROL columns cannot be referenced in a CHECK constraint. For additional restrictions, see check-constraint.

FIELDPROC

Designates an external-program-name as the field procedure exit routine for the column. It must be an ILE program that does not contain SQL. It cannot be a service program.

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, it is passed to the field procedure for decoding.

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 supplied for the column in the CREATE TABLE statement defines the data characteristics of the decoded values.

constant: Specifies a parameter that is passed to the field procedure when it is invoked. A parameter list is optional.

A field procedure cannot be defined for a column that is a ROWID or DATALINK or a distinct type based on a ROWID or DATALINK. The column must not be an identity column or a row change timestamp column. The column must not have a default value of CURRENT DATE, CURRENT TIME, CURRENT TIMESTAMP, or USER. The nullability attribute of the encoded and decoded form of the field must match. The column cannot be referenced in a check condition, unless it is referenced in a NULL predicate. If it is part of a foreign key, the corresponding parent key column must use the same field procedure. See SQL Programming for more details on how to create a field procedure.

datalink-options

Specifies the options associated with a DATALINK data type.

LINKTYPE URL

Defines the type of link as a Uniform Resource Locator (URL).

NO LINK CONTROL

Specifies that there will not be any check made to determine that the linked files exist. Only the syntax of the URL will be checked. There is no database manager control over the linked files.

FILE LINK CONTROL

Specifies that a check should be made for the existence of the linked files. Additional options may be used to give the database manager further control over the linked files.

If FILE LINK CONTROL is specified, each file can only be linked once. That is, its URL can only be specified in a single FILE LINK CONTROL column in a single table.

file-link-options

Additional options to define the level of database manager control of the linked files.

INTEGRITY

Specifies the level of integrity of the link between a DATALINK value and the actual file.

ALL: Any file specified as a DATALINK value is under the control of the database manager and may NOT be deleted or renamed using standard file system programming interfaces.

READ PERMISSION

Specifies how permission to read the file specified in a DATALINK value is determined.

FS: The read access permission is determined by the file system permissions. Such files can be accessed without retrieving the file name from the column.
DB: The read access permission is determined by the database. Access to the file will only be allowed by passing a valid file access token, returned on retrieval of the DATALINK value from the table, in the open operation. If READ PERMISSION DB is specified, WRITE PERMISSION BLOCKED must be specified.

WRITE PERMISSION

Specifies how permission to write to the file specified in a DATALINK value is determined.

FS: The write access permission is determined by the file system permissions. Such files can be accessed without retrieving the file name from the column.
BLOCKED: Write access is blocked. The file cannot be directly updated through any interface. An alternative mechanism must be used to perform updates to the information. For example, the file is copied, the copy updated, and then the DATALINK value updated to point to the new copy of the file.

RECOVERY

Specifies whether the database manager will support point in time recovery of files referenced by values in this column.

NO: Specifies that point in time recovery will not be supported.

ON UNLINK

Specifies the action taken on a file when a DATALINK value is changed or deleted (unlinked). Note that this is not applicable when WRITE PERMISSION FS is used.

RESTORE: Specifies that when a file is unlinked, the DataLink File Manager will attempt to return the file to the owner with the permissions that existed at the time the file was linked. In the case where the user is no longer registered with the file server, the result depends on the file system that contains the files. If the files are in the AIX® file system, the owner is "dfmunknown". If the files are in IFS, the owner is QDLFM. This can only be specified when INTEGRITY ALL and WRITE PERMISSION BLOCKED are also specified.
DELETE: Specifies that the file will be deleted when it is unlinked. This can only be specified when READ PERMISSION DB and WRITE PERMISSION BLOCKED are also specified.

MODE DB2OPTIONS

This mode defines a set of default file link options. The defaults defined by DB2OPTIONS are:

INTEGRITY ALL
READ PERMISSION FS
WRITE PERMISSION FS
RECOVERY NO

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 (table-name) or view (view-name). The name must identify a table or view that exists at the current server.

The use of LIKE is an implicit definition of n columns, where n is the number of columns in the identified table or view. The implicit definition includes the following attributes of the n columns (if applicable to the data type):

Column name (and system column name)
Data type, length, precision, and scale
CCSID
FIELDPROC (only copied for table-name)

If the LIKE clause is specified immediately following the table-name and not enclosed in parenthesis, the following column attributes are also included, otherwise they are not included (the default value, identity, row change timestamp, and hidden attributes can also be controlled by using the copy-options):

Default value, if a table-name is specified (view-name is not specified)
Nullability
Hidden attributes
Identity attributes
Column heading and text (see LABEL)

Start of change Any REFFLD information for the column will be copied to the new column definition. End of change

The implicit definition does not include any other optional attributes of the identified table or view. For example, the new table does not automatically include primary keys, foreign keys, or triggers. The new table has these and other optional attributes only if the optional clauses are explicitly specified.

If the specified table or view is a non-SQL created physical file or logical file, any non-SQL attributes are removed. For example, the date and time format will be changed to ISO.

copy-options

INCLUDING IDENTITY COLUMN ATTRIBUTES or EXCLUDING IDENTITY COLUMN ATTRIBUTES

Specifies whether identity column attributes are inherited.

INCLUDING IDENTITY COLUMN ATTRIBUTES

Specifies that the table inherits the identity attribute, if any, of the columns resulting from select-statement, table-name, or view-name. In general, the identity attribute is copied if the element of the corresponding column in the table, view, or select-statement is the name of a table column or the name of a view column that directly or indirectly maps to the name of a base table column with the identity attribute. If the INCLUDING IDENTITY COLUMN ATTRIBUTES clause is specified with the AS select-statement clause, the columns of the new table do not inherit the identity attribute in the following cases:

The select list of the select-statement includes multiple instances of an identity column name (that is, selecting the same column more than once).
The select list of the select-statement includes multiple identity columns (that is, a join returned more than one identity column).
The identity column is included in an expression in the select list.
The select-statement includes a set operation (UNION or INTERSECT).

If INCLUDING IDENTITY is not specified, the table will not have an identity column.

EXCLUDING IDENTITY COLUMN ATTRIBUTES

Specifies that the table does not inherit the identity attribute, if any, of the columns resulting from the fullselect, table-name, or view-name.

EXCLUDING COLUMN DEFAULTS or INCLUDING COLUMN DEFAULTS or USING TYPE DEFAULTS

Specifies whether column defaults are inherited.

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 INSERT for the new table.

INCLUDING COLUMN DEFAULTS

Specifies that the table inherits the default values of the columns resulting from the select-statement, table-name, or view-name. Start of change

In general, the default values are copied if the element of the corresponding column in the table, view, or select-statement is the name of a table column or the name of a view column that directly or indirectly maps to the name of a base table column with a default value. End of change

A default value is the value assigned to a column when a value is not specified on an INSERT.

Do not specify INCLUDING COLUMN DEFAULTS, if you specify USING TYPE DEFAULTS.

If INCLUDING COLUMN DEFAULTS is not specified, the default values are not inherited.

USING TYPE DEFAULTS

Specifies that the default values for the table depend on the data type of the columns that result from the select-statement, table-name, or view-name. If the column is nullable, then the default value is the null value. Otherwise, the default value is as follows:

Data type	Default value
Numeric	0
Fixed-length character or graphic string	Blanks
Fixed-length binary string	Hexadecimal zeros
Varying-length string	A string length of 0
Date	The current date at the time of INSERT
Time	The current time at the time of INSERT
Timestamp	The current timestamp at the time of INSERT
Datalink	A value corresponding to DLVALUE('','URL','')
XML	There is no default value
distinct-type	The default value of the corresponding source type of the distinct type.

Do not specify USING TYPE DEFAULTS if INCLUDING COLUMN DEFAULTS is specified.

INCLUDING IMPLICITLY HIDDEN COLUMN ATTRIBUTES or EXCLUDING IMPLICITLY HIDDEN COLUMN ATTRIBUTES

Specifies whether implicitly hidden columns are inherited.

INCLUDING IMPLICITLY HIDDEN COLUMN ATTRIBUTES: Specifies that the table inherits implicitly hidden columns from select-statement, table-name, or view-name and those columns will be defined with the implicitly hidden attribute in the new table.
If INCLUDING IMPLICITLY HIDDEN COLUMN ATTRIBUTES is not specified, the table will not have any implicitly hidden columns.
EXCLUDING IMPLICITLY HIDDEN COLUMN ATTRIBUTES: Specifies that the table does not inherit implicitly hidden columns from the fullselect, table-name, or view-name.

INCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES or EXCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES

Specifies whether the row change timestamp attribute is inherited.

INCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES

Specifies that the table inherits the row change timestamp attribute, if any, of the columns resulting from select-statement, table-name, or view-name. In general, the row change timestamp attribute is copied if the element of the corresponding column in the table, view, or select-statement is the name of a table column or the name of a view column that directly or indirectly maps to the name of a base table column with the row change timestamp attribute. If the INCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES clause is specified with the AS select-statement clause, the columns of the new table do not inherit the row change timestamp in the following cases:

The select list of the select-statement includes multiple instances of a row change timestamp column name (that is, selecting the same column more than once).
The select list of the select-statement includes multiple row change timestamp columns (that is, a join returned more than one row change timestamp column).
The row change timestamp column is included in an expression in the select list.
The select-statement includes a set operation (UNION or INTERSECT).

If INCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES is not specified, the table will not have a row change timestamp column.

EXCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES

Specifies that the table does not inherit the row change timestamp attribute, if any, of the columns resulting from the fullselect, table-name, or view-name.

as-result-table

column-name

Names a column 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 select-statement. 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 select-statement.

A list of column names must be specified if the result table of the select-statement has duplicate column names or an unnamed column. An unnamed column is a column derived from a constant, function, expression, or set operation (UNION or INTERSECT) that is not named using the AS clause of the select list.

FOR COLUMN system-column-name

Provides an IBM i name for the column. Do not use the same name for more than one column of the table or for a column-name of the table.

select-statement

Specifies that the columns of the table have the same name and description as the columns that would appear in the derived result table of the select-statement if the select-statement were to be executed. The use of AS (select-statement) is an implicit definition of n columns for the table, where n is the number of columns that would result from the select-statement.

The implicit definition includes the following attributes of the n columns (if applicable to the data type):

Column name (and system column name)
Data type, length, precision, and scale
CCSID
Nullability
FIELDPROC
Column heading and text (see LABEL)

The following attributes are not included (they may be included by using the copy-options):

Default value
Hidden attribute
Identity attributes
Row change timestamp attribute

The implicit definition does not include any other optional attributes of the identified table or view. For example, the new table does not automatically include a primary key or foreign key from a table. The new table has these and other optional attributes only if the optional clauses are explicitly specified.

Start of change Any column in the select-clause that is either a direct reference to a column in another table or view or uses only a CAST to change the result attributes will have REFFLD information generated for the definition in the file object. End of change

The select-statement must not reference variables, Start of change but may reference global variables. End of change

The select-statement must not contain a PREVIOUS VALUE or a NEXT VALUE expression. The UPDATE, SKIP LOCKED DATA, and USE AND KEEP EXCLUSIVE LOCKS clauses may not be specified.

Start of change If the select-statement contains an isolation-clause the isolation level specified in the isolation-clause applies to the entire SQL statement. End of change

WITH DATA

Specifies that the select-statement is executed. After the table is created, the result table rows of the select-statement are automatically inserted into the table.

WITH NO DATA

Specifies that the select-statement is used only to define the attributes of the new table. The table is not populated using the results of the select-statement.

unique-constraint

CONSTRAINT constraint-name

If the clause is not specified, a unique constraint name is generated by the database manager.

PRIMARY KEY(column-name,…)

Defines a primary key composed of the identified columns. A table can only have one primary key. Thus, this clause cannot be specified more than once and cannot be specified at all if the shorthand form has been used to define a primary key for the table. The identified columns cannot be the same as the columns specified in another UNIQUE constraint specified earlier in the CREATE TABLE statement. For example, PRIMARY KEY(A,B) would not be allowed if UNIQUE (B,A) had already been 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 once. The column must not be a LOB, DATALINK, or XML column. If a sort sequence is specified, the column must not contain a field procedure. The number of identified columns must not exceed 120, and the sum of their byte counts must not exceed 32766-n, where n is the number of columns specified that allow nulls. For information about byte-counts see Table 1.

The unique index is created as part of the system physical file, not a separate system logical file. When a primary key is added, a CHECK constraint is implicitly added to enforce the rule that the NULL value is not allowed in any of the columns that make up the primary key.

UNIQUE (column-name,…)

Defines a unique constraint composed of the identified columns. The UNIQUE clause can be specified more than once. The identified columns cannot be the same as the columns specified in another UNIQUE constraint or PRIMARY KEY that was specified earlier in the CREATE TABLE statement. For determining if a unique constraint is the same as another constraint specification, the column lists are compared. For example, UNIQUE (A,B) is the same as UNIQUE (B,A).

Each column-name must be an unqualified name that identifies a column of the table. The same column must not be identified more than once. The column must not be a LOB, DATALINK, or XML column. If a sort sequence is specified, the column must not contain a field procedure. The number of identified columns must not exceed 120, and the sum of their byte counts must not exceed 32766-n, where n is the number of columns specified that allows nulls. For information about byte-counts see Table 1.

A unique index on the identified column is created during the execution of the CREATE TABLE statement. The unique index is created as part of the system physical file, not as a separate system logical file.

referential-constraint

CONSTRAINT constraint-name

If the clause is not specified, a unique constraint name is generated by the database manager.

FOREIGN KEY

Each specification of the FOREIGN KEY clause defines a referential constraint. FOREIGN KEY is not allowed if the table is a declared global temporary table or a distributed table.

(column-name,…)

The foreign key of the referential constraint is composed of the identified columns. Each column-name must be an unqualified name that identifies a column of the table. The same column must not be identified more than once. The column must not be a LOB, DATALINK, or XML column and must not be a row change timestamp column. If a sort sequence is specified, the column must not contain a field procedure. The number of identified columns must not exceed 120, and the sum of their lengths must not exceed 32766-n, where n is the number of columns specified that allow nulls.

REFERENCES table-name

The table-name specified in a REFERENCES clause must identify the table being created or a base table that already exists at the application server, but it must not identify a catalog table, a declared temporary table, or a distributed table. If the parent is a partitioned table, the unique index that enforces the parent unique constraint must be non-partitioned.

A referential constraint is a duplicate if its foreign key, parent key, and parent table are the same as the foreign key, parent key, and parent table of a previously specified referential constraint. Duplicate referential constraints are allowed, but not recommended.

Let T2 denote the identified parent table and let T1 denote the table being created.

The specified foreign key must have the same number of columns as the parent key of T2. The description of the nth column of the foreign key and the description of the nth column of that parent key must have identical data types, lengths, CCSIDs, and FIELDPROCs.

(column-name,…): The parent key of the referential constraint is composed of the identified columns. Each column-name must be an unqualified name that identifies a column of T2. The same column must not be identified more than once. The column must not be a LOB, DATALINK, or XML column and must not be a row change timestamp column. If a sort sequence is specified, the column must not contain a field procedure. The number of identified columns must not exceed 120, and the sum of their byte counts must not exceed 32766-n, where n is the number of columns specified that allow nulls. For information about byte-counts see Table 1.
The list of column names must be identical to the list of column names in the primary key of T2 or a UNIQUE constraint that exists on T2. The names need not be specified in the same order as in the primary key; however, they must be specified in corresponding order to the list of columns in the foreign key clause. If a column name list is not specified, then T2 must have a primary key. Omission of the column name list is an implicit specification of the columns of that primary key.

The referential constraint specified by a FOREIGN KEY clause defines a relationship in which T2 is the parent and T1 is the dependent.

ON DELETE

Specifies what action is to take place on the dependent tables when a row of the parent table is deleted. There are five possible actions:

NO ACTION (default)
RESTRICT
CASCADE
SET NULL
SET DEFAULT

SET NULL must not be specified unless some column of the foreign key allows null values.

CASCADE must not be specified if T1 contains a DataLink column with FILE LINK CONTROL.

The delete rule applies when a row of T2 is the object of a DELETE or propagated delete operation and that row has dependents in T1. Let p denote such a row of T2.

If RESTRICT or NO ACTION is specified, an error occurs and no rows are deleted.
If CASCADE is specified, the delete operation is propagated to the dependents of p in T1.
If SET NULL is specified, each nullable column of the foreign key of each dependent of p in T1 is set to null. SET NULL is not allowed if the dependent table is a partitioned table and a foreign key column is also a partitioning key.
If SET DEFAULT is specified, each column of the foreign key of each dependent of p in T1 is set to its default value. SET DEFAULT is not allowed if the dependent table is a partitioned table and a foreign key column is also a partitioning key unless the default keeps the row in the same partition.

ON UPDATE

Specifies what action is to take place on the dependent tables when a row of the parent table is updated.

The update rule applies when a row of T2 is the object of an UPDATE or propagated update operation and that row has dependents in T1. Let p denote such a row of T2.

If RESTRICT or NO ACTION is specified, an error occurs and no rows are updated.

check-constraint

CONSTRAINT constraint-name

Names the check constraint. A constraint-name must not identify a constraint that was previously specified in the CREATE TABLE statement and must not identify a constraint that already exists at the current server.

If the clause is not specified, a unique constraint name is generated by the database manager.

CHECK (check-condition)

Defines a check constraint. At any time, the check-condition must be true or unknown for every row of the table.

The check-condition is a search-condition except:

It can only refer to columns of the table
The result of any expression in the check-condition cannot be a ROWID, XML, or DATALINK with FILE LINK CONTROL data type.

It must not contain any of the following:

Subqueries
Aggregate functions
Variables
Global variables
Parameter markers
Sequence-references
Complex expressions that contain LOBs (such as concatenation)
OLAP specifications
ROW CHANGE expressions
REGEXP_LIKE predicate
Special registers
User-defined functions other than functions that were implicitly generated with the creation of a distinct type

The following built-in scalar functions:

ATAN2	DLURLPATHONLY	LPAD	RPAD
CARDINALITY	DLURLSCHEME	MAX_CARDINALITY	SCORE
CONTAINS	DLURLSERVER	MONTHNAME	SOUNDEX
CURDATE	DLVALUE	MONTHS_BETWEEN	TABLE_NAME
CURTIME	ENCRYPT_AES	NEXT_DAY	TABLE_SCHEMA
DATAPARTITIONNAME	ENCRYPT_RC2	NOW	TIMESTAMP_FORMAT
DATAPARTITIONNUM	ENCRYPT_TDES	OVERLAY	TIMESTAMPDIFF
DAYNAME	GENERATE_UNIQUE	RAISE_ERROR	TRUNC_TIMESTAMP
DBPARTITIONNAME	GETHINT	RAND	VARCHAR_FORMAT
DECRYPT_BINARY	IDENTITY_VAL_LOCAL	REGEXP_COUNT	VERIFY_GROUP_FOR_USER
DECRYPT_BIT	INSERT	REGEXP_INSTR	WEEK_ISO
DECRYPT_CHAR	JSON_ARRAY	REGEXP_REPLACE	WRAP
DECRYPT_DB	JSON_OBJECT	REGEXP_SUBSTR	XMLPARSE
DIFFERENCE	JSON_QUERY	REPEAT	XMLVALIDATE
DLURLCOMPLETE ¹	JSON_VALUE	REPLACE	XSLTRANSFORM
DLURLPATH	LOCATE_IN_STRING	ROUND_TIMESTAMP
¹ For DataLinks with an attribute of FILE LINK CONTROL and READ PERMISSION DB.

ON INSERT VIOLATION

Specifies the action to take if the check-condition is false for a row being inserted. If this clause is not specified, an error will occur if the check-condition is false for an insert.

SET column-name = DEFAULT: The default value for column-name is inserted into the table instead of the value provided by the insert operation.
column-name must be referenced in the check-condition.

ON UPDATE VIOLATION

Specifies the action to take if the check-condition is false for a row being updated. If this clause is not specified, an error will occur if the check-condition is false for an update.

PRESERVE column-name: The current value for column-name remains in the table rather than being replaced by the value provided by the update operation.
column-name must be referenced in the check-condition.

For more information about search-condition, see Search conditions. For more information about check constraints involving LOB data types and expressions, see the Database Programming topic collection.

NOT LOGGED INITIALLY

Any changes made to the table by INSERT, DELETE, or UPDATE statements in the same unit of work after the table is created by this statement are not logged (journaled).

At the completion of the current unit of work, the NOT LOGGED INITIALLY attribute is deactivated and all operations that are done on the table in subsequent units of work are logged (journaled).

The NOT LOGGED INITIALLY option is useful for situations where a large result set needs to be created with data from an alternate source (another table or a file) and recovery of the table is not necessary. Using this option will save the overhead of logging (journaling) the data.

ACTIVATE NOT LOGGED INITIALLY is ignored if the table has a DATALINK column with FILE LINK CONTROL.

VOLATILE or NOT VOLATILE

Indicates to the optimizer whether the cardinality of table table-name can vary significantly at run time. Volatility applies to the number of rows in the table, not to the table itself. The default is NOT VOLATILE.

VOLATILE: Specifies that the cardinality of table-name can vary significantly at run time, from empty to large. To access the table, the optimizer will typically use an index, if possible.
NOT VOLATILE: Specifies that the cardinality of table-name is not volatile. Access plans that reference this table will be based on the cardinality of the table at the time the access plan is built. NOT VOLATILE is the default.

RCDFMT

Indicates the record format name of the table.

RCDFMT format-name: An unqualified name that designates the IBM i record format name of the table. A format-name is a system identifier.
If a record format name is not specified, the format-name is the same as the system-object-name of the table.

media-preference

Specifies the preferred storage media for the table or partition.

UNIT ANY: No storage media is preferred. Storage for the table or partition will be allocated from any available storage media. If UNIT ANY is specified on the table, any media-preference that is specified on a partition is used.
UNIT SSD: Solid state disk storage media is preferred. Storage for the table or partition may be allocated from solid state disk storage media, if available. If UNIT SSD is specified on the table, any media-preference specified on a partition is ignored.

memory-preference

KEEP IN MEMORY

Specifies whether the data for the table should be brought into a main storage pool when the data is used in a query.

NO: The data will not be brought into a main storage pool.
YES: The data will be brought into a main storage pool.

ON REPLACE

Specifies the action to take when replacing a table that exists at the current server. This option is ignored if an existing table is not being replaced.

PRESERVE ALL ROWS

Current rows of the specified table will be preserved. PRESERVE ALL ROWS is not allowed if WITH DATA is specified with result-table-as.

All rows of all partitions in a partitioned table are preserved. If the new table definition is a range partitioned table, the defined ranges must be able to contain all the rows from the existing partitions.

If a column is dropped, the column values will not be preserved. If a column is altered, the column values may be modified.

If the table is not a partitioned table or is a hash partitioned table, PRESERVE ALL ROWS and PRESERVE ROWS are equivalent.

PRESERVE ROWS

Current rows of the specified table will be preserved. PRESERVE ROWS is not allowed if WITH DATA is specified with result-table-as.

If a partition of a range partitioned table is dropped, the rows of that partition will be deleted without processing any delete triggers. To determine if a partition of a range partitioned table is dropped, the range definitions and the partition names (if any) of the partitions in the new table definition are compared to the partitions in the existing table definition. If either the specified range or the name of a partition matches, it is preserved. If a partition does not have a partition-name, its boundary-spec must match an existing partition.

If a partition of a hash partition table is dropped, the rows of that partition will be preserved.

If a column is dropped, the column values will not be preserved. If a column is altered, the column values may be modified.

DELETE ROWS

Current rows of the specified table will be deleted. Any existing DELETE triggers are not fired.

distribution-clause

IN NODEGROUP nodegroup-name

Specifies the nodegroup across which the data in the table will be distributed. The name must identify a nodegroup that exists at the current server. If this clause is specified, the table is created as a distributed table across all the systems in the nodegroup.

A LOB, DATALINK, XML, or IDENTITY column is not allowed in a distributed table.

The DB2 Multisystem product must be installed to create a distributed table. For more information about distributed tables, see the DB2 Multisystem topic collection.

DISTRIBUTE BY HASH (column-name,…)

Specifies the partitioning key. The partitioning key is used to determine on which node in the nodegroup 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 identified more than once. If the DISTRIBUTE BY clause is not specified, the first column of the primary key is used as the partitioning key. If there is no primary key, the first column of the table that is not Start of change

floating point End of change

, date, time, or timestamp is used as the partitioning key.

The columns that make up the partitioning key must be a subset of the columns that make up any unique constraints over the table. Floating point, Start of change date, time, timestamp, End of change LOB, XML, DataLink, ROWID, and columns that have a field procedure cannot be used in a partitioning key.

partitioning-clause

PARTITION BY RANGE

Specifies that ranges of column values are used to determine the target data partition when inserting a row into the table. The number of partitions must not exceed 256.

partition-expression

Specifies the key data over which the range is defined to determine the target data partition of the data.

column-name

Identifies a column in the data partitioning key. The partitioning key is used to determine into which partition in the table a row will be placed. The column-name must be an unqualified name that identifies a column of the table. The same column must not be identified more than once.

LOB, XML, DataLink, ROWID, row change timestamp columns, Start of change identity columns, End of change and any column with a field procedure cannot be used in a partitioning key.

The number of identified columns must not exceed 120. The sum of length attributes of the columns must not be greater than 2000.

NULLS LAST

Indicates that null values compare high.

NULLS FIRST

Indicates that null values compare low.

partition-element

Specifies ranges for a data partitioning key.

PARTITION partition-name

Names the data partition. The name must not be the same as any other data partition for the table.

If the clause is not specified, a unique partition name is generated by the database manager.

boundary-spec

Specifies the boundaries of a range partition. Start of change

If more than one partition key is specified, End of change

the boundaries must be specified in ascending sequence. The ranges must not overlap.

starting-clause

Specifies the low end of the range for a data partition. The number of specified starting values must be the same as the number of columns in the partitioning key. If a starting-clause is not specified for the first boundary-spec, the default is MINVALUE INCLUSIVE for each column of the partitioning key. If a starting-clause is not specified for a subsequent boundary-spec, the previous adjacent boundary-spec must contain an ending-clause. The default is the same as that ending-clause except that the INCLUSIVE or EXCLUSIVE attribute is reversed.

STARTING FROM

Introduces the starting-clause.

constant: Specifies a constant that must conform to the rules of a constant for the data type of the corresponding column of the partition key. If the corresponding column of the partition key is a distinct type, the constant must conform to the rules of the source type of the distinct type. The value must not be in the range of any other boundary-spec for the table.
MINVALUE: Specifies a value that is lower than the lowest possible value for the data type of the column-name to which it corresponds. If MINVALUE is specified, all subsequent values in the starting-clause must also be MINVALUE.
MAXVALUE: Specifies a value that is greater than the greatest possible value for the data type of the column-name to which it corresponds. If MAXVALUE is specified, all subsequent values in the ending-clause must also be MAXVALUE.

INCLUSIVE

Indicates that the specified range values are included in the data partition.

EXCLUSIVE

Indicates that the specified range values are excluded from the data partition. This specification is ignored when MINVALUE or MAXVALUE is specified.

ending-clause

Specifies the high end of the range for a data partition. The number of specified ending values must be the same as the number of columns in the data partitioning key. An ending-clause must be specified for the last boundary-spec. If an ending-clause is not specified for a previous boundary-spec, the next adjacent boundary-spec must contain a starting-clause. The default is the same as that starting-clause except that the INCLUSIVE or EXCLUSIVE attribute is reversed.

ENDING AT

Introduces the ending-clause.

constant: Specifies a constant that must conform to the rules of a constant for the data type of the corresponding column of the partition key. If the corresponding column of the partition key is a distinct type, the constant must conform to the rules of the source type of the distinct type. The value must not be in the range of any other boundary-spec for the table.
MINVALUE: Specifies a value that is lower than the lowest possible value for the data type of the column-name to which it corresponds. If MINVALUE is specified, all subsequent values in the starting-clause must also be MINVALUE.
MAXVALUE: Specifies a value that is greater than the greatest possible value for the data type of the column-name to which it corresponds. If MAXVALUE is specified, all subsequent values in the ending-clause must also be MAXVALUE.

INCLUSIVE

Indicates that the specified range values are included in the data partition.

EXCLUSIVE

Indicates that the specified range values are excluded from the data partition. This specification is ignored when MINVALUE or MAXVALUE is specified.

EVERY integer-constant

Specifies that multiple data partitions will be added where integer-constant specifies the width of each data partition range. If EVERY is specified, only a single SMALLINT, INTEGER, BIGINT, DECIMAL, NUMERIC, DATE, or TIMESTAMP column can be specified for the partition key.

The starting value of the first data partition is the specified STARTING value. The starting value of each subsequent partition is the starting value of the previous partition + integer-constant. If the starting-clause specified EXCLUSIVE, the starting value of every partition is EXCLUSIVE. Otherwise, the starting value of every partition is INCLUSIVE.

The ending value of every partition of the range is (start + integer-constant - 1). If the ending-clause specified EXCLUSIVE, the ending value of every partition is EXCLUSIVE. Otherwise, the ending value of every partition is INCLUSIVE.

The number of partitions added is determined by adding integer-constant repeatedly to the STARTING value until the ENDING value is reached. For example:

  CREATE TABLE FOO
    (A  INT)
    PARTITION BY RANGE(A)
      (STARTING(1) ENDING(10) EVERY(2))

is equivalent to the following CREATE TABLE statement:

  CREATE TABLE FOO
    (A  INT)
    PARTITION BY RANGE(A)
      (STARTING(1) ENDING(2),
       STARTING(3) ENDING(4),
       STARTING(5) ENDING(6),
       STARTING(7) ENDING(8),
       STARTING(9) ENDING(10))

In the case of dates and timestamps, the EVERY value must be a labeled duration. For example:

  CREATE TABLE FOO
    (A  DATE)
    PARTITION BY RANGE(A)
      (STARTING('2001-01-01') ENDING('2010-01-01') EVERY(3 MONTHS))

PARTITION BY HASH

Specifies that the hash function is used to determine the target data partition when inserting a row into the table.

(column-name,…): Specifies the partitioning key. The partitioning key is used to determine into which partition in the table 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 identified more than once.
Floating point, LOB, XML, date, time, timestamp, DataLink, ROWID, identity columns, and columns with a field procedure cannot be used in a partitioning key.
INTO integer PARTITIONS: Specifies the number of partitions. The number of partitions must not exceed 256.

materialized-query-definition

column-name

FOR COLUMN system-column-name

Provides an IBM i name for the column. Do not use the same name for more than one column of the table or for a column-name of the table.

select-statement

The implicit definition includes the following attributes of the n columns (if applicable to the data type):

Column name (and system column name)
Data type, length, precision, and scale
CCSID
Nullability
FIELDPROC
Column heading and text (see LABEL)

The following attributes are not included:

Default value
Hidden attribute
Identity attributes
Row change timestamp attribute

The select-statement must not refer to variables or global variables, or include parameter markers. If an expression in the SELECT clause of the select-statement is not a column name, then the expression must not reference a column with a field procedure.

The select-statement must not contain a PREVIOUS VALUE or a NEXT VALUE expression. The UPDATE, SKIP LOCKED DATA, and USE AND KEEP EXCLUSIVE LOCKS clauses may not be specified.

refreshable-table-options

Specifies that the table is a materialized query table and the REFRESH TABLE statement can be used to populate the table with the results of the select-statement.

A materialized query table whose select-statement contains a GROUP BY clause is summarizing data from the tables referenced in the select-statement. Such a materialized query table is also known as a summary table. A summary table is a specialized type of materialized query table.

When a materialized query table is defined, the following select-statement restrictions apply:

The select-statement cannot contain a reference to another materialized query table or to a view that refers to a materialized query table.
The select-statement cannot contain a reference to a declared temporary table, a table in QTEMP, a program-described file, or a non-SQL logical file in the FROM clause.
The select-statement cannot contain a data-change-file-reference.
The select-statement cannot contain a reference to a view that references another materialized query table or a declared temporary table. When a materialized query table is defined with ENABLE QUERY OPTIMIZATION, the select-statement cannot contain a reference to a view that contains one of the restrictions from the following paragraph.
The select-statement cannot contain an expression with a DataLink or a distinct type based on a DataLink where the DataLink is FILE LINK CONTROL.
The select-statement cannot contain a result column that is a not an SQL data type, such as binary with precision, DBCS-ONLY, or DBCS-EITHER.

When a materialized query table is defined with ENABLE QUERY OPTIMIZATION, the following additional select-statement restrictions apply:

Must not include any special registers.
Must not include any non-deterministic functions.
The ORDER BY clause is allowed, but is only used by REFRESH. It may improve locality of reference of data in the materialized query table.
If the subselect references a view, the select-statement in the view definition must satisfy the preceding restrictions.

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 materialized query table.

DATA INITIALLY IMMEDIATE

Specifies that the data is inserted into the materialized query table when it is created.

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.

MAINTAINED BY USER

Specifies that the materialized query table is maintained by the user. The user can use INSERT, DELETE, UPDATE, or REFRESH TABLE statements on the table.

ENABLE QUERY OPTIMIZATION or DISABLE QUERY OPTIMIZATION

Specifies whether this materialized query table can be used for optimization. The default is ENABLE QUERY OPTIMIZATION.

ENABLE QUERY OPTIMIZATION: Specifies that the materialized query table can be used for query optimization. If the select-statement specified does not satisfy the restrictions for query optimization, an error is returned.
DISABLE QUERY OPTIMIZATION: Specifies that the materialized query table cannot be used for query optimization. The table can still be queried directly.

Start of change If row level or column level access control is activated for any table that is directly or indirectly referenced in the select-statement, row access control is implicitly activated for the table being created. This restricts direct access to the contents of the materialized query table. A query that explicitly references the table will return a warning that there is no data in the table. To provide access to the materialized query table, an appropriate row permission can be created or an ALTER TABLE DEACTIVATE ROW ACCESS CONTROL on the materialized query table can be issued to remove the row level protection if that is appropriate. End of change

Notes

Table attributes: Tables are created as physical files. When a table is created, the file wait time and record wait time attributes are set to the default that is specified on the WAITFILE and WAITRCD keywords of the Create Physical File (CRTPF) command.

SQL tables are created so that space used by deleted rows will be reclaimed by future insert requests. This attribute can be changed via the command CHGPF and specifying the REUSEDLT(*NO) parameter. For more information about the CHGPF command, see CL Reference.

A distributed table is created on all of the servers across which the table is distributed. For more information about distributed tables, see DB2 Multisystem.

Table journaling: When a table is created, journaling may be automatically started.

If a data area called QDFTJRN exists in the same schema that the table is created into and the user is authorized to the data area, journaling will be started to the journal named in the data area if all the following are true:
- The identified schema for the table must not be QSYS, QSYS2, QRECOVERY, QSPL, QRCL, QRPLOBJ, QGPL, QTEMP, SYSIBM, or any of the iASP equivalents to these libraries.
- The journal specified in the data area must exist and the user must be authorized to start journaling to the journal.
- The first 10 bytes of the data area must contain the name of the schema in which to find the journal.
- The second 10 bytes must contain the name of the journal.
- The remaining bytes contain the object types being implicitly journaled and the options that affect when implicit journaling is performed. The object type must include the value *FILE or *ALL. The value *NONE can be used to prevent journaling from being started.
For more information, see Journal Management.
If the table is created into a schema that has specified (using the STRJRNLIB command) that journaling should implicitly be started.
If a data area called QDFTJRN does not exist in the same schema that the table is created into or the user is not authorized to the data area and the schema has not specified that journaling should be started, journaling will be started to a journal called QSQJRN if it exists in the same schema that the table is created into.

Table ownership: If SQL names were specified:

If a user profile with the same name as the schema into which the table is created exists, the owner of the table is that user profile.
Otherwise, the owner of the table is the user profile or group user profile of the thread executing the statement.

If system names were specified, the owner of the table is the user profile or group user profile of the Start of change thread End of change executing the statement.

Table authority: If SQL names are used, tables are created with the system authority of *EXCLUDE to *PUBLIC. If system names are used, tables are created with the authority to *PUBLIC as determined by the create authority (CRTAUT) parameter of the schema.

If the owner of the table is a member of a group profile (GRPPRF keyword) and group authority is specified (GRPAUT keyword), that group profile will also have authority to the table.

REPLACE rules: When a table is recreated by REPLACE using PRESERVE ROWS, the new definition of the table is compared to the old definition and logically, for each difference between the two, a corresponding ALTER operation is performed. When the DELETE ROWS option is used, the table is logically dropped and recreated; as long as objects dependent on the table remain valid, any modification is allowed. For more information see Table 1 in ALTER TABLE. For columns, constraints, and partitions, the comparisons are performed based on their names and attributes.

Column names and system column names can be changed by modifying either the column name or the system column name, leaving the other name unchanged. If neither name matches an existing column, a new column is created. The existence of another object dependent on a column name may prevent the name change.

New definition vs existing definition	Equivalent ALTER TABLE action
Column
Column exists in both and the attributes are the same	No change
Column exists in both and the attributes are different	ALTER COLUMN
Column exists only in new table definition	ADD COLUMN
Column exists only in existing table definition	DROP COLUMN RESTRICT
Constraint
Constraint exists in both and is the same	No change
Constraint exists in both and is different	DROP constraint RESTRICT and ADD constraint
Constraint exists only in new table definition	ADD constraint
Constraint exists only in existing table definition	DROP constraint RESTRICT
materialized-query-definition
materialized-query-definition exists in both and is the same	No change
materialized-query-definition exists in both and is different	ALTER MATERIALIZED QUERY
materialized-query-definition exists only in new table definition	ADD MATERIALIZED QUERY
materialized-query-definition exists only is existing table definition	DROP MATERIALIZED QUERY
partitioning-clause
partitioning-clause exists in both and is the same	No change
partitioning-clause exists in both and is different	ADD PARTITION, DROP PARTITION, and ALTER PARTITION
partitioning-clause exists only in new table definition	ADD partitioning-clause
partitioning-clause exists only in existing table definition	DROP PARTITIONING
NOT LOGGED INITIALLY
NOT LOGGED INITIALLY exists in both	No change
NOT LOGGED INITIALLY exists only in new table definition	NOT LOGGED INITIALLY
NOT LOGGED INITIALLY exists only in existing table definition	Logged initially
VOLATILE
VOLATILE attribute exists in both and is the same	No change
VOLATILE attribute exists only in new table definition	VOLATILE
VOLATILE attribute exists only in existing table definition	NOT VOLATILE
media-preference
media-preference exists in both and is the same	No change
media-preference exists only in new table definition	ALTER media-preference
media-preference exists only in existing table definition	UNIT ANY
memory-preference
memory-preference exists in both and is the same	No change
memory-preference exists only in new table definition	ALTER memory-preference
memory-preference exists only in existing table definition	KEEP IN MEMORY NO

Any attributes that cannot be specified in the CREATE TABLE statement are preserved:

Authorized users are maintained. The object owner could change.
Current journal auditing is preserved. However, unlike other objects, REPLACE of a table will generate a ZC (change object) journal audit entry.
Current data journaling is preserved.
Comments and labels are preserved.
Triggers are preserved, if possible. If it is not possible to preserve a trigger, an error is returned.
Masks and permissions are preserved, if possible. If it is not possible to preserve a mask or permission, an error is returned.
Any views, materialized query tables, and indexes dependent on the table will be preserved or recreated, if possible. If it is not possible to preserve a dependent view, materialized query table, or index, an error is returned.

Using an identity column: When a table has an identity column, the database manager 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 the database manager generates. ROWID columns 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 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 (using RMVJRNCHG), it is possible that a large gap in the sequence of 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 the database manager subsequently generates values up to 1000. Now, assume that the table 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 from 100 to 1001 in the values of the identity column.

When CYCLE is specified duplicate values for a column may be generated even when the column is GENERATED ALWAYS, unless a unique constraint or unique index is defined on the column.

Creating materialized query tables: To ensure that the materialized query table has data before being used by a query:

DATA INITIALLY IMMEDIATE should be used to create materialized query tables, or
the materialized query table should be created with query optimization disabled and then enable the table for query optimization after it is refreshed.

The isolation level at the time when the CREATE TABLE statement is executed is the isolation level for the materialized query table. The isolation-clause can be used to explicitly specify the isolation level.

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 statement, CREATE INDEX statement, ALTER TABLE statement, INSERT statement, MERGE statement, or UPDATE 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'.

Partitioned table performance: The larger the number of partitions in a partitioned table, the greater the overhead in SQL data change and SQL data statements. You should create a partitioned table with the minimum number of partitions that are required to minimize this overhead. It is also highly recommended that a parallelism degree greater than one be considered when accessing a partitioned table.

Creating a table using a remote select-statement: The select-statement for an as-result-table can refer to tables on a different server than where the table is being created. This can be done by using a three-part object name or an alias that is defined to reference a three-part name of a table or view. The select-statement cannot be for a materialized query table and the result cannot contain a column that has a field procedure defined. If the remote server is DB2 for LUW or DB2 for z/OS®, copy-options are not allowed. If the remote server is DB2 for LUW, a column list should be explicitly specified before the AS keyword.

Syntax alternatives: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:

INLINE LENGTH is a synonym for ALLOCATE.
constraint-name (without the CONSTRAINT keyword) may be specified following the FOREIGN KEY keywords in a referential-constraint
DEFINITION ONLY is a synonym for WITH NO DATA
PARTITIONING KEY is a synonym for DISTRIBUTE BY HASH.
PART is a synonym for PARTITION.
PARTITION partition-number may be specified instead of PARTITION partition-name. A partition-number must not identify a partition that was previously specified in the CREATE TABLE statement.
If a partition-number is not specified, a unique partition number is generated by the database manager.
VALUES is a synonym for ENDING AT.
SUMMARY between CREATE and TABLE when creating a materialized query table.

Maximum row sizes

There are two maximum row size restrictions referred to in the description of column-definition.

The maximum row buffer size is 32766 or, if a VARCHAR, VARGRAPHIC, VARBINARY, LOB, or XML column is specified, 32740.
The maximum row data size is 3 758 096 383 if a LOB or XML column is specified; this size is determined when a row is inserted or updated. If a LOB or XML column is not specified, then the maximum row data size is 32766 or, if a VARCHAR, VARGRAPHIC, or VARBINARY column is specified, 32740.

To determine the length of a row buffer and row data or both, add the corresponding length of each column of that row based on the byte counts of the data type.

The follow table gives the byte counts of columns by data type for columns that do not allow null values. If any column allows null values, one byte is required for every eight columns. A column that has a field procedure could have a different count based on the result of the field procedure.

Table 1. Byte Counts of Columns by Data Type
Data Type	Row Buffer Byte Count	Row Data Byte Count
SMALLINT	2	2
INTEGER	4	4
BIGINT	8	8
DECIMAL( p, s)	The integral part of (p/2) + 1	The integral part of (p/2) + 1
NUMERIC( p, s)	p	p
FLOAT (single precision)	4	4
FLOAT (double precision)	8	8
DECFLOAT(16)	8	8
DECFLOAT(34)	16	16
CHAR( n)	n	n
VARCHAR( n)	n+2	n+2
CLOB( n)	29+pad	n+29
GRAPHIC(n)	n*2	n*2
VARGRAPHIC (n)	n*2+2	n*2+2
DBCLOB( n)	29+pad	n*2+29
BINARY( n)	n	n
VARBINARY( n)	n+2	n+2
BLOB( n)	29+pad	n+29
DATE	10	4
TIME	8	3
TIMESTAMP(p)	when p is 0, 19; otherwise 20+p	The integral part of ((p+1)/2) + 7
DATALINK( n)	n+24	n+24
ROWID	42	28
XML	29+pad	2 147 483 647
distinct-type	The byte count for the source type.	The byte count for the source type.
Notes: pad is a value from 1 to 15 necessary for boundary alignment.

Precision as described to the database:

Floating-point fields are defined in the Db2 for i database with a decimal precision, not a bit precision. The algorithm used to convert the number of bits to decimal is decimal precision = CEILING(n/3.31), where n is the number of bits to convert. The decimal precision is used to determine how many digits to display using interactive SQL.
SMALLINT fields are stored with a decimal precision of 4,0.
INTEGER fields are stored with a decimal precision of 9,0.
BIGINT fields are stored with a decimal precision of 19,0.

LONG VARCHAR, LONG VARGRAPHIC, and LONG VARBINARY

The non-standard syntax of LONG VARCHAR, LONG VARGRAPHIC, and LONG VARBINARY is supported, but deprecated. The alternative standard syntax of VARCHAR(integer), VARGRAPHIC(integer), and VARBINARY(integer) is preferred. VARCHAR(integer), VARGRAPHIC(integer), and VARBINARY(integer) are recommended. After the CREATE TABLE statement is processed, the database manager considers a LONG VARCHAR column to be VARCHAR, a LONG VARGRAPHIC column to be VARGRAPHIC, and a LONG VARBINARY column to be VARBINARY. The maximum length is calculated in a product-specific fashion that is not portable.

LONG VARCHAR ^¹: For a varying length character string whose maximum length is determined by the amount of space available in the row.
LONG VARGRAPHIC ^¹: For a varying length graphic string whose maximum length is determined by the amount of space available in the row.
LONG VARBINARY ^¹: For a varying length binary string whose maximum length is determined by the amount of space available in the row.

The maximum length of a LONG column is determined as follows. Let:

i be the sum of the row buffer byte counts of all columns in the table that are not LONG VARCHAR, LONG VARGRAPHIC, or LONG VARBINARY
j be the number of LONG VARCHAR, LONG VARGRAPHIC, and LONG VARBINARY columns in the table
k be the number of columns in the row that allow nulls.

The length of each LONG VARCHAR and LONG VARBINARY column is INTEGER((32716 - i-((k+7)/8))/j).

The length of each LONG VARGRAPHIC column is determined by taking the length calculated for a LONG VARCHAR column and dividing it by 2. The integer portion of the result is the length.

Rules for System Name Generation

There are specific instances when the system generates a system table, view, index, or column name. These instances and the name generation rules are described in the following sections.

Rules for Column Name Generation

A system-column-name is generated if the system-column-name is not specified when a table or view is created and the column-name is not a valid system-column-name.

If the column-name does not contain special characters and is longer than 10 characters, a 10-character system-column-name will be generated as:

The first 5 characters of the name
A 5 digit unique number

For example:

The system-column-name for LONGCOLUMNNAME would be LONGC00001

If the column name is delimited:

The first 5 characters from within the delimiters will be used as the first 5 characters of the system-column-name. If there are fewer than 5 characters within the delimiters, the name will be padded on the right with underscore (_) characters. Lower case characters are folded to upper case characters. The only valid characters in a system-column-name are: A-Z, 0-9, @, #, $, and _. Any other characters will be changed to the underscore (_) character. If the first character ends up as an underscore, it will be changed to the letter Q.
A 5 digit unique number is appended to the 5 characters.

For example:

   The system-column-name for "abc" would be ABC__00001
   The system-column-name for "COL2.NAME" would be COL2_00001
   The system-column-name for "C 3" would be C_3__00001
   The system-column-name for "??" would be Q____00001
   The system-column-name for "*column1" would be QCOLU00001

Rules for Table Name Generation

A system name will be generated if a table, view, alias, or index is created Start of change without using the FOR SYSTEM NAME clause and has either End of change :

A name longer than 10 characters
A name that contains characters not valid in a system name

The SQL name or its corresponding system name may both be used in SQL statements to access the file once it is created. However, the SQL name is only recognized by Db2 for i and the system name must be used in other environments.

There are two separate methods for generating the system name:

If a data area with the name QGENOBJNAM exists in the same schema that the table is created into, the user can influence the generated name.
The data area is subject to the following restrictions:
- The user must be authorized to read the data area.
- The data area must have an attribute of CHAR(10).
- The first 5 characters of the data area value must be '?????'.
- The next 5 characters of the data area value must contain 5 numeric digits.
If any of the above conditions are not satisfied or any error occurs while accessing the starting value in the data area, the default name generation rules will be used as if the data area did not exist at all.

If the data area meets all of the restrictions above, the generated name will be the same as if the default name generation rules below except that after the first 5 (or 4) characters of the name, the unique number will initially contain the 5 digits specified in the data area (instead of '00001' or '0001').

For example, if the value of the data area was '?????00999':
```
   The system name for "??" would be "__00999"
   The system name for "longtablename" would be "lon00999"
   The system name for "LONGTableName" would be LONG00999
   The system name for "A b   " would be "A_b00999"
```
Otherwise, the default name generation rules are used:
If the name does not contain special characters and is longer than 10 characters, a 10-character system name will be generated as:
- The first 5 characters of the name
- A 5 digit unique number
For example:
```
   The system name for LONGTABLENAME would be LONGT00001
```
If the SQL name contains special characters, the system name is generated as:
- The first 4 characters of the name
- A 4 digit unique number
In addition:
- All special characters are replaced by the underscore (_)
- Any trailing blanks are removed from the name
- The name is delimited by double quotes (") if the delimiters are required for the name to be a valid system name.
For example:
```
   The system name for "??" would be "__0001"
   The system name for "longtablename" would be "long0001"
   The system name for "LONGTableName" would be LONG0001
   The system name for "A b   " would be "A_b0001"
```
SQL ensures the system name is unique by searching the cross reference file. If the name already exists in the cross reference file, the number is incremented until the name is no longer a duplicate.

If a unique name cannot be determined using the above rules, an additional character is added to the counter in the name, and the number is incremented until a unique name can be found or the range is exhausted. For example, if creating "longtablename" and names "long0001" through "long9999" already exist, the name would become "lon00001".

Examples

Example 1: Given database administrator authority, create a table named ‘ROSSITER.INVENTORY' with the following columns:

Part number: Small integer, must not be null
Description: Character of length 0 to 24, allows nulls
Quantity on hand,: Integer allows nulls

  CREATE TABLE ROSSITER.INVENTORY
    (PARTNO         SMALLINT     NOT NULL,
     DESCR          VARCHAR(24),
     QONHAND        INT)

Example 2: Create a table named DEPARTMENT with the following columns:

Department number: Character of length 3, must not be null
Department name: Character of length 0 through 36, must not be null
Manager number: Character of length 6
Administrative dept.: Character of length 3, must not be null
Location name: Character of length 16, allows nulls

The primary key is column DEPTNO.

  CREATE TABLE DEPARTMENT
    (DEPTNO    CHAR(3)     NOT NULL,
     DEPTNAME  VARCHAR(36) NOT NULL,
     MGRNO     CHAR(6),
     ADMRDEPT  CHAR(3)     NOT NULL,
     LOCATION  CHAR(16),
     PRIMARY KEY(DEPTNO))

Example 3: Create a table named REORG_PROJECTS which has the same column definitions as the columns in the view PRJ_LEADER.

  CREATE TABLE REORG_PROJECTS
    LIKE PRJ_LEADER

Example 4: Create an EMPLOYEE2 table with an identity column named EMP_NO. Define the identity column so that Db2 for i 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 EMPLOYEE2
    ( EMPNO INTEGER GENERATED ALWAYS AS IDENTITY,
      ID SMALLINT,
      NAME CHAR(30),
      SALARY DECIMAL(5,2),
      DEPTNO SMALLINT)

Example 5: 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 contains 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
    MAINTAINED BY USER

¹ This option is provided for compatibility with other products. It is recommended that VARCHAR(integer), VARGRAPHIC(integer), or VARBINARY(integer) be specified instead.