ADD COLUMN column-definition

Adds a column to the table. If the table has rows, every value of the column is set to its default value, unless the column is a ROWID column or an identity column (a column that is defined AS IDENTITY). The database manager generates default values for ROWID columns and identity columns. If the table previously had n columns, the ordinality of the new column is n+1. The value of n+1 must not exceed 8000.

A table can have only one ROWID or identity column.

A DataLink column with FILE LINK CONTROL cannot be added to a table that is a dependent in a referential constraint with a delete rule of CASCADE.

Adding a new column must not make the sum of the row buffer byte counts of the columns be greater than 32766 or, if a VARCHAR or VARGRAPHIC column is specified, 32740. Additionally, if a LOB is specified, the sum of the 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.

column-name
Names the column to be added to the table. Do not use the same name for more than one column of the table or for a system-column-name of the table. Do not qualify column-name.
FOR COLUMN system-column-name
Provides an i5/OS name for the column. Do not use the same name for more than one column-name or system-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. The data type can be a built-in data type or a distinct type.
built-in-type
Specifies a built-in data type. See CREATE TABLE for a description of built-in types.
distinct-type-name
Specifies the data type of a column is a distinct 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. If the column is to be used in the definition of the foreign key of a referential constraint, the data type of the corresponding column of the parent key must have the same distinct type.
DEFAULT
Specifies a default value for the column. This clause cannot be specified more than once in the same column-definition. DEFAULT cannot be specified for a ROWID column or an identity column (a column that is defined AS IDENTITY). The database manager generates default values for ROWID columns and identity columns. If a value is not specified following the DEFAULT keyword, then:

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, BIGINT, 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 for 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. For existing rows, the value is that of the USER special register at the time the ALTER TABLE statement is processed.
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.
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
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.
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 that is greater than or equal to the length attribute of USER. For existing rows, the value is that of the USER special register at the time the ALTER TABLE statement is processed.
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.

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). 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 into the table. ALWAYS is the recommended value.
BY DEFAULT
Specifies that the database manager will generate a value for the column when a row is inserted only if a value is not specified for the column. If a value is specified, the database manager uses that value.

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.

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 partitioned table or 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. See the AS IDENTITY clause in CREATE TABLE for the descriptions of the identity attributes.

A column in a table cannot be altered to an identity column if the table is a DDS-created physical file.

datalink-options
Specifies the options associated with a DATALINK column. See CREATE TABLE for a description of datalink-options.
NOT NULL
Prevents the column from containing null values. Omission of NOT NULL implies that the column can contain null values. If NOT NULL is specified in the column definition, then DEFAULT must also be specified.
column-constraint
The column-constraint of a column-definition provides a shorthand method of defining a constraint composed of a single column. Thus, if a column-constraint is specified in the definition of column C, the effect is the same as if that constraint were specified as a unique-constraint, referential-constraint or check-constraint in which C is the only identified column.
CONSTRAINT constraint-name
Names the constraint. 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. 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.

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 or DataLink column.

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. A references-clause is not allowed if the table is a global temporary table, a partitioned table, or a distributed table.
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.

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