Redistributing data across partitions by using REORG

When data becomes skewed across partitions performance can be slower. You can correct the problem by redistributing the data more evenly across partitions. One way to redistribute the data is to let the REORG TABLESPACE utility determine any limit key changes and redistribute the data accordingly.

About this task

Alternatively, you can explicitly specify limit key values. If you want to specify your own limit key values, follow the instructions in Changing the boundary between partitions.

Procedure

To redistribute data across partitions by using REORG:

Run the REORG TABLESPACE utility with the REBALANCE option.

REBALANCE specifies that you want Db2 to determine the limit key changes for the partitioned table space and redistribute the data accordingly. The data remains available.

Restriction: REBALANCE is not allowed in any of the following situations:

With the SCOPE PENDING option
For partitioned-by-growth table spaces
For table spaces with pending limit key changes

See the description of REBALANCE in the description of the REORG TABLESPACE syntax for a complete list of restrictions.

If the table has a clustering index that does not match the partitioning key, you must run REORG TABLESPACE twice. Running REORG twice ensures that the data is rebalanced and all rows are in clustering order. The first utility execution rebalances the data and the second utility execution sorts the data.

For example, assume that you have a table space that was created with the following SQL:

------------------------------------------
    SQL to create a table and index with
    separate columns for partitioning
    and clustering 
------------------------------------------
  CREATE TABLESPACE TS IN DB
        USING STOGROUP SG
        NUMPARTS 4 BUFFERPOOL BP0;
  CREATE TABLE TB (C01 CHAR(5) NOT NULL,
                  C02 CHAR(5) NOT NULL,
                  C03 CHAR(5) NOT NULL)
        IN DB.TS
           PARTITION BY (C01)
              (PART 1 VALUES ('00001'),
               PART 2 VALUES ('00002'),
               PART 3 VALUES ('00003'),
               PART 4 VALUES ('00004'));
  CREATE INDEX IX ON TB(C02) CLUSTER;

To rebalance the data across the four partitions, use the following REORG TABLESPACE control statement:

REORG TABLESPACE DB.TS REBALANCE

After this utility job completes, the table space is placed in advisory REORG-pending (AREO*) status to indicate that a subsequent reorganization is recommended to ensure that the rows are in clustering order. For this subsequent reorganization, use the following REORG TABLESPACE control statement:

REORG TABLESPACE DB.TS