Pending data definition changes

Pending data definition changes are data definition changes that do not take effect immediately because the object must be reorganized to apply the change. When you are ready to materialize pending data definition changes, you run the REORG utility to apply the pending changes to the definition and data. Objects that have pending definition changes remain available for use until it is convenient to apply the changes.

ALTER statements with certain options can cause pending changes to the definition of database objects. When an ALTER statement is issued that causes pending changes to the definition of an object, semantic validation and authorization checking are performed. However, changes to the table definition and data are not applied and the object is placed in advisory REORG-pending state (AREOR), until the REORG utility is run to resolve the pending changes.

Most pending data definition changes are supported only for universal table spaces, with the following exceptions:

Converting single-table simple or segmented (non-UTS) table spaces to partition-by-growth table spaces, with the MAXPARTITIONS attribute.
Converting partitioned (non-UTS) table spaces to partition-by-range table spaces, with the SEGSIZE attribute.
Changing partition boundaries for partitioned (non-UTS) table spaces.
FL 508 Moving tables from multi-table simple or multi-table segmented (non-UTS) table spaces to partition-by-growth table spaces, using the ALTER TABLESPACE statement with the MOVE TABLE option.

The pending changes are recorded in the SYSIBM.SYSPENDINGDDL catalog table. When the pending changes are applied, dependent packages are invalidated as needed, the corresponding entries in the SYSIBM.SYSPENDINGDDL catalog table are removed, and the advisory REORG-pending state is removed.

When pending definition changes occur

The following statements can result in pending data definition changes:

ALTER TABLESPACE

The following table lists clauses and specific conditions that cause an ALTER TABLESPACE statement to be processed as a pending definition change. The changes are not reflected in the definition or data at the time the ALTER TABLESPACE statement is issued. Instead, the entire table space is placed in an advisory REORG-pending state (AREOR). A subsequent reorganization of the entire table space applies the pending definition changes to the definition and data of the table space. The definition of the table space must not be in an incomplete state.

Clause or option	Pending definition change used if...
BUFFERPOOL	The data sets of the table space are already created, and any of the following conditions are true: Pending definition changes already exist for the table space or any objects within the base table space. The specified buffer pool has a different page size than the buffer pool that is currently being used for the table space.
DSSIZE	The data sets of the table space are already created, and any of the following conditions are true: Pending definition changes already exist for the table space or for any objects in the table space. The table space uses relative page numbering, and the DSSIZE value that is specified at the table space level is smaller than the value that is currently being used for one or more of the partitions in the table space. The table space uses absolute page numbering, and the specified DSSIZE value is different than the value that is currently being used for the table space.
MAXPARTITIONS	If the data sets of the table space are already created and the table space is not a PBG table space.
MEMBER CLUSTER	If the data sets of the table space are already created and any of the following conditions are true: Pending definition changes already exist for the table space or any objects in the table space. The MEMBER CLUSTER attribute is changed to a different value.
FL 508 MOVE TABLE	The data sets of the altered table space are already created.
PAGENUM	The change to the PAGENUM attribute is a pending change to the definition of the table space if the data sets of the table space are already created and if one of the following conditions is true: Pending definition changes already exist for the table space or any associated indexes. The specified PAGENUM attribute is different from the value that is currently being used for the table space.
SEGSIZE	The data sets of the table space are already created, and any of the following conditions are true: Pending definition changes already exist for the definition of the table space or any objects in the table space. The specified SEGSIZE value for a universal table space is different than the existing value. The table space is converted from a partitioned (non-UTS) table space to a partition-by-range table space.

When pending definition changes are specified for the BUFFERPOOL, DSSIZE, MAXPARTITIONS, or SEGSIZE attributes of partition-by-growth (PBG) table spaces, the number of partitions is determined based on the amount of existing data at the time the pending change is applied, and partition growth can occur. If LOB columns exist, additional LOB table spaces and auxiliary objects are implicitly created for the newly-created partitions independent of whether SQLRULES (DB2) or SQLRULES (STD) is in effect or whether the table space was explicitly or implicitly created. The new LOB objects inherit the buffer pool attribute and authorization from the existing LOB objects.

ALTER TABLE

The following table lists clauses and specific conditions that cause an ALTER TABLE statement to be processed as a pending definition change, which is not reflected in the definition or data at the time that the ALTER TABLE statement is issued. Instead, the table space or specific partitions are placed in an advisory REORG-pending state (AREOR). A subsequent reorganization of the table space, or the specific affected partitions, applies the pending definition changes to the definition and data of the table. The definition of the containing table space must not be in an incomplete state.

Clause or option	Pending definition change used if...
ALTER COLUMN	The statement altering the data type, length, precision, or scale of a column is a pending change to the definition of the table space if the data sets of the table space are already created and all of the following conditions are true: The DDL_MATERIALIZATION subsystem parameter is set to ALWAYS_PENDING. The base table space is a universal table space.
DROP COLUMN	The data sets of the table space are already created.
ADD PARTITION	The data sets are already defined and the new partition is added between existing logical partitions. The alteration is normally a pending change, and the added partition is placed in advisory REORG-pending (AREOR) status. Unless integer specifies the last logical partition, the next logical partition is also placed in AREOR status. However, if no other pending definition changes exist on the affected partitions, an immediate change can sometimes be used, possibly with a restrictive status.
ALTER PARTITION	The statement changes the limit keys for the following types of partitioned table spaces: Partition-by-range table spaces Partitioned (non-UTS) table spaces with table-controlled partitioning. The alteration is normally a pending change, and the altered partition is placed in advisory REORG-pending (AREOR) status. Unless integer specifies the last logical partition, the next logical partition is also placed in AREOR status. However, if no other pending definition changes exist on the affected partitions, an immediate change can sometimes be used, possibly with a restrictive status. The change is immediate with no restrictive status if any of the following conditions are true: The affected partition data sets never contained any data. There is no possibility of any data being discarded or moved between partitions based only on the range of possible data values (not on the actual data values). This situation can occur if the statement specifies the same existing values for the limit key, or if the new limit key for the last logical partition expands the range of possible data values.
The data sets of the table space are already created.

ALTER INDEX

The following table lists clauses and specific conditions that cause an ALTER INDEX statement to be processed as a pending definition change, which is not reflected in the current definition or data at the time that the ALTER statement is issued. Instead, the index is placed in an advisory REORG-pending (AREOR) state. A subsequent reorganization of the entire index with an appropriate utility materializes the changes and applies the pending definition changes to the catalog and data.

If there are no pending definition changes for the table space, you can run the REORG INDEX utility with SHRLEVEL CHANGE or the REORG TABLESPACE utility with SHRLEVEL CHANGE or REFERENCE to materialize the changes to the definition of the index. If pending definition changes also exist for the table space, you must run the REORG TABLESPACE utility with SHRLEVEL CHANGE or REFERENCE to enable the changes to the definition of the index (and the pending table space definition).

Clause or option	Pending definition change used if...
BUFFERPOOL	The data sets of the index are created, and all of the following conditions are true: The index is defined on a base table, or an associated XML table or auxiliary table, where the table space for the base table is a universal table space (UTS) or is being converted to a UTS by a pending definition change. The buffer pool is changed to a buffer pool with a different size, or the buffer pool is changed to a buffer pool with the same size and the table space or objects in the table space have pending definition changes.
COMPRESS	The data sets of the index are created, and all of the following conditions are true: The index is defined on a base table, or an associated XML table or auxiliary table, where the table space for the base table is a universal table space (UTS) or is being converted to a UTS by a pending definition change. The compress attribute is changed, or the table space or objects in the table space have pending definition changes.

Clause or option

Pending definition change used if...

BUFFERPOOL

The data sets of the index are created, and all of the following conditions are true:

The index is defined on a base table, or an associated XML table or auxiliary table, where the table space for the base table is a universal table space (UTS) or is being converted to a UTS by a pending definition change.
The buffer pool is changed to a buffer pool with a different size, or the buffer pool is changed to a buffer pool with the same size and the table space or objects in the table space have pending definition changes.

COMPRESS

The data sets of the index are created, and all of the following conditions are true:

The index is defined on a base table, or an associated XML table or auxiliary table, where the table space for the base table is a universal table space (UTS) or is being converted to a UTS by a pending definition change.
The compress attribute is changed, or the table space or objects in the table space have pending definition changes.