April 04, 2017
Designing and sizing table space requires DBAs to make numerous determinations. How many rows per day will be inserted/updated/deleted? How will the data be processed? What is the criteria to determine active versus historical data? How long will the data (active and historical) need to be stored before it will be deleted?
Robert Catterall has written extensively about this. Even though this post
dates back to 2009, the information remains relevant to today’s DBA. Frank Fillmore authors another good read on large table design options.
Still, table design is more than just figuring out the answers. Requirements change over time, and your table space may very well need to change with it. And in the case of partitioned table spaces, you may need to periodically adjust the partitioning key definition, which determines what rows are stored in each table space partition.
This IBM Knowledge Center document
explains how you can add partitions to the end of the table space. Say you currently have 10 partitions numbered 1 through 10. After adding a partition, you now have 11 physical partitions numbered 1 through 11.
This scenario is great?provided you're adding more data sequentially. If, however, you have a middle partition (say, no. 6) that is growing much larger than the other partitions, it would be best?in terms of saving time and CPU costs?to simply split that partition into two parts and only REORG them. This wasn't possible prior to DB2 12. You could only change the partitioning keys and REORG all the partitions with new key values. The process of changing the boundary between partitions is explained here
DB2 12, though, offers an important enhancement in this area. Now you can add a partition between two logical partitions. This greatly simplifies the process and reduces the overall CPU and time to complete the process.
The new physical partition will inherit its attributes from the new SYSIBM.SYSTABLESPACE fields: e.g. COMPRESS and TRACKMOD. If these fields are null, then the inheritance will come from the last logical partition. The ALTER will be immediate when DEFINE NO and no physical dataset exists; otherwise, the ALTER will be pending and will require a REORG to materialize the change. If the ALTER is pending, the ALTER ADD PARTITION (to the end of the partition) and ROTATE PARTITION options aren't allowed. The logical partitions will be renumbered, and the new physical partition will be added at the end.
You can accomplish an insert partition using two different syntax options:
1) Specify the high limit key (X) of the newly inserted table space:
ALTER table ADD PARTITION ENDING AT (X)
2) Specify the high limit key (X) of the newly inserted partition and include the high limit key (Y) of the existing partition with (N) as the physical partition number for the subsequent logical partition:
ALTER table ADD PARTITION ENDING AT (X) ALTER PARTITION (N) ENDING AT (Y)
A couple of other considerations when running the materializing REORG: The REORG will put a drain at the table space level during materialization time instead of at the partition level. During the alter pending, these new columns are updated in SYSIBM.SYSPENDINGDDL: REORG_SCOPE_LOWPART and REORG_SCOPE_HIGHPART. You should query this table for the low partition and high partition numbers affected by the alteration. Use this information during the REORG at a partition level to limit the number of parts that need to be processed. You should also be aware that dependent packages and dynamic statement cache will be invalidated. Again, the Knowledge Center has complete details
Posted April 04, 2017 | Permalink