By Long Tu and Paul McWilliams.

With the availability of function level V13R1M507 (FL 507) in Db2 13 for z/OS, database administrators (DBAs) can use an online change—with no application outages—to convert the data partitioning scheme of an existing table in a partition-by-range (PBR) universal table space (UTS) to use growth-based partitions that Db2 manages in a partition-by-growth (PBG) UTS.

Moreover, by "stacking" a PBR-to-PBG conversion with a previously PBG-to-PBR conversion (introduced by FL 500 in Db2 13), DBAs can also alter the partitioning scheme of an existing table in a PBR UTS with an online change that does not require an application outage.

Before the availability of this new capability, the only option for converting from a PBR to PBG requires a costly application outage. That is, DBAs needed to stop the applications, unload the data, drop the table, table space, and related objects, re-create all replacement objects with the new design, issue GRANT statements for the required authorizations, and then reload the data.

Tables with range-based or growth-based data partitions in universal tables spaces (UTS) have been the recommended direction for several releases of Db2 for z/OS. These UTS types were first introduced in version 9 of Db2, and since the release of version 10, they are documented as the strategic direction for future Db2 development. The new roundtrip conversion capabilities help set the stage for you to fully adopt UTS in your Db2 for z/OS environment.

Online PBR to PBG conversion for UTS

At application compatibility level V13R1M507 or higher, you can convert an existing table in partition-by-range (PBR) universal table space (UTS) to use growth-based data partitions in a partition-by-growth (PBG) UTS. The PBR-to-PBG conversion is a pending data definition change, and you can also stack it with a PBG-to-PBR conversion in the same materializing REORG operation.

To start the conversion, you can issue an ALTER TABLE statement and specify ALTER PARTITIONING TO PARTITION BY GROWTH, as shown in the updated syntax diagram.

This statement is an example of the basic syntax:

ALTER TABLE E8071.TB01

ALTER PARTITIONING TO PARTITION BY GROWTH;

The data set size and maximum number of partitions (MAXPARTITIONS) for the converted PBG table can also be specified in the ALTER TABLE statement, and you should consider specifying a value that satisfies the requirements for the converted PBG table. However, if you do not specify these optional values, Db2 selects appropriate values for you.

Also, if the table has DATA CAPTURE CHANGES, the maximum number of partitions must not be less than the number of partitions for the original PBR table. That is, if the PBR UTS has 10 partitions, the converted PBG UTS will also have at least 10 partitions.

The ALTER TABLE statement is executed as a pending change which means that Db2 records it in the SYSIBM.SYSPENDINGDDL catalog table and places the table space in advisory REORG-pending (AREOR) state.

When you are ready to materialize the change, you can run the online REORG TABLESPACE utility with the SHRLEVEL REFERENCE or CHANGE clause options. The utility materializes the PBR-to-PBG conversion and any other stacked pending changes. The initial allocation for the PBG table space has one partition. Of course, Db2 might grow more partitions as needed to accommodate the data in the converted table space.

Online partitioning scheme changes for existing tables in PBR UTS

Many pending options are allowed to be stacked with the PBR-to-PBG conversion. Consider issuing necessary schema changes, stacking those with PBR-to-PBG conversion, and then materializing all the pending changes in a single online REORG.

By using a stacked "round-trip" (PBR-to-PBG-to-PBR) conversion, you can use an online change to alter the following attributes of an existing table with PBR data partitions, which previously could only be achieved by a DROP and re-create of the table and table space:

• Alter the partitioning key
• Drop an existing partition entirely, assuming that the table is not defined with DATA CAPTURE CHANGES
• Alter limit key values of multiple partitions with a single ALTER statement
• Insert new partitions at any position by adjusting the partitioning scheme
• Remove legacy 40-byte truncated limit key values

Restrictions and other considerations

Although many table and table space alterations can be stacked with new PBR-to-PBG conversion, a few restrictions do apply, and a PBR-to-PBG conversion is not allowed if pending changes already exist for certain options, and certain immediate changes are also restricted until you run the REORG utility to materialize a PBR-to-PBG conversion.

For example, the following pending alterations cannot be stacked with PBR-to-PBG conversion:

• Altering the table SEGSIZE attribute to convert a tablespace from a non-UTS partitioned table space to PBR UTS.
• Altering the table PAGENUM attribute from absolute to relative page numbers
• Altering the table space with ADD PARTITION to insert a partition
• Altering the table space to PARTITION to alter a limit key
• Stacked roundtrip conversions in the reverse direction, that is PBG-to-PBR-to-PBG, are also not supported because it does not have a valid use case.

For more information, see Restrictions for pending data definition changes

Another thing to watch out for is that existing partitioned indexes on the conversion tables are converted to non-partitioned indexes with the default PIECSIZE of 4GB due to the conversion, so existing access paths that involve those indexes will be affected which may result in performance impact.

Recovery to point-in-time before the materializing REORG is also unsupported, which is normal for many pending definition changes.

Conclusions

With the availability of online conversions between the two strategic partitioning schemes for UTS, and especially with the round-trip stacking capability, online schema evolution becomes fully realized for tables in PBR and PBG table spaces, allowing you to fully evolve the partitioning schemes of your Db2 for z/OS tables without the previously required costly application outages.

We think our director of development, Haakon Roberts, summed it up best in his recent LinkedIn post about this new feature:

"This might sound rather dry, but the reality is ANYTHING but: Large tables are partitioned by range using a specified key. Once you've chosen the key to partition by, you have then been locked in to that decision in today's 24x7 environments because there has been no online ability to change to a potentially more suitable partitioning scheme.

This enhancement in FL507 takes what for decades has been an untenable task and now reduces it to some simple DDL followed by an online REORG."

The PTFs for APARs PH64949 and PH64950 introduced the functional code for online conversion from PBR to PBG.

For more information, see Function level 507 - Online conversion of table partitioning from PBR to PBG.

#Db2forz/OS
#Db2Z