Pending column alterations

You can specify whether column alterations are processed as immediate or pending definition changes.

The DDL_MATERIALIZATION subsystem parameter controls whether all column alterations in a Db2 subsystem are immediate or pending. The default is to make all column alterations immediate. To help you decide whether to make all column alterations pending, examine the differences between immediate and pending alteration behavior in the following table.

Situation that immediate or pending behavior affects	Behavior when immediate alteration is in effect	Behavior when pending alteration is in effect
An ALTER TABLE statement contains some operations that can be executed as pending or immediate changes, and some operations that can be executed only as immediate changes.	All operations in the ALTER statement are executed as immediate changes.	The ALTER statement fails.
The table on which the ALTER is executed, or objects that are related to the table have unmaterialized pending definition changes.	The ALTER statement fails.	The ALTER statement is executed as a pending definition change.
Which utility can be used to materialize column alterations	REORG or LOAD REPLACE can be used.	Only REORG with SHRLEVEL REFERENCE or CHANGE can be used.
When changes are materialized	Changes are materialized in the catalog and OBD at the time that the ALTER is executed.	Changes are materialized in the catalog, OBD, and data when REORG is run.
Object status after the ALTER statement is executed	Depending on the type of alteration, the table space is in AREO* or REORP status. Affected indexes are in AREO*, RBDP, or PSRBD status.	The table space is in AREOR status.
Scope of a REORG that can be used for materialization	Partition-level REORG or table-space-level REORG can be used.	Only table-space-level REORG can be used.
When table versions are generated	ALTER statements that are executed against tables in the same table space and in the same commit scope generate a single new table version number. ALTER statements that are executed in different commit scopes generate different table version numbers.	ALTER statements that are executed against tables in the same table space, and whose changes are materialized with the same REORG statement, generate a single new version number. ALTER statement changes that are materialized with different REORG statements generate different version numbers.

Consider setting your subsystem to always use pending column alterations if you commonly encounter these situations:

Columns with indexes defined on them are altered in a way that causes the indexes to be placed in restrictive states.
After an alter operation, non-unique indexes are unavailable until REBUILD INDEX is run. For unique indexes, the tables are unavailable for insert or update operations until REBUILD INDEX is run.

In this situation, executing the column alterations as pending changes eliminates the need to run REBUILD INDEX. The indexes are not in a restrictive state after the pending column alterations. When REORG is run on the entire table space to materialize the column alterations, the containing table space and indexes are unavailable for only a short time, during the SWITCH phase.
Tables in which column alterations are performed are in table spaces on which pending alterations are needed.
An immediate column alteration cannot be executed if other alterations to the containing table space are pending. The table and table space operations must be done in one of the following ways:
- The immediate column alteration must be performed before pending table space alterations.
- The pending table space operations must be materialized before the immediate column alteration can be done. This process requires that REORG TABLESPACE is run once to materialize the pending table space operations, and once to convert the data in the altered column to the new format.
In this situation, executing the column alterations as pending changes allows you to group materialization of all changes into the same REORG.