DB2 creates a separate compression dictionary for each table space partition. Multiple dictionaries tend to cause better overall compression ratios. In addition, it’s more likely the partition-level compression dictionaries can be rebuilt more frequently than non-partitioned dictionaries. Frequent rebuilding of the compression dictionary can lead to a better overall compression ratio.
The REORG, COPY, and RECOVER utilities can execute on table spaces at the partition level. If these utilities are set to execute on partitions instead of on the entire table space, valuable time can be saved by processing only the partitions that need to be reorganized, copied, or recovered. Partition independence and resource serialization further increase the availability of partitions during utility processing.
Modifying partitioning details, changing partition key ranges, and rotating partitions is more flexible than ever with online schema change support.
Disadvantages of Partitioning
Only one table can be defined in a partitioned table space. This isn’t really a disadvantage, merely a limitation.
Prior to DB2 V8, updating the partitioning key was problematic. Before the limit key values could be updated, the PARTKEYU DSNZPARM parameter had to be set to YES. And if updates were allowed, it was likely they ran slowly. If PARTKEYU was set to NO, you had to DELETE the row and then re-INSERT it to change a value in a column of a partitioning key. But things have changed. As of V8, a partitioning index is no longer required. Furthermore, any partitioning key can be updated without worrying about the PARTKEYU parameter, which is no longer even supported.
The range of key values for which data will be inserted into the table should be known and stable before you create and define the limit keys for the partitioning index. These ranges should distribute the data throughout the partitions according to the access needs of the applications using the data. If you provide a stop-gap partition to catch all the values lower (or higher) than the defined range, monitor that partition to ensure it doesn’t grow dramatically or cause performance problems if it’s smaller or larger than most other partitions.
More and more of your DB2 table spaces will be partitioned because of the advent of universal table spaces and the deprecation of support for simple table spaces. Learn the benefits of partitioning and use them to your advantage.