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 or VARGRAPHIC column is specified, 32740. Additionally, if a LOB 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 on the byte counts of columns according to data type, see Notes.
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.
You can use DECIMAL(p) for DECIMAL(p,0), and DECIMAL for DECIMAL(5,0).
You can use NUMERIC(p) for NUMERIC(p,0), and NUMERIC for NUMERIC(5,0).
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.
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.
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.
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.
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.
NULL is the only default value allowed for a datalink column.
If the value specified is not valid, an error is returned.
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 DB2 UDB for z/OS or DB2 UDB for iSeries.
For an identity column, the database manager inserts a specified value but does not verify that it is a unique value for the column unless the identity column has a unique constraint or a unique index that solely specifies the identity column.
An identity column is implicitly NOT NULL.
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.
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.
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.
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.
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.
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.
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.
If the clause is not specified, a unique constraint name is generated by the database manager.
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 or DATALINK column.
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.
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 or DATALINK column.
ROWID or DATALINK with FILE LINK CONTROL columns cannot be referenced in a CHECK constraint. For additional restrictions see, check-constraint.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.