ALTER TABLE

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:

• For the table identified in the statement,
  • The ALTER privilege on the table, and
  • The system authority *EXECUTE on the library containing the table
• 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
• Administrative authority

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

• The following system authorities:
  • The system authority *EXECUTE on the program, and
  • The system authority *EXECUTE on the library containing the program
• Administrative authority

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:

• The SELECT privilege for the table or view
• Administrative authority

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

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

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.

Description

table-name

Identifies the table to be altered. The table-name must identify a table that exists at the current server. It must not be a view, a catalog table, or a declared temporary table. If table-name identifies a materialized query table, ADD column-definition, ALTER column-alteration, and DROP COLUMN are not allowed.

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

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 IBM® i 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 that 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.

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 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 or a DEFAULT clause is not specified, then:

• if the column is nullable, the default value is the null value.
• if the column is not nullable, the default depends on the data type of the column:

  Data type	Default value
  Numeric	0
  Fixed-length character or graphic string	Blanks
  Fixed-length binary string	Hexadecimal zeros
  Varying-length string	A string length of 0
  Date	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, If the column is a row change timestamp, the current timestamp. Otherwise, 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.

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

constant

Specifies the constant as the default for the column. The specified constant must represent a value that could be assigned to the column in accordance with the rules of assignment as described in Assignments and comparisons. A floating-point constant or decimal floating-point constant must not be used for a SMALLINT, INTEGER, 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) or a row change timestamp column. It may also be specified if the data type of the column is a ROWID (or a distinct type that is based on a ROWID). Otherwise, it must not be specified.

ALWAYS

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

BY DEFAULT

Specifies that the database manager will generate a value for the column when a row is inserted or updated and a default value must be generated, unless an explicit value is specified.

For a ROWID column, the database manager uses a specified value, but it must be a valid unique row ID value that was previously generated by the database manager or DB2® for i.

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

AS IDENTITY

Specifies that the column is an identity column for the table. A table can have only one identity column. An identity column is not allowed in a distributed table. AS IDENTITY can be specified only if the data type for the column is an exact numeric type with a scale of zero (SMALLINT, INTEGER, BIGINT, DECIMAL or NUMERIC with a scale of zero, or a distinct type based on one of these data types). If a DECIMAL or NUMERIC data type is specified, the precision must not be greater than 31.

An identity column is implicitly NOT NULL. See the AS IDENTITY clause in CREATE TABLE for the descriptions of the identity attributes.

FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP

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

A table can have only one row change timestamp column. If data-type is specified, it must be a TIMESTAMP or a distinct type based on a TIMESTAMP. A row change timestamp column cannot have a DEFAULT clause and must be NOT NULL.

NOT NULL

Prevents the column from containing null values. Omission of NOT NULL implies that the column can contain null values. If NOT NULL is specified in the column definition, then DEFAULT must also be specified unless the column is an identity column. NOT NULL is required for a row change timestamp column

NOT HIDDEN

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

IMPLICITLY HIDDEN

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

column-constraint

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

A constraint-name must not be the same as a constraint name that was previously specified in the ALTER TABLE statement and must not identify a constraint that already exists at the current server.

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

PRIMARY KEY

Provides a shorthand method of defining a primary key composed of a single column. Thus, if PRIMARY KEY is specified in the definition of column C, the effect is the same as if the PRIMARY KEY(C) clause is specified as a separate clause.

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

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

UNIQUE

Provides a shorthand method of defining a unique constraint composed of a single column. Thus, if UNIQUE is specified in the definition of column C, the effect is the same as if the UNIQUE (C) clause is specified as a separate clause.

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

references-clause

The references-clause of a column-definition provides a shorthand method of defining a foreign key composed of a single column. Thus, if a references-clause is specified in the definition of column C, the effect is the same as if that references-clause were specified as part of a FOREIGN KEY clause in which C is the only identified column. The references-clause is not allowed if the table is a declared global temporary table or a distributed table. The column cannot be a row change timestamp column.

CHECK(check-condition)

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

constant

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

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

datalink-options

Specifies the options associated with a DATALINK column. See CREATE TABLE for a description of datalink-options.

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.

ALTER COLUMN column-alteration

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.

column-name

Identifies the column to be altered. The name must not be qualified and must identify an existing column in the table. The name must not identify a column that is being added or dropped in the same ALTER TABLE statement.

SET DATA TYPE data-type

Specifies the new data type of the column to be altered. The new data type must be compatible with the existing data type of the column. For more information about the compatibility of data types see Assignments and comparisons. However, changing a datetime data type to a character-string data type or a numeric data type to a character-string data type or a character-string data type to a numeric data type is not allowed. 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.

SET default-clause

Specifies the new default value of the column to be altered. The specified 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.

SET GENERATED ALWAYS or GENERATED BY DEFAULT

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), row change timestamp column, or 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.

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 NOT NULL

Specifies that the column cannot contain null values. All values for this column in existing rows of the table must be not null. If the specified column has a default value and a new default value is not specified, the existing default value must not be NULL. SET NOT NULL is not allowed if the column is identified in the foreign key of a referential constraint with a DELETE rule of SET NULL and no other nullable columns exist in the foreign key.

SET NOT HIDDEN or IMPLICITLY HIDDEN

Specifies the hidden attribute for the column.

NOT HIDDEN

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

IMPLICITLY HIDDEN

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

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.

constant

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

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

DROP DEFAULT

Drops the current default for the column. The specified column must have a default value and must not have NOT NULL as the null attribute. The new default value is the null value.

DROP NOT NULL

Drops the NOT NULL attribute of the column, allowing the column to have the null value. If a default value is not specified or does not already exist, the new default value is the null value. DROP NOT NULL is not allowed if the column is specified in the primary key of the table or is an identity column, row change timestamp column, or ROWID.

DROP IDENTITY

Drops the identity attributes of the column, making the column a simple numeric data type column. DROP IDENTITY is not allowed if the column is not an identity column.

DROP ROW CHANGE TIMESTAMP

Drops the row change timestamp attribute of the column, making the column a simple timestamp column. DROP ROW CHANGE TIMESTAMP is not allowed if the column is not a row change timestamp column.

DROP FIELDPROC

Drops the field procedure for the column. DROP FIELDPROC is not allowed if the column does not have a field procedure defined.

identity-alteration

Alters the identity attributes of the column. The column must exist in the specified table and must already be defined with the IDENTITY attribute. For a description of the attributes, see AS IDENTITY.

RESTART

Specifies the next value for an identity column. If WITH numeric-constant is not specified, the sequence is restarted at the value specified implicitly or explicitly as the starting value when the identity column was originally created. RESTART does not change the original START WITH value.

WITH numeric-constant

Specifies that numeric-constant will be used as the next value for the column. The numeric-constant must be an exact numeric constant that can be any positive or negative value that could be assigned to this column, without nonzero digits existing to the right of the decimal point.

DROP COLUMN

Drops the identified column from the table.

column-name

Identifies the column to be dropped. The column name must not be qualified. The name must identify a column of the specified table. The name must not identify a column that was already added or altered in this ALTER TABLE statement. The name must not identify the only column of a table. The name must not identify a partition key of a partitioned table or a distributed table.

CASCADE

Specifies that any views, indexes, triggers, or constraints that are dependent on the column being dropped are also dropped. ¹

RESTRICT

Specifies that the column cannot be dropped if any views, indexes, triggers, or constraints are dependent on the column. ¹

If all the columns referenced in a constraint are dropped in the same ALTER TABLE statement, RESTRICT does not prevent the drop.

ADD unique-constraint

CONSTRAINT constraint-name

Names the constraint. A constraint-name must not be the same as a constraint name that was previously specified in the ALTER TABLE statement and must not identify a constraint that already exists at the current server. The constraint-name must be unique within a schema.

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

UNIQUE (column-name,…)

Defines a unique constraint composed of the identified columns. Each column-name must be an unqualified name that identifies a column of the table. The same column must not be identified more than once. The column must not be a LOB, DATALINK, or XML column. 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.

PRIMARY KEY (column-name,…)

Defines a primary key composed of the identified columns. Each column-name must be an unqualified name that identifies a column of the table. The same column must not be identified more than once. The column must not be a LOB, DATALINK, or XML column. 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.

ADD referential-constraint

CONSTRAINT constraint-name

Names the constraint. A constraint-name must not identify a constraint that already exists at the current server.

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

FOREIGN KEY

Defines a referential constraint. 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.

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

REFERENCES table-name

The table-name specified in a REFERENCES clause must identify a base table that exists at the current server, but it must not identify a catalog table, a declared temporary table, or a distributed table. If the parent is a partitioned table, the unique index that enforces the parent unique constraint must be non-partitioned. 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.

(column-name,…)

The parent key of the referential constraint is composed of the identified columns. Each column-name must be an unqualified name that identifies a column of T2. The same column must not be identified more than once. The column must not be a LOB, DATALINK, or XML column and must not be a row change timestamp column. If a sort sequence is specified, the column must not contain a field procedure. The number of identified columns must not exceed 120, and the sum of their 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.

ON DELETE

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

• NO ACTION (default)
• RESTRICT
• CASCADE
• SET NULL
• SET DEFAULT

SET NULL must not be specified unless some column of the foreign key allows null values. 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.

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

ADD check-constraint

CONSTRAINT constraint-name

Names the constraint. A constraint-name must not identify a constraint that already exists at the current server. The constraint-name must be unique within a schema.

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

CHECK(check-condition)

Defines a check constraint. 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 and the column names must not be qualified.
• 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.
• It must not contain any of the following:
  • Subqueries
  • Aggregate functions
  • Variables
  • Global variables
  • Parameter markers
  • Sequence-references
  • Complex expressions that contain LOBs (such as concatenation)
  • OLAP specifications
  • REGEXP_LIKE predicate
  • ROW CHANGE expressions
  • Special registers
  • Any function that is not deterministic
  • User-defined functions other than functions that were implicitly generated with the creation of a distinct type
  • The following built-in scalar functions:

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

For more information about search-condition, see Search conditions.

DROP

PRIMARY KEY

Drops the definition of the primary key and all referential constraints in which the primary key is a parent key. The table must have a primary key.

UNIQUE constraint-name

Drops the unique constraint constraint-name and all referential constraints dependent on this unique constraint. The constraint-name must identify a unique constraint on the table. DROP UNIQUE will not drop a PRIMARY KEY unique constraint.

FOREIGN KEY constraint-name

Drops the referential constraint constraint-name. The constraint-name must identify a referential constraint in which the table is a dependent.

CHECK constraint-name

Drops the check constraint constraint-name. The constraint-name must identify a check constraint on the table.

CONSTRAINT constraint-name

Drops the constraint constraint-name. The constraint-name must identify a unique, referential, or check constraint on the table. If the constraint is a PRIMARY KEY or UNIQUE constraint, all referential constraints in which the primary key or unique key is a parent are also dropped.

CASCADE

Specifies for unique constraints that any referential constraints that are dependent on the constraint being dropped are also dropped.

RESTRICT

Specifies for unique constraints that the constraint cannot be dropped if any referential constraints are dependent on the constraint.

ADD partitioning-clause

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.

DROP PARTITIONING

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.

ADD PARTITION add-partition

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.

partition-name

Names the partition. A partition-name must not identify a data partition that already exists in the table.

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

boundary-spec

Specifies the boundaries of a range partition. If the specified table is not a range partitioned table, an error is returned. Both a starting-clause and an ending-clause must be specified. See CREATE TABLE for a description of the boundary-spec.

integer HASH PARTITIONS

Specifies the number of hash partitions to be added. If the specified table is not a hash partitioned table, an error is returned.

ALTER PARTITION

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.

partition-name

Specifies the name of the partition to alter. The partition-name must identify a data partition that exists in the table.

boundary-spec

Specifies the new boundaries of a range partition. Both a starting-clause and an ending-clause must be specified. See CREATE TABLE for a description of the boundary-spec.

DROP PARTITION

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.

partition-name

Specifies the name of the partition to drop. The partition-name must identify a data partition that exists in the table.

DELETE ROWS

Specifies that any data in the specified partition will be discarded. All data stored in the partition is dropped from the table without processing any delete triggers.

PRESERVE ROWS

Specifies that any data in the specified partition will be preserved by moving it to the remaining partitions without processing any delete or insert triggers. If the specified table is a range partitioned table, PRESERVE ROWS must not be specified. Dropping a hash partition will require data movement between the remaining data partitions.

ADD MATERIALIZED QUERY materialized-query-definition

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.

select-statement

Defines the query on which the table is based. The columns of the existing table must meet the following characteristics:

• The number of columns in the table must be the same as the number of result columns in the select-statement.
• The column attributes of each column of the table must be compatible to the column attributes of the corresponding result column in the select-statement.

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.

refreshable-table-options

Specifies the materialized query table options for altering a base table to a materialized query table.

DATA INITIALLY DEFERRED

Specifies that the data in the table is not validated as part of the ALTER TABLE statement. A REFRESH TABLE statement can be used to make sure the data in the materialized query table is the same as the result of the query in which the table is based.

DATA INITIALLY IMMEDIATE

Specifies that the data is inserted in the table from the result of the query as part of processing the ALTER TABLE statement.

REFRESH DEFERRED

Specifies that the data in the table can be refreshed at any time using the REFRESH TABLE statement. The data in the table only reflects the result of the query as a snapshot at the time when the REFRESH TABLE statement is processed or when it was last updated.

MAINTAINED BY USER

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

ENABLE QUERY OPTIMIZATION or DISABLE QUERY OPTIMIZATION

Specifies whether this materialized query table can be used for query optimization.

ENABLE QUERY OPTIMIZATION

The materialized query table can be used for query optimization.

DISABLE QUERY OPTIMIZATION

The materialized query table will not be used for query optimization. The table can still be queried directly.

ALTER MATERIALIZED QUERY materialized-query-table-alteration

Changes the attributes of a materialized query table. The table-name must identify a materialized query table.

select-statement

Defines the query on which the table is based. The columns of the existing table must meet the following characteristics:

• The number of columns in the table must be the same as the number of result columns in the select-statement.
• The column attributes of each column of the table must be compatible to the column attributes of the corresponding result column in the select-statement.

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.

refreshable-table-options

Specifies the materialized query table options for altering a base table to a materialized query table.

DATA INITIALLY DEFERRED

Specifies that the data in the table is not refreshed or validated as part of the ALTER TABLE statement. A REFRESH TABLE statement can be used to make sure the data in the materialized query table is the same as the result of the query in which the table is based.

DATA INITIALLY IMMEDIATE

Specifies that the data is inserted in the table from the result of the query as part of processing the ALTER TABLE statement.

REFRESH DEFERRED

Specifies that the data in the table can be refreshed at any time using the REFRESH TABLE statement. The data in the table only reflects the result of the query as a snapshot at the time when the REFRESH TABLE statement is processed or when it was last updated.

MAINTAINED BY USER

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

ENABLE QUERY OPTIMIZATION or DISABLE QUERY OPTIMIZATION

Specifies whether this materialized query table can be used for query optimization.

ENABLE QUERY OPTIMIZATION

The materialized query table can be used for query optimization.

DISABLE QUERY OPTIMIZATION

The materialized query table will not be used for query optimization. The table can still be queried directly.

SET refreshable-table-alteration

Changes how the table is maintained or whether the table can be used in query optimization.

MAINTAINED BY USER

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

REFRESH DEFERRED

Specifies that the data in the table can be refreshed at any time using the REFRESH TABLE statement. The data in the table only reflects the result of the query as a snapshot at the time when the REFRESH TABLE statement is processed or when it was last updated.

ENABLE QUERY OPTIMIZATION or DISABLE QUERY OPTIMIZATION

Specifies whether this materialized query table can be used for query optimization.

ENABLE QUERY OPTIMIZATION

The materialized query table can be used for query optimization.

DISABLE QUERY OPTIMIZATION

The materialized query table will not be used for query optimization. The table can still be queried directly.

DROP MATERIALIZED QUERY

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.

ACTIVATE NOT LOGGED INITIALLY

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

Causes all data currently in the table to be removed. If the unit of work in which this ALTER statement was issued is rolled back, the table data will NOT be returned to its original state. When this action is requested, no DELETE triggers defined on the affected table are fired.

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.

VOLATILE or NOT VOLATILE

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

VOLATILE

Specifies that the cardinality of table-name can vary significantly at run time, from empty to large. To access the table, the optimizer will typically use an index, if possible.

NOT VOLATILE

Specifies that the cardinality of table-name is not volatile. Access plans that reference this table will be based on the cardinality of the table at the time the access plan is built.

media-preference

Specifies the preferred storage media for the table or partition.

UNIT ANY

No storage media is preferred. Storage for the table or partition will be allocated from any available storage media. If UNIT ANY is specified on the table, any media-preference that is specified on a partition is used. If the table or partition is currently on solid state disk storage, it may be moved asynchronously onto other media, if available.

UNIT SSD

Solid state disk storage media is preferred. Storage for the table or partition may be allocated from solid state disk storage media, if available. If UNIT SSD is specified on the table, any media-preference specified on a partition is ignored. If the table or partition is not currently on solid state disk storage, it may be moved asynchronously onto solid state disk storage media, if available.

Notes

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:

• drop constraints
• drop materialized query table
• drop partition
• drop partitioning
• drop columns for which the RESTRICT option was specified
• alter all other column definitions
  • drop columns for which the CASCADE option was specified
  • alter column drop attributes (for example, DROP DEFAULT)
  • alter column alter attributes
  • alter column add attributes
  • add columns
• alter partition
• add or alter materialized query table
• add partition or add partitioning
• add constraints

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:

• The data in the table may be copied.²

  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:

  • The length attribute of a VARCHAR column is increasing and the current length attribute is greater than 20.
  • The length attribute of a VARGRAPHIC column is increasing and the current length attribute is greater than 10.
  • The allocated length of a VARCHAR column is changing and the current and new allocated lengths are both less than or equal to 20.
  • The allocated length of a VARGRAPHIC column is changing and the current and new allocated lengths are both less than or equal to 10.
  • The CCSID of a column is changing but no conversion is necessary between the old and new CCSID. For example, if one CCSID is 65535, no data conversion is necessary.
  • The default value is changing, and the length of the default value is not greater than the current allocated length.
  • DROP DEFAULT is specified.
  • DROP NOT NULL is specified, but at least one nullable column will still exist in the table after the alter table is complete.

  Altering a column that has a field procedure defined might require the field procedure to be run two times.

• Indexes may need to be rebuilt.³

  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:

  • The length attribute of a VARCHAR or VARGRAPHIC key is increasing.
  • The CCSID of a column is changing but no conversion is necessary between the old and new CCSID. For example, if one CCSID is 65535.

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:

• INLINE LENGTH is a synonym for ALLOCATE.
• If an ADD constraint is the first clause of the ALTER TABLE statement, the ADD keyword is optional, but strongly recommended. Otherwise, it is required.
• constraint-name (without the CONSTRAINT keyword) may be specified following the FOREIGN KEY keywords in a referential-constraint.
• PART is a synonym for PARTITION.
• PARTITION partition-number may be specified instead of PARTITION partition-name. A partition-number must not identify an existing partition of the table or a partition that was previously specified in the ALTER TABLE statement.

  If a partition-number is not specified, a unique partition number is generated by the database manager.

• VALUES is a synonym for ENDING AT.
• SET MATERIALIZED QUERY AS DEFINITION ONLY is a synonym for DROP MATERIALIZED QUERY.
• SET SUMMARY AS DEFINITION ONLY is a synonym for DROP MATERIALIZED QUERY
• SET MATERIALIZED QUERY AS (select-statement) is a synonym for ADD MATERIALIZED QUERY (select-statement)
• SET SUMMARY AS (select-statement) is a synonym for ADD MATERIALIZED QUERY (select-statement)

Cascaded Effects

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.

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

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.

Example 3: Assume a new table EQUIPMENT has been created with the following columns:

  EQUIP_NO

INT

  EQUIP_DESC

VARCHAR(50)

  LOCATION

VARCHAR(50)

  EQUIP_OWNER

CHAR(3)

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.

Change the default value for the EQUIP_OWNER column to 'ABC'.

Drop the LOCATION column. Also drop any views, indexes, or constraints that are built on that column.

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.

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.

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.

Example 5: Alter EMPLOYEE table. Drop the constraint REVENUE which was previously defined.

Example 6: Alter the EMPLOYEE table. Alter the column PHONENO to accept up to 20 characters for a phone number.

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