Restrictions for changes to objects that have pending data definition changes

When data definition statements specify pending changes, immediate data definition pages cannot be issued in the same statement. Certain immediate changes are also restricted in subsequent data definition statements until the REORG utility is run to materialized the pending data definition changes.

The following table lists immediate 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. Restrictions for data definition changes for pending data definition changes
Scope of change Pending data definition changes Restricted immediate definition changes for the table space and contained objects
Table space ALTER TABLESPACE with any of the following options:
  • BUFFERPOOL
  • DSSIZE
  • MAXPARTITIONS
  • SEGSIZE
  • ALTER INDEX with ADD COLUMN option
  • ALTER INDEX with PIECESIZE option
  • ALTER INDEX with REGENERATE option
  • ALTER INDEX with VCAT option
  • ALTER TABLE with immediate option(s)
  • ALTER TABLESPACE with CCSID option
  • 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 base table space or objects in base table space
  • DROP TABLE if table space was explicitly created
  • DROP TABLE of empty auxiliary table if pending changes exist for base table space or objects in base table space
Index ALTER INDEX statements with any of the following options:
  • BUFFERPOOL
  • ALTER INDEX with ADD COLUMN option
  • ALTER INDEX with COMPRESS YES option
  • ALTER INDEX with PIECESIZE option
  • ALTER INDEX with REGENERATE option
  • ALTER INDEX with VCAT option  ALTER TABLE with immediate option(s)
  • 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 base table space or objects in base table space RENAME INDEX