Db2 for z/OS and its ecosystem

 View Only

“Stacking limits” removed for pending changes with PBG to PBR conversions in Db2 13 for z/OS

By LONG TU posted Thu January 26, 2023 04:32 PM


By Long Tu with Paul McWilliams.

Starting in function level 500 or higher with APAR PH51359 (December 2022), Db2 13 for z/OS supports stacking of certain pending data definition changes (DDL) when you use the new capability in Db2 13 to convert a table in a partition-by-growth (PBG) table space to partition-by-range (PBR). That is, the pending definition changes in following table can now be issued together and materialized by a single execution of the REORG utility.

Object level

Stacked pending definition changes for PBG-to-PBR conversions

Table space



SEGSIZE (excluding conversion to UTS)









This new capability is especially useful if you need to enlarge the partition data set sizes to accommodate the distribution of data into the partitions, alter the columns to be used as partitioning keys, or alter other table space or index attributes, as part of the PBG to PBR conversion.

Before this change, Db2 issues SQL code -20385 if you try to issue any of these alterations when a PBG to PBR conversion is pending, or in the opposite situation, so at least two executions of the REORG utility are required to complete any of these changes if they are needed for the conversion.

For example, consider the following use cases before the introduction of this new capability:

Enlarging partition data set sizes to accommodate data distributed into partitions

The range-partitioning scheme of a converted PBR table space might result in a situation where the current data set sizes of the partitions are not large enough to accommodate the redistributed data into the partitions, for example:

  • The partition limit key ranges cause data to be unevenly distributed into partitions.
  • The number of partitions for the converted PBR table is fewer than the number of partitions of the original PBG table space.

If the partition data set sizes cannot accommodate the redistributed data, the REORG to materialize the PBG-to-PBR conversion fails due to a partition-full condition. To avoid such a REORG failure, you would need to complete two REORG utility operations to complete the following procedure:

  1. Execute an ALTER TABLESPACE statement with DSSIZE clause (which is a pending definition change) to increase the data set size.
  2. Run REORG TABLESPACE to materialize the data set size alteration.
  3. Execute an ALTER TABLE statement with ALTER PARTITIONING TO PARTITION BY clause for PBG-to-PBR conversion.
  4. Run REORG TABLESPACE to materialize the PBG-to-PBR conversion.

Altering columns that will be used as partitioning key columns

Before Db2 12, column alterations are always executed as immediate changes. Db2 just generated a new version for the new schema format and placed  the table space in advisory REORG-pending (AREO*) status to indicate the object needs to be reorganized for optimal performance. Then it can be followed by a PBG-to-PBR conversion. A subsequent REORG materializes the conversion and brings the data to current version format.


Starting with Db2 12, if DDL_MATERIALIZATION subsystem parameter is ALWAYS_PENDING, eligible column alterations are executed as pending changes. Executing a column data type alteration as a pending change has the following advantages:

  • Avoids the performance impact incurred by the conversion of data from older version formats
  • Avoids an application outage if the altered column is numeric and referenced in the key of a unique index.

If these column alterations are pending changes, a REORG is needed to materialize these changes before executing a PBG-to-PBR conversion that is materialized by a separate REORG.

Altering attributes for the converted PBR table space

Table space or index attributes might need to be altered to different values to accommodate a range-partitioning scheme. If these attributes are altered as pending definition changes, an additional REORG is needed to materialize these changes separately from the REORG that materializes the PBG-to-PBR conversion.


Each of the preceding use cases illustrates a challenge that is much easier to accomplish when you can complete the various required data definition changes as pending data definition changes and materialize them along with a PBG-to-PBR conversion in a single REORG operation.