Online schema enhancements

Db2 12 improves system and application availability by extending the scope of recovery to a point in time before materialization of pending definition changes. In addition, in Db2 12, column alterations can be pending definition changes.

Improvements in recovery to a point in time before materialization of pending definition changes

Db2 11 introduces the capability to recover the following objects to a point in time before the pending definition changes were materialized:

  • partition-by-range table spaces
  • LOB or XML table spaces

Db2 12 extends recovery to a point in time before pending definition changes were materialized in the following ways:

  • The object that is being recovered can be a partition-by-growth table space.

Pending column alterations

In Db2 12, ALTER TABLE ALTER COLUMN statements that change the data type, length, precision, or scale of columns can be pending alterations. By default, column alteration operations are immediate operations. To make column alterations in a subsystem pending operations, set the DDL_MATERIALIZATION subsystem parameter to ALWAYS_PENDING.

Improved availability when altering index compression

To improve the availability of indexes in universal table spaces, alterations to index compression are a pending change in Db2 12. For indexes in universal table spaces, alterations to index compression are a pending change that place the index in advisory REORG-pending (AREOR) status. Applications can continue to access the indexes. The updated value for the COMPRESS attribute in the ALTER INDEX statement is materialized by a subsequent online REORG INDEX or online REORG TABLESPACE at the table space level. With this improvement, database administrators can correct or remove a pending change to index compression without affecting the target index. Also, this improvement reduces the planning and costs that are associated with an application outage caused by the previous behavior.

Note: If the index is defined with the DEFINE NO attribute and data sets are not created yet, the alteration is still immediate. Also, for an index that is not in a universal table space, an alteration to index compression can be a pending change if other pending changes exist at the index, table, or table space level when the ALTER INDEX COMPRESS statement runs.

In previous releases, any alteration to use index compression placed the index in REBUILD-pending (RBDP) status. This behavior prevented applications from using the index until the REORG TABLESPACE utility or the REBUILD INDEX utility completed.