Pending data definition changes

When pending definition changes occur

The following situations can result in pending definition changes:

ALTER TABLESPACE

The following ALTER TABLESPACE options can cause pending changes to the definition of a table space:

• BUFFERPOOL 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 buffer pool has a different page size than the buffer pool that is currently being used for the table space.
• DSSIZE 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 DSSIZE is different than the value that is currently being used for the table space.
• MAXPARTITIONS is a pending change if the table space is not a partition-by-growth universal table space.
• SEGSIZE is a pending change to the definition of the table space if the data sets of the table space are already created and one of the following conditions is true:
  • Pending changes to the definition of the table space or its associated indexes already exist.
  • The specified SEGSIZE value for a universal table space is different than the existing value.
  • The table space is converted from a partitioned table space to a range-partitioned universal table space.

ALTER TABLE

The following ALTER TABLE options can cause pending changes to the definition of the table under certain conditions:

• DROP COLUMN, if the data sets of the table space are already created
• ALTER PARTITION, to change the limit keys for the following types of partitioned table spaces:
  • Range-partitioned universal table spaces
  • Partitioned table spaces (non-universal) with table-controlled partitioning. However, this operation is not a pending definition change under the following circumstances:
    • There is no possibility that data would move between partitions, and no other definition change is pending on the partition or the previous partition. In this case, the changes are immediate, and the partitions are not placed in a REORG-pending or advisory REORG-pending status.
    • The values in the limit key for the last partition are all altered from MAXVALUE to a value less than MAXVALUE, or from MINVALUE to a value greater than MINVALUE. In this case, the changes are immediate, and the partition is placed in REORG-pending status.
• MEMBER CLUSTER is a pending change.

ALTER INDEX

The following ALTER INDEX options can cause pending changes to the definition of the specified index under certain conditions:

• BUFFERPOOL is a pending change if all of the following conditions are true:
  • The data sets of the index are created
  • The index is defined on one of the following:
    • A table that is in a universal table space
    • An XML table that is associated with a base table that is in a universal table space
    • An auxiliary table that is associated with a base table that is in a universal table space
  • There are pending definition changes for the index or the table space
  • The buffer pool is changed to a buffer pool with a different size
• COMPRESS is a pending change if all of the following conditions are true:
  • The data sets of the index are created
  • The index is defined on one of the following:
    • A table that is in a universal table space
    • An XML table that is associated with a base table that is in a universal table space
    • An auxiliary table that is associated with a base table that is in a universal table space
  • There are pending definition changes for the index or the table space
  • The buffer pool is changed to a buffer pool with a different size

ALTER INDEX BUFFERPOOL results in an immediate definition change except when all of the following conditions are true:

• The data sets of the index are created
• The index is defined on one of the following objects:
  • A table that is in a universal table space
  • An XML table that is associated with a base table this is in a universal table space
  • An auxiliary table that is associated with a base table that is in a universal table space
• There are pending definition changes for the index or the table space, or the buffer pool is changed to a buffer pool with a different size.

When pending changes are restricted

ALTER TABLESPACE, ALTER TABLE and ALTER INDEX statements that result in pending definition changes are not supported in the following cases:

• Options that cause pending changes cannot be specified with options that take effect immediately
• Options that cause pending changes cannot be specified for the following objects:
  • The DB2® catalog
  • System objects
  • Objects in a work file database
• The DROP PENDING CHANGES clause cannot be specified for a catalog table space
• If the DROP PENDING CHANGES clause is specified, no other clauses can be specified on the ALTER TABLESPACE statement
• 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, if the definition of the table space or table on which the index is defined it not complete.

Most immediate definition changes are restricted while pending definition changes exist for an object. For a list of such restrictions, see Restrictions for changes to objects that have pending data definition changes.