The ALTER TABLE statement alters the definition of a table.
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
The privileges held by the authorization ID of the statement must include at least one of the following:
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:
If a field procedure is defined, the privileges
held by the authorization ID of the statement must include at least
one of the following: 

If a 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:
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 information on 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.
>>-ALTER TABLE--table-name--------------------------------------> .------------------------------------------------------------------------------. V .-COLUMN-. | >------+-ADD--+--------+--column-definition--+---------------------+----------+---+->< | '-BEFORE--column-name-' | | .-COLUMN-. | +-ALTER--+--------+--column-alteration---------------------------------+ | .-COLUMN-. .-CASCADE--. | +-DROP----+--------+--column-name--+----------+------------------------+ | '-RESTRICT-' | +-ADD--+-unique-constraint------+--------------------------------------+ | +-referential-constraint-+ | | '-check-constraint-------' | | .-CASCADE--. | +-DROP--+-PRIMARY KEY----------------------+--+----------+-------------+ | '-+-UNIQUE------+--constraint-name-' '-RESTRICT-' | | +-FOREIGN KEY-+ | | +-CHECK-------+ | | '-CONSTRAINT--' | +-ADD--partitioning-clause---------------------------------------------+ +-DROP PARTITIONING----------------------------------------------------+ +-ADD PARTITION--add-partition-----------------------------------------+ +-ALTER PARTITION--partition-name--boundary-spec--+------------------+-+ | '-media-preference-' | +-DROP PARTITION--partition-name--+-DELETE ROWS---+--------------------+ | '-PRESERVE ROWS-' | | .-MATERIALIZED-. | | .-+--------------+--QUERY-. | +-ADD--+-------------------------+--materialized-query-definition------+ | .-MATERIALIZED-. | +-ALTER--+--------------+--QUERY--materialized-query-table-alteration--+ | .-MATERIALIZED-. | +-DROP--+--------------+--QUERY----------------------------------------+ +-ACTIVATE--NOT LOGGED INITIALLY--+------------------+-----------------+ | '-WITH EMPTY TABLE-' | | .-CARDINALITY-. | +-+-VOLATILE-----+--+-------------+------------------------------------+ | '-NOT VOLATILE-' | '-ALTER--media-preference----------------------------------------------' media-preference .-UNIT ANY-. |--+-UNIT SSD-+-------------------------------------------------|
column-definition |--column-name--+-------------------------------------+---------> | .-COLUMN-. | '-FOR--+--------+--system-column-name-' (1) (2) >--data-type----------------------------------------------------> .----------------------------------------------------------------------. V | >----+------------------------------------------------------------------+-+--| +-default-clause---------------------------------------------------+ | .-GENERATED ALWAYS-----. (3) | +-+----------------------+------+--------------------------------+-+ | '-GENERATED BY DEFAULT-' +-identity-options---------------+ | | '-as-row-change-timestamp-clause-' | +-NOT NULL---------------------------------------------------------+ | .-NOT HIDDEN--------. | +-+-------------------+--------------------------------------------+ | '-IMPLICITLY HIDDEN-' | +-column-constraint------------------------------------------------+ +-FIELDPROC--external-program-name--+--------------------+---------+ | | .-,--------. | | | | V | | | | '-(----constant-+--)-' | | (4) | '-datalink-options-------------------------------------------------' data-type |--+-built-in-type------+---------------------------------------| '-distinct-type-name-'
built-in-type |--+-+---SMALLINT---+----------------------------------------------------------------------------------------------------------+--| | +-+-INTEGER-+--+ | | | '-INT-----' | | | '---BIGINT-----' | | .-(5,0)------------------------. | +-+-+-DECIMAL-+-+--+------------------------------+-------------------------------------------------------------------------+ | | '-DEC-----' | | .-,0--------. | | | '-+-NUMERIC-+-' '-(--integer--+-----------+--)-' | | '-NUM-----' '-, integer-' | | .-(--52--)------. | +-+-FLOAT--+---------------+-+----------------------------------------------------------------------------------------------+ | | '-(--integer--)-' | | | +-REAL---------------------+ | | | .-PRECISION-. | | | '-DOUBLE--+-----------+----' | | .-(--34--)-. | +---DECFLOAT--+----------+--------------------------------------------------------------------------------------------------+ | '-(--16--)-' | | .-(--1--)-------. | +-+-+-+-CHARACTER-+--+---------------+-------------------------------+--+----------------+-----------------+----------------+ | | | '-CHAR------' '-(--integer--)-' | +-FOR BIT DATA---+ | | | | '-+-+-CHARACTER-+--VARYING-+--(--integer--)--+-----------------+-' +-FOR SBCS DATA--+ | | | | | '-CHAR------' | '-allocate-clause-' +-FOR MIXED DATA-+ | | | | '-VARCHAR----------------' '-ccsid-clause---' | | | | .-(--1M--)-------------. | | | '---+-+-CHARACTER-+--LARGE OBJECT-+----+----------------------+--+-----------------+--+----------------+-' | | | '-CHAR------' | '-(--integer--+---+--)-' '-allocate-clause-' +-FOR SBCS DATA--+ | | '-CLOB------------------------' +-K-+ +-FOR MIXED DATA-+ | | +-M-+ '-ccsid-clause---' | | '-G-' | | .-(--1--)-------. | +-+---GRAPHIC----+---------------+----------------------------+--+--------------+-------------------------------------------+ | | '-(--integer--)-' | '-ccsid-clause-' | | +-+-GRAPHIC VARYING-+--(--integer--)--+-----------------+---+ | | | '-VARGRAPHIC------' '-allocate-clause-' | | | | .-(--1M--)-------------. | | | '---DBCLOB----+----------------------+--+-----------------+-' | | '-(--integer--+---+--)-' '-allocate-clause-' | | +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-+-NATIONAL CHARACTER-+--+---------------+-------------------------------+---------------------+--+------------------+-+ | | | +-NATIONAL CHAR------+ '-(--integer--)-' | | '-normalize-clause-' | | | | '-NCHAR--------------' | | | | | '-+-+-NATIONAL CHARACTER-+--VARYING-+--(--integer--)--+-----------------+-' | | | | | +-NATIONAL CHAR------+ | '-allocate-clause-' | | | | | '-NCHAR--------------' | | | | | '-NVARCHAR------------------------' | | | | .-(--1M--)-------------. | | | '-----+-+-NATIONAL CHARACTER-+--LARGE OBJECT-+------+----------------------+--+-----------------+-' | | | '-NCHAR--------------' | '-(--integer--+---+--)-' '-allocate-clause-' | | '-NCLOB--------------------------------' +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-BINARY--+---------------+------------------------------+-----------------+--------------------------------------------+ | | | '-(--integer--)-' | | | | | '-+-BINARY VARYING-+--(--integer--)--+-----------------+-' | | | | '-VARBINARY------' '-allocate-clause-' | | | | .-(--1M--)-------------. | | | '---+-BINARY LARGE OBJECT-+----+----------------------+--+-----------------+-' | | '-BLOB----------------' '-(--integer--+---+--)-' '-allocate-clause-' | | +-K-+ | | +-M-+ | | '-G-' | +-+-DATE-------------------+------------------------------------------------------------------------------------------------+ | | .-(--0--)-. | | | +-TIME--+---------+------+ | | | .-(--6--)-. | | | '-TIMESTAMP--+---------+-' | | .-(--200--)-----. | +---DATALINK--+---------------+--+-----------------+--+--------------+------------------------------------------------------+ | '-(--integer--)-' '-allocate-clause-' '-ccsid-clause-' | +---ROWID-------------------------------------------------------------------------------------------------------------------+ '---XML--+-----------------+--+--------------+------------------------------------------------------------------------------' '-allocate-clause-' '-ccsid-clause-'
allocate-clause |--ALLOCATE--(integer)------------------------------------------| ccsid-clause |--CCSID--integer--+------------------+-------------------------| '-normalize-clause-' normalize-clause .-NOT NORMALIZED-. |--+-NORMALIZED-----+-------------------------------------------| default-clause .-WITH-. |--+------+--DEFAULT--+-------------------------------------------------+--| +-constant----------------------------------------+ +-USER--------------------------------------------+ +-NULL--------------------------------------------+ +-CURRENT_DATE------------------------------------+ +-CURRENT_TIME------------------------------------+ +-CURRENT_TIMESTAMP-------------------------------+ '-cast-function-name--(--+-constant----------+--)-' +-USER--------------+ +-CURRENT_DATE------+ +-CURRENT_TIME------+ '-CURRENT_TIMESTAMP-' identity-options |--AS IDENTITY--+------------------------------------------------------+--| | .--------------------------------------------. | | V .-1----------------. (1) | | '-(----+-START WITH--+-numeric-constant-+---+-----+--)-' | .-1----------------. | +-INCREMENT BY--+-numeric-constant-+-+ | .-NO MINVALUE----------------. | +-+-MINVALUE--numeric-constant-+-----+ | .-NO MAXVALUE----------------. | +-+-MAXVALUE--numeric-constant-+-----+ | .-NO CYCLE-. | +-+-CYCLE----+-----------------------+ | .-CACHE--20------. | +-+-NO CACHE-------+-----------------+ | '-CACHE--integer-' | | .-NO ORDER-. | '-+-ORDER----+-----------------------'
as-row-change-timestamp-clause |--FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP---------------| column-constraint |--+-----------------------------+--+-+-PRIMARY KEY-+--------------+--| '-CONSTRAINT--constraint-name-' | '-UNIQUE------' | +-references-clause------------+ '-CHECK--(--check-condition--)-' datalink-options .-LINKTYPE URL-. .-NO LINK CONTROL--------------------------. |--+--------------+--+------------------------------------------+--| '-FILE LINK CONTROL--+-file-link-options-+-' '-MODE DB2OPTIONS---' file-link-options .--------------------------------------. V (1) | |----+-INTEGRITY ALL----------------+-----+---------------------| +-+-READ PERMISSION FS-+-------+ | '-READ PERMISSION DB-' | +-+-WRITE PERMISSION FS------+-+ | '-WRITE PERMISSION BLOCKED-' | +-RECOVERY NO------------------+ '-+-ON UNLINK RESTORE-+--------' '-ON UNLINK DELETE--'
column-alteration .---------------------------------------------------------------------------------------------------------. | .------------------------------------------------------------------. | V V (2) | | |--column-name----+-SET--+----------------------+----+-default-clause-------------------------------------------+-----+-+-+--| | '-DATA TYPE--data-type-' | .-GENERATED ALWAYS-----. (1) | | | +-+----------------------+--+------------------+-----------+ | | | '-GENERATED BY DEFAULT-' '-identity-options-' | | | +-NOT NULL-------------------------------------------------+ | | | .-NOT HIDDEN--------. | | | +-+-------------------+------------------------------------+ | | | '-IMPLICITLY HIDDEN-' | | | '-FIELDPROC--external-program-name--+--------------------+-' | | | .-,--------. | | | | V | | | | '-(----constant-+--)-' | | .------------------------------. | | V (2) | | +-DROP----+-DEFAULT--------------+-----+--------------------------------------------------------------+ | +-NOT NULL-------------+ | | +-IDENTITY-------------+ | | +-ROW CHANGE TIMESTAMP-+ | | '-FIELDPROC------------' | '-identity-alteration---------------------------------------------------------------------------------' identity-alteration .---------------------------------------------. V | (2) |----+-SET--+-INCREMENT BY--numeric-constant-+-+-+--------------| | +-+-NO MINVALUE----------------+-+ | | | '-MINVALUE--numeric-constant-' | | | +-+-NO MAXVALUE----------------+-+ | | | '-MAXVALUE--numeric-constant-' | | | +-+-NO CYCLE-+-------------------+ | | | '-CYCLE----' | | | +-+-NO CACHE-------+-------------+ | | | '-CACHE--integer-' | | | '-+-NO ORDER-+-------------------' | | '-ORDER----' | '-RESTART--+------------------------+-----' '-WITH--numeric-constant-'
unique-constraint |--+-----------------------------+--+-PRIMARY KEY-+--(----------> '-CONSTRAINT--constraint-name-' '-UNIQUE------' .-,-----------. V | >----column-name-+--)-------------------------------------------| referential-constraint |--+-----------------------------+--FOREIGN KEY-----------------> '-CONSTRAINT--constraint-name-' .-,-----------. V | >--(----column-name-+--)--references-clause---------------------| references-clause |--REFERENCES--table-name--+-----------------------+------------> | .-,-----------. | | V | | '-(----column-name-+--)-' .-ON DELETE NO ACTION--------. .-ON UPDATE NO ACTION-. (1) >--+----------------------------+--+---------------------+------| '-ON DELETE--+-RESTRICT----+-' '-ON UPDATE RESTRICT--' +-CASCADE-----+ +-SET NULL----+ '-SET DEFAULT-' check-constraint |--+-----------------------------+------------------------------> '-CONSTRAINT--constraint-name-' >--CHECK--(--check-condition--)---------------------------------|
partitioning-clause .-RANGE-. |--PARTITION BY--+-+-------+--range-partition-spec-+------------| '-HASH--hash-partition-spec-------' range-partition-spec .-,--------------------. .-,-----------------. V | V | |--(----partition-expression-+--)--(----partition-element-+--)--| partition-expression .-NULLS LAST--. |--column-name--+-------------+---------------------------------| '-NULLS FIRST-' partition-element |--+-+---------------------------+--boundary-spec--+------------------+-+--| | '-PARTITION--partition-name-' '-media-preference-' | | (1) | '-boundary-spec--EVERY--(--integer-constant--+--------+--)-----------' +-DAY----+ +-DAYS---+ +-MONTH--+ +-MONTHS-+ +-YEAR---+ '-YEARS--' boundary-spec |--+-----------------+--+---------------+-----------------------| '-starting-clause-' '-ending-clause-' starting-clause .-,------------. .-FROM-. V | .-INCLUSIVE-. |--STARTING--+------+--+-(----+-constant-+-+--)-+--+-----------+--| | +-MINVALUE-+ | '-EXCLUSIVE-' | '-MAXVALUE-' | '-+-constant-+-----------' +-MINVALUE-+ '-MAXVALUE-'
ending-clause .-,------------. .-AT-. V | .-INCLUSIVE-. |--ENDING--+----+--+-(----+-constant-+-+--)-+--+-----------+----| | +-MINVALUE-+ | '-EXCLUSIVE-' | '-MAXVALUE-' | '-+-constant-+-----------' +-MINVALUE-+ '-MAXVALUE-' hash-partition-spec .-,-----------. V | |--(----column-name-+--)--INTO--integer--PARTITIONS-------------| add-partition |--+-+----------------+--boundary-spec--+------------------+-+--| | '-partition-name-' '-media-preference-' | '-integer--+-HASH PARTITIONS-+----------------------------' '-HASH PARTITION--' materialized-query-definition |--(--select-statement--)--refreshable-table-options------------| refreshable-table-options .------------------------------------. V | (1) |--+-DATA INITIALLY DEFERRED--+--REFRESH DEFERRED----+-MAINTAINED BY USER-------------+-+------| '-DATA INITIALLY IMMEDIATE-' '-+-ENABLE QUERY OPTIMIZATION--+-' '-DISABLE QUERY OPTIMIZATION-' materialized-query-table-alteration |--+-(--select-statement--)--+---------------------------+-+----| | '-refreshable-table-options-' | | .------------------------------------. | | V | (2) | '-SET----+-REFRESH DEFERRED---------------+-+-----------' +-MAINTAINED BY USER-------------+ '-+-ENABLE QUERY OPTIMIZATION--+-' '-DISABLE QUERY OPTIMIZATION-'
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, an identity column (a column that is defined AS IDENTITY), or a row change timestamp. 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, identity column, or row change timestamp.
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, VARBINARY, or VARGRAPHIC column is specified, 32740.
Additionally, if a LOB or XML column 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 Maximum row sizes.
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.
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. If a value is not specified following the DEFAULT keyword,
then: | 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 | For existing rows, a date corresponding to 1 January 0001. For added rows, the current date. |
| Time | For existing rows, a time corresponding to 0 hours, 0 minutes, and 0 seconds. For added rows, the current time. |
| Timestamp | For existing rows, a date corresponding to 1 January 0001 and a time corresponding to 0 hours, 0 minutes, 0 seconds, and 0 microseconds. For added rows, the current timestamp. |
| Datalink | A value corresponding to DLVALUE('','URL','') |
| distinct-type | The default value of the corresponding source type of the distinct type. |
| 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 |
| 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. |
|
If the value specified is not valid, an error is returned.
or updated
and a default value must be generated.
ALWAYS is the recommended
value.
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.
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.
unless the column is an identity column
. NOT NULL
is required for a row change timestamp columnIf 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, 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.
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.

is a declared global temporary
table or a distributed table. The column cannot be a row change timestamp
column.
ROWID, XML, or DATALINK with FILE
LINK CONTROL columns cannot be referenced in a CHECK constraint. For
additional restrictions see, ADD 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
ALTER 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 defines
the data characteristics of the decoded values.
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 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.
BEFORE column-name
Identifies the column before which the new column is added. The
name must not be qualified and must identify an existing column in
the table. If the BEFORE clause is not specified, the column is added
at the end of the row.
Alters the definition of a column, including the attributes of an existing identity column. Only the attributes specified will be altered. Others will remain unchanged.
For
an XML column, only the CCSID can be changed.
The specified length, precision, and scale may be larger, smaller, or the same as the existing length, precision, and scale. However, if the new length, precision, or scale is smaller, truncation or numeric conversion errors may occur.
If the specified column has a default value and a new default value is not specified, the existing default value must represent a value that could be assigned to the column in accordance with the rules for assignment as described in Assignments and comparisons.
If the column is specified in a unique, primary, or foreign key, the new sum of the lengths of the columns of the keys must not exceed 32766-n, where n is the number of columns specified that allow nulls.
Changing the attributes will cause any existing values in the column to be converted to the new column attributes according to the rules for assignment to a column, except that string values will be truncated.
AS IDENTITY 
Specifies
that the column is changed to 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. The identity column is implicitly changed to NOT NULL. If the column has an explicit default value, the default value is dropped. See the AS IDENTITY clause in CREATE TABLE for the descriptions of the identity attributes.

SET 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
ALTER 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 defines
the data characteristics of the decoded values.
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 topic
collection for an example of a field procedure. 
DROP FIELDPROC
Drops
the field procedure for the column. DROP FIELDPROC is not allowed
if the column does not have a field procedure defined.
Drops the identified column from the table.
If all the columns referenced in a constraint are dropped in the same ALTER TABLE statement, RESTRICT does not prevent the drop.
If not specified, a unique constraint name is generated by the database manager.
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
lengths must not exceed 32766-n, where n is the number
of columns specified that allow nulls. The set of identified columns cannot be the same as the set of columns specified in another UNIQUE constraint or PRIMARY KEY on the table. For example, UNIQUE (A,B) is not allowed if UNIQUE (B,A) or PRIMARY KEY (A,B) already exists on the table. Any existing nonnull values in the set of columns must be unique. Multiple null values are allowed.
If a unique index already exists on the identified columns, that index is designated as a unique constraint index. Otherwise, a unique index is created to support the uniqueness of the unique key. The unique index is created as part of the system physical file, not as a separate system logical file.
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
lengths must not exceed 32766. The table must not already have a primary
key. The identified columns cannot be the same as the columns specified in another UNIQUE constraint on the table. For example, PRIMARY KEY (A,B) is not allowed if UNIQUE (B,A) already exists on the table. Any existing values in the set of columns must be unique.
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.
If a unique index already exists on the identified columns, that index is designated as a primary index. Otherwise, a primary index is created to support the uniqueness of the primary key. The unique index is created as part of the system physical file, not a separate system logical file.
If not specified, a unique constraint name is generated by the database manager.
FOREIGN KEY is not allowed
if the table is a declared global temporary table or a distributed
table.
Let T1 denote the table being altered.
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 T1. 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 the 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 allows nulls. 
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.
This table
is referred to as the parent table in the constraint relationship.
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 an existing referential constraint on the table. Duplicate referential constraints are allowed, but not recommended.
Let T2 denote the identified parent table.
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. 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 may be specified in any order. For example, if (A,B) is specified, a unique constraint defined as UNIQUE (B,A) would satisfy the requirement. 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 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 nth column of the parent key must have identical data types, lengths, CCSIDs, and FIELDPROCs.
Unless the table is empty, the values of the foreign key must be validated before the table can be used. Values of the foreign key are validated during the execution of the ALTER TABLE statement. Therefore, every nonnull value of the foreign key must match some value of the parent key of T2.
The referential constraint specified by the FOREIGN KEY clause defines a relationship in which T2 is the parent and T1 is the dependent.
SET NULL must not be specified unless some column of the foreign key allows null values. SET NULL and SET DEFAULT must not be specified if T1 has an update trigger.
CASCADE must not be specified if T1 has a delete trigger.
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.
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 not specified, a unique constraint name is generated by the database manager.
The check-condition is a search-condition, except:
The result of any expression in the check-condition cannot
be a ROWID, XML, or DATALINK with FILE LINK CONTROL data type. It
cannot reference any column that has a field procedure, unless the
column is referenced in a NULL predicate.
Global variables
| ATAN2 | DECRYPT_DB | GETHINT | ROUND_TIMESTAMP |
CARDINALITY![]() |
DIFFERENCE | IDENTITY_VAL_LOCAL | SCORE![]() |
CONTAINS![]() |
DLURLCOMPLETE 1 | INSERT | SOUNDEX |
| CURDATE | DLURLPATH | MAX_CARDINALITY![]() |
TIMESTAMP_FORMAT |
| CURTIME | DLURLPATHONLY | MONTHNAME | TIMESTAMPDIFF |
| DATAPARTITIONNAME | DLURLSCHEME | MONTHS_BETWEEN | TRUNC_TIMESTAMP |
| DATAPARTITIONNUM | DLURLSERVER | NEXT_DAY | VARCHAR_FORMAT |
| DAYNAME | DLVALUE | NOW | WEEK_ISO |
| DBPARTITIONNAME | ENCRYPT_AES | RAISE_ERROR | XMLPARSE![]() |
| DECRYPT_BINARY | ENCRYPT_RC2 | RAND | XMLVALIDATE![]() |
| DECRYPT_BIT | ENCRYPT_TDES | REPEAT | XSLTRANSFORM![]() |
| DECRYPT_CHAR | GENERATE_UNIQUE | REPLACE | |
1 For DataLinks with an attribute of FILE LINK CONTROL and READ PERMISSION DB. |
|||
For more information about search-condition, see Search conditions.
Changes
a non-partitioned table into a partitioned table. If the specified
table is a distributed table or already a partitioned table, an error
is returned. A DDS-created physical file cannot be partitioned. See CREATE TABLE for a description of the partitioning-clause. 
Changing a non-partitioned table that contains data into a partitioned table will require data movement between the data partitions. When using range partitioning, all existing data in the table must be assignable to the specified range partitions.
Changes a partitioned table into a non-partitioned table. If the specified table is already non-partitioned, an error is returned.
Changing a partitioned table that contains data into a non-partitioned table will require data movement between the data partitions.
Adds one or more partitions to a partitioned table. The specified table must be a partitioned table. The number of partitions must not exceed 256.
Changing the number of hash partitions in a partitioned table that contains data will require data movement between the data partitions.
If the clause is not specified, a unique partition name is generated by the database manager.
Alters the boundaries of a partition of a range partitioned table. If the specified table is not a range partitioned table, an error is returned.
Changing the boundaries of one or more partitions of a table that contains data may require data movement between the data partitions. All existing data in the table must be assignable to the specified range partitions.
Drops a partition of a partitioned table. If the specified table is not a partitioned table, an error is returned. If the last remaining partition of a partitioned table is specified, an error is returned.
Changes a base table to a materialized query table. If the specified table is already a materialized query table or if the table is referenced in another materialized query table, an error is returned.
The select-statement for a materialized query table must not contain a reference to the table being altered, a view over the table being altered, or another materialized query table. For additional details about specifying select-statement for a materialized query table, see CREATE TABLE.
Changes the attributes of a materialized query table. The table-name must identify a materialized query table.
The select-statement for a materialized query table must not contain a reference to the table being altered, a view over the table being altered, or another materialized query table. For additional details about specifying select-statement for a materialized query table, see CREATE TABLE.
Changes a materialized query table so that it is no longer a materialized query table. The table specified by table-name must be defined as a materialized query table. The definition of columns and data of the name are not changed, but the table can no longer be used for query optimization and is no longer valid for use with the REFRESH TABLE statement.
Activates the NOT LOGGED INITIALLY attribute of the table for this current unit of work.
Any changes made to the table by INSERT, DELETE, or UPDATE statements in the same unit of work after the table is altered 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).
ACTIVATE NOT LOGGED INITIALLY is not allowed in a transaction if data change operations are pending for table-name or cursors are currently open under commit that reference table-name.
ACTIVATE NOT LOGGED INITIALLY is ignored if the table has a DATALINK column with FILE LINK CONTROL or if running with isolation level No Commit (NC).
WITH EMPTY TABLE cannot be specified for a materialized query table or for a parent in a referential constraint.
A DELETE statement without a WHERE clause will typically perform as well or better than ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE and will allow a ROLLBACK to rollback the delete of the rows in the table.
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.

Specifies the preferred storage media for the table or partition.

Column references: A column can only be referenced once in an ADD, ALTER, or DROP COLUMN clause in a single ALTER TABLE statement. However, that same column can be referenced multiple times for adding or dropping constraints in the same ALTER TABLE statement.
Order of operations: The order of operations within an ALTER TABLE statement is:
Within each of these stages, the order in which the user specifies the clauses is the order in which they are performed, with one exception. If any columns are being dropped, that operation is logically done before any column definitions are added or altered.
QTEMP considerations: Any views or logical files in another job's QTEMP that are dependent on the table being altered will be dropped as a result of an ALTER TABLE statement.
Authority checking: Authority checking is performed only on the table being altered and any object explicitly referenced in the ALTER TABLE statement (such as tables referenced in the fullselect). Other objects may be accessed by the ALTER TABLE statement, but no authority to those objects is required. For example, no authority is required on views that exist on the table being altered, nor on dependent tables that reference the table being altered through a referential constraint.
Backup recommendation: It is strongly recommended that a current backup of the table and dependent views and logical files exist prior to altering a table.
Performance considerations: The following performance considerations apply to an ALTER TABLE statement when adding, altering, or dropping columns from a table:
Adding and dropping columns require the data to be copied.
Altering a column usually requires the data to be copied. The data does not need to be copied, however, if the alter only includes the following changes:
Altering a column that has a field procedure defined
might require the field procedure to be run two times.
An index does not need to be rebuilt when columns are added to a table or when columns are dropped or altered and those columns are not referenced in the index key.
Altering a column that is used in the key of an index or constraint usually requires the index to be rebuilt. The index does not need to be rebuilt, however, in the following cases:
Adding a row change timestamp column: When you add a row change timestamp column to an existing table, the initial values are generated during the alter operation.
Considerations for implicitly hidden columns: A column that is defined as implicitly hidden can be explicitly referenced on the ALTER statement. For example, an implicitly hidden column can be altered, can be specified as part of a referential constraint or a check constraint, or a materialized query table definition.
Altering materialized query tables: The isolation level at the time when a base table is first altered to become a materialized query table by the ALTER TABLE statement is the isolation level for the materialized query table.
Altering a table to change it to a materialized query table with query optimization enabled makes the table eligible for use in optimization. Therefore, ensure that the data in the table is accurate. The DATA INITIALLY IMMEDIATE clause can be used to refresh the data when the table is altered.
Syntax alternatives: The following syntax is supported for compatibility to prior releases. The syntax is non-standard and should not be used:
If a partition-number is not specified, a unique partition number is generated by the database manager.
Adding a column has no
cascaded effects to SQL views,
materialized query
tables,
or most logical files.
For example, adding a column to a table does not cause the column
to be added to any dependent views, even if those views were created
with a SELECT * clause.
Adding a column does cause
any SQL triggers to be recreated and include the new column.
Dropping or altering a column may cause several cascaded effects. Table 1 lists the cascaded effects of dropping a column.
| Operation | RESTRICT Effect | CASCADE Effect |
|---|---|---|
| Drop of a column referenced by a view | The drop of the column is not allowed. | The view and all views dependent on that view are dropped. |
| Drop of a column referenced by a non-view logical file | The drop is allowed, and the column
is dropped from the logical file if:
|
The drop is allowed, and the column
is dropped from the logical file if:
|
| Drop of a column referenced in the key of an index | The drop of the index is not allowed. | The index is dropped. |
Drop of a column referenced in the
key of an keyed physical file where the key is not a PRIMARY KEY![]() |
The physical file is changed to a
non-keyed physical file.![]() |
The physical file is changed to a
non-keyed physical file.![]() |
| Drop of a column referenced in a unique constraint | If all the columns referenced in
the unique constraint are dropped in the same ALTER COLUMN statement
and the unique constraint is not referenced by a referential constraint,
the columns and the constraint are dropped. (Hence, the index used
to satisfy the constraint is also dropped.) For example, if column
A is dropped, and a unique constraint of UNIQUE (A) or PRIMARY KEY
(A) exists and no referential constraints reference the unique constraint,
the operation is allowed. Otherwise, the drop of the column is not allowed. |
The unique constraint is dropped as are any referential constraints that refer to that unique constraint. (Hence, any indexes used by those constraints are also dropped). |
| Drop of a column referenced in a referential constraint | If all the columns referenced in
the referential constraint are dropped at the same time, the columns
and the constraint are dropped. (Hence, the index used by the foreign
key is also dropped). For example, if column B is dropped and a referential
constraint of FOREIGN KEY (A) exists, the operation is allowed. Otherwise, the drop of the column is not allowed. |
The referential constraint is dropped. (Hence, the index used by the foreign key is also dropped). |
Drop of a column referenced in an
SQL trigger![]() |
The drop of the column is not allowed.![]() |
The SQL trigger is dropped.![]() |
| Drop of a column referenced in an MQT | The drop of the column is not allowed. | The MQT is dropped. |
Table 2 lists the cascaded effects of altering a column. (Alter of a column in the following chart means altering a data type, precision, scale, length, or nullability characteristic.)
| Operation | Effect |
|---|---|
| Alter of a column referenced by a view | The alter is allowed. The views that are dependent on the table will be recreated. The new column attributes will be used when recreating the views. |
| Alter of a column referenced by a non-view logical file | The alter is allowed. The non-view logical files that are dependent on the table will be recreated. If the logical file shares a format with the file being altered, and that format is not used again in the logical file with another based-on file, the new column attributes will be used when recreating the logical file. Otherwise, the new column attributes will not be used when recreating the logical file. Instead, the current logical file attributes are used. |
| Alter of a column referenced in the key of an index. | The alter is allowed. (Hence, the index will usually be rebuilt.) |
| Alter of a column referenced in a unique constraint | The alter is allowed. (Hence, the
index will usually be rebuilt.)
|
| Alter of a column referenced in a referential constraint | The alter is allowed.
|
Alter of a column referenced in an
SQL trigger![]() |
The trigger is recreated.![]() |
| Alter of a column referenced in an MQT | The MQT is recreated to include the new attributes. |
Example 1: Add a new column named RATING, which is one character long, to the DEPARTMENT table.
ALTER TABLE DEPARTMENT ADD RATING CHAR
Example 2: Add a new column named PICTURE_THUMBNAIL to the EMPLOYEE table. Create PICTURE_THUMBNAIL as a BLOB column with a maximum length of 1K characters.
ALTER TABLE EMPLOYEE ADD PICTURE_THUMBNAIL BLOB(1K)
Example 3: Assume a new table EQUIPMENT has been created with the following columns:
Add a referential constraint to the EQUIPMENT table so that the owner (EQUIP_OWNER) must be a department number (DEPTNO) that is present in the DEPARTMENT table. If a department is removed from the DEPARTMENT table, the owner (EQUIP_OWNER) values for all equipment owned by that department should become unassigned (or set to null). Give the constraint the name DEPTQUIP.
ALTER TABLE EQUIPMENT FOREIGN KEY DEPTQUIP (EQUIP_OWNER) REFERENCES DEPARTMENT ON DELETE SET NULL
Change the default value for the EQUIP_OWNER column to 'ABC'.
ALTER TABLE EQUIPMENT ALTER COLUMN EQUIP_OWNER SET DEFAULT 'ABC'
Drop the LOCATION column. Also drop any views, indexes, or constraints that are built on that column.
ALTER TABLE EQUIPMENT DROP COLUMN LOCATION CASCADE
Alter the table so that a new column called SUPPLIER is added, the existing column called LOCATION is dropped, a unique constraint over the new column SUPPLIER is added, and a primary key is built over the existing column EQUIP_NO.
ALTER TABLE EQUIPMENT ADD COLUMN SUPPLIER INT DROP COLUMN LOCATION ADD UNIQUE SUPPLIER ADD PRIMARY KEY EQUIP_NO
Notice that the column EQUIP_DESC is a variable length column. If an allocated length of 25 was specified, the following ALTER TABLE statement would not change that allocated length.
ALTER TABLE EQUIPMENT ALTER COLUMN EQUIP_DESC SET DATA TYPE VARCHAR(60)
Example 4: Alter the EMPLOYEE table. Add the check constraint named REVENUE defined so that each employee must make a total of salary and commission greater than $30,000.
ALTER TABLE EMPLOYEE ADD CONSTRAINT REVENUE CHECK (SALARY + COMM > 30000)
Example 5: Alter EMPLOYEE table. Drop the constraint REVENUE which was previously defined.
ALTER TABLE EMPLOYEE DROP CONSTRAINT REVENUE
Example 6: Alter the EMPLOYEE table. Alter the column PHONENO to accept up to 20 characters for a phone number.
ALTER TABLE EMPLOYEE ALTER COLUMN PHONENO SET DATA TYPE VARCHAR (20)
Example 7: Alter the base table TRANSCOUNT to a materialized query table. The result of the select-statement must provide a set of columns that match the columns in the existing table (same number of columns and compatible attributes).
ALTER TABLE TRANSCOUNT ADD MATERIALIZED QUERY (SELECT ACCTID, LOCID, YEAR, COUNT(*) AS CNT FROM TRANS GROUP BY ACCTID, LOCID, YEAR ) DATA INITIALLY DEFERRED REFRESH DEFERRED MAINTAINED BY USER