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 system authority *EXECUTE on the library 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
- Administrative authority
If a field procedure is defined, the privileges held by the authorization ID of the statement must include at least one of the following:
- The following system authorities:
- The system authority *EXECUTE on the program, and
- The system authority *EXECUTE on the library containing the program
- Administrative authority
If a select-statement is specified, the privileges held by the authorization ID of the statement must include at least one of the following on the tables or views specified in these clauses:
- The SELECT privilege for the table or view
- Administrative authority
If a distinct type is referenced, the privileges held by the authorization ID of the statement must include at least one of the following:
- For each distinct type identified in the statement:
- The USAGE privilege on the distinct type, and
- The system authority *EXECUTE on the library containing the distinct type
- Administrative authority
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
- Administrative authority
For information on the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View and Corresponding System Authorities When Checking Privileges to a Distinct Type.
Syntax
- 1 data-type is optional for row change timestamp columns
- 2 The same clause must not be specified more than once.
- 3 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, or the column is a row change timestamp.
- 4 The datalink-options can only be specified for DATALINKs and distinct types sourced on DATALINKs.
- 1 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, or the column is a row change timestamp.
- 2 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, ADD column-definition, ALTER column-alteration, and DROP COLUMN are not allowed.
ADD COLUMN column-definition
Adds a column to the table. If the table has rows, every value of the column is set to its default value, unless the column is a ROWID column, an identity column (a column that is defined AS IDENTITY), or a row change timestamp. The database manager generates default values for ROWID columns and identity columns. If the table previously had n columns, the ordinality of the new column is n+1. The value of n+1 must not exceed 8000.
A table can have only one ROWID, identity column, or row change timestamp.
A DataLink column with FILE LINK CONTROL cannot be added to a table that is a dependent in a referential constraint with a delete rule of CASCADE.
Adding a new column must not make the sum of the row buffer byte counts of the columns be greater than 32766 or, if a VARCHAR, VARBINARY, or VARGRAPHIC column is specified, 32740. Additionally, if a LOB or XML column is specified, the sum of the byte counts of the columns must not be greater than 3 758 096 383 at the time of insert or update. For information on the byte counts of columns according to data type, see Maximum row sizes.
- column-name
- Names the column to be added to the table. Do not use the same name for more than one column of the table or for a system-column-name of the table. Do not qualify column-name.
- FOR COLUMN system-column-name
- Provides
an IBM® i name for
the column. Do not use the same name for more than one column-name or system-column-name of
the table.
If the system-column-name is not specified, and the column-name is not a valid system-column-name, a system column name is generated. For more information about how system column names are generated, see Rules for Column Name Generation.
- data-type
- Specifies the data type
of the column. The data type can be a built-in data type or a distinct
type.
- built-in-type
- Specifies a built-in data type. See CREATE TABLE for a description of built-in types.
- distinct-type-name
- Specifies that the data type of a column is a distinct type. The length, precision, and scale of the column are respectively the length, precision, and scale of the source type of the distinct type. If a distinct type name is specified without a schema name, the distinct type name is resolved by searching the schemas on the SQL path.
- DEFAULT
- Specifies
a default value for the column. This clause cannot be specified more
than once in the same column-definition.
DEFAULT cannot be specified for an XML column,
a ROWID column, an identity column (a column that is defined AS IDENTITY),
or a row change timestamp column. The database manager generates default
values for ROWID columns, identity columns, and row change timestamp
columns. If a value is not specified following the DEFAULT keyword or a DEFAULT clause is not specified, then:
- if the column is nullable, the default value is the null value.
- if the column is not nullable, the default depends on the data
type of the column:
Data type Default value Numeric 0 Fixed-length character or graphic string Blanks Fixed-length binary string Hexadecimal zeros Varying-length string A string length of 0 Date For existing rows, a date corresponding to 1 January 0001. For added rows, the current date. Time For existing rows, a time corresponding to 0 hours, 0 minutes, and 0 seconds. For added rows, the current time. Timestamp For existing rows, - If the column is a row change timestamp, the current timestamp.
- Otherwise, a date corresponding to 1 January 0001 and a time corresponding to 0 hours, 0 minutes, 0 seconds, and 0 microseconds.
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
distinct type, BINARY, VARBINARY, BLOB, CLOB, DBCLOB, DATE,
TIME, or TIMESTAMP data types. The following table describes the allowed
uses of these cast-functions.
Data Type Cast Function Name Distinct type N based on a BINARY, VARBINARY, BLOB, CLOB, or DBCLOB BINARY, VARBINARY, BLOB, CLOB, or DBCLOB * Distinct type N based on a DATE, TIME, or TIMESTAMP N (the user-defined cast function that was generated when N was created) ** or
DATE, TIME, or TIMESTAMP *Distinct type N based on other data types N (the user-defined cast function that was generated when N was created) ** BINARY, VARBINARY, BLOB, CLOB, or DBCLOB BINARY, VARBINARY, BLOB, CLOB, or DBCLOB * DATE, TIME, or TIMESTAMP DATE, TIME, or TIMESTAMP * Notes:* The name of the function must match the name of the data type (or the source type of the distinct type) with an implicit or explicit schema name of QSYS2.
** The name of the function must match the name of the distinct type for the column. If qualified with a schema name, it must be the same as the schema name for the distinct type. If not qualified, the schema name from function resolution must be the same as the schema name for the distinct type.
- constant
- Specifies a constant as the argument. The constant must conform to the rules of a constant for the source type of the distinct type or for the data type if not a distinct type. For BINARY, VARBINARY, BLOB, CLOB, DBCLOB, DATE, TIME, and TIMESTAMP functions, the constant must be a string constant.
- USER
- Specifies the value of the USER special register at the time of INSERT or UPDATE as the default value for the column. The data type of the source type of the distinct type of the column must be CHAR or VARCHAR with a length attribute that is greater than or equal to the length attribute of USER. For existing rows, the value is that of the USER special register at the time the ALTER TABLE statement is processed.
- CURRENT_DATE
- Specifies the current date as the default for the column. If CURRENT_DATE is specified, the data type of the source type of the distinct type of the column must be DATE.
- CURRENT_TIME
- Specifies the current time as the default for the column. If CURRENT_TIME is specified, the data type of the source type of the distinct type of the column must be TIME.
- CURRENT_TIMESTAMP 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) or a row change timestamp column.
It may also be specified if the data type of the column is a ROWID
(or a distinct type that is based on a ROWID). Otherwise, it must
not be specified.
- ALWAYS
- Specifies that the database manager will always generate a value for the column when a row is inserted or updated and a default value must be generated. ALWAYS is the recommended value.
- BY DEFAULT
- Specifies that the database manager will generate a value for
the column when a row is inserted or updated and
a default value must be generated, unless an explicit value is specified.
For a ROWID column, the database manager uses a specified value, but it must be a valid unique row ID value that was previously generated by the database manager or Db2® for i.
For an identity column or row change timestamp column, the database manager inserts or updates a specified value but does not verify that it is a unique value for the column unless the identity column or row change timestamp column has a unique constraint or a unique index that solely specifies the identity column or row change timestamp column.
- AS IDENTITY
- Specifies
that the column is an identity column for the table. A table can have
only one identity column. An identity column is not allowed in a distributed table. AS IDENTITY can be specified
only if the data type for the column is an exact numeric type with
a scale of zero (SMALLINT, INTEGER, BIGINT, DECIMAL or NUMERIC with
a scale of zero, or a distinct type based on one of these data types).
If a DECIMAL or NUMERIC data type is specified, the precision must
not be greater than 31.
An identity column is implicitly NOT NULL. See the AS IDENTITY clause in CREATE TABLE for the descriptions of the identity attributes.
- FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
- Specifies that the column is a timestamp and the values will be generated by the database manager. The database manager generates a value for the column for each row as a row is inserted, and for every row in which any column is updated. The value generated for a row change timestamp column is a timestamp corresponding to the time of the insert or update of the row. If multiple rows are inserted with a single SQL statement, the value for the row change timestamp column may be different for each row to reflect when each row was inserted. The generated value is not guaranteed to be unique.
- A table can have only one row change timestamp column. If data-type is
specified, it must be a TIMESTAMP 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.
- NOT NULL
- Prevents the column from containing null values. Omission of NOT NULL implies that the column can contain null values. If NOT NULL is specified in the column definition, then DEFAULT must also be specified unless the column is an identity column. NOT NULL is required for a row change timestamp column
- NOT HIDDEN
- Indicates the column is included in implicit references to the table in SQL statements. This is the default.
- IMPLICITLY HIDDEN
- Indicates the column is not visible in SQL statements unless it is referred to explicitly by name. For example, SELECT * does not include any hidden columns in the result. A table must contain at least one column that is not IMPLICITLY HIDDEN.
- column-constraint
- The column-constraint of a column-definition provides
a shorthand method of defining a constraint composed of a single column.
Thus, if a column-constraint is specified
in the definition of column C, the effect is the same as if that
constraint were specified as a unique-constraint, referential-constraint or check-constraint in
which C is the only identified column.
- CONSTRAINT constraint-name
- A constraint-name must
not be the same as a constraint name that was previously specified
in the ALTER TABLE statement and must not identify a constraint that
already exists at the current server.
If the clause is not specified, a unique constraint name is generated by the database manager.
- PRIMARY KEY
- Provides
a shorthand method of defining a primary key composed of a single
column. Thus, if PRIMARY KEY is specified in the definition of column
C, the effect is the same as if the PRIMARY KEY(C) clause is specified
as a separate clause.
This clause must not be specified in more than one column-definition and must not be specified at all if the UNIQUE clause is specified in the column definition. The column must not be a LOB, DataLink, or XML column. If a sort sequence is specified, the column must not contain a field procedure.
When a primary key is added, a CHECK constraint is implicitly added to enforce the rule that the NULL value is not allowed in the column that makes up the primary key.
- UNIQUE
- Provides
a shorthand method of defining a unique constraint composed of a single
column. Thus, if UNIQUE is specified in the definition of column C,
the effect is the same as if the UNIQUE (C) clause is specified as
a separate clause.
This clause cannot be specified more than once in a column definition and must not be specified if PRIMARY KEY is specified in the column-definition. The column must not be a LOB, DataLink, or XML column. If a sort sequence is specified, the column must not contain a field procedure.
- references-clause
- The references-clause of a column-definition provides a shorthand method of defining a foreign key composed of a single column. Thus, if a references-clause is specified in the definition of column C, the effect is the same as if that references-clause were specified as part of a FOREIGN KEY clause in which C is the only identified column. The references-clause is not allowed if the table is a declared global temporary table or a distributed table. The column cannot be a row change timestamp column.
- CHECK(check-condition)
- Provides
a shorthand method of defining a check constraint whose check-condition only
references a single column. Thus, if CHECK is specified in the column
definition of column C, no columns other than C can be referenced
in the check-condition of the check constraint.
The effect is the same as if the check constraint were specified as
a separate clause.
ROWID, XML, or DATALINK with FILE LINK CONTROL columns cannot be referenced in a CHECK constraint. For additional restrictions see, ADD check-constraint.
- FIELDPROC
- Designates an external-program-name as the field procedure exit routine for the column. It must be an ILE program that does not contain SQL. It cannot be a service program.
- datalink-options
- Specifies the options associated with a DATALINK column. See CREATE TABLE for a description of datalink-options.
- BEFORE column-name
- Identifies the column before which the new column is added. The name must not be qualified and must identify an existing column in the table. If the BEFORE clause is not specified, the column is added at the end of the row.
ALTER COLUMN column-alteration
Alters the definition of a column, including the attributes of an existing identity column. Only the attributes specified will be altered. Others will remain unchanged.
- column-name
- Identifies the column to be altered. The name must not be qualified and must identify an existing column in the table. The name must not identify a column that is being added or dropped in the same ALTER TABLE statement.
- SET DATA TYPE data-type
- Specifies the new data
type of the column to be altered. The new data type must be compatible
with the existing data type of the column. For more information about
the compatibility of data types see Assignments and comparisons.
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
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.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 to be considered an identity column (defined with the AS IDENTITY clause), row change timestamp column, or the data type of the column is a ROWID (or a distinct type that is based on a ROWID). Otherwise, it must not be specified.
- AS IDENTITY
- Specifies
that the column is changed to an identity column for the table. A
table can have only one identity column. An identity column is not
allowed in a distributed table. AS IDENTITY can be specified only
if the data type for the column is an exact numeric type with a scale
of zero (SMALLINT, INTEGER, BIGINT, DECIMAL or NUMERIC with a scale
of zero, or a distinct type based on one of these data types). If
a DECIMAL or NUMERIC data type is specified, the precision must not
be greater than 31.
The identity column is implicitly changed to NOT NULL. If the column has an explicit default value, the default value is dropped. See the AS IDENTITY clause in CREATE TABLE for the descriptions of the identity attributes.
- SET NOT NULL
- Specifies that the column cannot contain null values. All values for this column in existing rows of the table must be not null. If the specified column has a default value and a new default value is not specified, the existing default value must not be NULL. SET NOT NULL is not allowed if the column is identified in the foreign key of a referential constraint with a DELETE rule of SET NULL and no other nullable columns exist in the foreign key.
- SET NOT HIDDEN or IMPLICITLY HIDDEN
- Specifies
the hidden attribute for the column.
- NOT HIDDEN
- Indicates the column is included in implicit references to the table in SQL statements.
- IMPLICITLY HIDDEN
- Indicates the column is not visible in SQL statements unless it is referred to explicitly by name. For example, SELECT * does not include any hidden columns in the result. A table must contain at least one column that is not IMPLICITLY HIDDEN.
- SET FIELDPROC
- Designates an external-program-name as the field procedure exit routine for the column. It must be an ILE program that does not contain SQL. It cannot be a service program.
- 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, or ROWID.
- DROP IDENTITY
- Drops the identity attributes of the column, making the column a simple numeric data type column. DROP IDENTITY is not allowed if the column is not an identity column.
- DROP ROW CHANGE TIMESTAMP
- Drops the row change timestamp attribute of the column, making the column a simple timestamp column. DROP ROW CHANGE TIMESTAMP is not allowed if the column is not a row change timestamp column.
- DROP FIELDPROC
- Drops the field procedure for the column. DROP FIELDPROC is not allowed if the column does not have a field procedure defined.
- identity-alteration
- Alters
the identity attributes of the column. The column must exist in the
specified table and must already be defined with the IDENTITY attribute.
For a description of the attributes, see AS IDENTITY.
- RESTART
- Specifies the next value for an identity column. If WITH numeric-constant is
not specified, the sequence is restarted at the value specified implicitly
or explicitly as the starting value when the identity column was originally
created. RESTART does not change the original START WITH value.
- WITH numeric-constant
- Specifies that numeric-constant will be used as the next value for the column. The numeric-constant must be an exact numeric constant that can be any positive or negative value that could be assigned to this column, without nonzero digits existing to the right of the decimal point.
DROP COLUMN
Drops the identified column from the table.
- column-name
- Identifies the column to be dropped.
The column name must not be qualified. The name must identify a column
of the specified table. The name must not identify a column that was
already added or altered in this ALTER TABLE statement. The name must
not identify the only column of a table. The name
must not identify the last column of the table that is not hidden. The
name must not identify a partition key of a partitioned table or a
distributed table.
- 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. 1
- RESTRICT
- Specifies that the column cannot
be dropped if any views, indexes, triggers, materialized
query tables, or constraints are dependent on the column. 1
If all the columns referenced in a constraint are dropped in the same ALTER TABLE statement, RESTRICT does not prevent the drop.
ADD unique-constraint
- CONSTRAINT constraint-name
- Names the constraint.
A constraint-name must not be the same as
a constraint name that was previously specified in the ALTER TABLE
statement and must not identify a constraint that already exists at
the current server. The constraint-name must
be unique within a schema.
If not specified, a unique constraint name is generated by the database manager.
- UNIQUE (column-name,…)
- Defines
a unique constraint composed of the identified columns. Each column-name must
be an unqualified name that identifies a column of the table. The
same column must not be identified more than once. The
column must not be a LOB, DATALINK, or XML column. If a sort sequence
is specified, the column must not contain a field procedure. The
number of identified columns must not exceed 120, and the sum of their
lengths must not exceed 32766-n, where n is the number
of columns specified that allow nulls.
The set of identified columns cannot be the same as the set of columns specified in another UNIQUE constraint or PRIMARY KEY on the table. For example, UNIQUE (A,B) is not allowed if UNIQUE (B,A) or PRIMARY KEY (A,B) already exists on the table. Any existing nonnull values in the set of columns must be unique. Multiple null values are allowed.
If a unique index already exists on the identified columns, that index is designated as a unique constraint index. Otherwise, a unique index is created to support the uniqueness of the unique key. The unique index is created as part of the system physical file, not as a separate system logical file.
- PRIMARY KEY (column-name,…)
- Defines
a primary key composed of the identified columns. Each column-name must
be an unqualified name that identifies a column of the table. The
same column must not be identified more than once. The
column must not be a LOB, DATALINK, or XML column. If a sort sequence
is specified, the column must not contain a field procedure. The
number of identified columns must not exceed 120, and the sum of their
lengths must not exceed 32766. The table must not already have a primary
key.
The identified columns cannot be the same as the columns specified in another UNIQUE constraint on the table. For example, PRIMARY KEY (A,B) is not allowed if UNIQUE (B,A) already exists on the table. Any existing values in the set of columns must be unique.
When a primary key is added, a CHECK constraint is implicitly added to enforce the rule that the NULL value is not allowed in any of the columns that make up the primary key.
If a unique index already exists on the identified columns, that index is designated as a primary index. Otherwise, a primary index is created to support the uniqueness of the primary key. The unique index is created as part of the system physical file, not a separate system logical file.
ADD referential-constraint
- CONSTRAINT constraint-name
- Names
the constraint. A constraint-name must not
identify a constraint that already exists at the current server.
If not specified, a unique constraint name is generated by the database manager.
- FOREIGN KEY
- Defines
a referential constraint. FOREIGN KEY is not allowed
if the table is a declared global temporary table or a distributed
table.
Let T1 denote the table being altered.
- (column-name,…)
- The foreign key of the referential constraint is composed of the identified columns. Each column-name must be an unqualified name that identifies a column of T1. The same column must not be identified more than once. The column must not be a LOB, DATALINK, or XML column and must not be a row change timestamp column. If a sort sequence is specified, the column must not contain a field procedure. The number of the identified columns must not exceed 120, and the sum of their lengths must not exceed 32766-n, where n is the number of columns specified that allows nulls.
- REFERENCES table-name
- The table-name specified
in a REFERENCES clause must identify a base table that exists at the
current server, but it must not identify a catalog
table, a declared temporary table, or a distributed table. If the
parent is a partitioned table, the unique index that enforces the
parent unique constraint must be non-partitioned. This table
is referred to as the parent table in the constraint relationship.
A referential constraint is a duplicate if its foreign key, parent key, and parent table are the same as the foreign key, parent key, and parent table of an existing referential constraint on the table. Duplicate referential constraints are allowed, but not recommended.
Let T2 denote the identified parent table.
- (column-name,…)
- The parent key of the referential
constraint is composed of the identified columns. Each column-name must
be an unqualified name that identifies a column of T2. The same column
must not be identified more than once. The column
must not be a LOB, DATALINK, or XML column and must not be a row change
timestamp column. If a sort sequence is specified, the column must
not contain a field procedure. The number of identified columns
must not exceed 120, and the sum of their lengths must not exceed
32766-n, where n is the number of columns specified
that allow nulls.
The list of column names must be identical to the list of column names in the primary key of T2 or a UNIQUE constraint that exists on T2. The names may be specified in any order. For example, if (A,B) is specified, a unique constraint defined as UNIQUE (B,A) would satisfy the requirement. If a column name list is not specified then T2 must have a primary key. Omission of the column name list is an implicit specification of the columns of that primary key.
The specified foreign key must have the same number of columns as the parent key of T2. The description of the nth column of the foreign key and the nth column of the parent key must have identical data types, lengths, CCSIDs, and FIELDPROCs.
Unless the table is empty, the values of the foreign key must be validated before the table can be used. Values of the foreign key are validated during the execution of the ALTER TABLE statement. Therefore, every nonnull value of the foreign key must match some value of the parent key of T2.
The referential constraint specified by the FOREIGN KEY clause defines a relationship in which T2 is the parent and T1 is the dependent.
- ON DELETE
- Specifies
what action is to take place on the dependent tables when a row of
the parent table is deleted. There are five possible actions:
- NO ACTION (default)
- RESTRICT
- CASCADE
- SET NULL
- SET DEFAULT
SET NULL must not be specified unless some column of the foreign key allows null values. SET NULL and SET DEFAULT must not be specified if T1 has an update trigger.
CASCADE must not be specified if T1 has a delete trigger.
CASCADE must not be specified if T1 contains a DataLink column with FILE LINK CONTROL.
The delete rule applies when a row of T2 is the object of a DELETE or propagated delete operation and that row has dependents in T1. Let p denote such a row of T2.
- If RESTRICT or NO ACTION is specified, an error occurs and no rows are deleted.
- If CASCADE is specified, the delete operation is propagated to the dependents of p in T1.
- If SET NULL is specified, each nullable column of the foreign key of each dependent of p in T1 is set to null.
- If SET DEFAULT is specified, each column of the foreign key of each dependent of p in T1 is set to its default value.
- ON UPDATE
- Specifies
what action is to take place on the dependent tables when a row of
the parent table is updated.
The update rule applies when a row of T2 is the object of an UPDATE or propagated update operation and that row has dependents in T1. Let p denote such a row of T2.
- If RESTRICT or NO ACTION is specified, an error occurs and no rows are updated.
ADD check-constraint
- CONSTRAINT constraint-name
- Names the constraint.
A constraint-name must not identify a constraint
that already exists at the current server. The constraint-name must
be unique within a schema.
If not specified, a unique constraint name is generated by the database manager.
- CHECK(check-condition)
- Defines
a check constraint. The check-condition must
be true or unknown for every row of the table.
The check-condition is a search-condition, except:
- It can only refer to columns of the table and the column names must not be qualified.
- The result of any expression in the check-condition cannot be a ROWID, XML, or DATALINK with FILE LINK CONTROL data type.
- It must not contain any of the following:
- Subqueries
- Aggregate functions
- Variables
- Global variables
- Parameter markers
- Sequence-references
- Complex expressions that contain LOBs (such as concatenation)
- OLAP specifications
- ROW CHANGE expressions
- REGEXP_LIKE predicate
- Special registers
- Any function that is not deterministic
- User-defined functions other than functions that were implicitly generated with the creation of a distinct type
- The following built-in scalar functions:
ATAN2 DLURLPATHONLY LPAD RPAD CARDINALITY DLURLSCHEME MAX_CARDINALITY SCORE CONTAINS DLURLSERVER MONTHNAME SOUNDEX CURDATE DLVALUE MONTHS_BETWEEN TABLE_NAME CURTIME ENCRYPT_AES NEXT_DAY TABLE_SCHEMA DATAPARTITIONNAME ENCRYPT_RC2 NOW TIMESTAMP_FORMAT DATAPARTITIONNUM ENCRYPT_TDES OVERLAY TIMESTAMPDIFF DAYNAME GENERATE_UNIQUE RAISE_ERROR TRUNC_TIMESTAMP DBPARTITIONNAME GETHINT RAND VARCHAR_FORMAT DECRYPT_BINARY IDENTITY_VAL_LOCAL REGEXP_COUNT VERIFY_GROUP_FOR_USER DECRYPT_BIT INSERT REGEXP_INSTR WEEK_ISO DECRYPT_CHAR JSON_ARRAY REGEXP_REPLACE WRAP DECRYPT_DB JSON_OBJECT REGEXP_SUBSTR XMLPARSE DIFFERENCE JSON_QUERY REPEAT XMLVALIDATE DLURLCOMPLETE 1 JSON_VALUE REPLACE XSLTRANSFORM DLURLPATH LOCATE_IN_STRING ROUND_TIMESTAMP 1 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.
- partition-name
- Specifies the name of the partition to drop. The partition-name must identify a data partition that exists in the table.
- DELETE ROWS
- Specifies that any data in the specified partition will be discarded. All data stored in the partition is dropped from the table without processing any delete triggers.
- PRESERVE ROWS
- Specifies that any data in the specified partition will be preserved by moving it to the remaining partitions without processing any delete or insert triggers. If the specified table is a range partitioned table, PRESERVE ROWS must not be specified. Dropping a hash partition will require data movement between the remaining data partitions.
ADD MATERIALIZED QUERY materialized-query-definition
Changes a base table to a materialized query table. If the specified table is already a materialized query table or if the table is referenced in another materialized query table, an error is returned.
- select-statement
- Defines the query on which the table is based. The columns of
the existing table must meet the following characteristics:
- The number of columns in the table must be the same as the number of result columns in the select-statement.
- The column attributes of each column of the table must be compatible to the column attributes of the corresponding result column in the select-statement.
The select-statement for a materialized query table must not contain a reference to the table being altered, a view over the table being altered, or another materialized query table. For additional details about specifying select-statement for a materialized query table, see CREATE TABLE.
- refreshable-table-options
- Specifies
the materialized query table options for altering a base table to
a materialized query table.
- DATA INITIALLY DEFERRED
- Specifies that the data in the table is not validated as part of the ALTER TABLE statement. A REFRESH TABLE statement can be used to make sure the data in the materialized query table is the same as the result of the query in which the table is based.
- DATA INITIALLY IMMEDIATE
- Specifies that the data is inserted in the table from the result of the query as part of processing the ALTER TABLE statement.
- REFRESH DEFERRED
- Specifies that the data in the table can be refreshed at any time using the REFRESH TABLE statement. The data in the table only reflects the result of the query as a snapshot at the time when the REFRESH TABLE statement is processed or when it was last updated.
- MAINTAINED BY USER
- Specifies that the materialized query table is maintained by the user. The user can use INSERT, DELETE, UPDATE, or REFRESH TABLE statements on the table.
- ENABLE QUERY OPTIMIZATION or DISABLE QUERY OPTIMIZATION
- Specifies whether this materialized query table can be used for
query optimization.
- ENABLE QUERY OPTIMIZATION
- The materialized query table can be used for query optimization.
- DISABLE QUERY OPTIMIZATION
- The materialized query table will not be used for query optimization. The table can still be queried directly.
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.
The select-statement for a materialized query table must not contain a reference to the table being altered, a view over the table being altered, or another materialized query table. For additional details about specifying select-statement for a materialized query table, see CREATE TABLE.
- refreshable-table-options
- Specifies
the materialized query table options for altering a base table to
a materialized query table.
- DATA INITIALLY DEFERRED
- Specifies that the data in the table is not refreshed or validated as part of the ALTER TABLE statement. A REFRESH TABLE statement can be used to make sure the data in the materialized query table is the same as the result of the query in which the table is based.
- DATA INITIALLY IMMEDIATE
- Specifies that the data is inserted in the table from the result of the query as part of processing the ALTER TABLE statement.
- REFRESH DEFERRED
- Specifies that the data in the table can be refreshed at any time using the REFRESH TABLE statement. The data in the table only reflects the result of the query as a snapshot at the time when the REFRESH TABLE statement is processed or when it was last updated.
- MAINTAINED BY USER
- Specifies that the materialized query table is maintained by the user. The user can use INSERT, DELETE, UPDATE, or REFRESH TABLE statements on the table.
- ENABLE QUERY OPTIMIZATION or DISABLE QUERY OPTIMIZATION
- Specifies whether this materialized query table can be used for
query optimization.
- ENABLE QUERY OPTIMIZATION
- The materialized query table can be used for query optimization.
- DISABLE QUERY OPTIMIZATION
- The materialized query table will not be used for query optimization. The table can still be queried directly.
- SET refreshable-table-alteration
- Changes how the table is maintained or whether the table can be
used in query optimization.
- MAINTAINED BY USER
- Specifies that the materialized query table is maintained by the user. The user can use INSERT, DELETE, UPDATE, or REFRESH TABLE statements on the table.
- REFRESH DEFERRED
- Specifies that the data in the table can be refreshed at any time using the REFRESH TABLE statement. The data in the table only reflects the result of the query as a snapshot at the time when the REFRESH TABLE statement is processed or when it was last updated.
- ENABLE QUERY OPTIMIZATION or DISABLE QUERY OPTIMIZATION
- Specifies whether this materialized query table can be used for
query optimization.
- ENABLE QUERY OPTIMIZATION
- The materialized query table can be used for query optimization.
- DISABLE QUERY OPTIMIZATION
- The materialized query table will not be used for query optimization. The table can still be queried directly.
DROP MATERIALIZED QUERY
Changes
a materialized query table so that it is no longer a materialized
query table. The table specified by table-name must be defined
as a materialized query table. The definition of columns 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 ignored if the table has a DATALINK column with FILE LINK CONTROL or if running with isolation level No Commit (NC).
- WITH EMPTY TABLE
- Causes
all data currently in the table to be removed. If the unit of work
in which this ALTER statement was issued is rolled back, the table
data will NOT be returned to its original state. When this action
is requested, no DELETE triggers defined on the affected table are
fired.
WITH EMPTY TABLE cannot be specified for a materialized query table or for a parent in a referential constraint.
A DELETE statement without a WHERE clause will typically perform as well or better than ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE and will allow a ROLLBACK to rollback the delete of the rows in the table.
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.
- 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 constraints
- drop materialized query table
- drop partition
- drop partitioning
- drop columns for which the RESTRICT option was specified
- alter all other column definitions
- drop columns for which the CASCADE option was specified
- alter column drop attributes (for example, DROP DEFAULT)
- alter column alter attributes
- alter column add attributes
- add columns
- alter partition
- add or alter materialized query table
- add partition or add partitioning
- add constraints
Within each of these stages, the order in which the user specifies the clauses is the order in which they are performed, with one exception. If any columns are being dropped, that operation is logically done before any column definitions are added or altered.
QTEMP considerations: Any views or logical files
in another job's QTEMP that are dependent on the table being altered
will be dropped as a result of an ALTER TABLE statement. 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.2
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.3
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 row change timestamp column: When you add a row change timestamp column to an existing table, the initial values are generated during the alter operation.
Considerations for implicitly hidden columns: A column that is defined as implicitly hidden can be explicitly referenced on the ALTER statement. For example, an implicitly hidden column can be altered, can be specified as part of a referential constraint or a check constraint, or a materialized query table definition.
Altering materialized query tables: The isolation level at the time when a base table is first altered to become a materialized query table by the ALTER TABLE statement is the isolation level for the materialized query table.
Altering a table to change it to a materialized query table with query optimization enabled makes the table eligible for use in optimization. Therefore, ensure that the data in the table is accurate. The DATA INITIALLY IMMEDIATE clause can be used to refresh the data when the table is altered.
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 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.
- 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.
- 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
- 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.
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.
| Operation | RESTRICT Effect | CASCADE Effect |
|---|---|---|
| Drop of a column referenced by a view | The drop of the column is not allowed. | The view and all views dependent on that view are dropped. |
| Drop of a column referenced by a non-view logical file | The drop is allowed, and the column
is dropped from the logical file if:
|
The drop is allowed, and the column
is dropped from the logical file if:
|
| Drop of a column referenced in 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.)
| 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.
|
| 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 CHARExample 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 NULLChange 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 CASCADEAlter 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_NONotice 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 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.
| 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';