Db2 for z/OS: The Online Path from a Partition-by-Growth Table Space to Partition-by-Range

Last month, I posted to this blog an entry on the long-awaited capability to migrate tables, in an online way, from a multi-table segmented or simple table space to multiple single-table partition-by-growth table spaces (a capability delivered with function level 508 of Db2 12 for z/OS). This month, I’ll describe an even newer Db2 feature that also enables online migration of tables from one table space type to another. This feature, introduced with function level 500 of Db2 13 for z/OS, allows a DBA to migrate a table from a partition-by-growth table space to a partition-by-range table space with an ALTER TABLE statement and a subsequent online REORG. Read on to learn more.

The motivation

When the universal partition-by-growth (PBG) table space type was introduced with Db2 9 for z/OS, the response among a lot of DBAs was very enthusiastic, and there was good reason for this: it enabled a table to grow beyond 64 GB in size without having to be range-partitioned. Range-partitioning a table, after all, requires a good bit of up-front analysis. What should be the table’s partitioning key? How many partitions should the table have? What should be the limit key value for each partition? By contrast, a PBG table space has more of a “set it and forget it” quality – you just determine the appropriate DSSIZE value for the table space (the maximum size for a partition of the table space), and a maximum number of partitions (the MAXPARTITIONS specification – easily changed at a later time if need be), and you’re done. If the table space’s DSSIZE value is, for example, 16G (i.e., 16 GB), when partition 1 reaches that size then Db2 will automatically add a second partition for the table space, and when that one hits 16 GB then a third partition will be added by Db2, and so on. Easy.

Ah, but there came to be some “buyer’s remorse” at more than a few Db2 for z/OS sites as certain PBG table spaces got larger and larger. Why? Because the larger a table gets the more advantageous it can be to have the table in a partition-by-range (PBR) table space. I described these PBR versus PBG advantages (for large tables) in an entry I posted to this blog a few years ago. They include potentially greater (maybe much greater) insert throughput, thanks to the ability to have multiple row-inserting processes execute concurrently for different partition of the PBR table space; great suitability for data rows managed on a time-series basis; and maximum partition-level utility independence. Here was the dilemma, though: prior to Db2 13, the only way to get a table from a PBG to a PBR table space was to unload the table, drop the table, re-create the table in a PBR table space, and reload the table’s data. You had, then, this irritating situation: the advantages of PBR versus PBG would be more pronounced as a table got larger, but changing from PBG to PBR was more challenging as a table got larger, due to the unload/drop/re-create/re-load requirement.

Enter Db2 13, and this situation changes, big-time.

The Db2 13 difference

Let’s say you have table BIGTAB in a PBG table space, and you’d really like for BIGTAB to be in a PBR table space. In a Db2 13 system (Db2 13 became generally available on May 31 of this year), with function level 500 (or higher) activated, you can issue the following SQL statement (I have highlighted the new syntax in green, and I am assuming that the ACCT_NUM column of BIGTAB is the desired partitioning key):

ALTER TABLE BIGTAB
  ALTER PARTITIONING TO PARTITION BY RANGE (ACCT_NUM)
    (PARTITION 1 ENDING AT (199),
     PARTITION 2 ENDING AT (299),
     PARTITION 3 ENDING AT (399),
     PARTITION 4 ENDING AT (MAXVALUE));

That ALTER is a pending change. When an online REORG is subsequently executed for BIGTAB’s table space, coming out of that online REORG the BIGTAB table will be in a PBR table space. Done. The table will have the same indexes that it had before, and it’ll be immediately available for access by users and programs.

Some additional information, and considerations

Here are a few things to keep in mind:

• The partitions of the new PBR table space will initially have the same DSSIZE as the PBG table space that’s being migrated to PBR, and that’s kind of important. Here’s why: when you’re determining the partitioning scheme for the new PBR table space you need to consider whether all the rows that belong to a given partition (per the partitioning scheme of the PBR table space) will fit in the partition, given the DSSIZE. Suppose, for example (and continuing with the BIGTAB table referenced previously), that the DSSIZE value for BIGTAB’s PBG table space is 4G, and the number of rows in BIGTAB with an ACCT_NUM value greater than 199 and less than or equal to 299 (i.e., rows that would go into partition 2 of the new PBR table space) will not fit into a 4 GB data set. In that case the online REORG after the ALTER will fail. To avoid that failure, you’d need to either change the partitioning scheme so that the rows assigned to a given partition will fit in a 4 GB data set, or change the DSSIZE value of BIGTAB’s PBG table space to something larger than 4G. If you decide on the latter action (increase the DSSIZE value for BIGTAB’s PBG table space), understand that you’ll need to issue that ALTER for the table space (to go to a large DSSIZE value) and then execute an online REORG to materialize that change and then issue the ALTER to change from PBG to PBR and execute another online REORG to materialize that pending change. Why two online REORGs? Because, when you take action to change a PBG table space to PBR the online way, there can’t be any other outstanding (i.e., not yet materialized) pending changes for the PBG table space – the change to PBR has to be the only pending change for the PBG table space.

• The new PBR table space will use relative page numbering (RPN), which was introduced with Db2 12 for z/OS. This is a very good thing. To see why, check out the blog entry I wrote about RPN a couple of years ago.

• As is very often the case when a pending change is materialized, the online REORG that changes a PBG table space to PBR will invalidate packages dependent on the associated table. You can identify those dependent packages by querying the SYSPACKDEP table in the Db2 catalog.

• This ALTER + online REORG route from PBG to PBR is not available for a table that has an XML or a LOB column.

A closing thought

If you are on Db2 12 and you have some PBG table spaces that you’d like to change – easily, and in an online way – to PBR, the enhancement I’ve described herein could be a good reason for getting your Db2 13 migration project going.

Originally published on Robert’s Db2 Blog.