The ALTER TABLE statement alters the definition of a table.
This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).
To add a column of type DB2SECURITYLABEL to a table, the privileges held by the authorization ID of the statement must include at least a security label from the security policy associated with the table.
To remove the security policy from a table, the privileges held by the authorization ID of the statement must include SECADM authority.
To alter a table to ACTIVATE and DEACTIVATE row and column access control, the privileges held by the authorization ID of the statement must include the SECADM authority.
>>-ALTER TABLE--table-name--------------------------------------> .-----------------------------------------------------------------------------. V (1) .-COLUMN-. | >--+-----------+-ADD--+-+--------+--| column-definition |-+--------------------------+-+-----+->< | | +-| unique-constraint |-------------+ | | | | +-| referential-constraint |--------+ | | | | +-| check-constraint |--------------+ | | | | +-| distribution-clause |-----------+ | | | | '-RESTRICT ON DROP------------------' | | | | .-MATERIALIZED-. | | | | .-+--------------+--QUERY-. | | | +-ADD--+-------------------------+--| materialized-query-definition |-+ | | +-ALTER--+-FOREIGN KEY-+--constraint-name--| constraint-alteration |--+ | | | '-CHECK-------' | | | | .-COLUMN-. | | | +-ALTER--+--------+--| column-alteration |----------------------------+ | | | (2) | | | +-+-ACTIVATE---+--ROW ACCESS CONTROL----------------------------------+ | | | '-DEACTIVATE-' | | | | (3) | | | +-+-ACTIVATE---+--COLUMN ACCESS CONTROL-------------------------------+ | | | '-DEACTIVATE-' | | | +-RENAME COLUMN--source-column-name--TO--target-column-name-----------+ | | +-DROP--+-PRIMARY KEY---------------------------+---------------------+ | | | +-+-FOREIGN KEY-+--constraint-name------+ | | | | | +-UNIQUE------+ | | | | | | +-CHECK-------+ | | | | | | '-CONSTRAINT--' | | | | | | .-COLUMN-. .-CASCADE--. | | | | | +-+--------+--column-name--+----------+-+ | | | | | '-RESTRICT-' | | | | | '-RESTRICT ON DROP----------------------' | | | +-DROP DISTRIBUTION---------------------------------------------------+ | | | .-MATERIALIZED-. | | | +-DROP--+--------------+--QUERY---------------------------------------+ | | +-ADD PERIOD--| period-definition |-----------------------------------+ | | +-DROP PERIOD--period-name--------------------------------------------+ | | +-DATA CAPTURE--+-NONE---------------------------------+--------------+ | | | '-CHANGES--+-------------------------+-' | | | | '-INCLUDE LONGVAR COLUMNS-' | | | +-ACTIVATE NOT LOGGED INITIALLY--+------------------+-----------------+ | | | '-WITH EMPTY TABLE-' | | | +-PCTFREE--integer----------------------------------------------------+ | | +-LOCKSIZE--+-ROW---------+-------------------------------------------+ | | | +-BLOCKINSERT-+ | | | | '-TABLE-------' | | | +-APPEND--+-ON--+-----------------------------------------------------+ | | | '-OFF-' | | | | .-CARDINALITY-. | | | +-+-VOLATILE-----+--+-------------+-----------------------------------+ | | | '-NOT VOLATILE-' | | | | .-ADAPTIVE-. | | | +-COMPRESS--+-YES-+----------+-+--------------------------------------+ | | | | '-STATIC---' | | | | | '-NO---------------' | | | +-+-ACTIVATE---+--VALUE COMPRESSION-----------------------------------+ | | | '-DEACTIVATE-' | | | '-LOG INDEX BUILD--+-NULL-+-------------------------------------------' | | +-OFF--+ | | '-ON---' | +-ADD PARTITION--| add-partition |--------------------------------------------------------+ +-ATTACH PARTITION--| attach-partition |--------------------------------------------------+ +-DETACH PARTITION--partition-name--INTO--table-name1-------------------------------------+ +-ADD SECURITY POLICY--policy-name--------------------------------------------------------+ +-DROP SECURITY POLICY--------------------------------------------------------------------+ +-ADD VERSIONING--USE HISTORY TABLE--history-table-name-----------------------------------+ '-DROP VERSIONING-------------------------------------------------------------------------' add-partition |--+----------------+--| boundary-spec |--+---------------------+--> '-partition-name-' '-IN--tablespace-name-' >--+---------------------------------------------------------+--| '-INDEX IN--tablespace-name--+--------------------------+-' '-LONG IN--tablespace-name-' boundary-spec |--+-| starting-clause |--| ending-clause |-+-------------------| '-| ending-clause |----------------------' starting-clause .-,------------. .-FROM-. V | |--STARTING--+------+--+-(----+-constant-+-+--)-+---------------> | +-MINVALUE-+ | | '-MAXVALUE-' | '-+-constant-+-----------' +-MINVALUE-+ '-MAXVALUE-' .-INCLUSIVE-. >--+-----------+------------------------------------------------| '-EXCLUSIVE-' ending-clause .-,------------. .-AT-. V | .-INCLUSIVE-. |--ENDING--+----+--+-(----+-constant-+-+--)-+--+-----------+----| | +-MINVALUE-+ | '-EXCLUSIVE-' | '-MAXVALUE-' | '-+-constant-+-----------' +-MINVALUE-+ '-MAXVALUE-' attach-partition |--+----------------+--| boundary-spec |--FROM--table-name------> '-partition-name-' .-BUILD MISSING INDEXES----. >--+--------------------------+---------------------------------| '-REQUIRE MATCHING INDEXES-' column-definition |--column-name--+-------------------+--+--------------------+---| | (4) | '-| column-options |-' '-| data-type |-----' column-options .--------------------------------------------------------------------------------------------------. V | |----+----------------------------------------------------------------------------------------------+-+--| +-NOT NULL-------------------------------------------------------------------------------------+ | (5) | +-| lob-options |------------------------------------------------------------------------------+ | (6) | +-SCOPE--+-typed-table-name2-+-----------------------------------------------------------------+ | '-typed-view-name2--' | +-+-----------------------------+--+-+-UNIQUE------+-----------------------------------------+-+ | '-CONSTRAINT--constraint-name-' | '-PRIMARY KEY-' | | | +-| references-clause |-----------------------------------+ | | '-CHECK--(--check-condition--)--| constraint-attributes |-' | | (7) | +-----+-| default-clause |---+-----------------------------------------------------------------+ | '-| generated-clause |-' | +-COMPRESS SYSTEM DEFAULT----------------------------------------------------------------------+ | .-COLUMN-. | +-+--------+--SECURED WITH--security-label-name------------------------------------------------+ | .-NOT HIDDEN--------. | '-+-IMPLICITLY HIDDEN-+------------------------------------------------------------------------' lob-options .-LOGGED-----. .-NOT COMPACT-. |--●--+------------+--●--+-------------+--●---------------------| '-NOT LOGGED-' '-COMPACT-----' references-clause |--REFERENCES--+-table-name-+--+-----------------------+--------> '-nickname---' | .-,-----------. | | V | | '-(----column-name-+--)-' >--| rule-clause |--| constraint-attributes |-------------------| rule-clause .-ON DELETE NO ACTION-----. .-ON UPDATE NO ACTION-. |--●--+-------------------------+--●--+---------------------+--●--| '-ON DELETE--+-RESTRICT-+-' '-ON UPDATE RESTRICT--' +-CASCADE--+ '-SET NULL-' constraint-attributes .-ENFORCED----------------------. |--●--+-------------------------------+--●----------------------> | .-TRUSTED-----. | '-NOT ENFORCED--+-------------+-' '-NOT TRUSTED-' .-ENABLE QUERY OPTIMIZATION--. >--+----------------------------+--●----------------------------| '-DISABLE QUERY OPTIMIZATION-' default-clause .-WITH-. |--+------+--DEFAULT--+----------------------------------------------------+--| +-constant-------------------------------------------+ +-datetime-special-register--------------------------+ +-user-special-register------------------------------+ +-CURRENT SCHEMA-------------------------------------+ +-CURRENT MEMBER-------------------------------------+ +-NULL-----------------------------------------------+ +-cast-function--(--+-constant------------------+--)-+ | +-datetime-special-register-+ | | +-user-special-register-----+ | | '-CURRENT SCHEMA------------' | +-EMPTY_CLOB()---------------------------------------+ +-EMPTY_DBCLOB()-------------------------------------+ '-EMPTY_BLOB()---------------------------------------' generated-clause .-ALWAYS-----. |--+-GENERATED--+------------+--| as-row-change-timestamp-clause |------+--| | '-BY DEFAULT-' | | .-ALWAYS-. | '-GENERATED--+--------+--+-| as-generated-expression-clause |------+-' +-| as-row-transaction-timestamp-clause |-+ '-| as-row-transaction-start-id-clause |--' as-row-change-timestamp-clause (8) |--------FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP---------| as-generated-expression-clause |--AS--(--generation-expression--)------------------------------| as-row-transaction-timestamp-clause |--AS--ROW--+-BEGIN-+-------------------------------------------| '-END---' as-row-transaction-start-id-clause |--AS--TRANSACTION START ID-------------------------------------| unique-constraint |--+-----------------------------+--+-UNIQUE------+-------------> '-CONSTRAINT--constraint-name-' '-PRIMARY KEY-' .-,---------------. V | >--(------column-name---+--+------------------------------------+--)--| '-,--BUSINESS_TIME--WITHOUT OVERLAPS-' referential-constraint |--+-----------------------------+------------------------------> '-CONSTRAINT--constraint-name-' .-,-----------. V | >--FOREIGN KEY--(----column-name-+--)--| references-clause |----| check-constraint |--+-----------------------------+------------------------------> '-CONSTRAINT--constraint-name-' >--CHECK--(--| check-condition |--)-----------------------------> >--| constraint-attributes |------------------------------------| check-condition |--+-search-condition----------+--------------------------------| '-| functional-dependency |-' functional-dependency |--+-column-name-----------+--DETERMINED BY--+-column-name-----------+--| | .-,-----------. | | .-,-----------. | | V | | | V | | '-(----column-name-+--)-' '-(----column-name-+--)-' distribution-clause .-,-----------. .-HASH-. V | |--DISTRIBUTE BY--+------+--(----column-name-+--)---------------| materialized-query-definition |--(--fullselect--)--| refreshable-table-options |--------------| refreshable-table-options |--●--DATA INITIALLY DEFERRED--●--REFRESH--+-DEFERRED--+--●-----> '-IMMEDIATE-' .-ENABLE QUERY OPTIMIZATION--. >--+----------------------------+--●----------------------------> '-DISABLE QUERY OPTIMIZATION-' .-MAINTAINED BY SYSTEM--------------. >--+-----------------------------------+--●---------------------| '-MAINTAINED BY--+-USER-----------+-' '-FEDERATED_TOOL-' constraint-alteration .-------------------------------------------. V (9) | |--------+-+-ENABLE--+--QUERY OPTIMIZATION---+-+----------------| | '-DISABLE-' | '-+-ENFORCED----------------------+-' | .-TRUSTED-----. | '-NOT ENFORCED--+-------------+-' '-NOT TRUSTED-' column-alteration |--column-name--+-SET--+-DATA TYPE--| altered-data-type |---------------+----------------+--| | +-NOT NULL---------------------------------------+ | | +-INLINE LENGTH--integer-------------------------+ | | +-| default-clause |-----------------------------+ | | +-EXPRESSION--| as-generated-expression-clause |-+ | | '-+-NOT HIDDEN--------+--------------------------' | | '-IMPLICITLY HIDDEN-' | +-SET--| generation-alteration |-----------------------------------------+ +-+--------------------------------+--| identity-alteration |------------+ | '-SET--| generation-alteration |-' | +-SET--| generation-attribute |--| as-identity-clause |------------------+ | .-ALWAYS-. | +-SET GENERATED--+--------+--+-| as-generated-expression-clause |------+-+ | +-| as-row-transacton-start-id-clause |---+ | | '-| as-row-transaction-timestamp-clause |-' | +-DROP--+-DEFAULT---+----------------------------------------------------+ | +-GENERATED-+ | | '-NOT NULL--' | +-ADD SCOPE--+-typed-table-name-+----------------------------------------+ | '-typed-view-name--' | +-COMPRESS--+-SYSTEM DEFAULT-+-------------------------------------------+ | '-OFF------------' | +-SECURED WITH--security-label-name--------------------------------------+ '-DROP COLUMN SECURITY---------------------------------------------------' altered-data-type |--+-| built-in-type |-------+----------------------------------| | (10) | '-distinct-type-name------' built-in-type |--+-+-+-INTEGER-+-+-------------------------------------------------+--| | | '-INT-----' | | | '-BIGINT------' | | .-(5,0)-------------------. | +-+-+-DECIMAL-+-+--+-------------------------+--------------------+ | | '-DEC-----' | | .-,0-------. | | | '-+-NUMERIC-+-' '-(integer-+----------+-)-' | | '-NUM-----' '-,integer-' | | .-(53)------. | +-+-FLOAT--+-----------+--+---------------------------------------+ | | '-(integer)-' | | | +-REAL------------------+ | | | .-PRECISION-. | | | '-DOUBLE--+-----------+-' | | .-(34)-. | +-DECFLOAT--+------+----------------------------------------------+ | '-(16)-' | | .-(1)-------. | +-+-+-+-CHARACTER-+--+-----------+----------+--+--------------+-+-+ | | | '-CHAR------' '-(integer)-' | '-FOR BIT DATA-' | | | | '-+-VARCHAR----------------+--(integer)-' | | | | '-+-CHARACTER-+--VARYING-' | | | | '-CHAR------' | | | | .-(1M)-------------. | | | '-+-CLOB------------------------+--+------------------+-------' | | '-+-CHARACTER-+--LARGE OBJECT-' '-(integer-+---+-)-' | | '-CHAR------' +-K-+ | | +-M-+ | | '-G-' | | .-(1)-------. | +-+-GRAPHIC--+-----------+-------+--------------------------------+ | | '-(integer)-' | | | +-VARGRAPHIC--(integer)--------+ | | | .-(1M)-------------. | | | '-DBCLOB--+------------------+-' | | '-(integer-+---+-)-' | | +-K-+ | | +-M-+ | | '-G-' | | .-(1M)-------------. | '-+-BLOB----------------+--+------------------+-------------------' '-BINARY LARGE OBJECT-' '-(integer-+---+-)-' +-K-+ +-M-+ '-G-' as-identity-clause |--AS IDENTITY--------------------------------------------------> >--+------------------------------------------------------+-----| | .--------------------------------------------. | | V (9) .-1----------------. | | '-(--------+-START WITH--+-numeric-constant-+---+-+--)-' | .-1----------------. | +-INCREMENT BY--+-numeric-constant-+-+ | .-NO MINVALUE----------------. | +-+-MINVALUE--numeric-constant-+-----+ | .-NO MAXVALUE----------------. | +-+-MAXVALUE--numeric-constant-+-----+ | .-NO CYCLE-. | +-+-CYCLE----+-----------------------+ | .-CACHE 20----------------. | '-+-NO CACHE----------------+--------' '-CACHE--integer-constant-' generation-alteration |--SET GENERATED--+-ALWAYS-----+--------------------------------| '-BY DEFAULT-' identity-alteration .---------------------------------------------. V (9) | |--------+-SET INCREMENT BY--numeric-constant--+-+--------------| +-SET--+-NO MINVALUE----------------+-+ | '-MINVALUE--numeric-constant-' | +-SET--+-NO MAXVALUE----------------+-+ | '-MAXVALUE--numeric-constant-' | +-SET--+-NO CYCLE-+-------------------+ | '-CYCLE----' | +-SET--+-NO CACHE----------------+----+ | '-CACHE--integer-constant-' | +-SET--+-NO ORDER-+-------------------+ | '-ORDER----' | '-RESTART--+------------------------+-' '-WITH--numeric-constant-' generation-attribute .-ALWAYS-----. |--GENERATED--+------------+------------------------------------| '-BY DEFAULT-' period-definition |--+-SYSTEM_TIME---+--(--begin-column-name--,--end-column-name--)--| '-BUSINESS_TIME-'
If table-name identifies a materialized query table, alterations are limited to adding or dropping the materialized query, invoking the ACTIVATING NOT LOGGED INITIALLY clause, adding or dropping RESTRICT ON DROP, modifying data capture, pctfree, locksize, append, volatile, data row compression, value compression, and activating or deactivating row and column access control.
If table-name identifies a range-clustered table, alterations are limited to adding, changing, or dropping constraints, activating not logged initially, adding or dropping RESTRICT ON DROP, changing locksize, data capture, or volatile, and setting column default values.
Adding the new column must not make the total byte count of all columns exceed the maximum record size.
If the table is a system-period temporal table, the column is added to the associated history table as well.
If the added column is a generated column that is based on an expression, the expression must not reference a column for which a column mask is defined (SQLSTATE 42621).
If a column is added to a table on which a mask or a permission is defined, or to a table that is referenced in the definition of a mask or a permission, that mask or permission is invalidated. Access to a table that has column access control activated and an invalid mask defined on it is blocked until the invalid mask is either disabled, dropped, or recreated (SQLSTATE 560D0). Access to a table that has row access control activated and an invalid row permission defined on it is blocked until the invalid permission is either disabled, dropped, or recreated (SQLSTATE 560D0).
If the constraint name is not specified by the user, an 18 byte long identifier unique within the identifiers of the existing constraints defined on the table is generated by the system. (The identifier consists of "SQL" followed by a sequence of 15 numeric characters that are generated by a timestamp-based function.)
When used with a PRIMARY KEY or UNIQUE constraint, the constraint-name may be used as the name of an index that is created to support the constraint. See Notes for details on index names associated with unique constraints.
See PRIMARY KEY within the unique-constraint description.
See UNIQUE within the unique-constraint description.
See references-clause in "CREATE TABLE".
If a column is defined using a distinct type, then the default value of the column is the default value of the source data type cast to the distinct type.
Data Type | Default Value |
---|---|
Numeric | 0 |
Fixed-length character string | Blanks |
Varying-length character string | A string of length 0 |
Fixed-length graphic string | Double-byte blanks |
Varying-length graphic string | A string of length 0 |
Date | For existing rows, a date corresponding to January 1, 0001. For added rows, the current date. |
Time | For existing rows, a time corresponding to 0 hours, 0 minutes, and 0 seconds. For added rows, the current time. |
Timestamp | For existing rows, a date corresponding to January 1, 0001, and a time corresponding to 0 hours, 0 minutes, 0 seconds and 0 microseconds. For added rows, the current timestamp. |
Binary string (blob) | A string of length 0 |
Omission of DEFAULT from a column-definition results in the use of the null value as the default for the column.
Specific types of values that can be specified with the DEFAULT keyword are as follows.
If the value specified is not valid, an error (SQLSTATE 42894) is returned.
Specifies that the database manager generates values for the column. GENERATED must be specified if the column is to be considered an identity column, row change timestamp column, row-begin column, row-end column, transaction start-ID column, or generated expression column.
A table can only have one ROW CHANGE TIMESTAMP column (SQLSTATE 428C1). If data-type is specified, it must be TIMESTAMP or TIMESTAMP(6) (SQLSTATE 42842). A ROW CHANGE TIMESTAMP column cannot have a DEFAULT clause (SQLSTATE 42623). NOT NULL must be specified for a ROW CHANGE TIMESTAMP column (SQLSTATE 42831).
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 (SQLSTATE 01695). This can happen when a conflicting transaction is updating the same row in the system-period temporal table. The database configuration parameter systime_period_adj must be set to Yes for this adjustment to the timestamp value to occur otherwise an error is returned (SQLSTATE 57062). If multiple rows are inserted or updated within a single SQL transaction and an adjustment is not needed, the values for the row-begin column are the same for all the rows and are unique from the values generated for the column for another transaction. A row-begin column is required as the begin column of a SYSTEM_TIME period, which is the intended use for this type of generated column.
A table can have only one row-begin column (SQLSTATE 428C1). If data-type is not specified the column is defined as a TIMESTAMP(12). If data-type is specified, it must be TIMESTAMP(12) (SQLSTATE 42842). The column must be defined as NOT NULL (SQLSTATE 42831). A row-begin column is not updatable.
Specifies that a value for the data type of the column is assigned by the database manager whenever a row is inserted or any column in the row is updated. The assigned value is TIMESTAMP ’9999-12-30-00.00.00.000000000000’.
A row-end column is required as the second column of a SYSTEM_TIME period, which is the intended use for this type of generated column.
A table can have only one row-end column (SQLSTATE 428C1). If data-type is not specified, the column is defined as TIMESTAMP(12). If data-type is specified, it must be TIMESTAMP(12) (SQLSTATE 42842). The column must be defined as NOT NULL (SQLSTATE 42831). A row-end column is not updatable.
A transaction start-ID column is required for a system-period temporal table, which is the intended use for this type of generated column.
A table can have only one transaction start-ID column (SQLSTATE 428C1). If data-type is not specified, the column is defined as TIMESTAMP(12). If data-type is specified it must be TIMESTAMP(12). A transaction start-ID column is not updatable.
Allowing system default values to be stored in this manner causes a slight performance penalty during insert and update operations on the column because of extra checking that is done.
The base data type must not be a DATE, TIME, TIMESTAMP, XML, or structured data type (SQLSTATE 42842). If the base data type is a varying-length string, this clause is ignored. String values of length 0 are automatically compressed if a table has been set with VALUE COMPRESSION.
A check is performed to determine whether an existing index matches the unique key definition (ignoring any INCLUDE columns in the index). An index definition matches if it identifies the same set of columns without regard to the order of the columns or the direction (ASC/DESC) specifications. However, for partitioned tables, non-unique partitioned indexes whose columns are not a superset of the table-partitioning key columns are not considered matching indexes.
When a partition is attached to a range partitioned application-period temporal table that has a partitioned BUSINESS_TIME WITHOUT OVERLAPS index, the source table must have an index that matches the partitioned BUSINESS_TIME WITHOUT OVERLAPS index. Additionally, the PERIODNAME and PERIODPOLICY attributes on the indexes must also match.
BUSINESS_TIME WITHOUT OVERLAPS specifies that overlapping periods for BUSINESS_TIME are not allowed, and that values for the rest of the keys must be unique with respect to any period of BUSINESS_TIME. When BUSINESS_TIME WITHOUT OVERLAPS is specified, the end column and begin column of the period BUSINESS_TIME (in this order of the columns) will automatically be added to the index key in ascending order and enforce that there are no overlaps in time. The columns used to defined BUSINESS_TIME must not be specified as part of the constraint (SQLSTATE 428HW).
A check is performed to determine if an existing index matches the primary key definition (ignoring any INCLUDE columns in the index). An index definition matches if it identifies the same set of columns without regard to the order of the columns or the direction (ASC/DESC) specifications. However, for partitioned tables, non-unique partitioned indexes whose columns are not a superset of the table-partitioning key columns are not considered matching indexes.
When a partition is attached to a range partitioned application-period temporal table that has a partitioned BUSINESS_TIME WITHOUT OVERLAPS index, the source table must have an index that matches the partitioned BUSINESS_TIME WITHOUT OVERLAPS index. Additionally, the PERIODNAME and PERIODPOLICY attributes on the indexes must also match.
Only one primary key can be defined on a table.
BUSINESS_TIME WITHOUT OVERLAPS specifies that overlapping periods for BUSINESS_TIME are not allowed, and that values for the rest of the keys must be unique with respect to any period of BUSINESS_TIME. When BUSINESS_TIME WITHOUT OVERLAPS is specified, the end column and begin column of the period BUSINESS_TIME (in this order of the columns) will automatically be added to the index key in ascending order and enforce that there are no overlaps in time. The columns used to defined BUSINESS_TIME must not be specified as part of the constraint (SQLSTATE 428HW).
If row level or column level access control is activated for any table that is directly or indirectly referenced in the fullselect of materizalized-query-definition, and row level access control is not activated for the table being altered, row level access control is implicitly activated for the altered table. This restricts direct access to the contents of the materialized query table. A query that explicitly references the table before such a row permission is defined returns a warning that there is no data in the table (SQLSTATE 02000). To provide access to the materialized query table, an appropriate row permission can be created, or an ALTER TABLE DEACTIVATE ROW ACCESS CONTROL on the materialized query table can be issued to remove the row level protection if that is appropriate.
If the materialized query table references any table that has row level or column level access control activated, the functions referenced in the fullselect of materizalized-query-definition must be defined with the SECURED attribute (SQLSTATE 428EC).
If the table being altered to a materialized query table has any permissions (excluding the system generated default permission) or masks defined on it, ALTER fails (SQLSTATE 428EW).
as the result columns of fullselect (SQLSTATE 428EW). For details about specifying the fullselect for a materialized query table, see "CREATE TABLE". One additional restriction is that table-name cannot be directly or indirectly referenced in the fullselect.
Altering a string data type that results in the truncation of non-blank characters from existing data is not allowed (SQLSTATE 42837).
The administrative routine SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS can be called to do table reorganization as required. A data type alteration that requires a table reorganization cannot be specified if the table is in SET INTEGRITY PENDING state (SQLSTATE 57007).
A string data type cannot be altered if the column is a column of a table-partitioning key.
The specified length cannot be less than the existing length if the data type is a LOB (SQLSTATE 42837).
The data type of an identity column cannot be altered (SQLSTATE 42997).
The data type of a column defined as ROW BEGIN, ROW END, or TRANSACTION START ID cannot be altered (SQLSTATE 428FR).
The data type and nullability of BUSINESS_TIME period columns cannot be altered (SQLSTATE 428FR).
The table cannot have data capture enabled (SQLSTATE 42997).
Altering a column must not make the total byte count of all columns exceed the maximum record size (SQLSTATE 54010). If the column is used in a unique constraint or an index, the new length must not cause the sum of the stored lengths for the unique constraint or index to exceed the index key length limit for the page size (SQLSTATE 54008). For column stored lengths, see "Byte Counts" in "CREATE TABLE". For key length limits, see "SQL and XML limits".
If auto_reval is set to DISABLED, the cascaded effects of altering a column is shown in Table 2.
If either a row permission or a column mask is dependent on the column being altered (as recorded in the SYSCAT.CONTROLDEP catalog view), an error is returned (SQLSTATE 42917).
Operation | Effect |
---|---|
Altering a column that is referenced by a view or check constraint | The object is regenerated during alter processing. In the case of a view, function or method resolution for the object might be different after the alter operation, changing the semantics of the object. In the case of a check constraint, if the semantics of the object will change as a result of the alter operation, the operation fails. |
Altering a column in a table that has a dependent package, trigger, or SQL routine | The object is marked invalid, and is revalidated on next use. |
Altering the type of a column in a table that is referenced by an XSROBJECT enabled for decomposition | The XSROBJECT is marked inoperative for decomposition. Re-enabling the XSROBJECT might require readjustment of its mappings; following this, issue an ALTER XSROBJECT ENABLE DECOMPOSITION statement against the XSROBJECT. |
Altering a column that is referenced in the default expression of a global variable | The default expression of the global variable is validated during alter processing. If a user-defined function used in the default expression cannot be resolved, the operation fails. |
If the table is a system-period temporal table, the column is also changed in any associated history table. If the table is a system-period temporal table, string data type columns cannot be altered to a length that requires data truncation, and numeric data type columns cannot be altered to lower precision data types (SQLSTATE 42837).
If a row permission or column mask exists, which depends on the column to be altered, an error will be issued (SQLSTATE 42917).
If the table is a system-period temporal table, the column is also changed in any associated history table.
The data type of column-name must be a structured type, XML, or LOB data type (SQLSTATE 42842).
The default inline length for a structured type column is the inline length of its data type (specified explicitly or by default in the CREATE TYPE statement). If the inline length of a structured type is less than 292, the value 292 is used for the inline length of the column.
The explicit inline length value can only be increased (SQLSTATE 429B2); it cannot exceed 32673 (SQLSTATE 54010). For a structured type or XML data type column, it must be at least 292. For a LOB data type column, the INLINE LENGTH must not be less than the maximum LOB descriptor size.
Altering the column must not make the total byte count of all columns exceed the row size limit (SQLSTATE 54010).
UPDATE table-name SET lob-column = lob-column
WHERE LENGTH(lob-column) <= chosen-inline-length - 4
where table-name is the table that had the inline length of the LOB data type column
altered, lob-column is the LOB data type column
that was altered, and chosen-inline-length is the
new value that was chosen for the INLINE LENGTH.If a row permission or column mask exists, which depends on the column to be altered, an error will be returned (SQLSTATE 42917).
If the table is a system-period temporal table, inline length changes are propagated to the history table.
The generation-expression must not reference a column for which a column mask is defined (SQLSTATE 42621).
If the table is a system-period temporal table, the column is also changed in any associated history table.
IMPLICITLY HIDDEN must not be specified for the last column of the table that is not hidden (SQLSTATE 428GU).
This value can be any positive or negative value that could be assigned to this column (SQLSTATE 42815), and does not exceed the value of a large integer constant (SQLSTATE 42820), without nonzero digits existing to the right of the decimal point (SQLSTATE 428FA).
If this value is negative, this is a descending sequence after the ALTER statement. If this value is 0 or positive, this is an ascending sequence after the ALTER statement.
When CYCLE is in effect, duplicate values can be generated for an identity column. Although not required, if unique values are desired, a single-column unique index defined using the identity column will ensure uniqueness. If a unique index exists on such an identity column and a non-unique value is generated, an error occurs (SQLSTATE 23505).
When this option is specified, the values of the identity column are not stored in the cache. In this case, every request for a new identity value results in synchronous I/O to the log.
If a new value is needed for the identity column and there are no unused values available in the cache, the allocation of the value requires waiting for I/O to the log. However, when a new value is needed for the identity column and there is an unused value in the cache, the allocation of that identity value can happen more quickly by avoiding the I/O to the log.
In the event of a database deactivation, either normally or due to a system failure, all cached sequence values that have not been used in committed statements are lost (that is, they will never be used). The value specified for the CACHE option is the maximum number of values for the identity column that could be lost in case of system failure.
The minimum value is 2 (SQLSTATE 42815).
In a DB2 pureScale environment, if both CACHE and ORDER are specified, the specification of ORDER overrides the specification of CACHE and instead NO CACHE will be in effect.
The column must exist in the specified table (SQLSTATE 42703), and must already be defined with the IDENTITY attribute (SQLSTATE 42837). RESTART does not change the original START WITH value.
The numeric-constant is an exact numeric constant that can be any positive or negative value that could be assigned to this column (SQLSTATE 42815), without nonzero digits existing to the right of the decimal point (SQLSTATE 428FA). The numeric-constant will be used as the next value for the column.
The generation-expression must not reference a column for which a column mask is defined (SQLSTATE 42621).
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 (SQLSTATE 01695). This can happen when a conflicting transaction is updating the same row in the system-period temporal table. The database configuration parameter systime_period_adj must be set to Yes for this adjustment to the timestamp value to occur otherwise an error is returned (SQLSTATE 57062). If multiple rows are inserted or updated within a single SQL transaction and an adjustment is not needed, the values for the row-begin column are the same for all the rows and are unique from the values generated for the column for another transaction. A row-begin column is required as the begin column of a SYSTEM_TIME period, which is the intended use for this type of generated column.
A table can have only one row-begin column (SQLSTATE 428C1). If data-type is not specified the column is defined as a TIMESTAMP(12). If data-type is specified, it must be TIMESTAMP(12) (SQLSTATE 42842). The column must be defined as NOT NULL (SQLSTATE 42831). A row-begin column is not updatable.
Specifies that the maximum value for the data type of the column is assigned by the database manager whenever a row is inserted or any column in the row is updated.
A row-end column is required as the second column of a SYSTEM_TIME period, which is the intended use for this type of generated column.
A table can have only one row-end column (SQLSTATE 428C1). If data-type is not specified, the column is defined as TIMESTAMP(12). If data-type is specified, it must be TIMESTAMP(12) (SQLSTATE 42842). The column must be defined as NOT NULL (SQLSTATE 42831). A row-end column is not updatable.
A transaction start-ID column is required for a system-period temporal table, which is the intended use for this type of generated column.
A table can have only one transaction start-ID column (SQLSTATE 428C1). If data-type is not specified, the column is defined as TIMESTAMP(12). If data-type is specified it must be TIMESTAMP(12). A transaction start-ID column is not updatable.
If the table is a system-period temporal table, the NOT NULL attribute is also dropped from the corresponding column in any associated history table.
If either a row permission or column mask exists, which depends on the column to be altered, an error will be issued (SQLSTATE 42917).
Allowing system default values to be stored in this manner causes a slight performance penalty during insert and update operations on the column because of the extra checking that is done.
Existing data in the column is not changed. Consider offline table reorganization to enable existing data to take advantage of storing system default values using minimal space.
The base data type must not be DATE, TIME or TIMESTAMP (SQLSTATE 42842). If the base data type is a varying-length string, this clause is ignored. String values of length 0 are automatically compressed if a table has been set with VALUE COMPRESSION.
If the table being altered is a typed table, the column must not be inherited from a supertable (SQLSTATE 428DJ).
Activates row level access control on the table. The table must not be a typed table, a catalog table (SQLSTATE 55019), a created temporary table, a declared temporary table (SQLSTATE 42995), a nickname (SQLSTATE 42809), or a view (SQLSTATE 42809).
A default row permission is implicitly created and allows no access to any rows of the table, unless permitted by a row permission explicitly created by a user with SECADM authority.
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 implicitly by the DB2 database to control the set of rows in the table that are accessible.
If a trigger exists for the table, the trigger must be defined with the SECURED attribute (SQLSTATE 55019).
The table must not be referenced in the definition of a view if an INSTEAD OF trigger that is defined with the NOT SECURED attribute exists for the view (SQLSTATE 55019).
If a materialized query table references the table, the functions referenced in the fullselect of materizalized-query-definition must be defined with the SECURED attribute (SQLSTATE 55019).
If a materialized query table (or a staging table) that depends on the table (directly or indirectly through a view) for which row level access control is being activated and that materialized query table (or a staging table) does not already have row level access control activated, row level access control is implicitly activated for the materialized query table (or a staging table). This restricts direct access to the contents of the materialized query table (or a staging table). A query that explicitly references the table before such a row permission is defined will return a warning that there is no data in the table (SQLSTATE 02000). To provide access to the materialized query table (or a staging table), an appropriate row permission can be created, or an ALTER TABLE DEACTIVATE ROW ACCESS CONTROL statement on the materialized query table (or a staging table) can be issued to remove the row level protection if that is appropriate.
ACTIVATE ROW ACCESS CONTROL is ignored if row access control is already defined as activated for the table.
If the table is a system-period temporal table, the database manager automatically activates row access control on the history table and creates a default row permission for the history table.
Activates column level access control on the table. The table must not be a typed table, a catalog table (SQLSTATE 55019), a created temporary table, a declared temporary table (SQLSTATE 42995), a nickname (SQLSTATE 42809) or a view (SQLSTATE 42809).
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 implicitly by the database manager to mask the values returned for the columns referenced in the final result table of the queries.
If a trigger exists for the table, the trigger must be defined with the SECURED attribute (SQLSTATE 55019).
If a materialized query table references the table, the functions referenced in the fullselect of materizalized-query-definition must be defined with the SECURED attribute (SQLSTATE 55019).
The table must not be referenced in the definition of a view if an INSTEAD OF trigger that is defined with the NOT SECURED attribute exists for the view (SQLSTATE 55019). If a materialized query table that depends on the table (directly or indirectly through a view) for which column level access control is being activated and that materialized query table 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 returns a warning that there is no data in the table (SQLSTATE 02000). To provide access to the materialized query table, an appropriate row permission can be created, or an ALTER TABLE DEACTIVATE ROW ACCESS CONTROL statement on the materialized query table can be issued to remove the row level protection if that is appropriate.
ACTIVATE COLUMN ACCESS CONTROL is ignored if column level access control is already defined as activated for the table.
If the table is a system-period temporal table, the database manager automatically activates row access control on the history table and creates a default row permission for the history table.
Deactivates row level access control on the table. When the table is referenced in a data manipulation statement, any existing enabled row permissions defined on the table are not applied by the database manager to control the set of rows in the table that are accessible.
DEACTIVATE ROW ACCESS CONTROL is ignored if row access control is not activated for the table.
Deactivates column level access control on the table. When the table is referenced in a data manipulation statement, any existing enabled column masks defined on the table are not applied by the database manager to control the values returned for the columns referenced in the final result table of the queries.
DEACTIVATE COLUMN ACCESS CONTROL is ignored if column access control is not activated for the table.
The table must not be defined as a history table (SQLSTATE 42986). If the table is a system-period temporal table, the column is also renamed in any associated history table.
DROP COLUMN must not drop a column that is referenced in the definition of a row permission or a column mask (SQLSTATE 42917). However, a column for which a column mask is defined can be dropped. When the column is dropped, any column mask defined on that column is also dropped.
Operation | RESTRICT Effect | CASCADE Effect |
---|---|---|
Dropping a column that is referenced by a view or a trigger | Dropping the column is not allowed. | The object and all objects that are dependent on that object are dropped. |
Dropping a column that is referenced in the key of an index | If all columns that are referenced in the index are dropped in the same ALTER TABLE statement, dropping the index is allowed. Otherwise, dropping the column is not allowed. | The index is dropped. |
Dropping a column that is referenced in a unique constraint | If all columns that are referenced in the unique constraint are dropped in the same ALTER TABLE statement, and the unique constraint is not referenced by a referential constraint, the columns and the constraint are dropped. (The index that is used to satisfy the constraint is also dropped.) Otherwise, dropping the column is not allowed. | The unique constraint and any referential constraints that reference that unique constraint are dropped. (Any indexes that are used by those constraints are also dropped). |
Dropping a column that is referenced in a referential constraint | If all columns that are referenced in the referential constraint are dropped in the same ALTER TABLE statement, the columns and the constraint are dropped. Otherwise, dropping the column is not allowed. | The referential constraint is dropped. |
Dropping a column that is referenced by a system-generated column that is not being dropped. | Dropping the column is not allowed. | Dropping the column is not allowed. |
Dropping a column that is referenced in a check constraint | Dropping the column is not allowed. | The check constraint is dropped. |
Dropping a column that is referenced in a decomposition-enabled XSROBJECT | Dropping the column is not allowed. | The XSROBJECT is marked inoperative for decomposition. Re-enabling the XSROBJECT might require readjustment of its mappings; following this, issue an ALTER XSROBJECT ENABLE DECOMPOSITION statement against the XSROBJECT. |
Dropping a column that is referenced in the default expression of a global variable | Dropping the column is not allowed. | The global variable is dropped, unless the dropping of the global variable is disallowed because there are other objects, which do not allow the cascade, that depend on the global variable. |
If row level access control or column level access control is in effect for the table, this control remains after the table is no longer a materialized query table.
Defines a system period with the name SYSTEM_TIME. There must not be a column in the table with the name SYSTEM_TIME (SQLSTATE 42711). A table can have only one SYSTEM_TIME period (SQLSTATE 42711). begin-column-name must be defined as ROW BEGIN and end-column-name must be defined as ROW END (SQLSTATE 428HN).
Defines an application period with the name BUSINESS_TIME. There must not be a column in the table with the name BUSINESS_TIME (SQLSTATE 42711). A table can have only one BUSINESS_TIME period (SQLSTATE 42711). begin-column-name and end-column-name must both be defined as DATE or TIMESTAMP(p) where p is from 0 to 12 (SQLSTATE 42842), and the columns must be defined as NOT NULL (SQLSTATE 42831). begin-column-name and end-column-name must not identify a column that is defined with a GENERATED clause (SQLSTATE 428HZ). Business time period columns cannot be added to a table that is in set integrity pending state.
An implicit check constraint is generated to ensure that the value of end-column-name is greater than the value of begin-column-name. The name of the implicitly created check constraint is DB2_GENERATED_CHECK_CONSTRAINT_FOR_BUSINESS_TIME and must not be the name of an existing check constraint (SQLSTATE 42710).
When a BUSINESS_TIME period is dropped, all packages with the application-period temporal table dependency type on that table are invalidated. Other dependent objects like views and triggers that record a dependency on the table are also marked as invalid.
If the table is a typed table, then this option is not supported (SQLSTATE 428DH for root tables or 428DR for other subtables).
Any changes made to the table by an INSERT, DELETE, UPDATE, CREATE INDEX, DROP INDEX, or ALTER TABLE in the same unit of work after the table is altered by this statement are not logged. Any changes made to the system catalog by the ALTER statement in which the NOT LOGGED INITIALLY attribute is activated are logged. Any subsequent changes made in the same unit of work to the system catalog information are logged.
At the completion of the current unit of work, the NOT LOGGED INITIALLY attribute is deactivated and all operations that are done on the table in subsequent units of work are logged.
If using this feature to avoid locks on the catalog tables while inserting data, it is important that only this clause be specified on the ALTER TABLE statement. Use of any other clause in the ALTER TABLE statement will result in catalog locks. If no other clauses are specified for the ALTER TABLE statement, then only a SHARE lock will be acquired on the system catalog tables. This can greatly reduce the possibility of concurrency conflicts for the duration of time between when this statement is executed and when the unit of work in which it was executed is ended.
If the table is a typed table, this option is only supported on the root table of the typed table hierarchy (SQLSTATE 428DR).
If the table is a system-period temporal table or a history table, this option is not supported
For more information about the NOT LOGGED INITIALLY attribute, see the description of this attribute in "CREATE TABLE".
When this action is requested, no DELETE triggers defined on the affected table are fired. The index data is also deleted for all indexes that exist on the table.
A partitioned table with attached data partitions or logically detached partitions cannot be emptied (SQLSTATE 42928).
The table should be reorganized after setting APPEND OFF since the information about available free space is not accurate and may result in poor performance during insert.
If neither of the preceding two options are specified along with the COMPRESS YES clause, ADAPTIVE is used implicitly.
If the starting-clause is omitted, the new data partition is assumed to be at the end of the table. If the ending-clause is omitted, the new data partition is assumed to be at the start of the table.
The table space used by the new index partition, whether default or specified by the INDEX IN clause, must match the type (SMS or DMS), page size, and extent size of the table spaces used by all other index partitions (SQLSTATE 42838).
For rules governing the use of the LONG IN clause with partitioned tables, see "Large object behavior in partitioned tables".
If the table being attached has either row level access control or column level access control activated then the table to attach to must have the same controls activated. No row permissions or column masks are automatically carried over from the table being attached to the target table. The column masks and row permissions do not necessarily need to be 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 (SQLSTATE 428GE). Similarly, if the table being attached has column level access control activated and at least one column mask object enabled then the table to attach to must also have column level access control activated and a column mask object enabled for the corresponding columns (SQLSTATE 428GE).
If the starting-clause is omitted, the new data partition is assumed to be at the end of the table. If the ending-clause is omitted, the new data partition is assumed to be at the start of the table.
When a partition is detached from a table for which either row level access control or column level access control is defined, the new table that is created for the detached data will automatically have row level access control (though not column level access control) activated to protect the detached data. Direct access to this new table will return no rows until appropriate row permissions are defined for the table or row level access control is deactivated for this table.
Historical versions of the rows in the table are retained by the database manager. The database manager records extra information that indicates when a row was inserted into the table, and when it was updated or deleted. When a row in a system-period temporal table is updated, a previous version of the row is kept. When data in a system-period temporal table is deleted, the old version of the row is inserted as a historical record. An associated history table is used to store the historical rows of the table.
References to the table can include a time period search condition to indicate which system versions of the data are to be returned.
history-table-name identifies a history table where historical rows of the system-period temporal table are kept. history-table-name must identify a table that exists at the current server (SQLSTATE 42704), and is not a catalog table (SQLSTATE 42832), an existing system-period temporal table, an existing history table, a declared global temporary table, a created global temporary table, a materialized query table, or a view, (SQLSTATE 428HX).The identified history table must not contain an identity column, row change timestamp column, row-begin column, row-end column, transaction start-ID column, generated expression column, or include a period (SQLSTATE 428HX).
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.
When a table is altered with DROP VERSIONING, all packages with the system-period temporal table dependency type on that table are invalidated. Other dependent objects like views and triggers that record a dependency on the table are also marked as invalid.
If the change data table is not altered before the Capture program processes log records with the altered attributes, the Capture program will likely fail. If a copy containing the altered column is not altered before the subscription maintaining the copy runs, the subscription will likely fail.
Issuing the SET INTEGRITY statement on the target table is required to bring the attached partition online.
If the REQUIRE MATCHING INDEXES clause is specified, and the target table is a partitioned MDC table created in DB2 V9.7 Fix Pack 1 or later releases, the ALTER TABLE ... ATTACH PARTITION statement fails and returns SQL20307N (SQLSTATE 428GE). Removing the REQUIRE MATCHING INDEXES clause allows the attach process to proceed.
If the target partitioned MDC table was created at a level lower than DB2 V9.7 Fix Pack 1, the block indexes are nonpartitioned. The block indexes on the source MDC table are dropped during the ATTACH operation. Issuing a SET INTEGRITY statement on the target table is required to bring the attached partition online. New rows from the attached partition are added to existing nonpartitioned block indexes.
When detaching a partition from a partitioned MDC table created using DB2 V9.7 Fix Pack 1 or later releases, the block indexes are partitioned, and the previous restrictions do not apply. Assuming that no other dependent objects such as dependent MQTs exist, access to the newly detached table is allowed in the same unit of work. All the partitioned indexes, including block indexes, become indexes on the target table without the need to be re-created.
Altering a table to make some of its columns implicitly hidden can impact the behavior of data movement utilities that are working with the table. When a table contains implicitly hidden columns, utilities like IMPORT, INGEST, and LOAD require that you specify whether data for the hidden columns is included in the operation. For example, this might mean that a load operation that ran successfully before the table was altered, now fails (SQLCODE SQL2437N). Similarly, EXPORT requires that you specify whether data for the hidden columns is included in the operation.
Data movement utilities must use the DB2_DMU_DEFAULT registry variable, or the implicitlyhiddeninclude or implicitlyhiddenmissing file type modifiers when working with tables that contain implicitly hidden columns.
When the table is referenced in a data manipulation statement, all enabled row permissions that have been created for the table, including the default row permission, are implicitly applied by the database manager to control which rows in the table are accessible. A row access control search condition is derived by application of the logical OR operator to the search condition in each enabled row permission. This derived search condition acts as a filter to the table before any user specified operations, such as predicates, grouping, ordering, and so on, are processed. This derived search condition permits the authorization IDs that are specified in the permission definitions to access certain rows in the table.
When the ACTIVATE ROW ACCESS CONTROL clause is used, all the packages and dynamically cached statements that reference the table are invalidated.
Row access control remains enforced until the DEACTIVATE ROW ACCESS CONTROL clause is used to stop enforcing it.
The owner of the default row permission is SYSIBM. The default row permission is always enabled. The default row permission is dropped when row access control is deactivated or when the table is dropped.
When column masks are used to mask the column values, they determine the values in the final result table. If a column has a column mask and the column (specifically a simple reference to a column name or a column embedded in an expression) appears in the outermost select list, the column mask is applied to the column to produce the values for the final result table. If the column does not appear in the outermost select list but it participates in the final result table, for example, it appears in a materialized table expression or view, the column mask is applied to the column in such a way that the masked value is included in the result table of the materialized table expression or view so that it can be used in the final result table.
The application of column masks does not interfere with the operations of other clauses within the statement such as the WHERE, GROUP BY, HAVING, SELECT DISTINCT, and ORDER BY. The rows returned in the final result table remain the same, except that the values in the resulting rows may 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 may not reflect that order. Similarly, the masked values may not reflect the uniqueness enforced by SELECT DISTINCT.
The DEACTIVATE COLUMN ACCESS CONTROL clause is used to stop enforcing column access control for a table. Thereafter, when the table is referenced in a data manipulation statement, the column masks are not applied.
The explicitly created row permissions or column masks, if any, remain but have no effect.
All the packages and dynamically cached statements that reference the table are invalidated when row or column access control is deactivated.
Data change operations on an application-period temporal table may result in an automatic insert of one or two additional rows when a row is updated or deleted. When an update or delete of a row in an application-period temporal table is specified for a portion of the period represented by that row, the row is updated or deleted and one or two rows are automatically inserted to represent the portion of the row that is not changed. New values are generated for each generated column in an application-period temporal table for each row that is automatically inserted as a result of an update or delete operation on the table. If a generated column is defined as part of a unique or primary key, parent key in a referential constraint, or unique index, it is possible that an automatic insert will violate a constraint or index in which case an error is returned.
ALTER TABLE DEPARTMENT
ADD RATING CHAR(1)
ALTER TABLE PROJECT
ADD SITE_NOTES VARCHAR(1000) FOR BIT DATA
Column Name Data Type
EQUIP_NO INT
EQUIP_DESC VARCHAR(50)
LOCATION VARCHAR(50)
EQUIP_OWNER CHAR(3)
Add a referential constraint
to the EQUIPMENT table so that the owner (EQUIP_OWNER) must be a department
number (DEPTNO) that is present in the DEPARTMENT table. DEPTNO is
the primary key of the DEPARTMENT table. If a department is removed
from the DEPARTMENT table, the owner (EQUIP_OWNER) values for all
equipment owned by that department should become unassigned (or set
to null). Give the constraint the name DEPTQUIP. ALTER TABLE EQUIPMENT
ADD CONSTRAINT DEPTQUIP
FOREIGN KEY (EQUIP_OWNER)
REFERENCES DEPARTMENT
ON DELETE SET NULL
ALTER TABLE EQUIPMENT
ADD COLUMN EQUIP_QTY
SMALLINT NOT NULL DEFAULT 1
ALTER TABLE EMPLOYEE
ADD CONSTRAINT REVENUE
CHECK (SALARY + COMM > 30000)
ALTER TABLE EMPLOYEE
DROP CONSTRAINT REVENUE
ALTER TABLE SALARY1
DATA CAPTURE NONE
ALTER TABLE SALARY2
DATA CAPTURE CHANGES
ALTER TABLE EMPLOYEE
ADD COLUMN HEIGHT MEASURE DEFAULT MEASURE(1)
ADD COLUMN BIRTHDAY BIRTHDATE DEFAULT DATE('01-01-1850')
ADD COLUMN FLAGS BLOB(1M) DEFAULT BLOB(X'01')
ADD COLUMN PHOTO PICTURE DEFAULT BLOB(X'00')
The default values use various function names when specifying the
default. Since MEASURE is a distinct type based on INTEGER, the MEASURE
function is used. The HEIGHT column default could have been specified
without the function since the source type of MEASURE is not BLOB
or a datetime data type. Since BIRTHDATE is a distinct type based
on DATE, the DATE function is used (BIRTHDATE cannot be used here).
For the FLAGS and PHOTO columns the default is specified using the
BLOB function even though PHOTO is a distinct type. To specify a
default for BIRTHDAY, FLAGS and PHOTO columns, a function must be
used because the type is a BLOB or a distinct type sourced on a BLOB
or datetime data type. Column Name Data Type
BRANCH_NO SMALLINT
CUSTOMER_NO DECIMAL(7)
CUSTOMER_NAME VARCHAR(50)
ALTER TABLE CUSTOMERS
ADD DISTRIBUTE BY HASH (BRANCH_NO)
ALTER TABLE EMPLOYEE
ADD COLUMN PHONE_NO CHAR(4) NOT NULL
ADD COLUMN WORK_DEPT CHAR(3)
ADD PRIMARY KEY (EMP_NO, WORK_DEPT)
ALTER TABLE DEPARTMENT
ADD CONSTRAINT FD1
CHECK ( DEPTNAME DETERMINED BY DEPTNO) NOT ENFORCED
ALTER TABLE DEPARTMENT
DROP CHECK FD1
ALTER TABLE EMPLOYEE
ALTER COLUMN WORKDEPT
SET DEFAULT '123'
ALTER TABLE EMPLOYEE
ADD SECURITY POLICY DATA_ACCESS
ALTER TABLE EMPLOYEE
ALTER COLUMN SALARY
SECURED WITH EMPLOYEESECLABEL
Column Name Data Type
----------- ---------
EMP_NAME VARCHAR(50) NOT NULL
EMP_ID SMALLINT NOT NULL
EMP_POSITION VARCHAR(100) NOT NULL
SALARY DECIMAL(5,2)
PROMOTION_DATE DATE NOT NULL
Change this table
to allow salaries to be stored in a DECIMAL(6,2) column, make PROMOTION_DATE
an optional field that can be set to the null value, and remove the
EMP_POSITION column. ALTER TABLE SALARY_DATA
ALTER COLUMN SALARY SET DATA TYPE DECIMAL(6,2)
ALTER COLUMN PROMOTION_DATE DROP NOT NULL
DROP COLUMN EMP_POSITION
ALTER TABLE BOOKS
ADD COLUMN DATE_ADDED TIMESTAMP
WITH DEFAULT CURRENT TIMESTAMP
CREATE TABLE tt1
(c1 INT SECURED WITH C, c2 DB2SECURITYLABEL) SECURITY POLICY P;
CREATE TABLE tt2
(c3 INT SECURED WITH B, c4 DB2SECURITYLABEL) SECURITY POLICY P;
Table tt2 can be altered to
be a materialized query table with the following SQL: ALTER TABLE tt2 ADD (SELECT * FROM tt1 WHERE c1 > 10)
DATA INITIALLY DEFERRED REFRESH DEFERRED;
Table tt2 becomes a materialized query table with the
secure policy P. tt2.c3 has security label P.B. tt2.c4 has security
label P.C and it is also DB2SECURITYLABEL.