ALTER TABLE

The ALTER TABLE statement alters the definition of a table.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

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

For the table identified in the statement,
- The ALTER privilege on the table, and
- The USAGE privilege on the schema containing the table
Database administrator authority

To define a foreign key, the privileges held by the authorization ID of the statement must include at least one of the following on the parent table:

The REFERENCES privilege or object management authority for the table
The REFERENCES privilege on each column of the specified parent key
Database administrator authority

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

The following system authorities:
- The system authority *EXECUTE on the program, and
- The USAGE privilege on the schema containing the program
Database administrator authority

If a select-statement is specified, the privileges held by the authorization ID of the statement must include at least one of the following on the tables or views specified in these clauses:

The SELECT privilege for the table or view
Database administrator authority

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

For each distinct type identified in the statement:
- The USAGE privilege on the distinct type, and
- The USAGE privilege on the schema containing the distinct type
Database administrator authority

If an ACTIVATE or DEACTIVATE clause is specified:

The authorization ID of the statement must have security administrator authority. See Administrative authority.

To drop a partition from a table that has active row permissions with DELETE ROWS, the privileges held by the authorization ID of the statement must include at least one of the following:

The system authorities of *OBJOPR and *OBJEXIST on the table
Database administrator authority

To attach a partition, the privileges held by the authorization ID of the statement must also include at least one of the following:

For the source table identified in the statement,
- The SELECT privilege, and
- The system authority of *OBJEXIST, and
- The USAGE privilege on the schema containing the source table
Database administrator authority

To detach a partition, the privileges held by the authorization ID of the statement must also include at least one of the following:

For the source table identified in the statement,
- The SELECT, DELETE, and ALTER privileges, and
- The USAGE privilege on the schema containing the source table
Database administrator authority

To detach a partition, the privileges held by the authorization ID of the statement must include at least one of the following:

The privilege to create in the schema. For more information, see Privileges necessary to create in a schema.
Database administrator authority

To alter a table to become a system-period temporal table (with the ADD VERSIONING clause), alter a system-period temporal table when one or more of the changes also result in changes to the associated history table, or to DROP VERSIONING, the privileges that are held by the authorization ID of the statement must also include at least one of the following authorities:

The following system authorities:
- The ALTER privilege on the history table, and
- The USAGE privilege on the schema containing the history table
Database administrator authority

For information on the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View and Corresponding System Authorities When Checking Privileges to a Distinct Type.

Syntax

media-preference

memory-preference

column-definition

data-type

Notes:

¹ ATTACH PARTITION or DETACH PARTITION cannot be specified with any other clause.
² ADD VERSIONING or DROP VERSIONING cannot be specified with any other clause.
³ If an ACTIVATE or DEACTIVATE clause is specified, clauses other than ACTIVATE or DEACTIVATE are not allowed on the ALTER TABLE statement. Each clause must not be specified more than once.
⁴ data-type is optional for row change timestamp, row-begin, row-end, and transaction-start-ID columns.
⁵ The same clause must not be specified more than once.
⁶ The datalink-options can only be specified for DATALINKs and distinct types sourced on DATALINKs.

built-in-type

allocate-clause

ccsid-clause

normalize-clause

default-clause

generated-clause

identity-options

as-row-change-timestamp-clause

as-row-transaction-timestamp-clause

as-row-transaction-start-id-clause

as-generated-expression-clause

non-deterministic-expression

special-register

built-in-global-variable

column-constraint

datalink-options

file-link-options

column-alteration

generated-alteration

identity-alteration

unique-constraint

referential-constraint

references-clause

check-constraint

period-definition

partitioning-clause

range-partition-spec

partition-expression

partition-element

boundary-spec

starting-clause

ending-clause

hash-partition-spec

add-partition

attach-partition

materialized-query-definition

refreshable-table-options

materialized-query-table-alteration

Notes:

¹ GENERATED can be specified only if the column has a ROWID data type (or a distinct type that is based on a ROWID data type), or the column is an identity column, or the column is a row change timestamp.
² The same clause must not be specified more than once.
³ All five file-link-options must be specified, but they can be specified in any order.
⁴ The same clause must not be specified more than once.
⁵ GENERATED can be specified only if the column has a ROWID data type (or a distinct type that is based on a ROWID data type), the column is an identity column, identity-options are specified, as-row-transaction-timestamp-clause is specified, as-row-transaction-start-id-clause is specified, or the column is a row change timestamp.
⁶ The ON DELETE and ON UPDATE clauses may be specified in either order.
⁷ The same clause must not be specified more than once.
⁸ This syntax for a partition-element is valid if there is only one partition-expression with a numeric or datetime data type.
⁹ The same clause must not be specified more than once. MAINTAINED BY USER must be specified.
¹⁰ The same clause must not be specified more than once.

Description

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

ADD COLUMN column-definition

Adds a column to the table. The table cannot be a history table. If the table has rows, every value of the column is set to its default value, unless the column is a ROWID column or a generated column. The database manager generates default values for ROWID columns and generated columns. If the table previously had n columns, the ordinality of the new column is n+1. The value of n+1 must not exceed 8000.

A table can have only one ROWID and one of each type of generated column.

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

Adding a new column must not make the sum of the row buffer byte counts of the columns be greater than 32766 or, if a VARCHAR, VARBINARY, or VARGRAPHIC column is specified, 32740. Additionally, if a LOB or XML column is specified, the sum of the byte counts of the columns must not be greater than 3 758 096 383 at the time of insert or update. For information on the byte counts of columns according to data type, see Maximum row sizes.

If the table is a system-period temporal table, the column is also added to the associated history table. The following attributes are copied for the column in the history table:

Column name and system column name
Data type
Length, precision, and scale
FOR BIT DATA, FOR SBCS DATA, FOR MIXED DATA attribute
CCSID
Allocate attribute
Null attribute
Hidden attribute
Field procedure

column-name

Names the column to be added to the table. Do not use the same name for more than one column of the table or for a system-column-name of the table. A column named SYSTEM_TIME cannot be added to a table that has a period. Do not qualify column-name.

FOR COLUMN system-column-name

Provides an IBM® i name for the column. Do not use the same name for more than one column-name or system-column-name of the table.

If the system-column-name is not specified, and the column-name is not a valid system-column-name, a system column name is generated. For more information about how system column names are generated, see Rules for Column Name Generation.

data-type

Specifies the data type of the column. The data type can be a built-in data type or a distinct type.

built-in-type: Specifies a built-in data type. See CREATE TABLE for a description of built-in types.
distinct-type-name: Specifies that the data type of a column is a distinct type. The length, precision, and scale of the column are respectively the length, precision, and scale of the source type of the distinct type. If a distinct type name is specified without a schema name, the distinct type name is resolved by searching the schemas on the SQL path.

DEFAULT

Specifies a default value for the column. This clause cannot be specified more than once in the same column-definition. DEFAULT cannot be specified for the following types of columns because the system generates default values:

a ROWID column
an identity column
a row change timestamp column
a row-begin column
a row-end column
a transaction-start-ID column
a generated expression column

For an XML column, the default is NULL unless NOT NULL is specified; in that case there is no default.

If a value is not specified following the DEFAULT keyword or a DEFAULT clause is not specified, then:

if the column is a generated column, the default depends on the type of generated column. For these values, see GENERATED keyword.
if the column is nullable, the default value is the null value.

if the column is not nullable, the default depends on the data type of the column:

Data type	Default value
Numeric	0
Fixed-length character or graphic string	Blanks
Fixed-length binary string	Hexadecimal zeros
Varying-length string	A string length of 0
Date	For existing rows, a date corresponding to 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 January 1, 0001 and a time corresponding to 0 hours, 0 minutes, 0 seconds, and 0 fractional seconds. For added rows, the current timestamp.
Datalink	A value corresponding to DLVALUE('','URL','')
distinct-type	The default value of the corresponding source type of the distinct type.

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

constant

Specifies the constant as the default for the column. The specified constant must represent a value that could be assigned to the column in accordance with the rules of assignment as described in Assignments and comparisons. A floating-point constant or decimal floating-point constant must not be used for a SMALLINT, INTEGER, BIGINT, DECIMAL, or NUMERIC column. A decimal constant must not contain more digits to the right of the decimal point than the specified scale of the column.

USER

Specifies the value of the USER special register at the time of INSERT or UPDATE as the default value for the column. The data type of the column must be CHAR or VARCHAR with a length attribute that is greater than or equal to the length attribute of the USER special register. For existing rows, the value is that of the USER special register at the time the ALTER TABLE statement is processed.

NULL

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

CURRENT_DATE

Specifies the current date as the default for the column. If CURRENT_DATE is specified, the data type of the column must be DATE or a distinct type based on a DATE.

CURRENT_TIME

Specifies the current time as the default for the column. If CURRENT_TIME is specified, the data type of the column must be TIME or a distinct type based on a TIME.

CURRENT_TIMESTAMP or CURRENT_TIMESTAMP(integer)

Specifies the current timestamp as the default for the column. If CURRENT_TIMESTAMP is specified, the data type of the column must be TIMESTAMP or a distinct type based on a TIMESTAMP. The timestamp precision of the CURRENT_TIMESTAMP special register used as the default will always match the timestamp precision of the column, regardless of the precision specified for the special register.

cast-function-name

This form of a default value can only be used with columns defined as a Start of change

strongly typed End of change

distinct type, BINARY, VARBINARY, BLOB, CLOB, DBCLOB, DATE, TIME, or TIMESTAMP data types. The following table describes the allowed uses of these cast-functions.

Data Type	Cast Function Name
Strongly typed distinct type N based on a BINARY, VARBINARY, BLOB, CLOB, or DBCLOB	BINARY, VARBINARY, BLOB, CLOB, or DBCLOB *
Strongly typed distinct type N based on a DATE, TIME, or TIMESTAMP	N (the user-defined cast function that was generated when N was created) ** or DATE, TIME, or TIMESTAMP *
Strongly typed distinct type N based on other data types	N (the user-defined cast function that was generated when N was created) **
BINARY, VARBINARY, BLOB, CLOB, or DBCLOB	BINARY, VARBINARY, BLOB, CLOB, or DBCLOB *
DATE, TIME, or TIMESTAMP	DATE, TIME, or TIMESTAMP *
Notes: * The name of the function must match the name of the data type (or the source type of the strongly typed distinct type) with an implicit or explicit schema name of QSYS2. ** The name of the function must match the name of the distinct type for the column. If qualified with a schema name, it must be the same as the schema name for the distinct type. If not qualified, the schema name from function resolution must be the same as the schema name for the distinct type.

constant: Specifies a constant as the argument. The constant must conform to the rules of a constant for the source type of the distinct type or for the data type if not a distinct type. For BINARY, VARBINARY, BLOB, CLOB, DBCLOB, DATE, TIME, and TIMESTAMP functions, the constant must be a string constant.
USER: Specifies the value of the USER special register at the time of INSERT or UPDATE as the default value for the column. The data type of the source type of the distinct type of the column must be CHAR or VARCHAR with a length attribute that is greater than or equal to the length attribute of USER. For existing rows, the value is that of the USER special register at the time the ALTER TABLE statement is processed.
CURRENT_DATE: Specifies the current date as the default for the column. If CURRENT_DATE is specified, the data type of the source type of the distinct type of the column must be DATE.
CURRENT_TIME: Specifies the current time as the default for the column. If CURRENT_TIME is specified, the data type of the source type of the distinct type of the column must be TIME.
CURRENT_TIMESTAMP or CURRENT_TIMESTAMP(integer): Specifies the current timestamp as the default for the column. If CURRENT_TIMESTAMP is specified, the data type of the source type of the distinct type of the column must be TIMESTAMP. The timestamp precision of the CURRENT_TIMESTAMP special register used as the default will always match the timestamp precision of the column, regardless of the precision specified for the special register.

If the value specified is not valid, an error is returned.

GENERATED

Specifies that the database manager generates values for the column. GENERATED may be specified if the column is to be considered an identity column (defined with the AS IDENTITY clause), a row change timestamp column, a row-begin column, a row-end column, a transaction-start-ID column, or a generated expression column. It may also be specified if the data type of the column is a ROWID (or a distinct type that is based on a ROWID). Otherwise, it must not be specified. An identity, ROWID, or row change timestamp column cannot be added to a system-period temporal table.

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 type of generated column:

IDENTITY generates an identity value for each row
ROW CHANGE TIMESTAMP uses a value that corresponds to the timestamp of the ALTER TABLE statement
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
A generated expression uses 0 for numeric columns, a string with length 0 for varying-length character string columns, and blanks for fixed-length character string columns.

ALWAYS

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

BY DEFAULT

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

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

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

AS IDENTITY

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

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

FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP

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

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

AS ROW BEGIN

Specifies that the column contains timestamp data and that the values are generated by the database manager. The database manager generates a value for the column for each row as the row is inserted, and for every row in which any column is updated. The generated value is a timestamp that corresponds to the start time that is associated with the most recent transaction. If multiple rows are inserted with a single SQL statement, the values for the transaction start timestamp column are the same for each row.

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 inserted into an associated history table have the end timestamp value greater than the begin timestamp value. This can happen when a conflicting transaction is updating the same row in the system-period temporal table. The SYSTIME_PERIOD_ADJ QAQQINI option must be set to *ADJUST for this adjustment to the timestamp value to occur. 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 generated for the column for another transaction.

A row-begin column is intended to be used for a system-period temporal table and is required as the first column of a SYSTEM_TIME period. A table can have only one row-begin column. If data-type is not specified, the column is defined as a TIMESTAMP(12). If data-type is specified, it must be TIMESTAMP(12). It cannot be a distinct type. The column cannot have a DEFAULT clause and must be defined as NOT NULL. A row-begin column is not updatable.

For existing rows, the value of the column is set to a date that corresponds to January 1, 0001 and a time that corresponds to 0 hours, 0 minutes, 0 seconds, and 0 fractional seconds.

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’. For a system-period temporal table, when a row is deleted, the value of the row-end column in the historical row reflects when the row was deleted. If multiple rows are deleted with a single SQL statement, the values for the column in the historical rows are the same.

A row-end column is intended to be used for a system-period temporal table and is required as the second column of a SYSTEM_TIME period. A table can have only one row-end column. If data-type is not specified, the column is defined as TIMESTAMP(12). If data-type is specified, it must be TIMESTAMP(12). It cannot be a distinct type. The column cannot have a DEFAULT clause and must be defined as NOT NULL. A row-end column is not updatable.

For existing rows, the value of the column is set to a date that corresponds to December 30, 9999 and a time that corresponds to 0 hours, 0 minutes, 0 seconds, and 0 fractional seconds.

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 there is a row-begin column in the table for which the value did not need to be adjusted. Otherwise the value is generated. 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 generated for the column for another transaction.

A transaction-start-ID column is intended to be used for a system-period temporal table and is required for a system-period temporal table. A table can have only one transaction-start-ID column. If data-type is not specified, the column is defined as TIMESTAMP(12). If data-type is specified it must be TIMESTAMP(12). It cannot be a distinct type. A transaction-start-ID column cannot have a DEFAULT clause. A transaction-start-ID column is not updatable.

For existing rows, if the column is nullable, the null value is assigned as the value of the column. Otherwise, the value of the column is set to a timestamp with a date that corresponds to January 1, 0001 and a time that corresponds to 0 hours, 0 minutes, 0 seconds, and 0 fractional seconds.

DATA CHANGE OPERATION

Specifies that the database manager generates a value for each row that is inserted, for every row in which any column is updated, and for all rows deleted from a system-period temporal table when the history table is defined with ON DELETE ADD EXTRA ROW. The column will contain one of the following values:

I: insert operation
U: update operation
D: delete operation

If data-type is not specified, the column is defined as CHAR(1). If data-type is specified it must be CHAR(1). It cannot be a distinct type. The column cannot have a DEFAULT clause or a field procedure.

For existing rows, if the column is nullable, the null value is assigned as the value of the column. Otherwise, the value of the column is set to blank.

special-register

Specifies that the value of a special register is assigned by the database manager for each row that is inserted, for every row in which any column is updated, and for all rows deleted from a system-period temporal table when the history table is defined with ON DELETE ADD EXTRA ROW. The value of the special register at the time of the data change statement is used. If multiple rows are changed with a single SQL statement, the value for the column will be the same for all of the rows.

data-type must be defined according to the following table. It cannot be a distinct type.

Special register	Data type for the column
CURRENT CLIENT_ACCTNG	VARCHAR(255)
CURRENT CLIENT_APPLNAME	VARCHAR(255)
CURRENT CLIENT_PROGRAMID	VARCHAR(255)
CURRENT CLIENT_USERID	VARCHAR(255)
CURRENT CLIENT_WRKSTNNAME	VARCHAR(255)
CURRENT SERVER	VARCHAR(18)
SESSION_USER	VARCHAR(128)
USER	VARCHAR(18)

The column cannot have a DEFAULT clause or a field procedure.

For existing rows, if the column is nullable, the null value is assigned as the value of the column. Otherwise, the value of the column is set to a string with length 0.

built-in-global-variable

Specifies that the value of a built-in global variable is assigned by the database manager for each row that is inserted, for every row in which any column is updated, and for all rows deleted from a system-period temporal table when the history table is defined with ON DELETE ADD EXTRA ROW. The value of the built-in global variable at the time of the data change statement is used. If multiple rows are changed with a single SQL statement, the value for the column will be the same for all of the rows.

data-type must be defined according to the following table. It cannot be a distinct type.

Built-in global variable	Data type for the column
QSYS2.JOB_NAME	VARCHAR(28)
QSYS2.SERVER_MODE_JOB_NAME	VARCHAR(28)
SYSIBM.CLIENT_HOST	VARCHAR(255)
SYSIBM.CLIENT_IPADDR	VARCHAR(128)
SYSIBM.CLIENT_PORT	INTEGER
SYSIBM.PACKAGE_NAME	VARCHAR(128)
SYSIBM.PACKAGE_SCHEMA	VARCHAR(128)
SYSIBM.PACKAGE_VERSION	VARCHAR(64)
SYSIBM.ROUTINE_SCHEMA	VARCHAR(128)
SYSIBM.ROUTINE_SPECIFIC_NAME	VARCHAR(128)
SYSIBM.ROUTINE_TYPE	CHAR(1)

The column cannot have a DEFAULT clause or a field procedure.

For existing rows, if the column is nullable, the null value is assigned as the value of the column. Otherwise, the value of the column is set to 0 for numeric columns, a string with length 0 for varying-length character string columns, and blanks for fixed-length character string columns.

NOT NULL

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

NOT HIDDEN

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

IMPLICITLY HIDDEN

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

column-constraint

The column-constraint of a column-definition provides a shorthand method of defining a constraint composed of a single column. Thus, if a column-constraint is specified in the definition of column C, the effect is the same as if that constraint were specified as a unique-constraint, referential-constraint or check-constraint in which C is the only identified column.

CONSTRAINT constraint-name

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

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

PRIMARY KEY

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

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

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

UNIQUE

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

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

references-clause

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

CHECK(check-condition)

Provides a shorthand method of defining a check constraint whose check-condition only references a single column. Thus, if CHECK is specified in the column definition of column C, no columns other than C can be referenced in the check-condition of the check constraint. The effect is the same as if the check constraint were specified as a separate clause.

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

FIELDPROC

Designates an external-program-name as the field procedure exit routine for the column. It must be an ILE program that does not contain SQL. It cannot be a service program.

The field procedure encodes and decodes column values. Before a value is inserted in the column, it is passed to the field procedure for encoding. Before a value from the column is used, it is passed to the field procedure for decoding.

The field procedure is also invoked during the processing of the ALTER TABLE statement. When so invoked, the procedure provides Db2 with the column's field description. The field description defines the data characteristics of the encoded values. By contrast, the information supplied for the column defines the data characteristics of the decoded values.

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

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

datalink-options

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

BEFORE column-name

Identifies the column before which the new column is added. The name must not be qualified and must identify an existing column in the table. If the BEFORE clause is not specified, the column is added at the end of the row.

ALTER COLUMN column-alteration

Alters the definition of a column, including the attributes of an existing identity column. Only the attributes specified will be altered. Others will remain unchanged. If the table is a system-period temporal table, the change will be made to the corresponding history table column, except any change to the default value or a generated column attribute such as identity or row change timestamp is not copied.

column-name

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

SET DATA TYPE data-type

Specifies the new data type of the column to be altered. The new data type must be compatible with the existing data type of the column. For more information about the compatibility of data types see Assignments and comparisons. The following alters are not allowed:

Numeric data type to a character-string data type
Character-string data type to a numeric data type
Datetime data type to character-string data type
Datetime data type to a different datetime data type

For an XML column, only the CCSID can be changed.

The specified length, precision, and scale may be larger, smaller, or the same as the existing length, precision, and scale. However, if the new length, precision, or scale is smaller, truncation or numeric conversion errors may occur.

If the specified column has a default value and a new default value is not specified, the existing default value must represent a value that could be assigned to the column in accordance with the rules for assignment as described in Assignments and comparisons.

A row change timestamp column cannot be altered to a timestamp with a precision other than 6.

A row-begin, row-end, or transaction-start-ID column cannot be altered to a timestamp with a precision other than 12.

A generated expression column cannot be altered to a data type and length different than its required definition.

If the table is a system-period temporal table, the column is also changed in the associated history table. If the table is a system-period temporal table, the column cannot be altered in any way that could cause data loss, such as a shorter string length or lower precision.

If the column is specified in a unique, primary, or foreign key, the new sum of the lengths of the columns of the keys must not exceed 32766-n, where n is the number of columns specified that allow nulls.

Changing the attributes will cause any existing values in the column to be converted to the new column attributes according to the rules for assignment to a column, except that string values will be truncated.

Altering the data type attributes of a column can affect a row permission or column mask that is defined for the table. When data type attributes of a column change, row permissions and column masks are reevaluated using the new column attributes. If an error is encountered during the reevaluation process, the ALTER statement fails.

If a row permission or a column mask defined on a different table references this column, the row permission or column mask will not be reevaluated until it is used or is the object of an ALTER REGENERATE. A reevaluation error will result in failure of either the regenerate or the statement that first requires use of the column mask or row permission. The row permission or column mask may need to be dropped and recreated to fix the error.

SET default-clause

Specifies the new default value of the column to be altered. The specified default value must represent a value that could be assigned to the column in accordance with the rules for assignment as described in Assignments and comparisons.

SET GENERATED ALWAYS or GENERATED BY DEFAULT

Specifies that the database manager generates values for the column. GENERATED may be specified if the column is an identity column, row change timestamp column, or the data type of the column is a ROWID (or a distinct type that is based on a ROWID). For a row-begin, row-end, transaction-start-ID column, and generated expression column, only GENERATED ALWAYS is allowed. For other types of columns, GENERATED must not be specified.

AS IDENTITY

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

The column must not be nullable. If the column has an explicit default value, the default value is dropped. See the AS IDENTITY clause in CREATE TABLE for the descriptions of the identity attributes.

as-row-transaction-timestamp-clause

Specifies that the column is changed to a row-begin or row-end column for the table. The column must be a TIMESTAMP(12), must not have a default and must not be nullable.

as-row-transaction-start-id-clause

Specifies that the column is changed to a transaction-start-id column for the table. The column must be a TIMESTAMP(12) and must not have a default.

SET NOT NULL

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

SET NOT HIDDEN or IMPLICITLY HIDDEN

Specifies the hidden attribute for the column.

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

SET FIELDPROC

Designates an external-program-name as the field procedure exit routine for the column. It must be an ILE program that does not contain SQL. It cannot be a service program.

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

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

DROP DEFAULT

Drops the current default for the column. The specified column:

must have a default value and must not have NOT NULL as the null attribute, or
can have a default value of NULL if the default for the column was defined with DEFAULT NULL.

The new default value is the null value.

DROP NOT NULL

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

DROP GENERATED

Drops the generated attribute of the column. The column must be an identity column, a row change timestamp column, a row-begin column, a row-end column, a transaction-start-ID column, or a generated expression column. The generated attribute cannot be dropped for a row-begin column, row-end column, or transaction-start-ID column if versioning is active.

DROP IDENTITY

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

DROP ROW CHANGE TIMESTAMP

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

DROP FIELDPROC

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

identity-alteration

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

RESTART

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

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

DROP COLUMN

Drops the identified column from the table.

column-name

Identifies the column to be dropped. The column name must not be qualified. The name must identify a column of the specified table. The name must not identify:

a column that was already added or altered in this ALTER TABLE statement
the only column of a table
the last column of the table that is not hidden
a partition key of a partitioned table or a distributed table
a column in a system-period temporal table
a column in a history table
a column referenced in the definition of a period

When a column is dropped, any column mask defined for that column is dropped. A column referenced in the definition of a row permission or column mask for a different column cannot be dropped.

CASCADE

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

RESTRICT

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

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

ADD unique-constraint

CONSTRAINT constraint-name

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

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

UNIQUE (column-name,…)

Defines a unique constraint composed of the identified columns. Each column-name must be an unqualified name that identifies a column of the table. The same column must not be identified more than once. The column must not be a LOB, DATALINK, or XML column. If a sort sequence is specified, the column must not contain a field procedure. The number of identified columns must not exceed 120, and the sum of their lengths must not exceed 32766-n, where n is the number of columns specified that allow nulls. UNIQUE is not allowed if the table is a history table.

The set of identified columns cannot be the same as the set of columns specified in another UNIQUE constraint or PRIMARY KEY on the table. For example, UNIQUE (A,B) is not allowed if UNIQUE (B,A) or PRIMARY KEY (A,B) already exists on the table. Any existing nonnull values in the set of columns must be unique. Multiple null values are allowed.

If a unique index already exists on the identified columns, that index is designated as a unique constraint index. Otherwise, a unique index is created to support the uniqueness of the unique key. The unique index is created as part of the system physical file, not as a separate system logical file.

PRIMARY KEY (column-name,…)

Defines a primary key composed of the identified columns. Each column-name must be an unqualified name that identifies a column of the table. The same column must not be identified more than once. The column must not be a LOB, DATALINK, or XML column. If a sort sequence is specified, the column must not contain a field procedure. The number of identified columns must not exceed 120, and the sum of their lengths must not exceed 32766. The table must not already have a primary key. PRIMARY KEY is not allowed if the table is a history table.

The identified columns cannot be the same as the columns specified in another UNIQUE constraint on the table. For example, PRIMARY KEY (A,B) is not allowed if UNIQUE (B,A) already exists on the table. Any existing values in the set of columns must be unique.

When a primary key is added, a CHECK constraint is implicitly added to enforce the rule that the NULL value is not allowed in any of the columns that make up the primary key.

If a unique index already exists on the identified columns, that index is designated as a primary index. Otherwise, a primary index is created to support the uniqueness of the primary key. The unique index is created as part of the system physical file, not a separate system logical file.

ADD referential-constraint

CONSTRAINT constraint-name

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

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

FOREIGN KEY

Defines a referential constraint. FOREIGN KEY is not allowed if the table is a declared global temporary table, a distributed table, or a history table.

Let T1 denote the table being altered.

(column-name,…)

The foreign key of the referential constraint is composed of the identified columns. Each column-name must be an unqualified name that identifies a column of T1. The same column must not be identified more than once. The column must not be a LOB, DATALINK, or XML column and must not be a row change timestamp column. If a sort sequence is specified, the column must not contain a field procedure. The number of the identified columns must not exceed 120, and the sum of their lengths must not exceed 32766-n, where n is the number of columns specified that allows nulls.

REFERENCES table-name

The table-name specified in a REFERENCES clause must identify a base table that exists at the current server, but it must not identify a catalog table, a declared temporary table, a distributed table, or a history table. If the parent is a partitioned table, the unique index that enforces the parent unique constraint must be non-partitioned. This table is referred to as the parent table in the constraint relationship.

A referential constraint is a duplicate if its foreign key, parent key, and parent table are the same as the foreign key, parent key, and parent table of an existing referential constraint on the table. Duplicate referential constraints are allowed, but not recommended.

Let T2 denote the identified parent table.

(column-name,…)

The parent key of the referential constraint is composed of the identified columns. Each column-name must be an unqualified name that identifies a column of T2. The same column must not be identified more than once. The column must not be a LOB, DATALINK, or XML column and must not be a row change timestamp column. If a sort sequence is specified, the column must not contain a field procedure. The number of identified columns must not exceed 120, and the sum of their lengths must not exceed 32766-n, where n is the number of columns specified that allow nulls.

The list of column names must be identical to the list of column names in the primary key of T2 or a UNIQUE constraint that exists on T2. The names may be specified in any order. For example, if (A,B) is specified, a unique constraint defined as UNIQUE (B,A) would satisfy the requirement. If a column name list is not specified then T2 must have a primary key. Omission of the column name list is an implicit specification of the columns of that primary key.

The specified foreign key must have the same number of columns as the parent key of T2. The description of the nth column of the foreign key and the nth column of the parent key must have identical data types, lengths, CCSIDs, and FIELDPROCs.

Unless the table is empty, the values of the foreign key must be validated before the table can be used. Values of the foreign key are validated during the execution of the ALTER TABLE statement. Therefore, every nonnull value of the foreign key must match some value of the parent key of T2.

The referential constraint specified by the FOREIGN KEY clause defines a relationship in which T2 is the parent and T1 is the dependent.

ON DELETE

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

NO ACTION (default)
RESTRICT
CASCADE
SET NULL
SET DEFAULT

SET NULL must not be specified unless some column of the foreign key allows null values. SET NULL and SET DEFAULT must not be specified if T1 has an update trigger.

CASCADE must not be specified if T1 has a delete trigger.

CASCADE must not be specified if T1 contains a DataLink column with FILE LINK CONTROL.

The delete rule applies when a row of T2 is the object of a DELETE or propagated delete operation and that row has dependents in T1. Let p denote such a row of T2.

If RESTRICT or NO ACTION is specified, an error occurs and no rows are deleted.
If CASCADE is specified, the delete operation is propagated to the dependents of p in T1.
If SET NULL is specified, each nullable column of the foreign key of each dependent of p in T1 is set to null.
If SET DEFAULT is specified, each column of the foreign key of each dependent of p in T1 is set to its default value.

ON UPDATE

Specifies what action is to take place on the dependent tables when a row of the parent table is updated.

The update rule applies when a row of T2 is the object of an UPDATE or propagated update operation and that row has dependents in T1. Let p denote such a row of T2.

If RESTRICT or NO ACTION is specified, an error occurs and no rows are updated.

ADD check-constraint

CONSTRAINT constraint-name

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

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

CHECK(check-condition)

Defines a check constraint. The check-condition must be true or unknown for every row of the table.

The check-condition is a search-condition, except:

It can only refer to columns of the table and the column names must not be qualified.
The result of any expression in the check-condition cannot be a ROWID, XML, or DATALINK with FILE LINK CONTROL data type.

It must not contain any of the following:

Subqueries
Aggregate functions
TRY_CAST specification
Variables
Global variables
Parameter markers
Sequence-references
Complex expressions that contain LOBs (such as concatenation)
OLAP specifications
ROW CHANGE expressions
IS JSON, JSON_EXISTS, or REGEXP_LIKE predicates
Special registers
Any function that is not deterministic
User-defined functions other than functions that were implicitly generated with the creation of a strongly typed distinct type

The following built-in scalar functions:

ATAN2

DLURLSERVER

JSON_QUERY

REPLACE

BSON_TO_JSON

DLVALUE

JSON_TO_BSON

ROUND_TIMESTAMP

CARDINALITY

ENCRYPT_AES

JSON_VALUE

RPAD

CONTAINS

ENCRYPT_AES256 End of change

LOCATE_IN_STRING

SCORE

CURDATE

ENCRYPT_RC2

LPAD

SOUNDEX

CURTIME

ENCRYPT_TDES

MAX_CARDINALITY

TABLE_NAME

DATAPARTITIONNAME

GENERATE_UNIQUE

MONTHNAME

TABLE_SCHEMA

DATAPARTITIONNUM

GETHINT

MONTHS_BETWEEN

TIMESTAMP_FORMAT

DAYNAME

HASH

NEXT_DAY

TIMESTAMPDIFF

DBPARTITIONNAME

HASH_MD5

NOW

TIMESTAMPDIFF_BIG End of change

DECRYPT_BINARY

HASH_SHA1

OVERLAY

TRUNC_TIMESTAMP

DECRYPT_BIT

HASH_SHA256

RAISE_ERROR

VARCHAR_FORMAT

DECRYPT_CHAR

HASH_SHA512

RAND

VERIFY_GROUP_FOR_USER

DECRYPT_DB

IDENTITY_VAL_LOCAL

REGEXP_COUNT

WEEK_ISO

DIFFERENCE

INSERT

REGEXP_INSTR

WRAP

DLURLCOMPLETE ¹

INTERPRET

REGEXP_REPLACE

XMLPARSE

DLURLPATH

JSON_ARRAY

REGEXP_SUBSTR

XMLVALIDATE

DLURLPATHONLY

JSON_OBJECT

REPEAT

XSLTRANSFORM

DLURLSCHEME

¹ For DataLinks with an attribute of FILE LINK CONTROL and READ PERMISSION DB.

ON INSERT VIOLATION

Specifies the action to take if the check-condition is false for a row being inserted. If this clause is not specified, an error will occur if the check-condition is false for an insert.

SET column-name = DEFAULT: The default value for column-name is inserted into the table instead of the value provided by the insert operation.
column-name must be referenced in the check-condition.

ON UPDATE VIOLATION

Specifies the action to take if the check-condition is false for a row being updated. If this clause is not specified, an error will occur if the check-condition is false for an update.

PRESERVE column-name: The current value for column-name remains in the table rather than being replaced by the value provided by the update operation.
column-name must be referenced in the check-condition.

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

DROP

PRIMARY KEY: Drops the definition of the primary key and all referential constraints in which the primary key is a parent key. The table must have a primary key.
UNIQUE constraint-name: Drops the unique constraint constraint-name and all referential constraints dependent on this unique constraint. The constraint-name must identify a unique constraint on the table. DROP UNIQUE will not drop a PRIMARY KEY unique constraint.
FOREIGN KEY constraint-name: Drops the referential constraint constraint-name. The constraint-name must identify a referential constraint in which the table is a dependent.
CHECK constraint-name: Drops the check constraint constraint-name. The constraint-name must identify a check constraint on the table.
CONSTRAINT constraint-name: Drops the constraint constraint-name. The constraint-name must identify a unique, referential, or check constraint on the table. If the constraint is a PRIMARY KEY or UNIQUE constraint, all referential constraints in which the primary key or unique key is a parent are also dropped.
CASCADE: Specifies for unique constraints that any referential constraints that are dependent on the constraint being dropped are also dropped.
RESTRICT: Specifies for unique constraints that the constraint cannot be dropped if any referential constraints are dependent on the constraint.

ADD partitioning-clause

Changes a non-partitioned table into a partitioned table. If the specified table is a distributed table or already a partitioned table, an error is returned. A DDS-created physical file cannot be partitioned. See CREATE TABLE for a description of the partitioning-clause.

Changing a non-partitioned table that contains data into a partitioned table will require data movement between the data partitions. When using range partitioning, all existing data in the table must be assignable to the specified range partitions.

DROP PARTITIONING

Changes a partitioned table into a non-partitioned table. If the specified table is already non-partitioned, an error is returned.

Changing a partitioned table that contains data into a non-partitioned table will require data movement between the data partitions.

ADD PARTITION add-partition

Adds one or more partitions to a partitioned table. The specified table must be a partitioned table. The number of partitions must not exceed 256.

Changing the number of hash partitions in a partitioned table that contains data will require data movement between the data partitions.

partition-name: Names the partition. A partition-name must not identify a data partition that already exists in the table.
If the clause is not specified, a unique partition name is generated by the database manager.
boundary-spec: Specifies the boundaries of a range partition. If the specified table is not a range partitioned table, an error is returned. Both a starting-clause and an ending-clause must be specified. See CREATE TABLE for a description of the boundary-spec.

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

ALTER PARTITION

Alters the boundaries of a partition of a range partitioned table. If the specified table is not a range partitioned table, an error is returned.

Changing the boundaries of one or more partitions of a table that contains data may require data movement between the data partitions. All existing data in the table must be assignable to the specified range partitions.

partition-name: Specifies the name of the partition to alter. The partition-name must identify a data partition that exists in the table.
boundary-spec: Specifies the new boundaries of a range partition. Both a starting-clause and an ending-clause must be specified. See CREATE TABLE for a description of the boundary-spec.

DROP PARTITION

Drops a partition of a partitioned table. If the specified table is not a partitioned table, an error is returned. If the last remaining partition of a partitioned table is specified, an error is returned. A partition cannot be dropped from a system-period temporal table unless the PRESERVE ROWS option is specified or the partition is empty.

partition-name: Specifies the name of the partition to drop. The partition-name must identify a data partition that exists in the table.
DELETE ROWS: Specifies that any data in the specified partition will be discarded. All data stored in the partition is dropped from the table without processing any delete triggers.
PRESERVE ROWS: Specifies that any data in the specified partition will be preserved by moving it to the remaining partitions without processing any delete or insert triggers. If the specified table is a range partitioned table, PRESERVE ROWS must not be specified. Dropping a hash partition will require data movement between the remaining data partitions.

ATTACH PARTITION

Attaches another table as a new data partition. The data in the table being attached becomes a new partition of the table being attached to. There is no data movement involved. If the specified target table is not a partitioned table, an error is returned. If the target table already has the maximum number of partitions, an error is returned. If the table is hash partitioned, an error is returned.

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 exactly 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. Similarly, if the table being attached has column level access control activated and at least one column mask enabled then the table to attach to must also have column level access control activated and a column mask enabled for the corresponding columns.

partition-name

Names the data partition. The name must not be the same as any other data partition for the table. If this clause is not specified and the source table is partitioned, the partition name of the source table will be used. Otherwise, if this clause is not specified, a unique partition name is generated by the database manager.

boundary-spec

Specifies the boundaries for the new partition. If a boundary-spec is not specified:

The source table must be a partitioned table with a single partition and the boundary-spec of that partition will be implicitly used.
The number of partition keys and the partition key names of the source table must be the same as those of the target table.

The implicit or explicit range must not overlap that of an existing data partition. See CREATE TABLE for a description of the boundary-spec. If a boundary-spec is specified, the data in the source table must conform to the specified range or subsequent SQL operations may be unpredictable.

FROM table-name1

Specifies the table that is to be used as the source of data for the new partition. The table must exist in the same relational database as the target table. If the specified table is not a partitioned table, an error is returned. If the table is hash partitioned, an error is returned. The table must not be a view, a materialized query table, a declared global temporary table, a system-period temporal table, a history table, or a system table. The table must not have a NOT LOGGED INITIALLY attribute. The table definition of table-name1 cannot have multiple partitions, and it must match the altered table in the following ways:

The number of columns must be the same.
The data types, attributes, field procedures, generated expressions, default values, and CCSIDs 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.
For varying length or LOB data types, the ALLOCATE length of columns in the same ordinal position in the table must be the same.

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

DETACH PARTITION

Detaches a partition of a partitioned table. If the specified table is not a partitioned table, an error is returned. A new partitioned table named table-name1 with one partition will be created to contain the detached partition data. partition-name cannot be the last remaining partition of the table being altered. The table being altered must not be a system-period temporal table, a parent table of an enforced referential constraint, or use HASH partitioning.

If the source table contains an identity column, the corresponding column in the created table will not be an identity column. All other column attributes from the source table are defined for the corresponding column in the created table. If the source table has constraints, similar constraints are not added to the created table.

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

Privileges on the source table are not propagated to the created table.

partition-name

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

table-name1

Names the table. The name, including the implicit or explicit qualifier, must not identify an alias, file, index, table, or view that already exists at the current server.

If SQL names were specified, the table will be created in the schema specified by the implicit or explicit qualifier.

If system names were specified, the table will be created in the schema that is specified by the qualifier. If not qualified:

If the value of the CURRENT SCHEMA special register is *LIBL, the table will be created in the current library (*CURLIB).
Otherwise, the table will be created in the current schema.

If the specified partitioned table is journaled, the new table will be journaled to the same journal. Otherwise, the new table will not be journaled.

Table ownership: If SQL names were specified:

If a user profile with the same name as the schema into which the table is created exists, the owner of the table is that user profile.
Otherwise, the owner of the table is the user profile or group user profile of the thread executing the statement.

If system names were specified, the owner of the table is the user profile or group user profile of the thread executing the statement.

Table authority: If SQL names are used, the table is created with the system authority of *EXCLUDE to *PUBLIC. If system names are used, the table is created with the authority to *PUBLIC as determined by the create authority (CRTAUT) parameter of the schema.

ADD MATERIALIZED QUERY materialized-query-definition

Changes a base table to a materialized query table. If the specified table is already a materialized query table or if the table is referenced in another materialized query table, an error is returned.

select-statement

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

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

The select-statement for a materialized query table must not contain a reference to the table being altered, a view over the table being altered, or another materialized query table. For additional details about specifying select-statement for a materialized query table, see CREATE TABLE.

refreshable-table-options

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

DATA INITIALLY DEFERRED

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

DATA INITIALLY IMMEDIATE

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

REFRESH DEFERRED

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

MAINTAINED BY USER

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

ENABLE QUERY OPTIMIZATION or DISABLE QUERY OPTIMIZATION

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

ENABLE QUERY OPTIMIZATION: The materialized query table can be used for query optimization.
DISABLE QUERY OPTIMIZATION: The materialized query table will not be used for query optimization. The table can still be queried directly.

If row level or column level access control is activated for any table that is directly or indirectly referenced in the select-statement and row access control is not activated for the table being altered, row 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 will return a warning that there is no data in the table. 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 issued to remove the row level protection if that is appropriate.

ALTER MATERIALIZED QUERY materialized-query-table-alteration

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

select-statement

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

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

refreshable-table-options

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

DATA INITIALLY DEFERRED

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

DATA INITIALLY IMMEDIATE

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

REFRESH DEFERRED

MAINTAINED BY USER

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

ENABLE QUERY OPTIMIZATION or DISABLE QUERY OPTIMIZATION

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

ENABLE QUERY OPTIMIZATION: The materialized query table can be used for query optimization.
DISABLE QUERY OPTIMIZATION: The materialized query table will not be used for query optimization. The table can still be queried directly.

SET refreshable-table-alteration

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

MAINTAINED BY USER

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

REFRESH DEFERRED

ENABLE QUERY OPTIMIZATION or DISABLE QUERY OPTIMIZATION

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

ENABLE QUERY OPTIMIZATION: The materialized query table can be used for query optimization.
DISABLE QUERY OPTIMIZATION: The materialized query table will not be used for query optimization. The table can still be queried directly.

DROP MATERIALIZED QUERY

Changes a materialized query table so that it is no longer a materialized query table. The table specified by table-name must be defined as a materialized query table. The definition of columns are not changed, but the table can no longer be used for query optimization and is no longer valid for use with the REFRESH TABLE statement. Row level access and column level access for the table remain in their current activated or deactivated state.

ACTIVATE NOT LOGGED INITIALLY

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

Any changes made to the table by INSERT, DELETE, or UPDATE statements in the same unit of work after the table is altered by this statement are not logged (journaled).

At the completion of the current unit of work, the NOT LOGGED INITIALLY attribute is deactivated and all operations that are done on the table in subsequent units of work are logged (journaled).

ACTIVATE NOT LOGGED INITIALLY is not allowed in a transaction if data change operations are pending for table-name or cursors are currently open under commit that reference table-name.

ACTIVATE NOT LOGGED INITIALLY is not allowed if the table is a system-period temporal table or a history table. It is ignored if the table has a DATALINK column with FILE LINK CONTROL or if running with isolation level No Commit (NC).

WITH EMPTY TABLE

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

WITH EMPTY TABLE cannot be specified for a materialized query table or for a parent in a referential constraint. WITH EMPTY TABLE is ignored if running with isolation level No Commit (NC).

A DELETE statement without a WHERE clause will typically perform as well or better than ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE and will allow a ROLLBACK to rollback the delete of the rows in the table.

ADD PERIOD FOR 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. A table can have only one SYSTEM_TIME period. begin-column-name must be defined as ROW BEGIN and end-column-name must be defined as ROW END

DROP PERIOD SYSTEM_TIME

Drops the SYSTEM_TIME period from the table. The SYSTEM_TIME period cannot be dropped if the table is a system-period temporal table.

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. A SYSTEM_TIME period and a transaction-start-ID column must be defined in the table. The table must not be a materialized query table or a distributed table and must not have a ROWID column or a DATALINK column with FILE LINK CONTROL. Neither the table nor the history table can have the NOT LOGGED INITIALLY attribute activated.

Historical versions of the rows in the table are retained by the database manager. An associated history table is used to store the historical rows of the table. 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.

References to the table can include a period specification to indicate which 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, and is not a catalog table, an existing system-period temporal table, an existing history table, a declared global temporary table, a materialized query table, or a view.

The identified history table must not contain an identity column, row change timestamp column, row-begin column, row-end column, transaction start-ID column, or include a period. It cannot be part of a referential constraint or have a unique or primary key constraint.

Once a table has been defined as a history table, no direct inserts or updates can be performed on the table. Deletes are allowed.

ON DELETE ADD EXTRA ROW

Indicates that when a row is deleted from the system-period temporal table, the deleted row is added to the history table. This additional history row is not returned for a query with a period specification for a system-period temporal table. Values for the row begin, row end, and any generated expression columns are generated when this row is added.

The ON DELETE ADD EXTRA ROW clause is intended to be used when the system-period temporal table contains generated expression columns. The generated expression columns in an extra row contain information about the delete operation that resulted in the extra row in the history table.

The system-period temporal table and the identified history table must be in the same schema and must have the same number and order of columns. The following attributes for the corresponding columns of the two tables must be the same:

Name and system column name
Data type
Length, precision, and scale
FOR BIT DATA, FOR SBCS DATA, FOR MIXED DATA attribute
CCSID
Null attribute
Hidden attribute
Field procedure
Date and time formats and separators
Sort sequence and language ID

If the history table contains data, ensure that the data accurately represents historical rows. If the data does not accurately represent historical rows, the results of temporal queries might be unexpected.

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

ACTIVATE ROW ACCESS CONTROL or DEACTIVATE ROW ACCESS CONTROL

Specifies whether enabled row permissions are to be applied by Db2 to control the set of rows in the table that are accessible.

ACTIVATE ROW ACCESS CONTROL

Specifies to activate row access control for the table. If the table is an alias, row access control is activated for the base table. Row access control cannot be activated for a declared global temporary table or a table in QTEMP.

A default row permission is implicitly created and allows no access to any rows of the table unless an enabled row permission exists that provides access for the authorization IDs or group profiles that are specified in the definition of the permission. A query that references the table before such a row permission exists will return a warning that there is no data in the table.

If a trigger exists for the table, the trigger must be defined with the SECURED attribute and must not be a READ trigger.

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.

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 applied by Db2 to control the set of rows in the table that are accessible. A permission that gets an error when trying to be activated cannot be activated until any errors in the permission definition are resolved. This may require the permission to be dropped and recreated with a modified definition.

If a materialized query 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 does not already have row level access control activated, 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 will return a warning that there is no data in the table. 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 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 and row access control is not already 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.

DEACTIVATE ROW ACCESS CONTROL: Specifies to deactivate row access control for the table. When the table is referenced in a data manipulation statement, any enabled row permissions defined on the table are not applied by Db2 to control the set of rows in the table that are accessible.
DEACTIVATE ROW ACCESS CONTROL is ignored if row access control is already defined as not activated for the table.

ACTIVATE COLUMN ACCESS CONTROL or DEACTIVATE COLUMN ACCESS CONTROL

Specifies whether enabled column masks are to be applied by Db2 to mask column values returned from the table.

ACTIVATE COLUMN ACCESS CONTROL

Specifies to activate column access control for the table. If the table is an alias, column access control is activated for the base table.

If a trigger exists for the table, the trigger must be defined with the SECURED attribute and must not be a READ trigger.

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.

The access to the table is not restricted but when the table is referenced in a data manipulation statement, all enabled column masks that have been created for the table are applied by Db2 to mask the values returned for the columns referenced in the final result table of the queries or to determine the new values used in the data change statements. A column mask that gets an error when trying to be activated cannot be activated until any errors in the mask definition are resolved. This may require the column mask to be dropped and recreated with a modified definition.

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 does not already have row level access control activated, 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 will return a warning that there is no data in the table. 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 issued to remove the row level protection if that is appropriate.

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

DEACTIVATE COLUMN ACCESS CONTROL

Specifies to deactivate column access control for the table. When the table is referenced in a data manipulation statement, any enabled column masks defined on the table are not applied by Db2 to control the values returned for the columns referenced in the final result table of the queries or to determine whether the new values can be used in the data change statements.

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

VOLATILE or NOT VOLATILE

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

VOLATILE: Specifies that the cardinality of table-name can vary significantly at run time, from empty to large. To access the table, the optimizer will typically use an index, if possible.
NOT VOLATILE: Specifies that the cardinality of table-name is not volatile. Access plans that reference this table will be based on the cardinality of the table at the time the access plan is built.

media-preference

Specifies the preferred storage media for the table or partition.

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

memory-preference

KEEP IN MEMORY

Specifies whether the data for the table should be brought into a main storage pool when the data is used in a query.

NO: The data will not be brought into a main storage pool.
YES: The data will be brought into a main storage pool.

Notes

Column references: A column can only be referenced once in an ADD, ALTER, or DROP COLUMN clause in a single ALTER TABLE statement. However, that same column can be referenced multiple times for adding or dropping constraints in the same ALTER TABLE statement.

Order of operations: The order of operations within an ALTER TABLE statement is:

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

Within each of these stages, the order in which the user specifies the clauses is the order in which they are performed, with one exception. If any columns are being dropped, that operation is logically done before any column definitions are added or altered.

QTEMP considerations: Any views or logical files in another job's QTEMP that are dependent on the table being altered will be dropped as a result of an ALTER TABLE statement. These objects will not be dropped if the alter is changing cardinality, media preference, memory preference, adding or dropping constraints, activating or deactivating row and column access control, or when specifying ACTIVATE NOT LOGGED INITIALLY.

Authority checking: Authority checking is performed only on the table being altered and any object explicitly referenced in the ALTER TABLE statement (such as tables referenced in the fullselect). Other objects may be accessed by the ALTER TABLE statement, but no authority to those objects is required. For example, no authority is required on views that exist on the table being altered, nor on dependent tables that reference the table being altered through a referential constraint.

Backup recommendation: It is strongly recommended that a current backup of the table and dependent views and logical files exist prior to altering a table.

Performance considerations: The following performance considerations apply to an ALTER TABLE statement when adding, altering, or dropping columns from a table:

The data in the table may be copied.²
Adding and dropping columns require the data to be copied.

Altering a column usually requires the data to be copied. The data does not need to be copied, however, if the alter only includes the following changes:
- The length attribute of a VARCHAR or VARBINARY column is increasing and the current length attribute is greater than 20.
- The length attribute of a VARGRAPHIC column is increasing and the current length attribute is greater than 10.
- The allocated length of a VARCHAR or VARBINARY column is changing and the current and new allocated lengths are both less than or equal to 20.
- The allocated length of a VARGRAPHIC column is changing and the current and new allocated lengths are both less than or equal to 10.
- The CCSID of a column is changing but no conversion is necessary between the old and new CCSID. For example, if one CCSID is 65535, no data conversion is necessary.
- The default value is changing, and the length of the default value is not greater than the current allocated length.
- DROP DEFAULT is specified.
- DROP NOT NULL is specified, but at least one nullable column will still exist in the table after the alter table is complete.
Altering a column that has a field procedure defined might require the field procedure to be run two times.
Indexes may need to be rebuilt.³
An index does not need to be rebuilt when columns are added to a table or when columns are dropped or altered and those columns are not referenced in the index key.

Altering a column that is used in the key of an index or constraint usually requires the index to be rebuilt. The index does not need to be rebuilt, however, in the following cases:
- The length attribute of a VARCHAR, VARBINARY, or VARGRAPHIC key is increasing.
- The CCSID of a column is changing but no conversion is necessary between the old and new CCSID. For example, if one CCSID is 65535.

Adding a generated column: When you add a row change timestamp column, row-begin column, row-end column, transaction-start-ID column, or generated expression column to an existing table, the initial values are generated during the alter operation.

Considerations for implicitly hidden columns: A column that is defined as implicitly hidden can be explicitly referenced on the ALTER statement. For example, an implicitly hidden column can be altered, can be specified as part of a referential constraint or a check constraint, or a materialized query table definition.

Altering materialized query tables: The isolation level at the time when a base table is first altered to become a materialized query table by the ALTER TABLE statement is the isolation level for the materialized query table.

Altering a table to change it to a materialized query table with query optimization enabled makes the table eligible for use in optimization. Therefore, ensure that the data in the table is accurate. The DATA INITIALLY IMMEDIATE clause can be used to refresh the data when the table is altered.

ATTACH PARTITION effects on dependent views, indexes, and materialized query tables:

Views and DDS-created logical files on the source table are dropped.
Views that reference the target table will now include the new partition.
DDS-created logical files that referenced all the partitions prior to the attach will now include the new partition.
Materialized query tables on the source table are dropped.
Materialized query tables on the target table will be preserved but users will need to refresh the materialized query tables to include the data associated with the new partition.
Partitioned (non-spanning) indexes on the source table that correspond to partitioned indexes on the target table will be preserved as part of the target table's partitioned index as long as the logical page size of the index is the same. If the logical page size is different, the index over that partition will be dropped.
Partitioned (non-spanning) indexes on the source table that do not correspond to partitioned indexes on the target table will be dropped.
Partitioned (non-spanning) indexes on the target table that do not correspond to indexes on the source table will be modified to include the new partition.
Non-partitioned (spanning) indexes on the target table will be rebuilt to include the new partition.

DETACH PARTITION effects on dependent views, indexes, and materialized query tables:

Views on the source table will no longer include the detached partition.
DDS-created logical files that referenced all the partitions prior to the detach will include the remaining partitions.
Materialized query tables on the source table will be preserved but users will need to refresh the materialized query tables to remove the data associated with the detached partition.
Any partitioned (non-spanning) index on the source table will be modified to remove the detached partition index.
Any non-partitioned (spanning) index on the source table will be rebuilt.

Considerations for using row access control and column access control:

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 later another enabled row permission exists that provides access for the authorization IDs that are specified in the definition of the permission. 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 Db2 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, etc. are processed. This derived search condition permits the authorization IDs that are specified in the permission definitions to access certain rows in the table. See the description of subselect for information on how the application of enabled row permissions affects the fetch operation. See the data change statements for information on how the application of enabled row permissions affects the data change operation.

Row access control remains enforced until the DEACTIVATE ROW ACCESS CONTROL clause is used to stop enforcing it.

Implicit row permission 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, Db2 implicitly creates a default row permission for the table. The default row permission prevents all access to the table. The implicitly created row permission is in the same schema of the base table and has a name in the form of QIBM_DEFAULT_system-table-name_system_schema_name.

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 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 have been created for the table are applied to mask the column values referenced in the final result table of the query or to determine the new values used in the data change statements.

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 (a simple reference to a column name or embedded in an expression) appears in the outermost select list, the column mask is applied to the column to produce the value 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 nested table expression or view, the column mask is applied to the column returned in the result table of the nested 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 returned in the final result table remain the same, except that the values in the resultant 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 enforced by SELECT DISTINCT. If the masked column is embedded in an expression, the result of the expression can become different because the column mask is applied on the column before the expression evaluation can take place. For example, applying a column mask on column SSN can change the result of aggregate function COUNT(DISTINCT SSN) because the DISTINCT operation is performed on the masked values. On the other hand, if the expression in a query is the same as the expression used to mask the column value in the column mask definition, the result of the expression in the query might remain unchanged. For example, the expression in the query is 'XXX-XX-' || SUBSTR( SSN, 8, 4) and the same expression appears in the column mask definition. In this particular example, the user can replace the expression in the query with column SSN to avoid the same expression being evaluated twice.

The following are the contexts where the column masks are used by Db2 to mask the column values for the result of a query. Certain restrictions might apply to some contexts. Those restrictions are described in a separate list.

The outermost SELECT clause 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, the outermost SELECT clause of the corresponding nested table expression, common table expression, or view where the column appears.
The outermost SELECT clause that is used to derive the new values for an INSERT, UPDATE, or MERGE statement.
A scalar-fullselect expression that appears in the outermost SELECT clause of the above statements, the right side of a SET variable assignment statement, the VALUES INTO statement, or the VALUES statement.

A column mask is created without knowing all of the contexts in which it might be used. To mask a column value in the final result table, the column mask definition is merged into the statement by Db2. When the column mask definition is brought into the context of the statement, it might conflict with certain SQL semantics in the statement. Therefore, in some situations the combination of the statement and the application of a column mask can return an error. The following describes when the error might be returned:

If the FROM clause in a subselect references a recursive common table expression, and if the result of the recursive common table expression is used to derive the final result table, the column mask cannot be applied to a column that is referenced in the fullselect of the recursive common table expression.
If a user-defined function is defined with the NOT SECURED option, the argument of the function must not reference a column for which a column mask is enabled and column access control is activated for its table. This rule applies to user-defined functions that are referenced anywhere in the statement.

To avoid the above error situations, one of the following actions must be taken:

modify or remove the above contexts from the statement
disable the column mask
drop the column mask, modify the definition, and recreate the column mask
deactivate column access control for the table

In some situations, if the statement contains a SELECT DISTINCT or a GROUP BY and a column mask is applied to a column that directly or indirectly derives the result of SELECT DISTINCT or GROUP BY, the statement might return a result that is not deterministic. These situations include:

The column mask definition references other columns from the same table as the column to which the column mask is applied.
The column is referenced in the argument of built-in scalar functions (such as COALESCE, IFNULL, NULLIF, MAX, MIN, LOCATE).
The column is referenced in the argument of an aggregate function.
The column is embedded in an expression and the expression contains a function that is not deterministic or has an external action.

If the column is not nullable, the definition of its column mask will not, most likely, consider a null value for the column. After the column access control is activated for the table, if the table is the null-padded table in an outer join, the value of the column in the final result table might be a null. To ensure that the column mask can mask a null value, if the table is the null-padded table in an outer join, Db2 will add "WHEN target-column IS NULL THEN NULL" as the first WHEN clause to the column mask definition. This forces a null value to always be masked as a null value. For a nullable column, this removes the ability to mask a null value as something else. Example 4 shows this added WHEN clause.

For INSERT, UPDATE, and MERGE, when a column is referenced while deriving the values of a new row, if that column has an enabled column mask, the masked value is used to derive the new values. If the object table also has column access control activated, the column mask that is applied to derive the new values must return the column itself, not a constant or an expression. If the column mask does not mask the column to itself, the new value cannot be used for insert or update and an error is returned. The rules that are used to apply column masks in order to derive the new values follow the same rules described above for the final result table of a query. See the data change statements for how the column masks are used to affect the insertability and updatability.

A column mask can be applied only to a base table column. If a view, nested table expression, or common table expression column is involved in the final result table, the above error situations can occur inside the view, nested table expression, or common table expression.

Column access control does not affect the XMLTABLE built-in function. If the input to the XMLTABLE function is a column with a column mask, the column mask is not applied.

Column access control remains activated until the DEACTIVATE COLUMN ACCESS CONTROL clause is used to stop enforcing it.

Column masks and trigger transition variables: Values for OLD ROW and OLD TABLE transition variables will never contain masked values.

A SET transition-variable assignment statement can assign masked data to the variable. If a violation check constraint does not exist for the column, the masked data will be inserted or updated in the column and no error will be issued.

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 table is accessible based on the granted privileges.

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 unmasked column values are used for the final result table. The explicitly created row permissions or column masks, if any, remain but have no effect.

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 referenced in the trigger body. To ensure there is no security concern 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.

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.

Db2 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 for all versions of the user-defined function, and that all subsequent ALTER FUNCTION statements or changes to external programs 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 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 using a row-begin column and a row-end column. See AS ROW BEGIN, AS ROW END, and ADD PERIOD FOR period-definition
A transaction-start-ID column. See AS TRANSACTION START ID
A system-period data versioning definition specified on a subsequent ALTER TABLE statement using the ADD VERSIONING action which includes the name of the associated history table. See ADD VERSIONING USE HISTORY TABLE history-table-name.

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 generated for other transactions. Given that the column may contain null values, it is recommended that one of the following methods be used when retrieving 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 defined on the system-period temporal table are applied to all of the rows 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 defined on the history table are not applied.
When the history table is accessed directly, the row and column access rules 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 and row access control is not already 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.

Managing a history table: For an authorized user, limited modifications can be made directly to the history table. Rows can be deleted from the table and partitions can be added or dropped.

Read-only cursors and read-only views: The rules that are used to determine a read-only cursor or a read-only view remain unaffected by row and column access control. The effect of application of enabled column masks is not known until run time. Therefore, the data change operation on a writable cursor or a writable view could still fail at run time.

Syntax alternatives: The following syntax is supported for compatibility to prior releases. The syntax is non-standard and should not be used:

INLINE LENGTH is a synonym for ALLOCATE.
If an ADD constraint is the first clause of the ALTER TABLE statement, the ADD keyword is optional, but strongly recommended. Otherwise, it is required.
constraint-name (without the CONSTRAINT keyword) may be specified following the FOREIGN KEY keywords in a referential-constraint.
PART is a synonym for PARTITION.
PARTITION partition-number may be specified instead of PARTITION partition-name. A partition-number must not identify an existing partition of the table or a partition that was previously specified in the ALTER TABLE statement.
If a partition-number is not specified, a unique partition number is generated by the database manager.
VALUES is a synonym for ENDING AT.
SET MATERIALIZED QUERY AS DEFINITION ONLY is a synonym for DROP MATERIALIZED QUERY.
SET SUMMARY AS DEFINITION ONLY is a synonym for DROP MATERIALIZED QUERY
SET MATERIALIZED QUERY AS (select-statement) is a synonym for ADD MATERIALIZED QUERY (select-statement)
SET SUMMARY AS (select-statement) is a synonym for ADD MATERIALIZED QUERY (select-statement)

Cascaded Effects

Adding a column has no cascaded effects to SQL views, materialized query tables, or most logical files. For example, adding a column to a table does not cause the column to be added to any dependent views, even if those views were created with a SELECT * clause.

Adding a column does cause any SQL triggers to be recreated and include the new column.

Dropping or altering a column may cause several cascaded effects. Table 1 lists the cascaded effects of dropping a column.

Table 1. Cascaded effects of dropping a column
Operation	RESTRICT Effect	CASCADE Effect
Drop of a column referenced by a view	The drop of the column is not allowed.	The view and all views dependent on that view are dropped.
Drop of a column referenced by a non-view logical file	The drop is allowed, and the column is dropped from the logical file if: The logical file shares a format with the file being altered, and The dropped column is not used as a key field or in select/omit specifications, and That format is not used again in the logical file with another based-on file. Otherwise, the drop of the column is not allowed.	The drop is allowed, and the column is dropped from the logical file if: The logical file shares a format with the file being altered, and The dropped column is not used as a key field or in select or omit specifications, and That format is not used again in the logical file with another based-on file. Otherwise, the logical file is dropped.
Drop of a column referenced in an index as part of a key, in the WHERE clause, in the INCLUDE clause, or as an explicit or implicit column in the ADD columns clause	The drop of the index is not allowed.	The index is dropped.
Drop of a column referenced in the key of an keyed physical file where the key is not a PRIMARY KEY	The physical file is changed to a non-keyed physical file.	The physical file is changed to a non-keyed physical file.
Drop of a column referenced in a unique constraint	If all the columns referenced in the unique constraint are dropped in the same ALTER COLUMN statement and the unique constraint is not referenced by a referential constraint, the columns and the constraint are dropped. (Hence, the index used to satisfy the constraint is also dropped.) For example, if column A is dropped, and a unique constraint of UNIQUE (A) or PRIMARY KEY (A) exists and no referential constraints reference the unique constraint, the operation is allowed. Otherwise, the drop of the column is not allowed.	The unique constraint is dropped as are any referential constraints that refer to that unique constraint. (Hence, any indexes used by those constraints are also dropped).
Drop of a column referenced in a referential constraint	If all the columns referenced in the referential constraint are dropped at the same time, the columns and the constraint are dropped. (Hence, the index used by the foreign key is also dropped). For example, if column B is dropped and a referential constraint of FOREIGN KEY (A) exists, the operation is allowed. Otherwise, the drop of the column is not allowed.	The referential constraint is dropped. (Hence, the index used by the foreign key is also dropped).
Drop of a column referenced in an SQL trigger	The drop of the column is not allowed.	The SQL trigger is dropped.
Drop of a column referenced in an MQT	The drop of the column is not allowed.	The MQT is dropped.

Table 2 lists the cascaded effects of altering a column. (Alter of a column in the following chart means altering a data type, precision, scale, length, or nullability characteristic.)

Table 2. Cascaded effects of altering a column
Operation	Effect
Alter of a column referenced by a view	The alter is allowed. The views that are dependent on the table will be recreated. The new column attributes will be used when recreating the views.
Alter of a column referenced by a non-view logical file	The alter is allowed. The non-view logical files that are dependent on the table will be recreated. If the logical file shares a format with the file being altered, and that format is not used again in the logical file with another based-on file, the new column attributes will be used when recreating the logical file. Otherwise, the new column attributes will not be used when recreating the logical file. Instead, the current logical file attributes are used.
Alter of a column referenced in the key of an index.	The alter is allowed. (Hence, the index will usually be rebuilt.)
Alter of a column referenced in a unique constraint	The alter is allowed. (Hence, the index will usually be rebuilt.) If the unique constraint is referenced by a referential constraint, the attributes of the foreign keys no longer match the attributes of the unique constraint including the field procedure. The constraint will be placed in a defined and check-pending state.
Alter of a column referenced in a referential constraint	The alter is allowed. If the referential constraint is in the defined but check-pending state, the alter is allowed and an attempt is made to put the constraint in the enabled state. (Hence, the index used to satisfy the unique constraint will usually to be rebuilt.) If the referential constraint is in the enabled state, the constraint is placed in the defined and check-pending state.
Alter of a column referenced in an SQL trigger	The trigger is recreated.
Alter of a column referenced in an MQT	The MQT is recreated to include the new attributes.

Examples

Example 1: Add a new column named RATING, which is one character long, to the DEPARTMENT table.

  ALTER TABLE DEPARTMENT
    ADD RATING CHAR

Example 2: Add a new column named PICTURE_THUMBNAIL to the EMPLOYEE table. Create PICTURE_THUMBNAIL as a BLOB column with a maximum length of 1K characters.

  ALTER TABLE EMPLOYEE
    ADD PICTURE_THUMBNAIL  BLOB(1K)

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

EQUIP_NO: INT
EQUIP_DESC: VARCHAR(50)
LOCATION: VARCHAR(50)
EQUIP_OWNER: CHAR(3)

Add a referential constraint to the EQUIPMENT table so that the owner (EQUIP_OWNER) must be a department number (DEPTNO) that is present in the DEPARTMENT table. If a department is removed from the DEPARTMENT table, the owner (EQUIP_OWNER) values for all equipment owned by that department should become unassigned (or set to null). Give the constraint the name DEPTQUIP.

  ALTER TABLE EQUIPMENT
    FOREIGN KEY DEPTQUIP (EQUIP_OWNER)
      REFERENCES DEPARTMENT
      ON DELETE SET NULL

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

   ALTER TABLE EQUIPMENT
     ALTER COLUMN EQUIP_OWNER
     SET DEFAULT 'ABC'

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

   ALTER TABLE EQUIPMENT
     DROP COLUMN LOCATION CASCADE

Alter the table so that a new column called SUPPLIER is added, the existing column called LOCATION is dropped, a unique constraint over the new column SUPPLIER is added, and a primary key is built over the existing column EQUIP_NO.

   ALTER TABLE EQUIPMENT
     ADD COLUMN SUPPLIER INT
     DROP COLUMN LOCATION
     ADD UNIQUE SUPPLIER
     ADD PRIMARY KEY EQUIP_NO

Notice that the column EQUIP_DESC is a variable length column. If an allocated length of 25 was specified, the following ALTER TABLE statement would not change that allocated length.

   ALTER TABLE EQUIPMENT
     ALTER COLUMN EQUIP_DESC
     SET DATA TYPE VARCHAR(60)

Example 4: Alter the EMPLOYEE table. Add the check constraint named REVENUE defined so that each employee must make a total of salary and commission greater than $30,000.

  ALTER TABLE EMPLOYEE
    ADD CONSTRAINT REVENUE 
    CHECK (SALARY + COMM > 30000)

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

  ALTER TABLE EMPLOYEE
    DROP CONSTRAINT REVENUE

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

  ALTER TABLE EMPLOYEE
    ALTER COLUMN PHONENO SET DATA TYPE VARCHAR (20)

Example 7: Alter the base table TRANSCOUNT to a materialized query table. The result of the select-statement must provide a set of columns that match the columns in the existing table (same number of columns and compatible attributes).

  ALTER TABLE TRANSCOUNT
    ADD MATERIALIZED QUERY
    (SELECT  ACCTID, LOCID, YEAR, COUNT(*) AS CNT
       FROM TRANS
       GROUP BY ACCTID, LOCID, YEAR )
    DATA INITIALLY DEFERRED
    REFRESH DEFERRED
    MAINTAINED BY USER

Example 8: Alter the CUSTOMER table to allow versioning.

First, add the columns and the period definition that are required for versioning along with two columns that will be used for tracking identity of row changes:

ALTER TABLE CUSTOMER 
  ADD COLUMN SYSTEM_START TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN 
  ADD COLUMN SYSTEM_END TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END 
  ADD COLUMN TRANSACTION_ID TIMESTAMP(12) GENERATED ALWAYS AS TRANSACTION START ID 
  ADD COLUMN AUDIT_TYPE_CHANGE CHAR(1) GENERATED ALWAYS AS (DATA CHANGE OPERATION) 
  ADD COLUMN AUDIT_USER VARCHAR(128) GENERATED ALWAYS AS (SESSION_USER) 
  ADD PERIOD FOR SYSTEM_TIME (SYSTEM_START, SYSTEM_END);

Next, create the history table:

CREATE TABLE CUSTOMER_HISTORY LIKE CUSTOMER;

Finally, define a versioned relationship for the tables:

ALTER TABLE CUSTOMER ADD VERSIONING USE HISTORY TABLE CUSTOMER_HISTORY 
                         ON DELETE ADD EXTRA ROW;

Examples for column access control

Example 1: Based on the data in the CUSTOMER table, the SELECT DISTINCT statement returns one row with the SALARY value 100,000. A column mask, SALARY_MASK, is created to mask the salary value. After column access control is activated for the CUSTOMER table, the column mask is applied to SALARY column. A user with the 'MGR' authorization ID issues a SELECT DISTINCT statement. The SELECT DISTINCT statement still returns one row because the removal of duplicates is based on the unmasked value of the SALARY column, but the value that is returned in that row is based on the masked SALARY value, which can be either 125,000 or 110,000.

The table CUSTOMER contains:

SALARY	COMMISSION	EMPID
100,000	25,000	123456
100,000	10,000	654321

CREATE MASK SALARY_MASK ON CUSTOMER
   FOR COLUMN SALARY RETURN
      CASE WHEN(SESSION_USER = 'MGR')
              THEN SALARY + COMMISSION
           ELSE SALARY
      END
   ENABLE;

COMMIT;

ALTER TABLE CUSTOMER
   ACTIVATE COLUMN ACCESS CONTROL;

COMMIT;

SELECT DISTINCT SALARY FROM CUSTOMER;

Example 2: Based on the data in T1 and T2 tables, the SELECT DISTINCT statement using the COALESCE function returns one row with the T1.C1 value of 1. A column mask, C1_MASK, is created to mask the value of T1.C1. After column access control is activated for table T1, the column mask is applied to column C1 of table T1. A user with the 'EMP' authorization ID issues a SELECT DISTINCT statement. The SELECT DISTINCT statement still returns one row because the removal of duplicates is based on the unmasked value of T1.C1 from the COALESCE function, but the value that is returned in that row is based on the masked value of T1.C1 from the COALESCE function. The returned value can be either 2 or 3.

INSERT INTO T1(C1) VALUES(1);
INSERT INTO T1(C1) VALUES(1);

INSERT INTO T2(C1) VALUES(2);
INSERT INTO T2(C1) VALUES(3);

CREATE MASK C1_MASK ON T1
   FOR COLUMN C1 RETURN
      CASE WHEN(SESSION_USER = 'EMP')
         THEN NULL
         ELSE C1
      END
   ENABLE;

COMMIT;

ALTER TABLE T1
   ACTIVATE COLUMN ACCESS CONTROL;

COMMIT;

SELECT DISTINCT COALESCE(T1.C1, T2.C1) FROM T1, T2;

Example 3: Based on the data in the CUSTOMER table, the maximum income is the same in the states CA and IL, 50,000, thus, the SELECT DISTINCT statement returns one row. A column mask, INCOME_MASK, is created to mask the income value. After column access control is activated for the CUSTOMER table, the column mask is applied to the INCOME column before the MAX aggregate function is evaluated. However, the INCOME_ MASK column mask, masks the income value of 0 as 100,000 in state IL. As a result, the maximum income becomes 100,000 for state IL, but the maximum income is still 50,000 for state CA. X.B is used in a predicate in the SELECT DISTINCT statement, therefore, the original INCOME values and the original results of the MAX(INCOME) function must be preserved. So the SELECT DISTINCT statement still returns one row, but the value in that row might not be deterministic, that is, the value might be 50,000 from the 'CA' row or might be 100,000 from the 'IL' row.

The CUSTOMER table contains:

STATE	INCOME
CA	40,000
CA	50,000
IL	0
IL	10,000
IL	50,000

CREATE MASK INCOME_MASK ON CUSTOMER
   FOR COLUMN INCOME RETURN
      CASE WHEN(INCOME = 0)
         THEN 100000
         ELSE INCOME
      END
   ENABLE;

COMMIT;

ALTER TABLE CUSTOMER
   ACTIVATE COLUMN ACCESS CONTROL;

COMMIT;

SELECT DISTINCT B FROM
   (SELECT STATE, MAX(INCOME) FROM CUSTOMER
      GROUP BY STATE)
      X(A, B)
   WHERE B > 10000;

Example 4: The expression INCOME + RAND() is not deterministic because the RAND function is not deterministic. Based on the data in the CUSTOMER table, the SELECT DISTINCT statement will, most likely, return two distinct rows. However, it could return only one row. A column mask, INCOME_MASK, is created to mask the income value. After column access control is activated for the CUSTOMER table, the column mask is applied to the INCOME column, which causes the masked value for both rows to be the same. Because the RAND function is not deterministic, the SELECT DISTINCT statement will, most likely, still return two distinct rows, but it could return only one row. The uncertainty caused by the RAND function causes the result of the SELECT DISTINCT statement to not be deterministic.

The CUSTOMER table contains:

STATE	INCOME
CA	40,000
CA	50,000

CREATE MASK INCOME_MASK ON CUSTOMER
   FOR COLUMN INCOME RETURN
      CASE WHEN(INCOME = 40,000)
         THEN 50000
         ELSE INCOME
      END
   ENABLE;

COMMIT;

ALTER TABLE CUSTOMER
   ACTIVATE COLUMN ACCESS CONTROL;

COMMIT;

SELECT DISTINCT A FROM
   (SELECT INCOME + RAND() FROM CUSTOMER)
      X(A)
   WHERE A > 10000;

Example 5: A column mask, STATE_MASK, is created for the STATE column of the CUSTOMER table to return a value that shows the city name with the state if the city is SJ, SFO, or OKLD. Otherwise the city is not returned, just the state. After column access control is activated for the CUSTOMER table, a SELECT statement which groups results using the STATE column is issued. However, because the CITY column that is referenced in the STATE_MASK column mask is not a grouping column, an error is returned to signify that the STATE_MASK column mask is not appropriate for this statement.

The CUSTOMER table contains:

STATE	CITY	INCOME
CA	SJ	40,000
CA	SC	30,000
CA	SB	60,000
CA	SFO	80,000
CA	OKLD	50,000
CA	SJ	70,000
NY	NY	50,000

CREATE MASK STATE_MASK ON CUSTOMER
   FOR COLUMN STATE RETURN
      CASE WHEN(CITY = 'SJ')
          THEN CITY||', '||STATE
        WHEN(CITY = 'SFO')
          THEN CITY||', '||STATE
        WHEN(CITY = 'OKLD')
          THEN CITY||', '||STATE
        ELSE STATE
      END
   ENABLE;

COMMIT;

ALTER TABLE CUSTOMER
   ACTIVATE COLUMN ACCESS CONTROL;

COMMIT;

SELECT STATE, AVG(INCOME) FROM CUSTOMER
   GROUP BY STATE
   HAVING STATE = 'CA';

¹ A trigger is dependent on the column if it is referenced in the UPDATE OF column list or anywhere in the triggered action.

² In cases where enough storage does not exist to make a complete copy, a special copy that only requires approximately 16-32 megabytes of free storage is performed.

³ Any indexes that need to be rebuilt are rebuilt asynchronously by database server jobs.

⁴ A column will also be added to a logical file that shares its physical file's format when a column is added to that physical file (unless that format is used again in the logical file with another based-on file).