Improved data availability when altering limit keys

In Db2 11, you can alter the limit keys for partitioned table spaces without impacting the availability of the data.

In earlier versions of Db2, when you change limit key values, all affected partitions are placed in REORG-pending (REORP) status. The data becomes unavailable until you run the REORG utility.

In Db2 11, that restriction is removed. When you change the limit key values, the data remains available and applications can continue to access the data. In most cases however, the limit key changes are not materialized until the next time that you run REORG. The affected partitions are placed in advisory REORG-pending (AREOR) status. This type of definition change, in which the changes are not immediately materialized, is called a pending data definition change. For more information about such changes, see Pending data definition changes.

Important: The introduction of pending changes for limit keys affects how you materialize changes to limit keys. For more information about changes that might be required in your Db2 environment, see ALTER statements that change limit keys are pending changes in Application and SQL release incompatibilities.

In a few cases, a change to a limit key value is immediately materialized, so you do not need to run REORG on the partitions. Immediate materialization occurs when Db2 determines that both of the following conditions are true:

  • No data needs to be moved between partitions.
  • No other alter limit key operation is pending on the partition.

This new capability applies to the following types of partitioned table spaces:

  • partition-by-range table spaces
  • Partitioned (non-UTS) table spaces with table-controlled partitioning
Beyond improved data availability, the ability to alter limit keys as pending changes provides other benefits:
  • You can alter a limit key even when the table space has other pending definition changes.
  • Because the materialization of the limit key changes is deferred, you have a time period where you can drop limit key changes without affecting the target table.

For partitioned (non-UTS) table spaces with index-controlled partitioning, altering the limit key still causes the affected partitions to be placed in REORG-pending (REORP) status. Therefore, the data is unavailable until the affected range of partitions is reorganized. To prevent such outages, you can use the new subsystem parameter PREVENT_ALTER_LIMITKEY. Specifying a value of YES for this parameter restricts ALTER TABLE statements that alter limit keys for index-controlled partitioned table spaces.