IBM Db2 Administration Tool: Insert partition
Introduction
IBM Db2 12 function level 500 introduced the ability to add partitions between existing logical partitions for partition-by-range universal table spaces (UTS PBR).
Prior to Db2 12 FL500 this operation required a unload, drop, recreate, load, rebind, and so on. With Db2 12 FL500 this is now a non-disruptive change: ALTER TABLE .. ADD PARTITION followed by a materializing REORG.
IBM Db2 Administration Tool and Db2 Object Comparison Tool (AOC) supports this new function.
Ad-hoc addition of partition
Using the AOC ISPF panels you can use the line command “AL” for a table to add partitions
Select “ADD/INSERT/ALTER PARTITION” add you’ll be presented with the current partitioning:
Use the INS (or just I) to insert a new partition (step 1) and then enter the new limit key (step 2)
Use primary command “NEXT” (or click on NEXT) to generate the ALTER statement. With prompting on you will be prompted to run the ALTER:
Selecting option 1 will execute the DDL statement immediately.
The ALTER returns a SQLCODE +610 to indicate that a materializing REORG is required. Db2 Admin Tool will prompt you to run the REORG:
Press enter to generate a REORG job.
Deployment of inserted partitions
AOC can compare various source of Db2 objects and generally deploy changes to a target in the least disruptive manner.
In this case we are comparing DDL vs DDL, but AOC also supports DDL vs catalog, catalog vs catalog, and several object scenarios.
Here is the source and target DDL side-by-side showing a new partition has been added:
AOC will detect that the partition can be added
And the WSL will reflect that the change can be implemented using an ALTER followed by REORG
#Db2Toolsforz/OS