Redistributing data in partitioned table spaces
When data becomes skewed across partitions, performance can be slower. For example, data is skewed if some partitions are almost full while other partitions have a considerable amount of excess space. Performance might improve if you can redistribute the data more evenly across the partitions.
About this task
Redistributing data in partitioned table spaces is not always possible because of application dependencies or other factors. If a partition is full and redistributing the data is not practical, you might need to increase the partition size.
Procedure
To redistribute data in partitioned table spaces, use one of the following two methods:
- Change the partition boundaries.
- Redistribute the data across partitions by using the REORG TABLESPACE utility.
Example
Assume that a table space contains product data that is partitioned by product ID as follows: The first partition contains rows for product ID values 1 through 99. The second partition contains rows for values 100 to 199. The third partition contains rows for values 200 through 299. And the subsequent partitions are empty.
Suppose that after some time, because of the popularity of certain products, you want to redistribute the data across certain partitions. You want the third partition to contain values 200 through 249, the fourth partition to contain values 250 through 279, and the fifth partition to contain values 280 through 299.
To change the boundary for these partitions, issue the following statements:
ALTER TABLE PRODUCTS ALTER PARTITION 3
ENDING AT ('249');
ALTER TABLE PRODUCTS ALTER PARTITION 4
ENDING AT ('279');
ALTER TABLE PRODUCTS ALTER PARTITION 5
ENDING AT ('299'); Assume that the table is a partition-by-range table space or a partitioned (non-UTS) table space with table-controlled partitioning. Partitions 3, 4, and 5 are placed in advisory REORG-pending (AREOR) status. The data remains available. However, the changes are pending and are not materialized until you run REORG TABLESPACE.
Alternatively, instead of using ALTER TABLE statements with the ALTER PARTITION clause, you can use the REBALANCE keyword as follows:
REORG TABLESPACE dbname.tsname PART(3:5) REBALANCEIn this case, Db2 determines the appropriate limit key changes and redistributes the data accordingly.
