Db2 for z/OS and its ecosystem

Object level	Stacked pending definition changes for PBG-to-PBR conversions
Table space	BUFFERPOOL DSSIZE SEGSIZE (excluding conversion to UTS) MEMBER CLUSTER
Table	ALTER COLUMN DROP COLUMN
Index	BUFFERPOOL COMPRESS

 

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.

Conclusion

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.