REBALANCE option considerations
In addition to the considerations described in the previous sections, the following additional considerations apply to use of the REBALANCE option.
DSNUTILB considerations
The following DSNUTILB considerations apply to the REBALANCE option:
REORG PLUS invokes DSNUTILB when you specify REBALANCE and all of the following conditions exist:
- You are reorganizing a table space that contains a LOB column.
- You specify SHRLEVEL REFERENCE.
- You do not specify the AUX option on the REORG command.
- When invoking DSNUTILB, REORG PLUS ignores the REBALANCE option for partition-by-growth or nonpartitioned table spaces.
For more information about running a DSNUTILB reorganization, see Reorganization-jobs-that-invoke-DSNUTILB.
Specifying partitions
Use care when specifying partitions. REORG PLUS rebalances partitions by logical partition number, rather than physical partition number. If contiguous physical partitions are not contiguous by logical partition number, REORG PLUS cannot rebalance them. The following example illustrates this consideration.
A table space has four partitions. The logical and physical partition numbers match.
Logical partition number | Physical partition number |
|---|---|
1 | 1 |
2 | 2 |
3 | 3 |
4 | 4 |
You then alter the table space to rotate the first partition to last, resulting in the following mapping of logical and physical partitions:
Logical partition number | Physical partition number |
|---|---|
1 | 2 |
2 | 3 |
3 | 4 |
4 | 1 |
Next, you add a partition to the table space, resulting in the following mapping of logical and physical partitions:
Logical partition number | Physical partition number |
|---|---|
1 | 2 |
2 | 3 |
3 | 4 |
4 | 1 |
5 | 5 |
Finally, you rotate the first partition to last a second time, resulting in the following mapping of logical and physical partitions:
Logical partition number | Physical partition number |
|---|---|
1 | 3 |
2 | 4 |
3 | 1 |
4 | 5 |
5 | 2 |
If you then try to reorganize the table space and rebalance partitions 1:3, the job fails because these partitions are not logically contiguous. Their logical partition numbers are 3, 5, and, 1 respectively.
Rebalancing partitions when the clustering index does not match the partitioning key
If a partitioned table has a clustering index that is not the partitioning index and you run a reorganization that specifies REBALANCE on the table space, the rows will no longer be in clustering order and all reorganized partitions will be placed in AREO* status. To put the rows back in clustering order and reset the AREO* status, you must run another reorganization without specifying REBALANCE. The following example illustrates this consideration.
The following SQL creates a table space, a table, and an index with different columns for partitioning and clustering.
USING STOGROUP <storageGroup>
NUMPARTS 4 BUFFERPOOL BP0;
CREATE TABLE <tableSpaceName> (C01 CHAR(5) NOT NULL,
C02 CHAR(5) NOT NULL,
C03 CHAR(5) NOT NULL)
IN <databaseName>.<tableSpaceName>
PARTITION BY (C01)
(PART 1 VALUES ('00001'),
PART 2 VALUES ('00002'),
PART 3 VALUES ('00003'),
PART 4 VALUES ('00004'));
CREATE INDEX <indexName> ON <tableSpace>(C02) CLUSTER;
For the first table space reorganization, you would use the following syntax to rebalance the data across the four partitions:
The partitions are placed in AREO* status after the reorganization completes. This status indicates that you should run another reorganization to order the rows in clustering order. You would use the following syntax for the second reorganization:
Related topics