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