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.

Start of change

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.

End of change

Procedure

To redistribute data in partitioned table spaces, use one of the following two methods:

Example

Begin general-use programming interface information.

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');

Partitions 3, 4, and 5 are placed in REORG-pending (REORP) status. Those partitions are not available until they are reorganized.

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) REBALANCE

In this case, DB2® determines the appropriate limit key changes and redistributes the data accordingly. This method avoids leaving the partitions in REORP status.

End general-use programming interface information.