CREATE TABLE

The CREATE TABLE statement defines a table. 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 following system authorities:
- *USE to the Create Physical File (CRTPF) command
- *EXECUTE and *ADD to the library into which the table is created
- *OBJOPR and *OBJMGT to the journal
- *CHANGE to the data dictionary if the library into which the table is created is an SQL collection with a data dictionary
Administrative 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
Administrative 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
Administrative authority

The authorization ID of the statement has the REFERENCES privilege on a table when one of the following is true:

It is the owner of the table.
It was granted the REFERENCES privilege to the table.
It was granted the system authorities of either *OBJREF or *OBJMGT to the table.

The authorization ID of the statement has the REFERENCES privilege on a column of the table when one of the following is true:

It is the owner of the table.
It was granted the REFERENCES privilege to the column.
It was granted the system authority of *OBJREF to the column or the system authority of *OBJMGT to the table.

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

For each user-defined type identified in the statement:
- The USAGE privilege on the user-defined type, and
- The system authority *EXECUTE on the library containing the user-defined type
Administrative authority

The authorization ID of the statement has the USAGE privilege on a user-defined type when one of the following is true:

It is the owner of the user-defined type.
It was granted the USAGE privilege to the user-defined type.
It was granted the system authorities of *OBJOPR and *EXECUTE to the user-defined type.

If a user-defined cast function is referenced, the privileges held by the authorization ID of the statement must include at least one of the following:

For each user-defined function identified in the statement:
- The EXECUTE privilege on the user-defined function, and
- The system authority *EXECUTE on the library containing the user-defined function
Administrative authority

The authorization ID of the statement has the EXECUTE privilege on a user-defined function when one of the following is true:

It is the owner of the user-defined function.
It was granted the EXECUTE privilege to the user-defined function.
It was granted the system authorities of *OBJOPR and *EXECUTE to the user-defined function.

Syntax

>>-CREATE TABLE--table-name--(----------------------------------> .-,-----------------------------. V | >---------+-column-definition------+--+--)----------------------> +-unique-constraint------+ +-referential-constraint-+ '-check-constraint-------' >-----+-------------------+------------------------------------>< '-nodegroup-clause--' column-definition |---column-name----+-------------------------------------+------> | .-COLUMN-. | '-FOR-+--------+--system-column-name--' >----data-type--------------------------------------------------> .----------------------------------------------------------------------------. V | >--------+----------------------------------------------------------------------+--+> +-NOT NULL-------------------------------------------------------------+ +-default-clause-------------------------------------------------------+ | (1) | +-datalink-options-----------------------------------------------------+ '-+------------------------------+---+-+-UNIQUE------+---------------+-' '-CONSTRAINT--constraint-name--' | '-PRIMARY KEY-' | +-references-clause-------------+ '-CHECK--(--check-condition--)--' >---------------------------------------------------------------|

Notes:

The datalink-options can only be specified for DATALINKs and
distinct-types sourced on DATALINKs.

data-type |--+---BIGINT-------------------------------------------------------------------------------------------------------+-> +-+-INTEGER-+----------------------------------------------------------------------------------------------------+ | '-INT-----' | +---SMALLINT-----------------------------------------------------------------------------------------------------+ +--+-DECIMAL-+---+-------------------------------+---------------------------------------------------------------+ | +-DEC-----+ '-(--integer--+-----------+--)--' | | '-NUMERIC-' '-, integer-' | +-+-FLOAT--+----------------+-+----------------------------------------------------------------------------------+ | | '-(--integer--)--' | | | +-REAL----------------------+ | | | .-PRECISION-. | | | '-DOUBLE-+-----------+------' | +----+-BLOB----------------+-----+------------------------+---+------------------+-------------------------------+ | '-BINARY LARGE OBJECT-' '-(--integer--+---+---)--' '-allocate-clause--' | | +-K-+ | | '-M-' | +--+--+-CHARACTER-+---+----------------+-------------------------------------------------+---+-----------------+-+ | | '-CHAR------' '-(--integer--)--' | +-FOR BIT DATA----+ | | +--+--+-VARCHAR-------------------+---(--integer--)--+---+------------------+---------+ +-FOR SBCS DATA---+ | | | | '--+-CHARACTER-+---VARYING--' | '-allocate-clause--' | +-FOR MIXED DATA--+ | | | | '-CHAR------' | | '-CCSID--integer--' | | | '-LONG VARCHAR------------------------------------' | | | '----+-CLOB-------------------+-----+------------------------+---+------------------+-' | | +-CHAR LARGE OBJECT------+ '-(--integer--+---+---)--' '-allocate-clause--' | | '-CHARACTER LARGE OBJECT-' +-K-+ | | '-M-' | +--+----GRAPHIC-----+----------------+---------------------------------+---+-----------------+-------------------+ | | '-(--integer--)--' | '-CCSID--integer--' | | +--+--+-VARGRAPHIC------+---(--integer--)--+---+------------------+-+ | | | | '-GRAPHIC VARYING-' | '-allocate-clause--' | | | | '-LONG VARGRAPHIC-----------------------' | | | '----DBCLOB------+------------------------+---+------------------+--' | | '-(--integer--+---+---)--' '-allocate-clause--' | | +-K-+ | | '-M-' | +---DATE---------------------------------------------------------------------------------------------------------+ +---TIME---------------------------------------------------------------------------------------------------------+ +---TIMESTAMP----------------------------------------------------------------------------------------------------+ +---DATALINK--+----------------+---+------------------+---+-----------------+------------------------------------+ | '-(--integer--)--' '-allocate-clause--' '-CCSID--integer--' | '---distinct-type------------------------------------------------------------------------------------------------' >---------------------------------------------------------------| allocate-clause |---ALLOCATE--(integer)-----------------------------------------|

default-clause .-WITH-. |--+------+--DEFAULT--+--------------------------------------------------------+-> +-constant-----------------------------------------------+ +-NULL---------------------------------------------------+ +-USER---------------------------------------------------+ +-CURRENT_DATE-------------------------------------------+ +-CURRENT_TIME-------------------------------------------+ +-CURRENT_TIMESTAMP--------------------------------------+ | (1) | '-cast-function-name--(--+-constant----------+---)-------' +-USER--------------+ +-CURRENT_DATE------+ +-CURRENT_TIME------+ '-CURRENT_TIMESTAMP-' >---------------------------------------------------------------| datalink-options .-NO LINK CONTROL--------------------------. |---LINKTYPE URL--+------------------------------------------+--| '-FILE LINK CONTROL--+-file-link-options-+-' '-MODE DB2OPTIONS---' file-link-options .-----------------------------------. V (2) | |-----+------------------------------+--+-----------------------| +-INTEGRITY ALL----------------+ +-+-READ PERMISSION FS-+-------+ | '-READ PERMISSION DB-' | +-+-WRITE PERMISSION FS------+-+ | '-WRITE PERMISSION BLOCKED-' | +-RECOVERY NO------------------+ '-+-ON UNLINK RESTORE-+--------' '-ON UNLINK DELETE--'

Notes:

This form of the DEFAULT value can only be used with columns that are
defined as a distinct type.

The file-link-options can be specified in any order.

unique-constraint |--+------------------------------+---+-UNIQUE------+--(--------> '-CONSTRAINT--constraint-name--' '-PRIMARY KEY-' .-,--------------. V | >--------column-name---+--)-------------------------------------| referential-constraint (1) |---+------------------------------+--FOREIGN KEY---------------> '-CONSTRAINT--constraint-name--' .-,--------------. V | >----(-----column-name---+---)---references-clause--------------| references-clause |---REFERENCES--table-name----+----------------------------+----> | .-,--------------. | | V | | '-(-----column-name---+---)--' (2) .-ON DELETE NO ACTION--------. .-ON UPDATE NO ACTION--. >-----+----------------------------+---+----------------------+-| '-ON DELETE--+-RESTRICT----+-' '-ON UPDATE RESTRICT---' +-CASCADE-----+ +-SET NULL----+ '-SET DEFAULT-' check-constraint |---+------------------------------+----------------------------> '-CONSTRAINT--constraint-name--' >----CHECK--(--check-condition--)-------------------------------| nodegroup-clause |---IN----nodegroup-name-----+------------------------------------------------------------------+-> | .-,--------------. | | V | | | .-USING HASHING-. | '-PARTITIONING KEY--(-----column-name---+---)---+---------------+--' >---------------------------------------------------------------|

Notes:

For compatibility with other products, constraint-name (without the
CONSTRAINT keyword) may be specified following FOREIGN KEY.

The ON DELETE and ON UPDATE clauses may be specified in either
order.

Description

table-name

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

If SQL names were specified, the table will be created in the collection or library specified by the implicit or explicit qualifier. The qualifier is the owner of the table if a user profile with that name exists. Otherwise, the owner of the table is the user profile or group user profile of the job executing the statement.

If system names were specified, the table will be created in the collection or library that is specified by the qualifier. If not qualified, the table will be created in the current library (*CURLIB). If there is no current library, the table will be created in QGPL. The owner of the table is the user profile or group user profile of the job executing the statement.

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.

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 record buffer byte counts of the columns must not be greater than 32766 or, if a VARCHAR or VARGRAPHIC column is specified, 32740. Additionally, if a LOB is specified, the sum record data byte counts of the columns must not be greater than 15 728 640. For information on the byte counts of columns according to data type, see Notes.

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 OS/400 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 on how system column names are generated, see "Rules for Column Name Generation".

data-type

Specifies the data type of the column. Use:

BIGINT

For a big integer.

INTEGER or INT

For a large integer.

SMALLINT

For a small 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 31. 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)

NUMERIC(integer)

NUMERIC

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

REAL

For single-precision floating point.

DOUBLE PRECISION or DOUBLE

For double-precision floating point.

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

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 15 728 640. 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 15 728 640. The maximum length of the string is integer.
integer K: The maximum value for integer is 15 360. The maximum length of the string is 1024 times integer.
integer M: The maximum value for integer is 15. The maximum length of the string is 1 048 576 times integer.

CHARACTER(integer) or CHAR(integer)

CHARACTER or CHAR

For a fixed-length character string of length integer. 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 character is assumed.

VARCHAR(integer)

CHARACTER VARYING (integer) or CHAR VARYING (integer)

For a varying-length character string of maximum length integer, 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).

LONG VARCHAR ³⁷

For a varying length character string whose maximum length is determined by the amount of space available in the row. For information on how to calculate the maximum length, see Rules for determining LONG VARCHAR and LONG VARGRAPHIC size.

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

CLOB or CHAR LARGE OBJECT or CHARACTER LARGE OBJECT

For a character large object string of the specified maximum length. The maximum length must be in the range of 1 through 15 728 640. If FOR MIXED DATA or a mixed data CCSID is specified, the range is 4 through 15 728 640. 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 15 728 640. The maximum length of the string is integer.
integer K: The maximum value for integer is 15 360. The maximum length of the string is 1024 times integer.
integer M: The maximum value for integer is 15. The maximum length of the string is 1 048 576 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 character 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).

LONG VARGRAPHIC ³⁷

For a varying length graphic string whose maximum length is determined by the amount of space available in the row. For information on how to calculate the maximum length, see Rules for determining LONG VARCHAR and LONG VARGRAPHIC size.

DBCLOB(integer[K|M])

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 7 864 320. 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 7 864 320. The maximum length of the string is integer.
integer K: The maximum value for integer is 7 680. The maximum length of the string is 1024 times integer.
integer M: The maximum value for integer is 7. The maximum length of the string is 1 048 576 times integer.

DATE

For a date.

TIME

For a time.

TIMESTAMP

For a timestamp.

DATALINK(integer) or DATALINK

For a DataLink of the specified maximum length. The maximum length must be in the range of 1 through 32718 (32717 if null capable). If FOR MIXED DATA or a mixed data CCSID is specified, the range is 4 through 32718 (32717 if null capable). 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.

distinct-type

For a user-defined type that is a distinct type. The length and scale of the column are respectively the length and scale of the source type of the distinct type. If a distinct type name is specified without a collection name, the distinct type name is resolved by searching the collections on the SQL path.

ALLOCATE(integer)

Specifies for VARCHAR, VARGRAPHIC, 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 record buffer size limit. For information on the maximum record buffer size, see Maximum record sizes. If FOR MIXED 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 UCS-2 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

Indicates 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 SBCS DATA

Indicates 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

Indicates 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 database 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

Indicates 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 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 a list of valid CCSIDs, see Appendix E, CCSID Values.

NOT NULL

Prevents the column from containing null values. Omission of NOT NULL implies that the column can be null.

DEFAULT

Specifies a default value for the column. This clause cannot be specified more than once in a column-definition. If a value is not specified and the column is nullable, the default value is the null value. If a value is not specified and the column is not nullable, the default depends on the data type of the column:

Data type	Default value
Numeric	0
Fixed-length string	Blanks
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 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.

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.

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 greater than or equal to 18.

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.

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.

CURRENT_TIMESTAMP

Specifies the current timestamp as the default for the column. If CURRENT_TIMESTAMP is specified, the data type of the column must be TIMESTAMP.

cast-function-name

This form of a default value can only be used with columns defined as a distinct type, 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 BLOB, CLOB, or DBCLOB

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) **

BLOB, CLOB, or DBCLOB

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 collection name, it must be the same as the collection name for the distinct type. If not qualified, the collection name from function resolution must be the same as the collection 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 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 18.
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: 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.

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 or not DB2 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

CONSTRAINT constraint-name

Names the constraint. A constraint-name 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. 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 key composed of a single column. Thus, if UNIQUE is specified in the definition of column C, the effect is the same as if the UNIQUE(C) clause is specified as a separate clause.

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.

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.

CHECK(check-condition)

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.

unique-constraint

CONSTRAINT constraint-name

Names the constraint. A constraint-name 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(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 or DATALINK column. The number of identified columns must not exceed 120, and the sum of their lengths must not exceed 2000-n, where n is the number of columns specified that allow nulls. The unique index is created as part of the AS/400 physical file, not a separate AS/400 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 key 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 or DATALINK column. The number of identified columns must not exceed 120, and the sum of their lengths must not exceed 2000-n, where n is the number of columns specified that allows nulls.

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 AS/400 physical file, not as a separate AS/400 logical file.

referential-constraint

CONSTRAINT constraint-name

Names the constraint. A constraint-name 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.

FOREIGN KEY

Each specification of the FOREIGN KEY clause defines a referential constraint.

(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 or DATALINK column. The number of identified columns must not exceed 120, and the sum of their lengths must not exceed 2000-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 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 and lengths.

(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 or DATALINK column. The number of identified columns must not exceed 120, and the sum of their lengths must not exceed 2000-n, where n is the number of columns specified that allow nulls.
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.

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.
If SET DEFAULT is specified, each column of the foreign key of each dependent of p in T1 is set to its default value.

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 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
It must not contain any of the following:
- Subqueries
- Column functions
- Host variables
- Parameter markers
- CURRENT TIMEZONE, CURRENT SERVER, CURRENT PATH, and USER special registers
- NODENAME scalar function
- User-defined functions
- Functions that were implicitly generated with the creation of a distinct type
- DLVALUE, DLURLPATH, DLURLPATHONLY, DLURLSERVER, or DLURLSCHEME scalar functions
- DLURLCOMPLETE scalar function (for DataLinks with an attribute of FILE LINK CONTROL and READ PERMISSION DB)

For more information on search-condition, see Search Conditions. For more information on check constraints involving LOB data types and expressions, see the Database Programming book.

nodegroup-clause

IN nodegroup-name

Specifies the nodegroup across which the data in the table will be partitioned. 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 AS/400 systems in the nodegroup.

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

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

PARTITIONING KEY(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 PARTITIONING KEY 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 floating point, 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, date, time, and timestamp columns cannot be used in a partitioning key.

USING HASHING

Specifies that the data in the partitioning key will be hashed in order to distribute the row to the appropriate AS/400 in the nodegroup.

Notes

Tables are created as physical files. If SQL names are used, tables are created with the system authority of *EXCLUDE on *PUBLIC. If system names are used, tables are created with the authority to *PUBLIC as determined by the create authority (CRTAUT) parameter of the collection or library. When a table is created, journaling is automatically started on the journal named QSQJRN in the collection or library.

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.

Maximum record sizes

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

The maximum record buffer size is 32766 or, if a VARCHAR, VARGRAPHIC, or LOB column is specified, 32740.
The maximum record data size is 15 728 640, if a LOB is specified. If a LOB is not specified, then the maximum record data size is 32766 or, if a VARCHAR or VARGRAPHIC column is specified, 32740.

To determine the length of a record buffer and/or record data add the corresponding length of each column of that record 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.

Data Type Record Buffer Byte Count Record 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
BLOB( n) 29+pad n+29
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
DATE 10 4
TIME 8 3
TIMESTAMP 26 10
DATALINK( n) n+24 n+24
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 AS/400 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.

Rules for determining LONG VARCHAR and LONG VARGRAPHIC size

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

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

The length of each LONG VARCHAR column is INTEGER((m-24-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.

Reuse of deleted records

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 AS/400 command CHGPF and specifying the REUSEDLT(*NO) parameter. For more information on the CHGPF command, see the CL Reference information in the Programming category of the AS/400 Information Center.

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 with either:

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 UDB for AS/400 and the system name must be used in other environments.

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.

Examples

Example 1

Given that you have administrative authority, create a table named 'ROSSITER.INVENTORY' with the following columns:

Part number: Integer between 1 and 9 999, must not be null
Description: Character of length 0 to 24
Quantity on hand: Integer between 0 and 100000

The primary key is PARTNO.

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

Example 2

Create a table named CORPDATA.DEPT 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 department: Character of length 3, must not be null

   CREATE TABLE CORPDATA.DEPT
                (DEPTNO    CHAR(3)     NOT NULL,
                 DEPTNAME  VARCHAR(36) NOT NULL,
                 MGRNO     CHAR(6),
                 ADMRDEPT  CHAR(3)     NOT NULL)

Footnotes:

³⁷: This option is provided for compatibility with other products. It is recommended that VARCHAR(integer) or VARGRAPHIC(integer) be specified instead.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]