This Db2 for z/OS News from the Lab blog entry was originally published on 2018-08-24.

By John Campbell, Jennie Chang, and Regina Liu.

This post is part of a series that provides John Campbell's perspective on some of the most popular new capabilities in Db2 12 for z/OS.

Db2 12 for z/OS introduces a new partition insertion feature that provides the ability to add a new partition into the middle of an existing partitioned table space. This feature is implemented by a pending ALTER, which can be initiated by using an ALTER TABLE statement with ADD PARTITION ENDING AT and a new limit key value. You must run a REORG TABLESPACE SHRLEVEL CHANGE or REFERENCE to materialize the partition insertion, but you can limit the REORG to only the affected partitions -- in other words, the partition that's almost full and the newly inserted partition. This feature is available only for partition-by-range (PBR) universal table spaces (UTS), but relative page numbering is not required. The table must not have LOB or XML columns.

Prior to this enhancement, inserting a new partition into the middle of a table was a complicated process. To do so, you needed to add a new partition at the end of a table space, and then alter the limit key values for multiple partitions to include the new partition value in the intended location. In addition, you needed to run a REORG on every altered partition. This enhancement provides a quick and direct method to insert the new partition and significantly reduces the total amount of data being reorganized.

Moreover, you do not need to manually manage adjacent partitions that could possibly reach their space limits. Once you determine the limit key for the newly inserted partition, the procedure for handling a "partition full" condition can be easily automated by adding the new partition and then running a REORG against the new and adjacent partitions.

A notable aspect of this feature is that you must translate the logical partition numbers into physical partition numbers. The new physical partition is added at the end of the partitioned table space, and the new logical partition is added in the middle. The logical partitions are then renumbered accordingly. Keep this consideration in mind for Db2 utilities, which operate on partition ranges based on physical partition numbers.

With this more straightforward partition insertion process, you can now modify the partition configuration of tables with greater convenience.

John Campbell is an IBM Distinguished Engineer for Db2 for z/OS development; Regina Liu is a Senior Software Engineer for Db2 for z/OS RDS development; and Jennie Chang is a technical writer for Db2 for z/OS.