ALTER TABLE statement

The ALTER TABLE statement alters the definition of a table.

Invocation

This statement can be embedded in an application program or issued by using dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges that are held by the authorization ID of the statement must include at least one of the following authorities:

ALTER privilege on the table to be altered
CONTROL privilege on the table to be altered
ALTERIN privilege on the schema of the table
SCHEMAADM authority on the schema of the table
DBADM authority

To create or drop a foreign key, the privileges that are held by the authorization ID of the statement must include one of the following authorities on the parent table:

REFERENCES privilege on the table
REFERENCES privilege on each column of the specified parent key
CONTROL privilege on the table
SCHEMAADM authority on the schema of the table
DBADM authority

To drop the primary key or a unique constraint on table T, the privileges of the authorization ID of the statement must include at least one of the following authorities on every table that is a dependent of T's parent key:

ALTER privilege on the table
CONTROL privilege on the table
ALTERIN privilege on the schema of the table
SCHEMAADM authority on the schema of the table
DBADM authority

To alter a table to become a materialized query table (by using a fullselect), the privileges that are held by the authorization ID of the statement must include at least one of the following authorities:

CONTROL privilege on the table
SCHEMAADM authority on the schema of the table
DBADM authority

and at least one of the following authorities on each table or view that is identified in the fullselect (excluding group privileges):

All of the following:
- SELECT privilege on the table or view or SELECTIN privilege on the schema of the table or view
- ALTER privilege on the table or view or ALTERIN privilege (including group privileges) on the schema of the table or view
CONTROL privilege on the table or view
DATAACCESS on the schema of the table or view
DATAACCESS authority on the database

To alter a table so that it is no longer a materialized query table, the privileges of the authorization ID of the statement must include at least one of the following authorities on each table or view that is identified in the fullselect that is used to define the materialized query table:

ALTER privilege on the table or view
CONTROL privilege on the table or view
ALTERIN privilege on the schema of the table or view
SCHEMAADM authority on the schema of the table
DBADM authority

To add a column of type DB2SECURITYLABEL to a table, the privileges of the authorization ID of the statement must include at least a security label from the security policy that is associated with the table.

To remove the security policy from a table, the privileges that are held by the authorization ID of the statement must include SECADM authority.

To alter a table to attach a data partition, the privileges of the authorization ID of the statement must also include at least one of the following authorities on the source table:

SELECT privilege on the table or SELECTIN privilege on the schema containing the source table and DROPIN privilege on the schema of the table
CONTROL privilege on the table
DATAACCESS authority on the schema of the table
DATAACCESS authority on the database

and at least one of the following authorities on the target table:

All of the following:
- ALTER privilege on the table or ALTERIN privilege on the schema of the table
- INSERT privilege on the table or INSERTIN privilege on the schema of the table
CONTROL privilege on the table
DATAACCESS authority on the schema of the table
DATAACCESS authority on the database

To alter a table to detach a data partition, the privileges of the authorization ID of the statement must also include at least one of the following authorities on the target table of the detached partition:

CREATETAB authority on the database, and USE privilege on the table spaces used by the table, and one of:
- IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the new table does not exist
- CREATEIN privilege on the schema, if the schema name of the new table refers to an existing schema
- SCHEMAADM authority on the schema of the table and USE privilege on the table spaces that are used by the table
DBADM authority

and at least one of the following authorities on the source table:

All of the following:
- SELECT privilege on the table or SELECTIN privilege on the schema of the table
- ALTER privilege on the table or ALTERIN privilege on the schema of the table
- DELETE privilege on the table or DELETEIN privilege on the schema of the table
CONTROL privilege on the table
DATAACCESS authority on the schema of the table
DATAACCESS authority on the database

To alter a table to activate not logged initially with empty table, the privileges that are held by the authorization ID of the statement must include at least one of the following authorities:

All of the following:
- ALTER privilege on the table or ALTERIN privilege on the schema of the table
- DELETE privilege on the table or DELETEIN privilege on the schema of the table
CONTROL privilege on the table
SCHEMAADM authority on the schema of the table
DBADM authority

To alter a table that is protected by a security policy to activate not logged initially with empty table, the privileges that are held by the authorization ID of the statement must include at least one of the following authorities:

CONTROL privilege on the table
SCHEMAADM authority on the schema of the table
DBADM authority

To alter a table to ACTIVATE and DEACTIVATE row and column access control, the privileges that are held by the authorization ID of the statement must include the SECADM authority.

To alter a table with ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE, if that table activated row access control, the privileges that are held by the authorization ID of the statement must include at least one of the following authorities:

CONTROL privilege on the table
SCHEMAADM authority on the schema of the table
DBADM authority

When altering system period temporal tables, one of the following authorities is required by the authorization ID of the statement:

ALTER privilege on the history table
CONTROL privilege on the history table
ALTERIN privilege on the schema of the history table
SCHEMAADM authority on the schema of the history table
DBADM authority

This condition applies when altering a table to become a system-period temporal table (with the ADD VERSIONING clause). It also applies when altering a system-period temporal table where one or more changes result in changes to the associated history table.

Syntax

Constraint-attributes

default-clause

generated-clause

as-row-change-timestamp-clause

as-generated-expression-clause

as-row-transaction-timestamp-clause

as-row-transaction-start-id-clause

unique-constraint

referential-constraint

check-constraint

check-condition

functional-dependency

distribution-clause

materialized-query-definition

refreshable-table-options

constraint-alteration

generation-alteration

identity-alteration

generation-attribute

period-definition

Notes:

¹ The same clause must not be specified more than once (SQLSTATE 42614).
² If an ACTIVATE or DEACTIVATE clause is specified for row access control, no other clause except ACTIVATE or DEACTIVATE column access control can be specified in the same ALTER TABLE statement (SQLSTATE 42613).
³ If an ACTIVATE or DEACTIVATE clause is specified for column access control, no other clause except ACTIVATE or DEACTIVATE row access control can be specified in the same ALTER TABLE statement (SQLSTATE 42613).
⁴ If the first column option chosen is generated-clause, data-type can be omitted; it is computed by the generation expression.
⁵ The lob-options clause applies to large object types (CLOB, DBCLOB, and BLOB), and to distinct types that are based on large object types only.
⁶ The SCOPE clause applies to the REF type only.
⁷ The default-clause and generated-clause cannot both be specified for the same column definition (SQLSTATE 42614).
⁸ Data type is optional for a row change timestamp column if the first column-option specified is a generated-clause. The data type default is TIMESTAMP(6). Data type is optional for row-begin, row-end, and transaction-start-ID columns if the first column-option is a generated-clause; the data type default is TIMESTAMP(12).
⁹ The same clause must not be specified more than once.
¹⁰ The specified distinct type cannot have any data type constraints and the source type cannot be an anchored data type (SQLSTATE 428H2).
¹¹ The FOR BIT DATA clause can be specified in any order with the other column constraints that follow. The FOR BIT DATA clause cannot be specified with string units CODEUNITS32 (SQLSTATE 42613).

Description

table-nameThe table-name must identify a table that exists at the current server. It cannot be a nickname (SQLSTATE 42809) and must not be a view, a catalog table, a created temporary table, or a declared temporary table (SQLSTATE 42995).

If table-name identifies a materialized query table, alterations are limited to adding or dropping the materialized query, starting the ACTIVATING NOT LOGGED INITIALLY clause, adding or dropping RESTRICT ON DROP, modifying data capture, pctfree, locksize, append, volatile, data row compression, value compression, and activating or deactivating row and column access control.

If table-name identifies a shadow table, alterations can include adding or dropping primary keys in addition to the alterations that are supported for a materialized query table.

If table-name identifies a range-clustered table, alterations are limited to adding, changing, or dropping constraints, activating not logged initially, adding or dropping RESTRICT ON DROP, changing locksize, data capture, or volatile, and setting column default values.

ADD column-definition

Adds a column to the table.

A column cannot be added to the following tables:

A history table for a system-period temporal table (SQLSTATE 428HZ)
A typed table (SQLSTATE 428DH)
A table whose compression dictionary is being created in an asynchronous background operation (SQL20054N)

For all existing rows in the table, the value of the new column is set to its default value. The new column is the last column of the table; that is, if initially n columns exist, the added column is column n+1.

Adding the new column must not cause the total byte count of all columns to exceed the maximum record size.

If the table is a column-organized table, a LOB column can not be added to an existing table.

Note: This restriction has been removed starting from Db2®version 11.5.1

If the table is a system-period temporal table, the column is added to the associated history table as well.

If the added column is a generated column that is based on an expression, the expression must not reference a column for which a column mask is defined (SQLSTATE 42621).

If a column is added to a table on which a mask or a permission is defined, or to a table that is referenced in the definition of a mask or a permission, that mask or permission is invalidated. Access to a table that activates column access control and a defined invalid mask on it is blocked until the invalid mask is either disabled, dropped, or re-created (SQLSTATE 560D0). Access to a table that activates row access control and a defined invalid row permission on it is blocked until the invalid permission is either disabled, dropped, or re-created (SQLSTATE 560D0).

column-nameIs the name of the column to be added to the table. The name cannot be qualified. Existing column names or period names in the table cannot be used (SQLSTATE 42711).

data-typeIs one of the data types that are listed under CREATE TABLE.

NOT NULL

Prevents the column from containing null values. The default-clause must also be specified (SQLSTATE 42601).

lob-optionsSpecifies options for LOB data types. See lob-options in CREATE TABLE.

SCOPE

Specify a scope for a reference type column.

typed-table-name2

The name of a typed table. The data type of column-name must be REF(S), where S is the type of typed-table-name2 (SQLSTATE 428DM). No checking is done of the default value for column-name to ensure that the value references an existing row in typed-table-name2.

typed-view-name2

The name of a typed view. The data type of column-name must be REF(S), where S is the type of typed-view-name2 (SQLSTATE 428DM). No checking is done of the default value for column-name to ensure that the values reference an existing row in typed-view-name2.

CONSTRAINT constraint-name

Names the constraint. A constraint-name must not identify a constraint that was already specified within the same ALTER TABLE statement, or as the name of any other existing constraint on the table (SQLSTATE 42710).

If the constraint name is not specified by the user, an 18-byte long identifier unique within the identifiers of the existing constraints that are defined on the table is generated by the system. (The identifier consists of "SQL" followed by a sequence of 15 numeric characters that are generated by a timestamp-based function).

When used with a PRIMARY KEY or UNIQUE constraint:

the constraint-name might be used as the name of an index that is created to support the constraint. See Notes for details on index names that are associated with unique constraints.
you can use an existing index that is defined with RANDOM columns for the key index if applicable. If there is more than one index that can satisfy the primary or unique requirement, then the index that is chosen cannot be predicted. An existing primary or unique key index cannot be altered to be an index with random ordering. If a primary or unique key index with random ordering is required, a suitable index must first be defined with the RANDOM keyword. Then, the table must be altered to add the primary or unique key.

PRIMARY KEY

Provides a shorthand method of defining a primary key that is 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 were specified as a separate clause. The column cannot contain null values, so the NOT NULL attribute must also be specified (SQLSTATE 42831).

See PRIMARY KEY within the unique-constraint description.

UNIQUE

Provides a shorthand method of defining a unique key that is 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 were specified as a separate clause.

See UNIQUE within the unique-constraint description.

references-clauseProvides a shorthand method of defining a foreign key that is 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.

See references-clause in CREATE TABLE.

rule-clause

See rule-clause in CREATE TABLE.

CHECK (check-condition)

Provides a shorthand method of defining a check constraint that applies to a single column. See check-condition in CREATE TABLE.

constraint-attributesSee constraint-attributes in CREATE TABLE.

default-clauseSpecifies a default value for the column.

WITH

An optional keyword.

DEFAULT

Provides a default value when a value is not supplied on INSERT or is specified as DEFAULT on INSERT or UPDATE. If a specific default value is not specified following the DEFAULT keyword, the default value depends on the data type of the column as shown in Table 1. If a column is defined as an XML or structured type, then a DEFAULT clause cannot be specified.

If a column is defined that uses a distinct type, then the default value of the column is the default value of the source data type cast to the distinct type.

Table 1. Default Values (when no value specified)
Data Type	Default Value
Numeric	0
Fixed-length character string	A string of blanks
Varying-length character string	A string of length 0
Fixed-length graphic string	A string of double-byte blanks
Varying-length graphic string	A string of length 0
Fixed-length binary string	A string of hexadecimal zeros
Varying-length binary string	A string of length 0
Date	For existing rows, a date corresponding to January 1, 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 01 January 0001, and a time corresponding to 0 hours, 0 minutes, 0 seconds, and 0 microseconds. For added rows, the current time stamp.
Binary string (blob)	A string of length 0
Boolean	FALSE

Omission of DEFAULT from a column-definition results in the use of the null value as the default for the column.

Specific types of values that can be specified with the DEFAULT keyword are as follows.

constantSpecifies the constant as the default value for the column. The specified constant must:

Represent a value that might be assigned to the column in accordance with the rules of assignment as described in Chapter 3
Not be a floating-point constant unless the column is defined with a floating-point data type
Be a numeric constant or a decimal floating-point special value if the data type of the column is decimal floating-point. Floating-point constants are first interpreted as DOUBLE and then converted to decimal floating-point. For DECFLOAT(16) columns, decimal constants must have a precision value less than or equal to 16.
Not have nonzero digits beyond the scale of the column data type if the constant is a decimal constant (for example, 1.234 cannot be the default for a DECIMAL(5,2) column)
The constant must be expressed with no more than 254 bytes including the quotation characters, any introducer character such as the X for a hexadecimal constant, and characters from the fully qualified function name and parentheses when the constant is the argument of a cast-function.

datetime-special-registerSpecifies the value of the datetime special register (CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP) at the time of INSERT, UPDATE, or LOAD as the default for the column. The data type of the column must be the data type that corresponds to the special register specified (for example, data type must be DATE when CURRENT DATE is specified). For existing rows, the value is the current date, current time, or current time stamp when the ALTER TABLE statement is processed.

user-special-registerSpecifies the value of the user special register (CURRENT USER, SESSION_USER, SYSTEM_USER) at the time of INSERT, UPDATE, or LOAD as the default for the column. The data type of the column must be a character string with a length not less than the length attribute of a user special register. USER can be specified in place of SESSION_USER and CURRENT_USER can be specified in place of CURRENT USER. For existing rows, the value is the CURRENT USER, SESSION_USER, or SYSTEM_USER of the ALTER TABLE statement.

CURRENT SCHEMA

Specifies the value of the CURRENT SCHEMA special register at the time of INSERT, UPDATE, or LOAD as the default for the column. If CURRENT SCHEMA is specified, the data type of the column must be a character string with a length greater than or equal to the length attribute of the CURRENT SCHEMA special register. For existing rows, the value of the CURRENT SCHEMA special register at the time the ALTER TABLE statement is processed.

CURRENT MEMBER

Specifies the value of the CURRENT MEMBER special register at the time of INSERT, UPDATE, or LOAD as the default for the column. If CURRENT MEMBER is specified, the data type of the column must allow assignment from an integer. For existing rows, the value of the CURRENT MEMBER special register at the time the ALTER TABLE statement is processed.

NULL

Specifies NULL as the default for the column. If NOT NULL was specified, DEFAULT NULL must not be specified within the same column definition.

cast-functionThis form of a default value can be used with columns defined as a distinct type, BLOB, or datetime (DATE, TIME, or TIMESTAMP) data type. For distinct type only, except for distinct types based on BLOB or datetime types, 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. For a distinct type based on a datetime type, where the default value is a constant, a function must be used and the name of the function must match the name of the source type of the distinct type with an implicit or explicit schema name of SYSIBM. For other datetime columns, the corresponding datetime function can also be used. For a BLOB or a distinct type based on BLOB, a function must be used and the name of the function must be BLOB with an implicit or explicit schema name of SYSIBM.

Specifies one 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. If the cast-function is BLOB, the constant must be a string constant.; Specifies CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP. The source type of the distinct type of the column must be the data type that corresponds to the specified special register.; Specifies CURRENT USER, SESSION_USER, or SYSTEM_USER. The data type of the source type of the distinct type of the column must be a string data type with a length of at least 8 bytes. If the cast-function is BLOB, the length attribute must be at least 8 bytes.
CURRENT SCHEMA: Specifies the value of the CURRENT SCHEMA special register. The data type of the source type of the distinct type of the column must be a character string with a length greater than or equal to the length attribute of the CURRENT SCHEMA special register. If the cast-function is BLOB, the length attribute must be at least 8 bytes.

EMPTY_CLOB(), EMPTY_DBCLOB(), or EMPTY_BLOB()

Specifies a zero-length string as the default for the column. The column must have the data type that corresponds to the result data type of the function.

If the value specified is not valid, an error (SQLSTATE 42894) is returned.

generated-clauseSpecifies a generated value for the column. This clause must not be specified with default-clause in a column definition (SQLSTATE 42623). A generated column cannot be added to a system-period temporal table (SQLSTATE 428HZ). For details on column generation, see CREATE TABLE.

GENERATED

Specifies that the database manager generates values for the column. GENERATED must be specified whether the column is to be considered an identity column, row change timestamp column, row-begin column, row-end column, transaction start-ID column, or generated expression column.

If the column is nullable, the null value is assigned as the value for the column in existing rows. Otherwise, the value for the column in existing rows depends on the definition of the column:

ROW CHANGE TIMESTAMP uses a date that corresponds to January 1, 0001 and a time that corresponds to 0 hours, 0 minutes, 0 seconds, and 0 fractional seconds
ROW BEGIN uses a date that corresponds to January 1, 0001 and a time that corresponds to 0 hours, 0 minutes, 0 seconds, and 0 fractional seconds
ROW END uses a date that corresponds to December 30, 9999, and a time that corresponds to 0 hours, 0 minutes, 0 seconds, and 0 fractional seconds
TRANSACTION START ID uses a date that corresponds to January 1, 0001, and a time that corresponds to 0 hours, 0 minutes, 0 seconds, and 0 fractional seconds
Expressions use the value that is derived from the expression

ALWAYS

Specifies that the database manager always generates a value for the column when a row is inserted or updated and a value must be generated. The result of the expression is stored in the table. GENERATED ALWAYS is the recommended option unless data propagation or unload and reload operations are running. GENERATED ALWAYS is the default for generated columns.

BY DEFAULT

Specifies that the database manager generates a value for the column when a row is inserted into the table, or updated, specifying DEFAULT for the column, unless an explicit value is specified. BY DEFAULT can be specified with as-row-change-timestamp-clause only. BY DEFAULT is the recommended option when you use data propagation or running unload and reload operations.

FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP

Specifies that the column is a timestamp column with values that are generated by the database manager. A value is generated for the column in each row that is inserted, and for any row in which any column is updated. The value that is generated for a ROW CHANGE TIMESTAMP column is a timestamp that corresponds to the insert or update time for that row. If multiple rows are inserted or updated with a single statement, the value of the ROW CHANGE TIMESTAMP column might be different for each row.

A table can have one ROW CHANGE TIMESTAMP column (SQLSTATE 428C1) only. If data-type is specified, it must be TIMESTAMP or TIMESTAMP(6) (SQLSTATE 42842). A ROW CHANGE TIMESTAMP column cannot have a DEFAULT clause (SQLSTATE 42623). NOT NULL must be specified for a ROW CHANGE TIMESTAMP column (SQLSTATE 42831).

AS (generation-expression)

Specifies that the definition of the column is based on an expression. Requires the table to be placed in set integrity pending no access state, by using the SET INTEGRITY statement with the OFF NO ACCESS option. After the ALTER TABLE statement, the SET INTEGRITY statement with the IMMEDIATE CHECKED and FORCE GENERATED options must be used to update and check all the values in that column against the new expression. For details on specifying a column with a generation-expression, see CREATE TABLE.

AS ROW BEGIN

Specifies that the value is assigned by the database manager whenever a row is inserted into the table or any column in the row is updated. The value is generated by using a reading of the time-of-day clock during execution of the first of the following events in the transaction:

A data change statement that requires a value to be assigned to the row-begin or transaction start-ID column in a table
A deletion of a row in a system-period temporal table

For a system-period temporal table, the database manager ensures uniqueness of the generated values for a row-begin column across transactions. The timestamp value might be adjusted to ensure that rows that are inserted into an associated history table have the end timestamp value greater than the begin timestamp value (SQLSTATE 01695). This can happen when a conflicting transaction is updating the same row in the system-period temporal table. The database configuration parameter systime_period_adj must be set to Yes for this adjustment to the timestamp value to occur otherwise an error is returned (SQLSTATE 57062). If multiple rows are inserted or updated within a single SQL transaction and an adjustment is not needed, the values for the row-begin column are the same for all the rows and are unique from the values that are generated for the column for another transaction. A row-begin column is required as the begin column of a SYSTEM_TIME period, which is the intended use for this type of generated column.

A table can have only one row-begin column (SQLSTATE 428C1). If data-type is not specified, the column is defined as a TIMESTAMP(12). If data-type is specified, it must be TIMESTAMP(12) (SQLSTATE 42842). The column must be defined as NOT NULL (SQLSTATE 42831). A row-begin column cannot be updated.

AS ROW END

Specifies that a value for the data type of the column is assigned by the database manager whenever a row is inserted or any column in the row is updated. The assigned value is TIMESTAMP '9999-12-30-00.00.00.000000000000'.

A row-end column is required as the second column of a SYSTEM_TIME period, which is the intended use for this type of generated column.

A table can have only one row-end column (SQLSTATE 428C1). If data-type is not specified, the column is defined as TIMESTAMP(12). If data-type is specified, it must be TIMESTAMP(12) (SQLSTATE 42842). The column must be defined as NOT NULL (SQLSTATE 42831). A row-end column cannot be updated.

AS TRANSACTION START ID

Specifies that the value is assigned by the database manager whenever a row is inserted into the table or any column in the row is updated. The database manager assigns a unique timestamp value per transaction or the null value. The null value is assigned to the transaction start-ID column if the column is nullable and if a row-begin column in the table for which the value did not need to be adjusted exists. Otherwise, the value is generated by using a reading of the time-of-day clock during execution of the first of the following events in the transaction:

A data change statement that requires a value to be assigned to the row-begin or transaction start-ID column in a table
A deletion of a row in a system-period temporal table

If multiple rows are inserted or updated within a single SQL transaction, the values for the transaction start-ID column are the same for all the rows and are unique from the values that are generated for the column for another transaction.

A transaction start-ID column is required for a system-period temporal table, which is the intended use for this type of generated column.

A table can have only one transaction start-ID column (SQLSTATE 428C1). If data-type is not specified, the column is defined as TIMESTAMP(12). If data-type is specified, it must be TIMESTAMP(12). A transaction start-ID column cannot be updated.

COMPRESS SYSTEM DEFAULT

Specifies that system default values (that is, the default values used for the data types when no specific values are specified) are to be stored in minimal space. If the VALUE COMPRESSION clause is not specified, a warning is returned (SQLSTATE 01648) and system default values are not stored in minimal space.

Allowing system default values to be stored in this manner causes a slight performance penalty during insert and update operations on the column because of extra checking that is done.

The base data type must not be a DATE, TIME, TIMESTAMP, XML, or structured data type (SQLSTATE 42842). If the base data type is a varying-length string, this clause is ignored. String values of length 0 are automatically compressed if a table is set with VALUE COMPRESSION.

COLUMN SECURED WITH security-label-name

Identifies a security label that exists for the security policy that is associated with the table. The name must not be qualified (SQLSTATE 42601). The table must be associated with a security policy (SQLSTATE 55064). The table must not be a system-period temporal table.

NOT HIDDEN or IMPLICITLY HIDDEN

Specifies whether the column is to be defined as hidden. The hidden attribute determines whether the column is included in an implicit reference to the table, or whether it can be explicitly referenced in SQL statements. The default is NOT HIDDEN.

NOT HIDDEN: Specifies that the column is included in implicit references to the table, and that the column can be explicitly referenced.
IMPLICITLY HIDDEN: Specifies that the column is not visible in SQL statements unless the column is explicitly referenced by name. For example, assuming that a table includes a column that is defined with the IMPLICITLY HIDDEN clause, the result of a SELECT * does not include the implicitly hidden column. However, the result of a SELECT that explicitly refers to the name of an implicitly hidden column includes that column in the result table.

ADD unique-constraint

Defines a unique or primary key constraint. A primary key or unique constraint cannot be added to a table that is a subtable (SQLSTATE 429B3). If the table is a supertable at the top of the hierarchy, the constraint applies to the table and all its subtables.

CONSTRAINT constraint-name

Names the primary key or unique constraint. For more information, see constraint-name in CREATE TABLE statement.

UNIQUE (column-name, ... BUSINESS_TIME WITHOUT OVERLAPS)

Defines a unique key that is composed of the identified columns and periods. The identified columns must be defined as NOT NULL. Each column-name must identify a column of the table and the same column must not be identified more than once. The name cannot be qualified. The number of identified columns plus two times the number of identified periods must not exceed 64, and the sum of their stored lengths must not exceed the index key length limit for the page size. For column stored lengths, see Byte Counts in CREATE TABLE. For key length limits, see SQL and XML limits. No LOB, distinct type based on any of these types, or structured type can be used as part of a unique key, even if the length attribute of the column is small enough to fit within the index key length limit for the page size (SQLSTATE 54008). The set of columns in the unique key cannot be the same as the set of columns of the primary key or another unique key (SQLSTATE 01543). If LANGLEVEL is SQL92E or MIA, an error is returned, SQLSTATE 42891. Any existing values in the set of identified columns must be unique (SQLSTATE 23515).

A check is run to determine whether an existing index matches the unique key definition (ignoring any INCLUDE columns in the index). An index definition matches if it identifies the same set of columns without regard to the order of the columns or the direction (ASC/DESC/RANDOM) specifications. However, for partitioned tables, non-unique partitioned indexes whose columns are not a superset of the table-partitioning key columns are not considered matching indexes.

If a matching index definition is found, the description of the index is changed to indicate that it is required by the system and it is changed to unique after ensuring uniqueness if it was a non-unique index. If the table has more than one matching index, an existing unique index is selected. If multiple unique indexes exist, the selection is arbitrary with one exception:

For partitioned tables, matching unique partitioned indexes are favored over matching unique nonpartitioned indexes or matching non-unique indexes (partitioned or nonpartitioned).

If no matching index is found, a unique bidirectional index is automatically created for the columns, as described in CREATE TABLE. See Notes for details on index names that are associated with unique constraints.

BUSINESS_TIME WITHOUT OVERLAPS

For a constraint, BUSINESS_TIME indicates the period name in this table. The period must exist in the table (SQLSTATE 42727).

BUSINESS_TIME WITHOUT OVERLAPS specifies that overlapping periods for BUSINESS_TIME are not allowed, and that values for the rest of the keys must be unique regarding any period of BUSINESS_TIME. When BUSINESS_TIME WITHOUT OVERLAPS is specified, the end column and begin column of the period BUSINESS_TIME (in this order of the columns) is automatically added to the index key in ascending order and enforce that no overlaps in time exist. The columns that are used to defined BUSINESS_TIME must not be specified as part of the constraint (SQLSTATE 428HW).

When a partition is attached to a range partitioned application-period temporal table that has a partitioned BUSINESS_TIME WITHOUT OVERLAPS index, the source table must have an index that matches the partitioned BUSINESS_TIME WITHOUT OVERLAPS index. Additionally, the PERIODNAME and PERIODPOLICY attributes on the indexes must also match.

PRIMARY KEY (column-name, ... BUSINESS_TIME WITHOUT OVERLAPS)

Defines a primary key that is composed of the identified columns. Each column-name must identify a column of the table, and the same column must not be identified more than once. The name cannot be qualified. The number of identified columns must not exceed 64, and the sum of their stored lengths must not exceed the index key length limit for the page size. For column stored lengths, see Byte Counts in CREATE TABLE. For key length limits, see SQL limits. The table must not have a primary key and the identified columns must be defined as NOT NULL. No LOB, distinct type based on any of these types, or structured type can be used as part of a primary key, even if the length attribute of the column is small enough to fit within the index key length limit for the page size (SQLSTATE 54008). The set of columns in the primary key cannot be the same as the set of columns in a unique key (SQLSTATE 01543). (If LANGLEVEL is SQL92E or MIA, an error is returned, SQLSTATE 42891). Any existing values in the set of identified columns must be unique (SQLSTATE 23515). column-name must not be the name of a row change timestamp, or a begin or end column of the period (SQLSTATE 428HW).

A check runs to determine whether an existing index matches the primary key definition (ignoring any INCLUDE columns in the index). An index definition matches if it identifies the same set of columns without regard to the order of the columns or the direction (ASC/DESC/RANDOM) specifications. However, for partitioned tables, non-unique partitioned indexes whose columns are not a superset of the table-partitioning key columns are not considered matching indexes.

If a matching index definition is found, the description of the index is changed to indicate that it is the primary index, as required by the system, and it is changed to unique after uniqueness is ensured if it was a non-unique index. If the table has more than one matching index, an existing unique index is selected. If multiple unique indexes exist, the selection is arbitrary with one exception:

For partitioned tables, matching unique partitioned indexes are favored over matching unique nonpartitioned indexes or matching non-unique indexes (partitioned or nonpartitioned).

If the primary key is being added to a shadow table, the columns of the primary key must match the columns of an enforced primary key constraint or an enforced unique constraint of the base table that is referenced in the fullselect of materizalized-query-definition.

A primary key cannot be created on a materialized query table that is not defined with MAINTAINED BY REPLICATION.

Only one primary key can be defined on a table.

BUSINESS_TIME WITHOUT OVERLAPS

For a constraint, BUSINESS_TIME indicates the period name in this table. The period must exist in the table (SQLSTATE 42727).

constraint-attributesSee constraint-attributes in CREATE TABLE.

ADD referential-constraint

Defines a referential constraint. See referential-constraint in CREATE TABLE.

ADD check-constraint

Defines a check constraint or functional dependency. See check-constraint in CREATE TABLE.

constraint-attributes

See constraint-attributes in CREATE TABLE.

ADD distribution-clause

Defines a distribution key. The table must be defined in a table space on a single-partition database partition group (SQLSTATE 55037) and must not already have a distribution key (SQLSTATE 42889). If a distribution key exists for the table, the existing key must be dropped before you add the new distribution key. A distribution key cannot be added to a table that is a subtable (SQLSTATE 428DH) .

DISTRIBUTE BY HASH (column-name...): Defines a distribution key by using the specified columns. Each column-name must identify a column of the table, and the same column must not be identified more than once. The name cannot be qualified. A column cannot be used as part of a distribution key if the data type of the column is a BLOB, CLOB, DBCLOB, XML, distinct type on any of these types, or structured type.

ADD RESTRICT ON DROP

Specifies that the table cannot be dropped, and that the table space that contains the table cannot be dropped.

ADD MATERIALIZED QUERY

materialized-query-definitionChanges a regular table to a materialized query table for use during query optimization. The table that is specified by table-name must not:

Be previously defined as a materialized query table
Be a typed table
Have any constraints, unique indexes, or triggers defined
Reference a nickname that is marked with caching disabled
Be referenced in the definition of another materialized query table
Be referenced in the definition of a view that is enabled for query optimization

If table-name does not meet these criteria, an error is returned (SQLSTATE 428EW).

If row level or column level access control is activated for any table that is directly or indirectly referenced in the fullselect of materizalized-query-definition, and row level access control is not activated for the altered table, row level access control is implicitly activated for the altered table. This restricts direct access to the contents of the materialized query table. A query that explicitly references the table before such a row permission is defined returns a warning that no data in the table exists (SQLSTATE 02000). To provide access to the materialized query table, an appropriate row permission can be created, or an ALTER TABLE DEACTIVATE ROW ACCESS CONTROL on the materialized query table can be entered to remove the row level protection if that is appropriate.

If the materialized query table references any table that has row level or column level access control that is activated, the functions that are referenced in the fullselect of materizalized-query-definition must be defined with the SECURED attribute (SQLSTATE 428EC).

If the table that is altered to a materialized query table has any permissions (excluding the system generated default permission) or masks defined on it, ALTER fails (SQLSTATE 428EW).

fullselectDefines the query in which the table is based. The columns of the existing table must:

have the same number of columns
have the same data types
have the same column names in the same ordinal positions

As the result columns of fullselect (SQLSTATE 428EW). For information about specifying the fullselect for a materialized query table, see CREATE TABLE. One extra restriction is that table-name cannot be directly or indirectly referenced in the fullselect.

refreshable-table-optionsSpecifies the refreshable options for altering a materialized query table.

DATA INITIALLY DEFERRED

The data in the table must be validated by using the REFRESH TABLE or SET INTEGRITY statement.

REFRESH

Indicates how the data in the table is maintained.

DEFERRED: The data in the table can be refreshed at any time by using the REFRESH TABLE statement. The data in the table reflects the result of the query as a snapshot at the time the REFRESH TABLE statement is processed only. Materialized query tables that are defined with this attribute do not allow INSERT, UPDATE, or DELETE statements (SQLSTATE 42807).
IMMEDIATE: The changes that are made to the underlying tables as part of a DELETE, INSERT, or UPDATE are cascaded to the materialized query table. In this case, the content of the table, at any point-in-time, is the same as if the specified subselect is processed. Materialized query tables (MQTs) defined with this attribute do not allow INSERT, UPDATE, or DELETE statements (SQLSTATE 42807). Column-organized MQTs that use the REFRESH IMMEDIATE option are not supported when the MAINTAINED BY SYSTEM clause is specified (SQL20058N).

ENABLE QUERY OPTIMIZATION

The materialized query table can be used for query optimization.

DISABLE QUERY OPTIMIZATION

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

MAINTAINED BY

Specifies whether the data in the materialized query table is maintained by the system, user, or replication tool.

SYSTEM: Specifies that the data in the materialized query table is maintained by the system.
USER: Specifies that the data in the materialized query table is maintained by the user. The user is allowed to run update, delete, or insert operations against user-maintained materialized query tables. The REFRESH TABLE statement, which is used for system-maintained materialized query tables, cannot be started against user-maintained materialized query tables. Only a REFRESH DEFERRED materialized query table can be defined as MAINTAINED BY USER.
REPLICATION: Specifies that the data in the materialized query table is maintained by an external replication technology. The REFRESH TABLE statement, which is used for system-maintained materialized query tables, cannot be issued against replication-maintained materialized query tables, which are referred to as shadow tables. Only a REFRESH DEFERRED materialized query table can be defined as MAINTAINED BY REPLICATION, and the altered table must be a column-organized table.
FEDERATED_TOOL: Specifies that the data in the materialized query table is maintained by a federated replication tool. The REFRESH TABLE statement, which is used for system-maintained materialized query tables, cannot be started against federated_tool-maintained materialized query tables. Only a REFRESH DEFERRED materialized query table can be defined as MAINTAINED BY FEDERATED_TOOL.

ALTER FOREIGN KEY constraint-name

Alters the constraint attributes of the referential constraint constraint-name. The constraint-name must identify an existing referential constraint (SQLSTATE 42704).

ALTER CHECK constraint-name

Alters the constraint attributes of the check constraint or functional dependency constraint-name. The constraint-name must identify an existing check constraint or functional dependency (SQLSTATE 42704).

constraint-alterationOptions for changing attributes that are associated with referential or check constraints.

ENABLE QUERY OPTIMIZATION or DISABLE QUERY OPTIMIZATION

Specifies whether the constraint or functional dependency can be used for query optimization under appropriate circumstances.

ENABLE QUERY OPTIMIZATION: The constraint is assumed to be true and can be used for query optimization.
DISABLE QUERY OPTIMIZATION: The constraint cannot be used for query optimization.

ENFORCED or NOT ENFORCED

Specifies whether the constraint is enforced by the database manager during normal operations such as insert, update, or delete. A foreign key constraint cannot be altered from NOT ENFORCED to ENFORCED if the parent key is not enforced (SQLSTATE 42888).

ENFORCED

Change the constraint to ENFORCED. ENFORCED cannot be specified for a functional dependency (SQLSTATE 42621).

NOT ENFORCED

Change the constraint to NOT ENFORCED.

TRUSTED: The data can be trusted to conform to the constraint. TRUSTED must be used only if the data in the table is independently known to conform to the constraint. Query results might be unpredictable if the data does not conform to the constraint. This is the default option.; Informational constraints must not be violated at any time. Informational constraints are used in query optimization, and the incremental processing of REFRESH IMMEDIATE MQT and staging tables. These processes might produce unpredictable results or incorrect MQT and staging table content if the constraints are violated. For example, the order in which parent-child tables are maintained is important. When you want to add rows to a parent-child table, you must insert rows into the parent table first. To remove rows from a parent-child table, you must delete rows from the child table first. This ensures that no orphan rows in the child table exist at any time. If informational constraints are violated, the incremental maintenance of dependent MQT data and staging table data might be optimized based on the violated informational constraints, producing incorrect data.
NOT TRUSTED: The data cannot be trusted to conform to the constraint. NOT TRUSTED is intended for cases where the data conforms to the constraint for most rows, but it is not independently known that all the rows or future additions conform to the constraint. If a constraint is NOT TRUSTED and enabled for query optimization, then it is not used to run optimizations that depend on the data that conforms completely to the constraint. NOT TRUSTED can be specified only for foreign keys (SQLSTATE 42601).

ALTER column-alteration

Alters the definition of a column. Only the specified attributes are altered; others remain unchanged. Columns of a typed table cannot be altered (SQLSTATE 428DH). The table must not be defined as a history table (SQLSTATE 428FR). Columns that are used in expression-based index keys cannot be altered (SQLSTATE 42893), unless the operation involves the following column attributes:

Identity (by using clauses under identity-alteration)
Default compression (by using COMPRESS clause)
Security (by using SECURED WITH or DROP COLUMN SECURITY clauses)

column-nameSpecifies the name of the column that is to be altered. The column-name must identify an existing column of the table (SQLSTATE 42703). The name must not be qualified. The name must not identify a column that is otherwise being added, altered, or dropped in the same ALTER TABLE statement (SQLSTATE 42711).

SET DATA TYPE altered-data-type

Specifies the new data type of the column. The new data type must be castable from the existing data type of the column (SQLSTATE 42837) except when one of the data types is a distinct type, in which case the source data type of the distinct type is used in determining whether the data types are castable. A LOB column cannot be altered to a different data type (SQLSTATE 42837). A non-LOB column cannot be altered to a LOB data type (SQLSTATE 42837).

Altering a column to a character or graphic string data type that results in the truncation of non-blank characters from existing data is not allowed (SQLSTATE 42837). Similarly, altering a column to a binary string data type that results in truncation of bytes other than hexadecimal zeros is not allowed.

Data type alterations require a classic table reorganization before the table can be fully accessed (SQLSTATE 57007), except in the following situations:

Increasing the length of a VARCHAR, VARGRAPHIC, or VARBINARY column
Decreasing the length of a VARCHAR, VARGRAPHIC, or VARBINARY column without truncating trailing blanks from existing data, when no indexes exist on the column

The administrative routine SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS can be called to do table reorganization. A data type alteration that requires a table reorganization cannot be specified if the table is in SET INTEGRITY PENDING state (SQLSTATE 57007).

A string data type cannot be altered if the column is a column of a table-partitioning key.

If the column is a column of a distribution key, then the new data type must meet the following requirements (SQLSTATE 42997):

Be the same data type as the current column type
Have the same length of the current column type, except when increasing column length of VARCHAR, VARGRAPHIC, and VARBINARY data type columns
Cannot be modified to FOR BIT DATA or vice versa in the cases of CHAR and VARCHAR data types

If the data type is LOB, the specified length attribute cannot allow for the possibility of any truncated data (SQLSTATE 42837).

The data type of an identity column cannot be altered (SQLSTATE 42997).

The data type of a column that is defined as ROW BEGIN, ROW END, or TRANSACTION START ID cannot be altered (SQLSTATE 428FR).

The data type and nullability of BUSINESS_TIME period columns cannot be altered (SQLSTATE 428FR).

The table cannot have data capture enabled (SQLSTATE 42997).

The data type of a column cannot be altered if any of the following conditions are true (SQLSTATE 42893):

The column is a generated expression column and the data of the generated expression column changes if the column is altered
The column is referenced in an expression of a generated expression column and the data of the generated expression column changes if the column is altered
The column is referenced in a check constraint and the check constraint is not satisfied if the column is altered
The column is used in a referential integrity constraint and the referential integrity constraint is not satisfied if the column is altered

Altering a column must not cause the total byte count of all columns to exceed the maximum record size (SQLSTATE 54010). If the column is used in a unique constraint or an index, the new length must not cause the sum of the stored lengths for the unique constraint or index to exceed the index key length limit for the page size (SQLSTATE 54008). For column stored lengths, see Byte Counts in CREATE TABLE. For key length limits, see SQL and XML limits.

If auto_reval is set to DISABLED, the cascaded effects of altering a column is shown in Table 2.

If either a row permission or a column mask depends on the column that is altered (as recorded in the SYSCAT.CONTROLDEP catalog view), an error is returned (SQLSTATE 42917).

Table 2. Cascaded effects of altering a column
Operation	Effect
Altering a column that is referenced by a view or check constraint	The object is regenerated during alter processing. If a view, function or method resolution for the object is different after the alter operation, changes the semantics of the object. In the case of a check constraint, if the semantics of the object change as a result of the alter operation, the operation fails.
Altering a column in a table that has a dependent package, trigger, or SQL routine	The object is marked invalid, and is re-validated on next use.
Altering the type of a column in a table that is referenced by an XSROBJECT enabled for decomposition	The XSROBJECT is marked inoperative for decomposition. Reenabling the XSROBJECT might require readjustment of its mappings; afterward, issue an ALTER XSROBJECT ENABLE DECOMPOSITION statement against the XSROBJECT.
Altering a column that is referenced in the default expression of a global variable	The default expression of the global variable is validated during alter processing. If a user-defined function that is used in the default expression cannot be resolved, the operation fails.

If the table is a system-period temporal table, the column is also changed in any associated history table. If the table is a system-period temporal table, string data type columns cannot be altered to a length that requires data truncation, and numeric data type columns cannot be altered to lower precision data types (SQLSTATE 42837).

built-in-type: See "CREATE TABLE" for the description of built-in data types.

SET NOT NULL

Specifies that the column cannot contain null values. No value for this column in existing rows of the table can be the null value (SQLSTATE 23502). This clause is not allowed if the column is specified 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 (SQLSTATE 42831).

Altering this attribute for a column requires a classic table reorganization before full table access is allowed (SQLSTATE 57007).

If a row permission or column mask exists, which depends on the column to be altered, an error is issued (SQLSTATE 42917).

If the table is a system-period temporal table, the column is also changed in any associated history table.

SET INLINE LENGTH integer

Changes the inline length of an existing structured type, XML, or LOB data type column. The inline length indicates the maximum size in bytes of an instance of a structured type, XML, or LOB data type to store in the base table row. Instances of a structured type or XML data type that cannot be stored inline in the base table row are stored separately, similar to the way that LOB values are stored.

The data type of column-name must be a structured type, XML, or LOB data type (SQLSTATE 42842).

The default inline length for a structured type column is the inline length of its data type (specified explicitly or by default in the CREATE TYPE statement). If the inline length of a structured type is less than 292, the value 292 is used for the inline length of the column.

The explicit inline length value can be increased only (SQLSTATE 429B2); it cannot exceed 32673 (SQLSTATE 54010). For a structured type or XML data type column, it must be at least 292. For a LOB data type column, the INLINE LENGTH must not be less than the maximum LOB descriptor size.

Altering the column must not cause the total byte count of all columns to exceed the row size limit (SQLSTATE 54010).

Data that is already stored separately from the rest of the row is not moved inline into the base table row by this statement.

To take advantage of the altered inline length of a structured type column, start the REORG command against the specified table after altering the inline length of its column.

To take advantage of the altered inline length of an XML data type column in an existing table, update all rows with an UPDATE statement.

The REORG command has no effect on the row storage of XML documents.

To take advantage of the altered inline length of a LOB data type column, use the REORG command with the LONGLOBDATA option or UPDATE the corresponding LOB column.

For example:

UPDATE table-name SET lob-column = lob-column 
   WHERE LENGTH(lob-column) <= chosen-inline-length - 4

where table-name is the table that had the inline length of the LOB data type column that is altered, lob-column is the LOB data type column that was altered, and chosen-inline-length is the new value that was chosen for the INLINE LENGTH.

If a row permission or column mask exists, which depends on the column to be altered, an error is returned (SQLSTATE 42917).

If the table is a system-period temporal table, inline length changes are propagated to the history table.

SET default-clause

Specifies a new default value for the column that is to be altered. The column must not already be defined as a generated column (SQLSTATE 42623). 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. Altering the default value does not change the value that is associated with this column for existing rows.

SET EXPRESSION AS (generation-expression)

Changes the expression for the column to the specified generation-expression. SET EXPRESSION requires the table to be put in set integrity pending state by using the SET INTEGRITY statement with the OFF option. After the ALTER TABLE statement, the SET INTEGRITY statement with the IMMEDIATE CHECKED and FORCE GENERATED options must be used to update and check all the values in that column against the new expression. The column must already be defined as a generated column based on an expression (SQLSTATE 42837), and must not have appeared in the PARTITIONING KEY, DIMENSIONS, or KEY SEQUENCE clauses of the table (SQLSTATE 42997). The generation-expression must conform to the same rules that apply when defining a generated column. The result data type of the generation-expression must be assignable to the data type of the column (SQLSTATE 42821).

The generation-expression must not reference a column for which a column mask is defined (SQLSTATE 42621).

SET NOT HIDDEN or SET IMPLICITLY HIDDEN

Specifies the hidden attribute for the column.

If the table is a system-period temporal table, the column is also changed in any associated history table.

NOT HIDDEN: Specifies that the column is included in implicit references to the table, and that the column can be explicitly referenced.
IMPLICITLY HIDDEN: Specifies that the column is not visible in SQL statements unless the column is explicitly referenced by name. For example, assuming that a table includes a column that is defined with the IMPLICITLY HIDDEN clause, the result of a SELECT * does not include the implicitly hidden column. However, the result of a SELECT that explicitly refers to the name of an implicitly hidden column includes that column in the result table.
IMPLICITLY HIDDEN must not be specified for the last column of the table that is not hidden (SQLSTATE 428GU).

SET generation-alteration

Specifies that the generation attribute for the column is to be changed. GENERATED might be specified whether the column is an identity column or a row change timestamp column (SQLSTATE 42837). If the table is a system-period temporal table, the column in the associated history table is not affected by the change. If a default for the column exists, that default must be dropped, which can be done in the same column-alteration by using one of the DROP DEFAULT clauses. SET GENERATED must not be specified for a column of a temporal history table (SQLSTATE 428FR).

GENERATED ALWAYS: Specifies that the database manager always generates a value for the column when a row is inserted or updated and a value must be generated. GENERATED ALWAYS is the recommended option unless data propagation or unload and reload operations are being performed. ALWAYS is the default for generated columns.
GENERATED BY DEFAULT: Specifies that the database manager generates a value for the column when a row is inserted into the table, or updated, specifying DEFAULT for the column, unless an explicit value is specified. GENERATED BY DEFAULT can be specified with as-row-change-timestamp-clause only. GENERATED BY DEFAULT is the recommended option when using data propagation or performing unload and reload operations.

identity-alteration

Alters the identity attributes of the column. The column must be an identity column.

SET INCREMENT BY numeric-constant

Specifies the interval between consecutive values of the identity column. The next value to be generated for the identity column will be determined from the last assigned value with the increment applied. The column must already be defined with the IDENTITY attribute (SQLSTATE 42837).

This value can be any positive or negative value that might be assigned to this column (SQLSTATE 42815), and does not exceed the value of a large integer constant (SQLSTATE 42820), without nonzero digits that exist to the right of the decimal point (SQLSTATE 428FA).

If this value is negative, this is a descending sequence after the ALTER statement. If this value is 0 or positive, this is an ascending sequence after the ALTER statement.

SET NO MINVALUE or MINVALUE numeric-constant

Specifies the minimum value at which a descending identity column either cycles or stops generating values, or the value to which an ascending identity column cycle after it reaches the maximum value. The column must exist in the specified table (SQLSTATE 42703), and must already be defined with the IDENTITY attribute (SQLSTATE 42837).

NO MINVALUE: For an ascending sequence, the value is the original starting value. For a descending sequence, the value is the minimum value of the data type of the column.
MINVALUE numeric-constant: Specifies the numeric constant that is the minimum value. This value can be any positive or negative value that might be assigned to this column (SQLSTATE 42815), without nonzero digits that exist to the right of the decimal point (SQLSTATE 428FA), but the value must be less than or equal to the maximum value (SQLSTATE 42815).

SET NO MAXVALUE or MAXVALUE numeric-constant

Specifies the maximum value at which an ascending identity column either cycles or stops generating values, or the value to which a descending identity column cycle after it reaches the minimum value. The column must exist in the specified table (SQLSTATE 42703), and must already be defined with the IDENTITY attribute (SQLSTATE 42837).

NO MAXVALUE: For an ascending sequence, the value is the maximum value of the data type of the column. For a descending sequence, the value is the original starting value.
MAXVALUE numeric-constant: Specifies the numeric constant that is the maximum value. This value can be any positive or negative value that might be assigned to this column (SQLSTATE 42815), without nonzero digits that exist to the right of the decimal point (SQLSTATE 428FA), but the value must be greater than or equal to the minimum value (SQLSTATE 42815).

SET NO CYCLE or CYCLE

Specifies whether this identity column continues to generate values after generating either its maximum or minimum value. The column must exist in the specified table (SQLSTATE 42703), and must already be defined with the IDENTITY attribute (SQLSTATE 42837).

NO CYCLE: Specifies that values are not generated for the identity column after the maximum or minimum value is reached.
CYCLE: Specifies that values continue to be generated for this column after the maximum or minimum value is reached. If this option is used, then after an ascending identity column reaches the maximum value, it generates its minimum value; or after a descending sequence reaches the minimum value, it generates its maximum value. The maximum and minimum values for the identity column determine the range that is used for cycling.
When CYCLE is in effect, duplicate values can be generated for an identity column. Although not required, if unique values are wanted, a single-column unique index that is defined by using the identity column ensures uniqueness. If a unique index exists on such an identity column and a non-unique value is generated, an error occurs (SQLSTATE 23505).

SET NO CACHE or CACHE integer-constant

Specifies whether to keep some pre-allocated values in memory for faster access. This is a performance and tuning option. The column must already be defined with the IDENTITY attribute (SQLSTATE 42837).

NO CACHE

Specifies that values for the identity column are not to be pre-allocated.

In a Db2 pureScale® environment, if the identity values must be generated in order of request, the NO CACHE option must be used.

When this option is specified, the values of the identity column are not stored in the cache. In this case, every request for a new identity value results in synchronous I/O to the log.

CACHE integer-constant

Specifies how many values of the identity sequence are pre-allocated and kept in memory. When values are generated for the identity column, pre-allocating and storing values in the cache reduces synchronous I/O to the log.

If a new value is needed for the identity column and no unused values available in the cache exist, the allocation of the value requires waiting for I/O to the log. However, when a new value is needed for the identity column and an unused value in the cache exist, the allocation of that identity value can happen more quickly by avoiding the I/O to the log.

If a database deactivates, either normally or due to a system failure, all cached sequence values that are not used in committed statements are lost (that is, they are never used). The maximum number of identity column values that can be lost is calculated as follows:

If ORDER is specified, the maximum is the value that is specified for the CACHE option.
In a multi-partition or Db2 pureScale, the maximum is the value that is specified for the CACHE option times the number of members that generate new identity values.

The minimum value is 2 (SQLSTATE 42815).

In a Db2 pureScale environment, if both CACHE and ORDER are specified, the specification of ORDER overrides the specification of CACHE and instead NO CACHE is in effect.

SET NO ORDER or ORDER

Specifies whether the identity column values must be generated in order of request. The column must exist in the specified table (SQLSTATE 42703), and must already be defined with the IDENTITY attribute (SQLSTATE 42837).

NO ORDER: Specifies that the identity column values do not need to be generated in order of request.
ORDER: Specifies that the identity column values must be generated in order of request.

RESTART or RESTART WITH numeric-constant

Resets the state of the sequence that is associated with the identity column. If WITH numeric-constant is not specified, the sequence for the identity column is restarted at the value that was specified, either implicitly or explicitly, as the starting value when the identity column was originally created.

The column must exist in the specified table (SQLSTATE 42703), and must already be defined with the IDENTITY attribute (SQLSTATE 42837). RESTART does not change the original START WITH value.

The numeric-constant is an exact numeric constant that can be any positive or negative value that might be assigned to this column (SQLSTATE 42815), without nonzero digits that exist to the right of the decimal point (SQLSTATE 428FA). The numeric-constant will be used as the next value for the column.

SET generation-attribute as-identity-clause

Changes the column to an identity column. This column alteration must not be specified whether the column has a default or is already a generated column (SQLSTATE 42837). If the table is a system-period temporal table, the column in the associated history table is not affected by the change.

GENERATED ALWAYS: Specifies that the database manager always generates a value for the column when a row is inserted or updated and a value must be generated. ALWAYS is the default for generated columns.
GENERATED BY DEFAULT: Specifies that the database manager generates 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.; Specifies that the column is the identity column for the table. A table can have a single identity column (SQLSTATE 428C1). The column must be specified as not nullable (SQLSTATE 42997) only, and the data type associated with the column must be an exact numeric data type with a scale of zero (SQLSTATE 42815). An exact numeric data type is one of: SMALLINT, INTEGER, BIGINT, DECIMAL, or NUMERIC with a scale of zero, or a distinct type based on one of these types. For details on identity options, see CREATE TABLE.

SET GENERATED ALWAYS

Changes the column to a generated expression column, a row-begin column, a row-end column, or a transaction-start-ID column. GENERATED ALWAYS specifies that the database manager always generates a value for the column when a row is inserted or updated and a value must be generated.

AS (generation-expression)

Specifies that the definition of the column is based on an expression. The column must not already be defined with a generation expression, cannot be the identity column, or cannot have an explicit default (SQLSTATE 42837). The generation-expression must conform to the same rules that apply when defining a generated column. The result data type of the generation-expression must be assignable to the data type of the column (SQLSTATE 42821). The column must not be referenced in the distribution key column or in the multidimensional clustering (MDC) key (SQLSTATE 42997).

The generation-expression must not reference a column for which a column mask is defined (SQLSTATE 42621).

AS ROW BEGIN

A data change statement that requires a value to be assigned to the row-begin or transaction start-ID column in a table
A deletion of a row in a system-period temporal table

AS ROW END

Specifies that the maximum value for the data type of the column is assigned by the database manager whenever a row is inserted or any column in the row is updated.

A row-end column is required as the second column of a SYSTEM_TIME period, which is the intended use for this type of generated column.

AS TRANSACTION START ID

Specifies that the value is assigned by the database manager whenever a row is inserted into the table or any column in the row is updated. The database manager assigns a unique timestamp value per transaction or the null value. The null value is assigned to the transaction start-ID column if the column is nullable and if a row-begin column exists in the table for which the value did not need to be adjusted. Otherwise, the value is generated by using a reading of the time-of-day clock during execution of the first of the following events in the transaction:

A data change statement that requires a value to be assigned to the row-begin or transaction start-ID column in a table
A deletion of a row in a system-period temporal table

A transaction start-ID column is required for a system-period temporal table, which is the intended use for this type of generated column.

DROP DEFAULT

Drops the current default for the column. The specified column must have a default value (SQLSTATE 42837). This action is propagated to the history table for a system-period temporal table.

DROP GENERATED

Drops the generated attributes of the column. The column must be defined as a generated column (SQLSTATE 42837). The column must not be defined as a row-begin column, row-end column, or a transaction-start-ID column in a system-period temporal table (SQLSTATE 428FR).

DROP NOT NULL

Drops the NOT NULL attribute of the column, allowing the column to have the null value. This clause is not allowed if the column is specified in the primary key, in a unique constraint of the table (SQLSTATE 42831), a row-begin column, or a row-end column (SQLSTATE 42837).

Altering this attribute for a column requires a classic table reorganization before full table access is allowed (SQLSTATE 57007).

The table cannot have data capture enabled (SQLSTATE 42997). DROP NOT NULL is blocked for columns that belong to the BUSINESS_TIME period (SQLSTATE 428FR).

If the table is a system-period temporal table, the NOT NULL attribute is also dropped from the corresponding column in any associated history table.

If either a row permission or column mask exists, which depends on the column to be altered, an error is issued (SQLSTATE 42917).

ADD SCOPE

Add a scope to an existing reference type column that does not already define a scope that is defined (SQLSTATE 428DK). If the altered table is a typed table, the column must not be inherited from a supertable (SQLSTATE 428DJ).

typed-table-name

The name of a typed table. The data type of column-name must be REF(S), where S is the type of typed-table-name (SQLSTATE 428DM). No checking is done of any existing values in column-name to ensure that the values reference existing rows in typed-table-name.

typed-view-name

The name of a typed view. The data type of column-name must be REF(S), where S is the type of typed-view-name (SQLSTATE 428DM). No checking is done of any existing values in column-name to ensure that the values reference existing rows in typed-view-name.

COMPRESS

Specifies whether default values for this column are to be stored more efficiently.

SYSTEM DEFAULT

Specifies that system default values (that is, the default values used for the data types when no specific values are specified) are to be stored by using minimal space. If the table is not already set with the VALUE COMPRESSION attribute activated, a warning is returned (SQLSTATE 01648), and system default values are not stored by using minimal space.

Allowing system default values to be stored in this manner causes a slight performance penalty during insert and update operations on the column because of the extra checking that is done.

Existing data in the column is not changed. Consider offline table reorganization to enable existing data to take advantage of storing system default values by using minimal space.

OFF

Specifies that system default values are to be stored in the column as regular values. Existing data in the column is not changed. Offline reorganization is recommended to change existing data.

The base data type must not be DATE, TIME, or TIMESTAMP (SQLSTATE 42842). If the base data type is a varying-length string, this clause is ignored. String values of length 0 are automatically compressed if a table was set with VALUE COMPRESSION.

If the altered table is a typed table, the column must not be inherited from a supertable (SQLSTATE 428DJ).

SECURED WITH security-label-name

Identifies a security label that exists for the security policy that is associated with the table. The name must not be qualified (SQLSTATE 42601). The table must be associated with a security (SQLSTATE 55064). The table must not be a system-period temporal table.

DROP COLUMN SECURITY

Alters a column to make it a non-protected column.

ACTIVATE ROW ACCESS CONTROL

Activates row level access control on the table. The table must not be a typed table, a catalog table (SQLSTATE 55019), a created temporary table, a declared temporary table (SQLSTATE 42995), a nickname (SQLSTATE 42809), a view (SQLSTATE 42809), or an external table (SQLSTATE 42858). The table must not identify a shadow table or a base table of a shadow table (SQLSTATE 428HZ).

A default row permission is implicitly created and allows no access to any rows of the table, unless permitted by a row permission that is explicitly created by a user with SECADM authority.

When the table is referenced in a data manipulation statement, all enabled row permissions that were created for the table, including the default row permission, are applied implicitly by the database manager to control the set of rows in the table that are accessible.

If a trigger exists for the table, the trigger must be defined with the SECURED attribute (SQLSTATE 55019).

The table must not be referenced in the definition of a view if an INSTEAD OF trigger that is defined with the NOT SECURED attribute exists for the view (SQLSTATE 55019).

If a materialized query table references the table, the functions that are referenced in the fullselect of materizalized-query-definition must be defined with the SECURED attribute (SQLSTATE 55019).

If a materialized query table (or a staging table) that depends on the table (directly or indirectly through a view) for which row level access control is being activated and that materialized query table (or a staging table) did not already activate row level access control, row level access control is implicitly activated for the materialized query table (or a staging table). This restricts direct access to the contents of the materialized query table (or a staging table). A query that explicitly references the table before such a row permission is defined returns a warning that no data in the table exists (SQLSTATE 02000). To provide access to the materialized query table (or a staging table), an appropriate row permission can be created, or an ALTER TABLE DEACTIVATE ROW ACCESS CONTROL statement on the materialized query table (or a staging table) can be issued to remove the row level protection if that is appropriate.

ACTIVATE ROW ACCESS CONTROL is ignored if row access control is already defined as activated for the table.

If the table is a system-period temporal table, the database manager automatically activates row access control on the history table and creates a default row permission for the history table.

If the table is a column-organized table, the database manager automatically activates row access control on the synopsis table and creates a default row permission for the synopsis table.

ACTIVATE COLUMN ACCESS CONTROL

Activates column level access control on the table. The table must not be a typed table, a catalog table (SQLSTATE 55019), a created temporary table, a declared temporary table (SQLSTATE 42995), a nickname (SQLSTATE 42809), a view (SQLSTATE 42809), or an external table (SQLSTATE 42858). The table must not identify a shadow table or a base table of a shadow table (SQLSTATE 428HZ).

The access to the table is not restricted but when the table is referenced in a data manipulation statement, all enabled column masks that were created for the table are applied implicitly by the database manager to mask the values that are returned for the referenced columns in the final result table of the queries.

If a trigger exists for the table, the trigger must be defined with the SECURED attribute (SQLSTATE 55019).

The table must not be referenced in the definition of a view if an INSTEAD OF trigger that is defined with the NOT SECURED attribute exists for the view (SQLSTATE 55019). If a materialized query table that depends on the table (directly or indirectly through a view) for which column level access control is being activated and that materialized query table did not already activate row level access control, row level access control is implicitly activated for the materialized query table. This restricts direct access to the contents of the materialized query table. A query that explicitly references the table before such a row permission is defined returns a warning that no data in the table exists (SQLSTATE 02000). To provide access to the materialized query table, an appropriate row permission can be created, or an ALTER TABLE DEACTIVATE ROW ACCESS CONTROL statement on the materialized query table can be issued to remove the row level protection if that is appropriate.

ACTIVATE COLUMN ACCESS CONTROL is ignored if column level access control is already defined as activated for the table.

If the table is a system-period temporal table, the database manager automatically activates row access control on the history table and creates a default row permission for the history table.

If the table is a column-organized table, the database manager automatically activates row access control on the synopsis table and creates a default row permission for the synopsis table.

DEACTIVATE ROW ACCESS CONTROL

Deactivates row level access control on the table. When the table is referenced in a data manipulation statement, any existing enabled row permissions that are defined on the table are not applied by the database manager to control the set of rows in the table that are accessible. The table must not identify a shadow table or a base table of a shadow table (SQLSTATE 428HZ).

DEACTIVATE ROW ACCESS CONTROL is ignored if row access control is not activated for the table.

DEACTIVATE COLUMN ACCESS CONTROL

Deactivates column level access control on the table. When the table is referenced in a data manipulation statement, any existing enabled column masks defined on the table are not applied by the database manager to control the values that are returned for the columns that are referenced in the final result table of the queries. The table must not identify a shadow table or a base table of a shadow table (SQLSTATE 428HZ).

DEACTIVATE COLUMN ACCESS CONTROL is ignored if column access control is not activated for the table.

RENAME COLUMN source-column-name TO target-column-name

Renames the column that is specified in source-column-name to the name that is specified in target-column-name. If the auto_reval database configuration parameter is set to DISABLED, the RENAME COLUMN option of the ALTER TABLE statement behaves like it is under the control of revalidation immediate semantics.

The table must not be defined as a history table (SQLSTATE 42986). If the table is a system-period temporal table, the column is also renamed in any associated history table.

Columns that are used in expression-based index keys cannot be renamed (SQLSTATE 42893).

RENAME COLUMN must not rename a column that is referenced in the definition of a row permission or a column mask. Also, It must not rename a column for which a column mask is defined (SQLSTATE 42917). If you rename a column that belongs to a table on which a mask or a permission is defined, or to a table that is referenced in the definition of a mask or a permission, that mask or permission is invalidated. Access to a table that activated column access control and a defined invalid mask on it is blocked until the invalid mask is either disabled, dropped, or re-created (SQLSTATE 560D0). Access to a table that activated row access control and defined an invalid row permission on it is blocked until the invalid permission is either disabled, dropped, or re-created (SQLSTATE 560D0).

source-column-name

Specifies the name of the column that is to be renamed. The source-column-name must identify an existing column of the table (SQLSTATE 42703). The name must not be qualified. The name must not identify a column that is otherwise being added, altered, or dropped in the same ALTER TABLE statement (SQLSTATE 42711).

target-column-name

The new name for the column. The name must not be qualified. Existing column names or period names in the table must not be used (SQLSTATE 42711).

DROP PRIMARY KEY

Drops the definition of the primary key and all referential constraints dependent on this primary key. The table must have a primary key (SQLSTATE 42888).

DROP FOREIGN KEY constraint-name

Drops the referential constraint constraint-name. The constraint-name must identify a referential constraint (SQLSTATE 42704). For information about implications of dropping a referential constraint, see Notes.

DROP UNIQUE constraint-name

Drops the definition of the unique constraint constraint-name and all referential constraints dependent on this unique constraint. The constraint-name must identify an existing UNIQUE constraint (SQLSTATE 42704). For information on implications of dropping a unique constraint, see Notes.

DROP CHECK constraint-name

Drops the check constraint constraint-name. The constraint-name must identify an existing check constraint that is defined on the table (SQLSTATE 42704).

DROP CONSTRAINT constraint-name

Drops the constraint constraint-name. The constraint-name must identify an existing check constraint, referential constraint, primary key, or unique constraint defined on the table (SQLSTATE 42704). For information about implications of dropping a constraint, see Notes.

DROP COLUMN

Drops the identified column from the table. The table must not be a typed table (SQLSTATE 428DH). The table cannot have data capture enabled (SQLSTATE 42997).

Dropping a column requires a classic table reorganization before full table access is allowed (SQLSTATE 57007).

An XML column can be dropped only if all of the other XML columns in the table are dropped at the same time only.

DROP COLUMN must not drop a column that is referenced in the definition of a row permission or a column mask (SQLSTATE 42917). However, a column for which a column mask is defined can be dropped. When the column is dropped, any column mask that is defined on that column is also dropped.

column-nameIdentifies the column that is to be dropped. The column name must not be qualified. The name must identify a column of the specified table (SQLSTATE 42703). The name must not identify the only column of the table (SQLSTATE 42814), or a column referenced in the definition of a period (SQLSTATE 42817). The name must not identify the last column of the table that is not hidden (SQLSTATE 428GU). The name must not identify a column in a table that is defined as a system-period temporal table or history table (SQLSTATE 428FR). The name must not identify a column that is part of the distribution key, table-partitioning key, or organizing dimensions (SQLSTATE 42997).

CASCADE

Specifies the following actions, based on the object:

Any views that depend on the column that is dropped are marked inoperative
Any indexes, triggers, SQL functions, constraints, or global variables that depend on the column that is dropped are also dropped
Any decomposition-enabled XSROBJECTs that depend on the table that contains the column are made inoperative for decomposition.

A trigger depends on the column if it is referenced in the UPDATE OF column list, or anywhere in the triggered action. A decomposition-enabled XSROBJECT depends on a table if it contains a mapping of an XML element or attribute to the table. If an SQL function or global variable depends on another database object, it might not be possible to drop the function or global variable by using the CASCADE option. CASCADE is the default.

RESTRICT

Specifies that the column cannot be dropped if any views, indexes, triggers, constraints, or global variables depend on the column, or if any decomposition-enabled XSROBJECT depends on the table that contains the column (SQLSTATE 42893). A trigger depends on the column if it is referenced in the UPDATE OF column list, or anywhere in the triggered action. A decomposition-enabled XSROBJECT depends on a table if it contains a mapping of an XML element or attribute to the table. The first dependent object that is detected is identified in the administration log.

Table 3. Cascaded Effects of Dropping a Column
Operation	RESTRICT Effect	CASCADE Effect
Dropping a column that is referenced by a view or a trigger	Dropping the column is not allowed.	The object and all objects that depend on that object are dropped.
Dropping a column that is referenced in the key of an index	If all columns that are referenced in the index are dropped in the same ALTER TABLE statement, dropping the index is allowed. Otherwise, dropping the column is not allowed.	The index is dropped.
Dropping a column that is referenced in a unique constraint	If all columns that are referenced in the unique constraint are dropped in the same ALTER TABLE statement, and the unique constraint is not referenced by a referential constraint, the columns and the constraint are dropped. (The index that is used to satisfy the constraint is also dropped). Otherwise, dropping the column is not allowed.	The unique constraint and any referential constraints, which reference that unique constraint, are dropped. (Any indexes that are used by those constraints are also dropped).
Dropping a column that is referenced in a referential constraint	If all columns that are referenced in the referential constraint are dropped in the same ALTER TABLE statement, the columns and the constraint are dropped. Otherwise, dropping the column is not allowed.	The referential constraint is dropped.
Dropping a column that is referenced by a system-generated column that is not being dropped.	Dropping the column is not allowed.	Dropping the column is not allowed.
Dropping a column that is referenced in a check constraint	Dropping the column is not allowed.	The check constraint is dropped.
Dropping a column that is referenced in a decomposition-enabled XSROBJECT	Dropping the column is not allowed.	The XSROBJECT is marked inoperative for decomposition. Reenabling the XSROBJECT might require readjustment of its mappings; afterward, issue an ALTER XSROBJECT ENABLE DECOMPOSITION statement against the XSROBJECT.
Dropping a column that is referenced in the default expression of a global variable	Dropping the column is not allowed.	The global variable is dropped, unless the dropping of the global variable is disallowed because other objects exist, which do not allow the cascade, that depends on the global variable.

DROP RESTRICT ON DROP

Removes the restriction, if there is one, on dropping the table and the table space that contains the table.

DROP DISTRIBUTION

Drops the distribution definition for the table. The table must have a distribution definition (SQLSTATE 428FT). The table space for the table must be defined on a single partition database partition group.

DROP MATERIALIZED QUERY

Changes a materialized query table so that it is no longer considered to be a materialized query table. The table that is specified by table-name must be defined as a materialized query table that is not replicated (SQLSTATE 428EW). The definition of the columns of table-name is not changed, but the table can no longer be used for query optimization, and the REFRESH TABLE statement can no longer be used.

If row level access control or column level access control is in effect for the table, this control remains after the table is no longer a materialized query table.

ADD PERIOD period-definition

Adds a period definition to the table.

SYSTEM_TIME (begin-column-name, end-column-name)

Defines a system period with the name SYSTEM_TIME. There must not be a column in the table with the name SYSTEM_TIME (SQLSTATE 42711). A table can have only one SYSTEM_TIME period (SQLSTATE 42711). begin-column-name must be defined as ROW BEGIN and end-column-name must be defined as ROW END (SQLSTATE 428HN).

BUSINESS_TIME (begin-column-name, end-column-name)

Defines an application period with the name BUSINESS_TIME. There must not be a column in the table with the name BUSINESS_TIME (SQLSTATE 42711). A table can have only one BUSINESS_TIME period (SQLSTATE 42711). begin-column-name and end-column-name must both be defined as DATE or TIMESTAMP(p) where p is 0 - 12 (SQLSTATE 42842), and the columns must be defined as NOT NULL (SQLSTATE 42831). begin-column-name and end-column-name must not identify a column that is defined with a GENERATED clause (SQLSTATE 428HZ). Business time period columns cannot be added to a table that is in set integrity pending state.

An implicit check constraint is generated to ensure that the value of end-column-name is greater than the value of begin-column-name. The name of the implicitly created check constraint is DB2_GENERATED_CHECK_CONSTRAINT_FOR_BUSINESS_TIME and must not be the name of an existing check constraint (SQLSTATE 42710).

DROP PERIOD period-name

Drops the identified period from the table. The name must not identify a period that was already added or altered in this ALTER TABLE statement (SQLSTATE 42711). Any implicitly generated check constraints for the period (created when the period was defined) and any indexes that reference the period are also dropped.

period-name

Identifies the period. Valid period names are BUSINESS_TIME or SYSTEM_TIME. The period must exist in the table (SQLSTATE 4274M).

When a BUSINESS_TIME period is dropped, all packages with the application-period temporal table dependency type on that table are invalidated. Other dependent objects like views and triggers that record a dependency on the table are also marked as invalid.

SYSTEM_TIME period cannot be dropped if the table is a system-period temporal table (SQLSTATE 428HZ).

DATA CAPTURE

Indicates whether extra information for data replication is to be written to the log.

If the table is a typed table, then this option is not supported (SQLSTATE 428DH for root tables or 428DR for other subtables).

NONE

Indicates that no extra information is logged.

CHANGES

Indicates that extra information with regards to SQL changes to this table is written to the log. This option is required if this table is replicated and the Capture program is used to capture changes for this table from the log.

INCLUDE LONGVAR COLUMNS: Allows data replication utilities to capture changes that are made to LONG VARCHAR or LONG VARGRAPHIC columns. The clause can be specified for tables that do not have any LONG VARCHAR or LONG VARGRAPHIC columns since it is possible to ALTER the table to include such columns.

ACTIVATE NOT LOGGED INITIALLY

Activates the NOT LOGGED INITIALLY attribute of the table for this current unit of work.

Any changes that are made to the table by an INSERT, DELETE, UPDATE, CREATE INDEX, DROP INDEX, or ALTER TABLE in the same unit of work after the table is altered by this statement are not logged. Any changes that are made to the system catalog by the ALTER statement in which the NOT LOGGED INITIALLY attribute is activated are logged. Any subsequent changes that are made in the same unit of work to the system catalog information are logged.

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.

If you use this feature to avoid locks on the catalog tables while you insert data, it is important that only this clause is specified on the ALTER TABLE statement. Use of any other clause in the ALTER TABLE statement results in catalog locks. If no other clauses are specified for the ALTER TABLE statement, then only a SHARE lock is acquired on the system catalog tables. This can greatly reduce the possibility of concurrency conflicts during time between when this statement is run and when the unit of work in which it was run is ended.

If the table is a typed table, this option is only supported on the root table of the typed table hierarchy (SQLSTATE 428DR).

If the table is a system-period temporal table or a history table, this option is not supported.

For more information about the NOT LOGGED INITIALLY attribute, see the description of this attribute in CREATE TABLE statement.

Note: If non-logged activity occurs against a table that has the NOT LOGGED INITIALLY attribute activated, and if a statement fails (causing a rollback), or a ROLLBACK TO SAVEPOINT is run, the entire unit of work is rolled back (SQL1476N). Furthermore, the table for which the NOT LOGGED INITIALLY attribute was activated is marked inaccessible after the rollback occurs and can be dropped only. Therefore, the opportunity for errors within the unit of work in which the NOT LOGGED INITIALLY attribute is activated is minimized.

WITH EMPTY TABLE

Causes all data currently in table to be removed. When the data is removed, it cannot be recovered except through use of the RESTORE facility. If the unit of work in which this alter statement was issued is rolled back, the table data is not returned to its original state.

When this action is requested, no DELETE triggers defined on the affected table are fired. The index data is also deleted for all indexes that exist on the table.

A partitioned table with attached data partitions or logically detached partitions cannot be emptied (SQLSTATE 42928).

PCTFREE integer

Specifies the percentage of each page that is to be left as free space during a load or a table reorganization operation. The first row on each page is added without restriction. When more rows are added to a page, at least integer percent of the page is left as free space. The PCTFREE value is considered only by the load and table reorg utilities. The value of integer can range 0 - 99. A PCTFREE value of -1 in the system catalog (SYSCAT.TABLES) is interpreted as the default value. The default PCTFREE value for a table page is 0. If the table is a typed table, this option is only supported on the root table of the typed table hierarchy (SQLSTATE 428DR).

LOCKSIZE

Indicates the size (granularity) of locks used when the table is accessed. Use of this option in the table definition does not prevent normal lock escalation from occurring.

If the table is a typed table, this option is only supported on the root table of the typed table hierarchy (SQLSTATE 428DR).

The LOCKSIZE keyword is not supported for column-organized tables (SQLSTATE 42858).

ROW: Indicates the use of row locks. This is the default lock size when a table is created.
BLOCKINSERT: Indicates the use of block locks during insert operations. This means that the appropriate exclusive lock is acquired on the block before insertion, and row locking is not done on the inserted row. This option is useful when separate transactions are inserting into separate cells in the table. Transactions inserting into the same cells can still do so concurrently, but insert into distinct blocks, and this can impact the size of the cell if more blocks are needed. This option is only valid for MDC tables (SQLSTATE 42613).
TABLE: Indicates the use of table locks. This means that the appropriate share or exclusive lock is acquired on the table, and that intent locks (except intent none) are not used. For partitioned tables, this lock strategy is applied to both the table lock and the data partition locks for any data partitions that are accessed. Use of this value can improve the performance of queries by limiting the number of locks that need to be acquired. However, concurrency is also reduced because all locks are held over the complete table.

APPEND

Indicates whether data is appended to the end of the table data or placed where free space is available in data pages. If the table is a typed table, this option is only supported on the root table of the typed table hierarchy (SQLSTATE 428DR).

ON: Indicates that table data is appended and information about free space on pages is not kept. The table must not have a clustered index (SQLSTATE 428CA).
OFF: Indicates that table data is placed in available space. This is the default when a table is created.
The table is reorganized after you set APPEND OFF since the information about available free space is not accurate and can result in poor performance during insert.

VOLATILE CARDINALITY or NOT VOLATILE CARDINALITY

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. CARDINALITY is an optional keyword. The default is NOT VOLATILE.

VOLATILE: Specifies that the cardinality of table table-name can vary significantly at run time, from empty to large. To access the table, the optimizer uses an index scan (rather than a table scan, regardless of the statistics) if that index is index-only (all referenced columns are in the index), or that index is able to apply a predicate in the index scan. The list prefetch access method is not used to access the table. If the table is a typed table, this option is only supported on the root table of the typed table hierarchy (SQLSTATE 428DR).
NOT VOLATILE: Specifies that the cardinality of table-name is not volatile. Access plans to this table continues to be based on existing statistics and on the current optimization level.

COMPRESS

Specifies whether data compression applies to the rows of the table.

YES

Specifies that row and XML compression are enabled. Insert and update operations on the table are subject to compression. Index compression is enabled for new indexes unless explicitly disabled in the CREATE INDEX statement. Existing indexes can be compressed by using the ALTER INDEX statement.

After a table is altered to enable row compression, all rows in the table can be compressed immediately by running one of the following actions:

REORG command
Online table move
Data unload and reload

ADAPTIVE: Enables adaptive compression for the table. Data rows are subject to compression with both table-level and page-level compression dictionaries. XML documents in the XML storage object are subject to compression with a table-level XML compression dictionary. Page-level compression dictionaries are created automatically as rows are inserted or updated. Table-level compression dictionaries are created for both row and XML data automatically after sufficient data is added, unless they exist.
STATIC: Enables classic row compression for the table. Data rows are subject to compression with a table-level compression dictionary, and XML documents in the XML storage object are subject to compression by using a table-level XML compression dictionary. If no table-level compression dictionaries exist for either row or XML data, they will be created automatically after sufficient data is added.

If neither of the preceding two options are specified along with the COMPRESS YES clause, ADAPTIVE is used implicitly.

NO

Specifies that data row and XML compression are disabled. Inserted and updated data rows and XML documents in the table is no longer subject to compression. Any rows and XML documents in the table that are already in compressed format remain in compressed format until they are converted to non-compressed format when they are updated.

An offline reorganization of the table decompresses any rows that are remain compressed.

If table-level or page-level compression dictionaries exist, they are discarded during table reorganization or truncation (such as a LOAD REPLACE operation). Index compression is disabled for new indexes that are created on that table unless explicitly enabled in the CREATE INDEX statement. Index compression for existing indexes can be explicitly disabled by using the ALTER INDEX statement.

VALUE COMPRESSION

This determines the row format that is to be used. Each data type has a different byte count depending on the row format that is used. For more information, see Byte Counts in CREATE TABLE statement. An update operation causes an existing row to be changed to the new row format.

Offline table reorganization is recommended to improve the performance of update operations on existing rows. This can also result in the table to take up less space.

If the row size, which is calculated by using the appropriate column in the table Byte Counts of Columns by Data Type (see CREATE TABLE), would no longer fit within the row size limit, as indicated in the table Limits for Number of Columns and Row Size In Each Table Space Page Size, an error is returned (SQLSTATE 54010). If the table is a typed table, this option is only supported on the root table of the typed table hierarchy (SQLSTATE 428DR).

ACTIVATE: The NULL value is stored by using 3 bytes. This is the same or less space than when VALUE COMPRESSION is not active for columns of all data types, except for CHAR(1). Whether a column is defined as nullable has no effect on the row size calculation. The zero-length data values for columns whose data type is VARCHAR, VARGRAPHIC, VARBINARY, CLOB, DBCLOB, or BLOB are to be stored by using 2 bytes only, which is less than the storage required when VALUE COMPRESSION is not active. When a column is defined by using the COMPRESS SYSTEM DEFAULT option, this also allows the system default value for the column to be stored by using 3 bytes of total storage. The row format that is used to support this determines the byte counts for each data type, and tends to cause data fragmentation when updating to or from NULL, a zero-length value, or the system default value.
DEACTIVATE: The null value is stored with space set aside for possible future updates. This space is not set aside for varying-length columns. It also does not support efficient storage of system default values for a column. If columns exist with the COMPRESS SYSTEM DEFAULT attribute, a warning is returned (SQLSTATE 01648).

LOG INDEX BUILD

Specifies the level of logging that is to be performed during create, re-create, or reorganize index operations on this table.

NULL: Specifies that the value of the logindexbuild database configuration parameter is used to determine whether index build operations are to be logged. This is the default when the table is created.
OFF: Specifies that any index build operations on this table will be logged minimally. This value overrides the setting of the logindexbuild database configuration parameter.
ON: Specifies that any index build operations on this table will be logged completely. This value overrides the setting of the logindexbuild database configuration parameter.

ADD PARTITION add-partition

Adds one or more data partitions to a partitioned table. If the specified table is not a partitioned table, an error is returned (SQLSTATE 428FT). The number of data partitions must not exceed 32 767.

Names the data partition. The name must not be the same as any other data partition for the table (SQLSTATE 42710). If this clause is not specified, the name will be 'PART' followed by the character form of an integer value to make the name unique for the table.
boundary-spec: Specifies the range of values for the new data partition. This range must not overlap that of an existing data partition (SQLSTATE 56016). For a description of the starting-clause and the ending-clause, see CREATE TABLE.
If the starting-clause is omitted, the new data partition is assumed to be at the end of the table. If the ending-clause is omitted, the new data partition is assumed to be at the start of the table.
IN tablespace-name: Specifies the table space where the data partition is to be stored. The named table space must have the same page size, be in the same database partition group, and manage space in the same way as the other table spaces of the partitioned table (SQLSTATE 42838). This can be a table space that is already being used for another data partition of the same table, or a table space that is not being used by this table, but it must be a table space on which the authorization ID of the statement holds the USE privilege (SQLSTATE 42727). If this clause is not specified, the table space of the first visible or attached data partition of the table is used.
INDEX IN tablespace-name: Specifies the table space where partitioned indexes on the data partition are stored. If the INDEX IN clause is not specified, partitioned indexes on the data partition are stored in the same table space as the data partition.
The table space that is used by the new index partition, whether default or specified by the INDEX IN clause, must match the type (SMS or DMS), page size, and extent size of the table spaces used by all other index partitions (SQLSTATE 42838).
LONG IN tablespace-name: Specifies the table space where the data partition that contains long column data is to be stored. The named table space must have the same page size, be in the same database partition group, and manage space in the same way as the other table spaces and data partitions of the partitioned table (SQLSTATE 42838); it must be a table space on which the authorization ID of the statement holds the USE privilege. The page size and extent size for the named table space can be different from the page size and extent size of the other data partitions of the partitioned table.
For rules that govern the use of the LONG IN clause with partitioned tables, see Large object behavior in partitioned tables.

ATTACH PARTITION attach-partition

Attaches another table as a new data partition. The data object of the table being attached becomes a new partition of the table being attached to. There is no data movement involved. The table is placed in set integrity pending state, and referential integrity checking is deferred until execution of a SET INTEGRITY statement. The ALTER TABLE ATTACH operation does not allow the use of the IN or LONG IN clause. The placement of LOBs for that data partition is determined at the time the source table is created. For rules that govern the use of the LONG IN clause with partitioned tables, see Large object behavior in partitioned tables.

If the table being attached has either row level access control or column level access control activated, then the table to attach to must have the same controls activated. No row permissions or column masks are automatically carried over from the table being attached to the target table. The column masks and row permissions do not necessarily need to be the same on both tables, although this would be best from a security perspective. But if the table being attached has row level access control activated then the table to attach to must also have row level access control activated (SQLSTATE 428GE). Similarly, if the table being attached has column level access control activated and at least one column mask object that is enabled then the table to attach to must also have column level access control activated and a column mask object that is enabled for the corresponding columns (SQLSTATE 428GE).

partition-nameNames the data partition. The name must not be the same as any other data partition for the table (SQLSTATE 42710). If this clause is not specified, the name is 'PART' followed by the character form of an integer value to make the name unique for the table.

boundary-specSpecifies the range of values for the new data partition. This range must not overlap that of an existing data partition (SQLSTATE 56016). For a description of the starting-clause and the ending-clause, see CREATE TABLE.

If the starting-clause is omitted, the new data partition is assumed to be at the end of the table. If the ending-clause is omitted, the new data partition is assumed to be at the start of the table.

FROM table-name1

Specifies the table that is to be used as the source of data for the new partition. The table definition of table-name1 cannot have multiple data partitions, and it must match the altered table in the following ways (SQLSTATE 428GE):

The number of columns must be the same.
The data types of the columns in the same ordinal position in the table must be the same.
The nullability characteristic of the columns in the same ordinal position in the table must be the same.
If the target table has a row change timestamp column, the corresponding column of the source table must be a row change timestamp column.
If the data is also distributed, it must be distributed over the same database partition group by using the same distribution keys.
If either table is a random distribution table that uses the random by generation method, the other table must be one too.
If the data in either table is organized, the organization must match.
For structured, XML, or LOB data type, the value for INLINE LENGTH must be the same.
If the target table has a defined BUSINESS_TIME period, the source table must have a defined BUSINESS_TIME period on the corresponding columns.

After the data from table-name1 is successfully attached, an operation equivalent to DROP TABLE table-name1 is performed to remove this table, which no longer has data, from the database.

BUILD MISSING INDEXES

Specifies that if the source table does not have indexes that correspond to the partitioned indexes on the target table, a SET INTEGRITY operation builds partitioned indexes on the new data partition to correspond to the partitioned indexes on the existing data partitions. Indexes on the source table that do not match the partitioned indexes on the target table are dropped during attach processing.

REQUIRE MATCHING INDEXES

Specifies that the source table must have indexes to match the partitioned indexes on the target table; otherwise, an error is returned (SQLSTATE 428GE) and information is written to the administration log about the indexes that do not match.

If the REQUIRE MATCHING INDEXES clause is not specified and the indexes on the source table do not match all the partitioned indexes on the target table, the following behavior occurs:

For indexes on the target table that do not have a match on the source table and are either unique indexes or XML indexes that are defined with REJECT INVALID VALUES, the ATTACH operation fails (SQLSTATE 428GE).
For all other indexes on the target table that do not have a match on the source table, the index object on the source table is marked invalid during the attach operation. If the source table does not have any indexes, an empty index object is created and marked as invalid. The ATTACH operation succeeds, but the index object on the new data partition is marked as invalid. Typically, SET INTEGRITY is the next operation to run against the data partition. SET INTEGRITY forces a rebuild, if required, of the index object on data partitions that were recently attached. The index rebuild can increase the time that is required to bring the new data online.
Information is written to the administration log about the indexes that do not match.

DETACH PARTITION partition-name INTO table-name1

Detaches the data partition partition-name from the altered table, and uses the data partition to create a new table named table-name1. The data partition is detached from the altered table and is used to create the new table without any data movement. The specified data partition cannot be the last remaining partition of the table being altered (SQLSTATE 428G2). The table being altered to detach a partition must not be a system-period temporal table (SQLSTATE 428HZ).

When a partition is detached from a table for which either row level access control or column level access control is defined, the new table that is created for the detached data will automatically have row level access control (though not column level access control) activated to protect the detached data. Direct access to this new table returns no rows until appropriate row permissions are defined for the table or row level access control is deactivated for this table.

ADD SECURITY POLICY policy-name

Adds a security policy to the table. The security policy must exist at the current server (SQLSTATE 42704). The table must not already have a security policy (SQLSTATE 55065), and must not be a typed table (SQLSTATE 428DH), materialized query table (MQT), or staging table (SQLSTATE 428FG). This clause does not activate row or column protection by itself. For more information, see Protection of data using LBAC.

DROP SECURITY POLICY

Removes the security policy and all LBAC protection from the table. The table that is specified by table-name must be protected by a security policy (SQLSTATE 428GT). If the table has a column with data type DB2SECURITYLABEL, the data type is changed to VARCHAR (128) FOR BIT DATA. If the table has one or more protected columns, those columns become unprotected.

ADD VERSIONING USE HISTORY TABLE history-table-name

Specifies that the table is a system-period temporal table. The table must not already be defined as a system-period temporal table or a history table (SQLSTATE 428HM). A SYSTEM_TIME period and a transaction-start-ID column must be defined in the table (SQLSTATE 428HM).

The table must not be a materialized query table (SQLSTATE 428HM).

Historical versions of the rows in the table are retained by the database manager. The database manager records extra information that indicates when a row was inserted into the table, and when it was updated or deleted. When a row in a system-period temporal table is updated, a previous version of the row is kept. When data in a system-period temporal table is deleted, the old version of the row is inserted as a historical record. An associated history table is used to store the historical rows of the table.

References to the table can include a time period search condition to indicate which system versions of the data are to be returned.

history-table-name identifies a history table where historical rows of the system-period temporal table are kept. history-table-name must identify a table that exists at the current server (SQLSTATE 42704), and is not a catalog table (SQLSTATE 42832), an existing system-period temporal table, an existing history table, a declared global temporary table, a created global temporary table, a materialized query table, or a view (SQLSTATE 428HX).

The identified history table must not contain an identity column, row change timestamp column, row-begin column, row-end column, transaction start-ID column, generated expression column, or include a period (SQLSTATE 428HX).

The system-period temporal table and the identified history table must have the same number and order of columns (SQLSTATE 428HX). The following attributes for the corresponding columns of the two tables must be the same (SQLSTATE 428HX):

Column name
Column data type
Column length (including inline LOB lengths), precision, and scale
Column FOR BIT attribute for character string columns
Column null attribute
Column hidden attribute

If row access control or column access control is activated for the system-period temporal table and row access control is not activated on the history table, the database manager automatically activates row access control on the history table and creates a default row permission for the history table.

DROP VERSIONING

Specifies that the table is no longer a system-period temporal table. The table must be a system-period temporal table (SQLSTATE 428HZ). Historical data is no longer recorded and maintained for the table. The definition of the columns and data of the table are not changed, but the table is no longer treated as a system-period temporal table. The SYSTEM_TIME period is retained. Subsequent queries that reference the table must not specify a SYSTEM_TIME period specification for the table. The relationship between the system-period temporal table and the associated history table is removed. The history table is not dropped and the contents of the history table are not affected.

When a table is altered with DROP VERSIONING, all packages with the system-period temporal table dependency type on that table are invalidated. Other dependent objects like views and triggers that record a dependency on the table are also marked as invalid.

Rules

Any enforced unique or primary key constraint that is defined on the table must be a superset of the distribution key, if there is one (SQLSTATE 42997).
Primary or unique keys cannot be subsets of dimensions (SQLSTATE 429BE).
A column can be referenced in one ADD, ALTER, or DROP COLUMN clause in a single ALTER TABLE statement only (SQLSTATE 42711).
A column length, data type, or hidden attribute cannot be altered, nor can the column be dropped, if the table has any materialized query tables that depend on the table (SQLSTATE 42997).
A column length cannot be altered if the table has any views enabled for query optimization that depend on the table (SQLSTATE 42997).
The table must be put in set integrity pending state, by using the SET INTEGRITY statement with the OFF option (SQLSTATE 55019) before:
- Adding a column with a generation expression
- Altering the generated expression of a column
- Changing a column to have a generated expression
An existing column cannot be altered to become of type DB2SECURITYLABEL (SQLSTATE 42837).
Defining a column of type DB2SECURITYLABEL fails if the table is not associated with a security policy (SQLSTATE 55064).
A column of type DB2SECURITYLABEL cannot be altered or dropped (SQLSTATE 42817).
An ALTER TABLE operation to mark a table as protected fails if there exists an MQT that depends on that table (SQLSTATE 55067).
Attaching a partition to a protected partitioned table fails if the source table and the target table are not protected by using the same security policy, do not have the same row security label column, and do not have the same set of protected columns (SQLSTATE 428GE).
If a generated column is referenced in a table-partitioning key, the generated column expression cannot be altered (SQLSTATE 42837).
The isolation-clause cannot be specified in the fullselect of the materialized-query-definition (SQLSTATE 42601).
Adding or attaching a data partition to a partitioned table fails with SQL0612N after detaching the same partition name, if asynchronous index cleanup has not finished deleting index entries for the partition (SQLSTATE 42711).

Notes

A REORG-recommended operation occurs when changes that result from an ALTER TABLE statement affect the row format of existing data. When this occurs, most subsequent operations on the table are restricted until a table reorganization operation completes successfully (SQLSTATE 57007). Many REORG-recommended ALTER TABLE actions may be run against a table in a single unit of work, either in the same statement or spread over multiple statements. This is considered to be a single REORG-recommended operation. For example, dropping two columns in a single ALTER TABLE statement or in two statements in the same unit of work is considered to be a single REORG-recommended operation. There can be up to three units of work containing REORG-recommended operations before a classic offline table reorganization must be done (SQLTATE 55019). The reorg pending state and the number of REORG-recommended operations are returned from SYSIBMADM.ADMINTABINFO columns reorg_pending and num_reorg_rec_alters respectively.
The following is the full list of REORG-recommended ALTER statements that cause a version change and place the table into a REORG-pending state:
- DROP COLUMN
- ALTER COLUMN SET NOT NULL
- ALTER COLUMN DROP NOT NULL
- ALTER COLUMN SET DATA TYPE, except in the following situations:
  - Increasing the length of a VARCHAR or VARGRAPHIC column
  - Decreasing the length of a VARCHAR or VARGRAPHIC column without truncating trailing blanks from existing data, when no indexes exist on the column
The following table operations are allowed after a successful REORG-recommended operation occurs:
- ALTER TABLE. However, the following operations are not allowed (SQLSTATE 57007):
  - ADD CHECK CONSTRAINT
  - ADD REFERENTIAL CONSTRAINT
  - ADD UNIQUE CONSTRAINT
- DROP TABLE
- RENAME TABLE
- REORG TABLE
- TRUNCATE TABLE
- Table scan access of table data
Altering a table to make it a materialized query table puts the table in set integrity pending state. If the table is defined as REFRESH IMMEDIATE, the table must be taken out of set integrity pending state before INSERT, DELETE, or UPDATE commands can be started on the table that is referenced by the fullselect. The table can be taken out of set integrity pending state by using REFRESH TABLE or SET INTEGRITY, with the IMMEDIATE CHECKED option, to completely refresh the data in the table based on the fullselect. If the data in the table accurately reflects the result of the fullselect, the IMMEDIATE UNCHECKED option of SET INTEGRITY can be used to take the table out of set integrity pending state.
Altering a table to change it to a REFRESH IMMEDIATE materialized query table causes any packages with INSERT, DELETE, or UPDATE usage on the table that is referenced by the fullselect to be invalidated.
Altering a table to change from a materialized query table to a regular table causes any packages dependent on the table to be invalidated.
Altering a table to change from a MAINTAINED BY FEDERATED_TOOL materialized query table to a regular table does not cause any change in the subscription setup of the replication tool. Because a subsequent change to a MAINTAINED BY SYSTEM materialized query table causes the replication tool to fail, you must change the subscription setting when you change a MAINTAINED BY FEDERATED_TOOL materialized query table.
If a deferred materialized query table is associated with a staging table, the staging table is dropped if the materialized query table is altered to a regular table.
ADD column clauses are processed before all other clauses. Other clauses are processed in the order that they are specified.
Any columns added through an alter table operation is not automatically added to any existing view of the table.
Adding or attaching a data partition to a partitioned table, or detaching a data partition from a partitioned table, causes any packages that depend on that table to be invalidated.
After you detach a data partition from a data partitioned table, the STATUS of the detached partition in the SYSCAT.DATAPARTITIONS catalog can be 'L' when the partition is logically detached and the detach operation is not completed. If the STATUS of the detached partition is 'L', the following operations cannot be performed on the source table (SQLSTATE 55057):
- Adding a unique or primary key constraint that attempts to create a nonpartitioned index
- Adding, dropping, or renaming a column
- Activating value compression or compression
- Deactivating value compression or compression
To drop the partitioning for a table, the table must be dropped and then re-created.
To drop the organization for a table, the table must be dropped and then re-created.
When an index is automatically created for a unique or primary key constraint, the database manager tries to use the specified constraint name as the index name with a schema name that matches the schema name of the table. If this matches an existing index name or no name for the constraint was specified, the index is created in the SYSIBM schema with a system-generated name that is formed of "SQL" followed by a sequence of 15 numeric characters that are generated by a timestamp-based function.
When a nonpartitioned index is created on a partitioned table with attached data partitions, the index does not include the data in the attached data partitions. Use the SET INTEGRITY statement to maintain all indexes for all attached data partitions.
When you create a partitioned index in the presence of attached data partitions (STATUS of 'A' in SYSCAT.DATAPARTITIONS), an index partition for each attached data partition is also created. If the partitioned index is being created as unique, or is an XML index that is created with REJECT INVALID VALUES, then the index creation can fail if an attached data partition contains any violations (duplicates for a unique index, or invalid values for the XML index).
If a table has a nonpartitioned index, you cannot access a new data partition in that table within the same transaction as the add or attach operation that created the partition, if the transaction does not have the table locked in exclusive mode (SQLSTATE 57007).
Any table that might be involved in a DELETE operation on table T is said to be delete-connected to T. Thus, a table is delete-connected to T if it is a dependent of T or it is a dependent of a table in which deletes from T cascade.
A package has an insert (update/delete) usage on table T if records are inserted into (updated in/deleted from) T either directly by a statement in the package, or indirectly through constraints or triggers run by the package on behalf of one of its statements. Similarly, a package has an update usage on a column if the column is modified directly by a statement in the package, or indirectly through constraints or triggers run by the package on behalf of one of its statements.
In a federated system, a remote base table that was created by using transparent DDL can be altered. However, transparent DDL does impose some limitations on the modifications that can be made:
- A remote base table can be altered by adding new columns or specifying a primary key only.
- Specific clauses that are supported by transparent DDL include:
  - ADD COLUMN column-definition
  - NOT NULL and PRIMARY KEY in the column-options clause
  - ADD unique-constraint (PRIMARY KEY only)
- You cannot specify a comment on an existing column in a remote base table.
- An existing primary key in a remote base table cannot be altered or dropped.
- Altering a remote base table invalidates any packages that depend on the nickname that is associated with that remote base table.
- The remote data source must support the changes being requested through the ALTER TABLE statement. Depending on how the data source responds to requests it does not support, an error might be returned or the request might be ignored.
- An attempt to alter a remote base table that was not created by using transparent DDL returns an error.

Any changes, whether implicit or explicit, to primary key, unique keys, or foreign keys might have the following effects on packages, indexes, and other foreign keys:

Table 4. Changes to keys, and their effects on packages, indexes, and other foreign keys
Action	Effect on packages, indexes, and other foreign keys
Primary key or unique key is added	There is no effect on packages, foreign keys, or existing unique keys. (If the primary or unique key uses an existing unique index that was created in a previous version and was not converted to support deferred uniqueness, the index is converted, and packages with update usage on the associated table are invalidated).
Primary key or unique key is dropped	The index is dropped if it was automatically created for the constraint. Any packages dependent on the index are invalidated. The index is set back to non-unique if it was converted to unique for the constraint and it is no longer system-required. Any packages dependent on the index are invalidated. The index is set to no longer system required if it was an existing unique index that is used for the constraint. There is no effect on packages. A primary key or unique constraint of the table cannot be dropped if it is the last enforced primary key or unique constraint whose set of columns is in the select list of an associated shadow table. A primary key or unique constraint cannot be dropped if the table has an associated shadow table, and the primary key of the associated shadow table depends on the constraint being dropped. All dependent foreign keys are dropped. Further action is taken for each dependent foreign key, as specified in the next row.
Foreign key is added, dropped, altered from NOT ENFORCED to ENFORCED, or altered from ENFORCED to NOT ENFORCED	All packages with an insert usage on the object table are invalidated. All packages with an update usage on at least one column in the foreign key are invalidated. All packages with a delete usage on the parent table are invalidated. All packages with an update usage on at least one column in the parent key are invalidated.
Foreign key or a functional dependency is altered from ENABLE QUERY OPTIMIZATION to DISABLE QUERY OPTIMIZATION	All packages with dependencies on the constraint for optimization purposes are invalidated.

Adding a column to a table results in invalidation of all packages with insert usage on the altered table. If the added column is the first user-defined structured type column in the table, packages with DELETE usage on the altered table is also invalidated.
Adding a check or referential constraint to a table that exists and that is not in set integrity pending state, or altering the existing check or referential constraint from NOT ENFORCED to ENFORCED on an existing table that is not in set integrity pending state causes the existing rows in the table to be immediately evaluated against the constraint. If the verification fails, an error is returned (SQLSTATE 23512). If a table is in set integrity pending state, adding a check or referential constraint, or altering a constraint from NOT ENFORCED to ENFORCED does not immediately lead to the enforcement of the constraint. Issue the SET INTEGRITY statement with the IMMEDIATE CHECKED option to begin enforcing the constraint.
Adding, altering, or dropping a check constraint results in invalidation of all packages with either an insert usage on the object table, an update usage on at least one of the columns that are involved in the constraint, or a select usage using the constraint to improve performance.
Adding a distribution key invalidates all packages with an update usage on at least one of the columns of the distribution key.
A distribution key that was defined by default is not affected by dropping the primary key and adding a different primary key.
Dropping a column or changing its data type removes all runstats information from the table being altered. Runstats must be run on the table after it is again accessible. The statistical profile of the table is preserved if the table does not contain a column that was explicitly dropped.
Altering a column (to change its length, data type, nullability, or hidden attribute) or dropping a column invalidates all packages that reference (directly or indirectly through a referential constraint or trigger) its table.
Altering a column (to change its length, data type, nullability, or hidden attribute) regenerates views (except typed views) that depend on its table. If a problem occurs while regenerating such a view, an error is returned (SQLSTATE 56098). Any typed views that depend on the table are marked inoperative.
Altering a column (to change its length, data type, or hidden attribute) marks all dependent triggers and SQL functions as invalid; they are implicitly recompiled on next use. If a problem occurs while regenerating such an object, an error is returned (SQLSTATE 56098).
Altering a column (to change its length, data type, or nullability attribute) might cause errors (SQLSTATE 54010) while processing a trigger or an SQL function when a statement that involves the trigger or SQL function is prepared or bound. This can occur if the row size based on the sum of the lengths of the transition variables and transition table columns is too long. If such a trigger or SQL function is dropped, a subsequent attempt to re-create it returns an error (SQLSTATE 54040).
A WLM activity event monitor created in an earlier version must be dropped and re-created to add new table columns that are introduced by this fix pack and any subsequent fix packs or releases.
Altering a structured or XML type column to increase the inline length invalidates all packages that reference the table, either directly or indirectly through a referential constraint or trigger.
Altering a structured or XML type column to increase the inline length regenerates views that depend on the table.
A compression dictionary can be created for the XML storage object of a table only if the XML columns are added to the table, or if the table is migrated the using an online table move.
Changing the LOCKSIZE for a table results in invalidation of all packages that have a dependency on the altered table.
Changing VOLATILE or NOT VOLATILE CARDINALITY results in invalidation of all dynamic SQL statements that have a dependency on the altered table.
Replication: Exercise caution when you increase the length or changing the data type of a column. The change data table that is associated with an application table might already be at or near the row size limit. The change data table must be altered before the application table, or the two tables must be altered within the same unit of work to ensure that the alteration can be completed for both tables. Consideration should be given to copies, which might also be at or near the row size limit, or reside on platforms that lack the ability to increase the length of an existing column.
If the change data table is not altered before the Capture program processes log records with the altered attributes, the Capture program will likely fail. If a copy that contains the altered column is not altered before the subscription maintaining the copy runs, the subscription will likely fail.
When detaching a partition from a protected table, the target table that is automatically created by the database server is protected in the same way the source table is protected.
When a table is altered such that it becomes protected with row level granularity, any cached dynamic SQL sections that depend on such a table are invalidated. Similarly, any packages that depend on such a table are also invalidated.
When a column of a table, T, is altered such that it becomes a protected column, any cached dynamic SQL sections that depend on table T are invalidated. Similarly, any packages that depend on table T are also invalidated.
When a column of a table, T, is altered such that it becomes a non protected column, any cached dynamic SQL sections that depend on table T are invalidated. Similarly, any packages that depend on table T are also invalidated.
For existing rows in the table, the value of the security label column defaults to the security label for write access of the session authorization ID at the time the ALTER statement that adds a row security label column is executed.
Add materialized query: When a base table is altered to become a materialized query table, the label-based access control security attributes (security policy, column security labels, row security label column) are derived in the same way when creating a new materialized query table. If the base table that is altered already has label-based access control security attributes, these attributes are factored in the derivation process as follows:
- Column access control: The existing security label for a column is aggregated with the corresponding security label that is derived from the query that defines the materialized query table.
- Row access control: The row access control attributes are set up exactly in the same way as for a new materialized query table.
In Db2 Version 9.7 Fix Pack 1 or later releases, new multidimensional clustering (MDC) table block indexes are partitioned. Adding a data partition to a data partitioned multidimensional clustering (MDC) table creates the corresponding empty index partitions for the new partition, including the MDC block indexes. Also, a new index partition entry is added to SYSCAT.SYSINDEXPARTITIONS for each MDC block index and for each partitioned index.
When you attach a data partition to a partitioned MDC table created with Db2 V9.7 Fix Pack 1 or later releases, the source table that is specified by attach-partition can be a nonpartitioned MDC table or a single-partition partitioned MDC table.
- If the source table is nonpartitioned: MDC block indexes on the source table will be inherited and become the partitioned MDC indexes for the new partition after the ATTACH operation completes.
- If the source table is partitioned: If the source table is a partitioned MDC table that is created with Db2 V9.7 Fix Pack 1 or later releases, the block indexes are partitioned. The block indexes become the new block indexes on the partition.
- If the source partitioned MDC table is created at a level lower than Db2 V9.7 Fix Pack 1, the block indexes on the table are nonpartitioned. During the ATTACH operation, the block indexes are dropped and created as partitioned indexes similar to the other partitioned indexes on the source table.
  Issuing the SET INTEGRITY statement on the target table is required to bring the attached partition online.
  
  If the REQUIRE MATCHING INDEXES clause is specified, and the target table is a partitioned MDC table that is created in Db2 V9.7 Fix Pack 1 or later releases, the ALTER TABLE ... ATTACH PARTITION statement fails and returns SQL20307N (SQLSTATE 428GE). Removing the REQUIRE MATCHING INDEXES clause allows the attach process to proceed.
If the target partitioned MDC table was created at a level lower than Db2 V9.7 Fix Pack 1, the block indexes are nonpartitioned. The block indexes on the source MDC table are dropped during the ATTACH operation. Issuing a SET INTEGRITY statement on the target table is required to bring the attached partition online. New rows from the attached partition are added to existing nonpartitioned block indexes.
When you detach a data partition from a data partitioned MDC table that is created at a level lower than Db2 V9.7 Fix Pack 1, the block indexes are nonpartitioned. The following restrictions apply:
- Access to the newly detached table is not allowed in the same unit of work as the detach operation.
- Block indexes on the target table, created as part of the detach operation, are rebuilt upon the first access to the table after the detach operation is committed. If the source table had any partitioned indexes before the detach operation, then the index object for the target table is marked invalid to allow for recreation of the block indexes. As a result, access time is increased while the block indexes and all other partitioned indexes are re-created.
When you detach a partition from a partitioned MDC table created by using Db2 V9.7 Fix Pack 1 or later releases, the block indexes are partitioned, and the previous restrictions do not apply. Assuming that no other dependent objects such as dependent MQTs exist, access to the newly detached table is allowed in the same unit of work. All the partitioned indexes, including block indexes, become indexes on the target table without the need to be re-created.
Considerations for implicitly hidden columns: A column that is defined as implicitly hidden can be explicitly referenced in an ALTER TABLE statement. For example, an implicitly hidden column can be altered or specified as part of a referential constraint, check constraint, or materialized query table definition.
Altering a table to make some of its columns implicitly hidden can impact the behavior of data movement utilities that are working with the table. When a table contains implicitly hidden columns, utilities like IMPORT, INGEST, and LOAD require that you specify whether data for the hidden columns is included in the operation. For example, this might mean that a load operation that ran successfully before the table was altered, now fails (SQLCODE SQL2437N). Similarly, EXPORT requires that you specify whether data for the hidden columns is included in the operation.

Data movement utilities must use the DB2_DMU_DEFAULT registry variable, or the implicitlyhiddeninclude or implicitlyhiddenmissing file type modifiers when working with tables that contain implicitly hidden columns.
Row access control that is activated explicitly: The ACTIVATE ROW ACCESS CONTROL clause is used to activate row access control for a table. When this happens, a default row permission is implicitly created and allows no access to any rows of the table, unless permitted by a row permission that is explicitly created by the security administrator. The default row permission is always enabled.
When the table is referenced in a data manipulation statement, all enabled row permissions that have been created for the table, including the default row permission, are implicitly applied by the database manager to control which rows in the table are accessible. A row access control search condition is derived by application of the logical OR operator to the search condition in each enabled row permission. This derived search condition acts as a filter to the table before any user specified operations, such as predicates, grouping, ordering, are processed. This derived search condition permits the authorization IDs that are specified in the permission definitions to access certain rows in the table.

When the ACTIVATE ROW ACCESS CONTROL clause is used, all the packages and dynamically cached statements that reference the table are invalidated.

Row access control remains enforced until the DEACTIVATE ROW ACCESS CONTROL clause is used to stop enforcing it.
Implicit object that is created when row access control is activated for a table: When the ACTIVATE ROW ACCESS CONTROL clause is used to activate row access control for a table, the database manager implicitly creates a default row permission for the table. The default row permission prevents all access to the table. The implicitly created row permission resides in the same schema of the base table and has a name in the form of SYS_DEFAULT_ROW_PERMISSION__table-name ... up to 128 characters. Notice two underscores after "PERMISSION". If this name is not unique, the last 4 characters are reserved for a unique number 'nnnn', where 'nnnn' is a four-alphanumeric-character string that start at '0000' and is incremented by one value each time until a unique name is found.
The owner of the default row permission is SYSIBM. The default row permission is always enabled. The default row permission is dropped when row access control is deactivated or when the table is dropped.
Activating column access control: The ACTIVATE COLUMN ACCESS CONTROL clause is used to activate column level access control for a table. The access to the table is not restricted but when the table is referenced in a data manipulation statement, all enabled column masks that were created for the table are applied to mask the column values referenced in the final result table.
When column masks are used to mask the column values, they determine the values in the final result table. If a column has a column mask and the column (specifically a simple reference to a column name or a column that is embedded in an expression) appears in the outermost select list, the column mask is applied to the column to produce the values for the final result table. If the column does not appear in the outermost select list but it participates in the final result table, for example, it appears in a materialized table expression or view, the column mask is applied to the column in such a way that the masked value is included in the result table of the materialized table expression or view so that it can be used in the final result table.

The application of column masks does not interfere with the operations of other clauses within the statement such as the WHERE, GROUP BY, HAVING, SELECT DISTINCT, and ORDER BY. The rows that are returned in the final result table remain the same, except that the values in the resulting rows might have been masked by the column masks. As such, if the masked column also appears in an ORDER BY sort-key, the order is based on the original column values and the masked values in the final result table might not reflect that order. Similarly, the masked values might not reflect the uniqueness that is enforced by SELECT DISTINCT.
A column mask is applied in the following contexts:
- The outermost SELECT clause or clauses of a SELECT or SELECT INTO statement, or if the column does not appear in the outermost select list but it participates in the final result table, one or more outermost SELECT clauses of the corresponding materialized table expression or view where the column appears.
- The outermost SELECT clause or clauses of a SELECT FROM INSERT, SELECT FROM UPDATE, or SELECT FROM DELETE operation.
- The outermost SELECT clause or clauses that are used to derive the new values for an INSERT, UPDATE, or MERGE statement, or a SET transition-variable-name assignment statement. The same masking applies to a scalar fullselect expression that appears in the outermost SELECT clause or clauses of the previously mentioned statements, the right side of a SET host-variable assignment statement, the VALUES INTO statement, or the VALUES statement.
Column masks are not applied when the masked column appears in the following contexts:
- WHERE clauses.
- GROUP BY clauses.
- HAVING clauses.
- SELECT DISTINCT.
- ORDER BY clauses.
Row and column access control are not enforced when EXPLAIN tables are populated: Row and column access control can be enforced for EXPLAIN tables. However, the enabled row permissions and column masks are not applied when the database manager inserts rows into those tables.
Row and column access control are not enforced when event monitor tables are populated: Row and column access control can be enforced for event monitor tables. However, the enabled row permissions and column masks are not applied when the database manager inserts rows into those tables.
Row and column access control are not enforced when temporal history tables are populated: Row and column access control can be enforced for temporal history tables. However, the enabled row permissions and column masks are not applied when the database manager accesses those tables for operations on the system-period temporal tables.
Stop enforcing row or column access control: The DEACTIVATE ROW ACCESS CONTROL clause is used to stop enforcing row access control for a table. The default row permission is dropped. Thereafter, when the table is referenced in a data manipulation statement, explicitly created row permissions are not applied.
The DEACTIVATE COLUMN ACCESS CONTROL clause is used to stop enforcing column access control for a table. Thereafter, when the table is referenced in a data manipulation statement, the column masks are not applied.

The explicitly created row permissions or column masks, if any, remain but have no effect.

All the packages and dynamically cached statements that reference the table are invalidated when row or column access control is deactivated.
Secure triggers for row and column access control: Triggers are used for database integrity, and as such, a balance between row and column access control (security) and database integrity is needed. Enabled row permissions and column masks are not applied to the initial values of transition variables and transition tables. Row and column access control enforced for the triggering table is also ignored for any transition variables or transition tables that are referenced in the trigger body. To ensure that no security concern exist for SQL statements in the trigger action to access sensitive data in transition variables and transition tables, the trigger must be created or altered with the SECURED option. If a trigger is not secure, row and column access control cannot be enforced for the triggering table (SQLSTATE 55019).
Secure user-defined functions for row and column access control: If a row permission or column mask definition references a user-defined function, the function must be altered with the SECURED option because the sensitive data might be passed as arguments to the function. When a user-defined function is referenced in a data manipulation statement where a table that enforces row or column access control is referenced, and the function arguments reference the columns from such a table, if the function is not secure, this impacts the access plan selection and might yield poor performance. The database manager considers the SECURED option an assertion that declares the user has established a change control audit procedure for all changes to the user-defined function. It is assumed that such a control audit procedure is in place and that all subsequent ALTER FUNCTION statements or changes to external packages are being reviewed by this audit process.
Database operations where row and column access control is not applicable: Row and column access control must not compromise database integrity. Columns that are involved in primary keys, unique keys, indexes, check constraints, and referential integrity must not be subject to row and column access control. Column masks can be defined for those columns but they are not applied during the process of key building or constraint or RI enforcement.
Defining a system-period temporal table: A system-period temporal table definition includes the following aspects:
- A system period named SYSTEM_TIME, which is defined by using a row-begin column and a row-end column. See the descriptions of AS ROW BEGIN, AS ROW END, and period-definition.
- A transaction-start-ID column. See the description of AS TRANSACTION START ID.
- A system-period data versioning definition that is specified on a subsequent ALTER TABLE statement by using the ADD VERSIONING action, which includes the name of the associated history table. See the description of the ADD VERSIONING clause under ALTER TABLE.
To ensure that the history table cannot be implicitly dropped when a system-period temporal table is dropped, use the WITH RESTRICT ON DROP clause in the definition of the history table.
Defining an application-period temporal table: An application-period temporal table definition includes an application period with the name BUSINESS_TIME. The application period is defined by using a begin column and an end column with both columns having the same data type that is either DATE or TIMESTAMP(p). See the description of period-definition.
Data-change operations on an application-period temporal table can result in an automatic insert of one or two extra rows when a row is updated or deleted. When an update or delete of a row in an application-period temporal table is specified for a portion of the period represented by that row, the row is updated or deleted and one or two rows are automatically inserted to represent the portion of the row that is not changed. New values are generated for each generated column in an application-period temporal table for each row that is automatically inserted as a result of an update or delete operation on the table. If a generated column is defined as part of a unique or primary key, parent key in a referential constraint, or unique index, it is possible that an automatic insert violates a constraint or index in which case an error is returned.
Considerations for transaction-start-ID columns: A transaction-start-ID column contains a null value if the column allows null values, and there is a row-begin column, and the value of the row-begin column is unique from values of row-begin columns that are generated for other transactions. Given that the column might contain null values, it is recommended that one of the following methods be used when you retrieve a value from the column:
- COALESCE (transaction_start_id_col, row_begin_col)
- CASE WHEN transaction_start_id_col IS NOT NULL THEN transaction_start_id_col ELSE row_begin_col END
Considerations for system-period temporal tables and row and column access control: Row and column access control can be defined on both the system-period temporal table and the associated history table.
- When a system-period temporal table is accessed, any row and column access rules that are defined on the system-period temporal table are applied to all of the rows that are returned from the system-period temporal table, regardless of whether the rows are stored in the system-period temporal table or the history table. The row and column access rules that are defined on the history table are not applied.
- When the history table is accessed directly, the row and column access rules that are defined on the history table are applied.
When a system-period temporal table is defined and row access control or column access control is activated for the system-period temporal table, the database manager automatically activates row access control on the history table and creates a default row permission for the history table.
Considerations for column-organized tables: The following options can be specified to alter a column-organized table definition (underlined options are defaults):
- ACTIVATE NOT LOGGED INITIALLY
- ACTIVATE/DEACTIVATE COLUMN ACCESS CONTROL
- ACTIVATE/DEACTIVATE ROW ACCESS CONTROL
- ADD COLUMN, unless there is an outstanding asynchronous background process that is creating the table's column compression dictionary (SQL20054N)
- ADD CONSTRAINT <constraint-name> CHECK NOT ENFORCED (when used with {ENABLE | DISABLE} QUERY OPTIMIZATION)
- ADD CONSTRAINT <constraint-name> {PRIMARY KEY | UNIQUE | FOREIGN KEY NOT ENFORCED}
- ADD MATERIALIZED QUERY
- ADD PERIOD {SYSTEM_TIME | BUSINESS_TIME}
- ADD RESTRICT ON DROP
- ADD VERSIONING USE HISTORY TABLE
- ALTER COLUMN <column name> SET DATA TYPE (increase VARCHAR/VARGRAPHIC column length only)
- ALTER COLUMN <column-name> SET {NOT HIDDEN | IMPLICITLY HIDDEN}
- DATA CAPTURE NONE
- DROP {PRIMARY KEY | UNIQUE | CONSTRAINT} <constraint-name>
- DROP DEFAULT
- DROP GENERATED
- DROP MATERIALIZED QUERY
- DROP PERIOD {SYSTEM_TIME | BUSINESS_TIME}
- DROP RESTRICT ON DROP
- DROP VERSIONING
- LOG INDEX BUILD {NULL | OFF | ON}
- SET GENERATED [ALWAYS | BY DEFAULT]
- SET DEFAULT
- SET GENERATED AS ROW {BEGIN | END}
- SET GENERATED AS TRANSACTION START ID
Other options are not supported for column-organized tables.
Considerations for random distribution tables
- Altering the table to make it a materialized query table is not supported
Considerations for random distribution tables that use generate by random method
- The following options are not supported to alter a random distribution key column definition (in addition to those options, which are already not supported for a distribution key column in a non-random distribution table):
  - ALTER COLUMN <column-name> SET NOT HIDDEN
  - ALTER COLUMN <column-name> DROP NOT NULL
  - ALTER COLUMN <column-name> DROP GENERATED
  - ALTER COLUMN <column-name> COMPRESS {OFF | SYSTEM DEFAULT}
  - RENAME COLUMN <column-name>
- Random distribution cannot be dropped with ALTER TABLE DROP DISTRIBUTION statement.
Syntax alternatives: The following alternatives are non-standard. They are supported for compatibility with earlier product versions or with other database products.
- The ADD keyword is optional for:
  - Unnamed PRIMARY KEY constraints
  - Unnamed referential constraints
  - Referential constraints whose name follows the phrase FOREIGN KEY
- The CONSTRAINT keyword can be omitted from a column-definition defining a references-clause
- constraint-name can be specified following FOREIGN KEY (without the CONSTRAINT keyword)
- SET SUMMARY AS can be specified in place of SET MATERIALIZED QUERY AS
- SET MATERIALIZED QUERY AS DEFINITION ONLY can be specified in place of DROP MATERIALIZED QUERY
- SET MATERIALIZED QUERY AS (fullselect) can be specified in place of ADD MATERIALIZED QUERY (fullselect)
- ADD PARTITIONING KEY can be specified in place of ADD DISTRIBUTE BY HASH; the optional USING HASHING clause can also still be specified in this case
- DROP PARTITIONING KEY can be specified in place of DROP DISTRIBUTION
- The LONG VARCHAR and LONG VARGRAPHIC data types continue to be supported but are deprecated and not recommended, especially for portable applications
- A comma can be used to separate multiple options in the identity-alteration clause
- PART can be specified in place of PARTITION
- VALUES can be specified in place of ENDING AT
- NOMINVALUE, NOMAXVALUE, NOCYCLE, NOCACHE, and NOORDER can be specified in place of NO MINVALUE, NO MAXVALUE, NO CYCLE, NO CACHE, and NO ORDER, respectively
- DROP EXPRESSION can be specified in place of DROP GENERATED to drop the generated expression attribute for a column.
- DROP IDENTITY can be specified in place of DROP GENERATED to drop the identity attribute for a column.
- When you specify the value of the datetime special register, NOW() can be specified in place of CURRENT_TIMESTAMP.
When an ALTER TABLE statement invalidates an existing VIEW, the statistics profile for the invalidated VIEW is blanked.

Examples

Add a column named RATING, which is one character long, to the DEPARTMENT table.
```
   ALTER TABLE DEPARTMENT
      ADD RATING CHAR(1)
```
Add a column named SITE_NOTES to the PROJECT table. Create SITE_NOTES as a varying-length column with a maximum length of 1000 bytes. The values of the column do not have an associated character set and therefore must not be converted.
```
   ALTER TABLE PROJECT
      ADD SITE_NOTES  VARCHAR(1000) FOR BIT DATA
```
Assume a table that is called EQUIPMENT exists defined with the following columns:
```
   Column Name        Data Type
   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. DEPTNO is the primary key of the DEPARTMENT table. If a department is removed from the DEPARTMENT table, the owner (EQUIP_OWNER) values for all equipment that is owned by that department should become unassigned (or set to null). Give the constraint the name DEPTQUIP.
```
   ALTER TABLE EQUIPMENT
        ADD CONSTRAINT DEPTQUIP
         FOREIGN KEY (EQUIP_OWNER)
           REFERENCES DEPARTMENT
              ON DELETE SET NULL
```
Also, an extra column is needed to allow the recording of the quantity that is associated with this equipment record. Unless otherwise specified, the EQUIP_QTY column should have a value of 1 and must never be null.
```
   ALTER TABLE EQUIPMENT
     ADD COLUMN EQUIP_QTY
     SMALLINT NOT NULL DEFAULT 1
```
Alter table EMPLOYEE. Add the check constraint that is 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)
```
Alter table EMPLOYEE. Drop the constraint REVENUE, which was previously defined.
```
   ALTER TABLE EMPLOYEE
     DROP CONSTRAINT REVENUE
```

Alter a table to log SQL changes in the default format.

   ALTER TABLE SALARY1
     DATA CAPTURE NONE

Alter a table to log SQL changes in an expanded format.

   ALTER TABLE SALARY2
     DATA CAPTURE CHANGES

Alter the EMPLOYEE table to add four new columns with default values.
```
  ALTER TABLE EMPLOYEE
     ADD COLUMN HEIGHT MEASURE   DEFAULT MEASURE(1)
     ADD COLUMN BIRTHDAY BIRTHDATE DEFAULT DATE('01-01-1850')
     ADD COLUMN FLAGS BLOB(1M)  DEFAULT BLOB(X'01')
     ADD COLUMN PHOTO PICTURE   DEFAULT BLOB(X'00')
```
The default values use various function names when specifying the default. Since MEASURE is a distinct type based on INTEGER, the MEASURE function is used. The HEIGHT column default could have been specified without the function since the source type of MEASURE is not BLOB or a datetime data type. Since BIRTHDATE is a distinct type based on DATE, the DATE function is used (BIRTHDATE cannot be used here). For the FLAGS and PHOTO columns the default is specified by using the BLOB function even though PHOTO is a distinct type. To specify a default for BIRTHDAY, FLAGS and PHOTO columns, a function must be used because the type is a BLOB or a distinct type that is sourced on a BLOB or datetime data type.
A table called CUSTOMERS is defined with the following columns:
```
   Column Name        Data Type
   BRANCH_NO          SMALLINT
   CUSTOMER_NO        DECIMAL(7)
   CUSTOMER_NAME      VARCHAR(50)
```
In this table, the primary key is made up of the BRANCH_NO and CUSTOMER_NO columns. To distribute the table, you need to create a distribution key for the table. The table must be defined in a table space on a single-node database partition group. The primary key must be a superset of the distribution key columns, and at least one of the columns of the primary key must be used as the distribution key. Make BRANCH_NO the distribution key as follows:
```
   ALTER TABLE CUSTOMERS 
     ADD DISTRIBUTE BY HASH (BRANCH_NO)
```
A remote table EMPLOYEE was created in a federated system by using transparent DDL. Alter the remote table EMPLOYEE to add the columns PHONE_NO and WORK_DEPT; also, add a primary key on the existing column EMP_NO and the new column WORK_DEPT.
```
   ALTER TABLE EMPLOYEE
     ADD COLUMN PHONE_NO CHAR(4) NOT NULL
     ADD COLUMN WORK_DEPT CHAR(3)
     ADD PRIMARY KEY (EMP_NO, WORK_DEPT)
```

Alter the DEPARTMENT table to add a functional dependency FD1, then drop the functional dependency FD1 from the DEPARTMENT table.

   ALTER TABLE DEPARTMENT
     ADD CONSTRAINT FD1
       CHECK ( DEPTNAME DETERMINED BY DEPTNO) NOT ENFORCED

   ALTER TABLE DEPARTMENT
     DROP CHECK FD1

Change the default value for the WORKDEPT column in the EMPLOYEE table to 123.

   ALTER TABLE EMPLOYEE
     ALTER COLUMN WORKDEPT
       SET DEFAULT '123'

Associate the security policy DATA_ACCESS with the table EMPLOYEE.

   ALTER TABLE EMPLOYEE
     ADD SECURITY POLICY DATA_ACCESS

Alter the table EMPLOYEE to protect the SALARY column.

   ALTER TABLE EMPLOYEE
     ALTER COLUMN SALARY
     SECURED WITH EMPLOYEESECLABEL

Assume that you have a table that is named SALARY_DATA that is defined with the following columns:

Column Name            Data Type
-----------            ---------
EMP_NAME               VARCHAR(50) NOT NULL
EMP_ID                 SMALLINT NOT NULL
EMP_POSITION           VARCHAR(100) NOT NULL
SALARY                 DECIMAL(5,2)
PROMOTION_DATE         DATE NOT NULL

Change this table to allow salaries to be stored in a DECIMAL(6,2) column, make PROMOTION_DATE an optional field that can be set to the null value, and remove the EMP_POSITION column.

   ALTER TABLE SALARY_DATA
     ALTER COLUMN SALARY SET DATA TYPE DECIMAL(6,2)
     ALTER COLUMN PROMOTION_DATE DROP NOT NULL
     DROP COLUMN EMP_POSITION

Add a column named DATE_ADDED to the table BOOKS. The default value for this column is the current time stamp.

   ALTER TABLE BOOKS 
     ADD COLUMN DATE_ADDED TIMESTAMP 
     WITH DEFAULT CURRENT TIMESTAMP

Alter table with label-based access control security attributes into a materialized query table. Base tables tt1 and tt2 exist and were created with the following SQL:
```
   CREATE TABLE tt1
      (c1 INT SECURED WITH C, c2 DB2SECURITYLABEL) SECURITY POLICY P;
   CREATE TABLE tt2
      (c3 INT SECURED WITH B, c4 DB2SECURITYLABEL) SECURITY POLICY P;
```
Table tt2 can be altered to be a materialized query table with the following SQL:
```
   ALTER TABLE tt2 ADD (SELECT * FROM tt1 WHERE c1 > 10) 
      DATA INITIALLY DEFERRED REFRESH DEFERRED;
```
Table tt2 becomes a materialized query table with the secure policy P. tt2.c3 has security label P.B. tt2.c4 has security label P.C and it is also DB2SECURITYLABEL.