Restrictions for pending data definition changes

When pending data definition changes are restricted

ALTER TABLESPACE, ALTER TABLE and ALTER INDEX statements that result in pending definition changes are subject to the following restrictions:

• Options that cause pending changes cannot be specified with options that take effect immediately.
• Options that cause pending changes cannot be specified for the Db2 catalog, other system objects, or objects in a work file database.
• The DROP PENDING CHANGES clause of the ALTER TABLESPACE statement cannot be specified for a catalog table space.
• If the table space, or any table it contains is in an incomplete state, you cannot specify options that cause pending changes.
• For ALTER INDEX, options that cause pending changes cannot be specified if the definition of the table space or table on which the index is defined is not complete.

Restrictions for objects with existing pending data definition changes

The following table lists data definition changes that are restricted until any pending data definition changes are materialized for specific types of objects. Db2 issue SQLCODE -20385 for statements that cannot be processed because of pending data definition changes.

Table 1. Restricted data definition changes for pending data definition changes

Scope of change	Pending data definition changes	Restricted immediate definition changes for a table space, the table it contains, or indexes on the table	Restricted pending definition changes for a table space, the table it contains, or indexes on the table
Table space	ALTER TABLESPACE with any of the following options: BUFFERPOOL DSSIZE MAXPARTITIONS FL 508 MOVE TABLE PAGENUM SEGSIZE	ALTER INDEX with ADD COLUMN option ALTER INDEX with DSSIZE option at index level ALTER INDEX with PIECESIZE option ALTER INDEX with REGENERATE option ALTER INDEX with VCAT option ALTER PARTITION with FREEPAGE option ALTER TABLE with immediate option(s) that are not KEYLABEL ALTER TABLESPACE with CCSID option ALTER TABLESPACE with DSSIZE option at a partition level ALTER TABLESPACE with FREEPAGE option ALTER TABLESPACE with VCAT option if table space is not partitioned by growth CREATE INDEX on table in table space CREATE TABLE in table space DROP INDEX of index enforcing ROWID GENERATED BY DEFAULT column in explicitly created table space DROP INDEX of empty auxiliary index in explicitly created LOB table space if pending changes exist for the base table space, the table it contains, or indexes on the table DROP TABLE of empty auxiliary table if pending changes exist for the base table space, the table it contains, or indexes on the table	None
Table space (continued)	FL 508 Only ALTER TABLESPACE with MOVE TABLE option	ALTER TABLESPACE on the source table space, unless one of the following conditions is true: DROP PENDING CHANGES is specified The table space has a pending definition change to be converted to a universal table space and one of the following options is specified: BUFFERPOOL DSSIZE MEMBER CLUSTER SEGSIZE ALTER TABLESPACE with MAXPARTITIONS option to convert the source table space to a partition-by-growth table space, unless one of the following conditions is true: The table space is a single-table table space The table space has pending definition changes to move all but one table from the table space RENAME TABLE Before materializing a pending ALTER TABLESPACE MOVE TABLE statement, the following operations will cause the REORG to fail during the UTILINIT phase: ALTER TABLESPACE on the target table space that changes its attributes to become invalid for a MOVE TABLE operation DROP and CREATE to re-create the target table space	ALTER INDEX of other attributes ALTER TABLE with any pending option
Table	ALTER TABLE statements with any of the following options: DROP COLUMN ALTER COLUMN ADD PARTITION ALTER PARTITION	ALTER INDEX with ADD COLUMN option ALTER INDEX with REGENERATE option ALTER TABLE with immediate option(s) that are not KEYLABEL ALTER TABLE ADD CLONE if an associated LOB or XML table space has pending definition changes ALTER TABLE ADD MQT definition referencing table with pending definition changes ALTER TABLE with ADD VERSIONING option if the history table has pending definition changes ALTER TABLE with ENABLE ARCHIVE option ALTER TABLESPACE with CCSID option CREATE FUNCTION of SQL table function referencing table with pending definition changes CREATE INDEX on table CREATE MASK on table or referencing table with pending definition changes CREATE PERMISSION on table or referencing table with pending definition changes CREATE TABLE of MQT referencing table with pending definition changes CREATE TRIGGER of INSTEAD OF trigger if view is dependent on table with pending definition changes CREATE TRIGGER on triggering table with pending definition changes DROP INDEX of index enforcing ROWID GENERATED BY DEFAULT column in explicitly created table space DROP TABLE of empty auxiliary table if pending changes exist for the base table space, the table it contains, or indexes on the table RENAME TABLE	None
Column	ALTER TABLE statements with any of the following options: DROP COLUMN ALTER COLUMN	All restricted operations when the scope of change is table, plus the following operations: ALTER INDEX with NOT PADDED option if index key references column with pending definition changes ALTER TABLE with FOREIGN KEY clause referencing parent column with pending definition changes CREATE VIEW	None
Partition	ALTER TABLE statements with any of the following options: ALTER PARTITION ADD PARTITION	All restrictions that apply when the scope of change is table, plus ALTER TABLE ADD PARTITION to insert a partition if the last logical partition has pending definition changes to alter the limit key value	ALTER TABLE ALTER PARTITION if table has pending definition changes due to insertion of a partition
Index	ALTER INDEX statements with any of the following options: BUFFERPOOL COMPRESS	ALTER INDEX with ADD COLUMN option ALTER INDEX with COMPRESS YES option ALTER INDEX with DSSIZE option at index level ALTER INDEX with PIECESIZE option ALTER INDEX with REGENERATE option ALTER INDEX with VCAT option ALTER TABLE with immediate option(s) that are not KEYLABEL CREATE INDEX on table in table space DROP INDEX of index enforcing ROWID GENERATED BY DEFAULT column in explicitly created table space DROP TABLE of empty auxiliary table if pending changes exist for the base table space, the table it contains, or indexes on the table RENAME INDEX	None

Note: If you materialize a pending definition change that results in regeneration errors of any dependent advanced triggers, the REORG might fail. For more information, see DSNU276I.

Restrictions for recovery to before pending definition changes

In some cases, recovery to a point-in-time before the materialization of a pending definition change is not supported at all, and various restrictions apply when it is supported. For more information, see "Restrictions for point-in-time recoveries" in Point-in-time recovery.

If you do recover to a point-in-time before the materialization of most pending definition changes, many operations become restricted for the object until you run the REORG utility. The restrictions do not apply if the pending definition change is an ALTER TABLE statement with the ALTER PARTITION or ALTER COLUMN options. For more information about these restrictions and how to resolve them, see Recovering to a point in time before pending definition changes were materialized.