Changing Table Space Partitioning
Historically, one of the biggest impediments to managing DB2
database systems has been administering partitioned table spaces. Prior to DB2
V8, it was either difficult or impossible to modify the structure and many of
the parameters of a partitioned table space. Exacerbating this problem is the
fact that most partitioned table spaces are the largest, most critical, table
spaces in the system with the highest availability requirements. Fortunately,
DB2 V8 removes many of the barriers to managing partitioned table spaces.
As of DB2 V8 you can add partitions, rotate partitions, and change
the partitioning key values. In order to gain this flexibility, though, you will
need to change from index-controlled partitioning to table-controlled
partitioning. Then, you can use the ALTER TABLE statement to modify most of the
To add a partition to an existing table space the ALTER
TABLE statement has been augmented with the ADD PART parameter. For example,
consider a table space that is partitioned having one fiscal quarter worth of
data per partition. Eventually, you might run out of partitions and need to add
one. Assume that the last partition holds data up to the third quarter of 2004,
but now you need to add data past this date. Assuming that your fourth quarter
ends in December, the following SQL uses ALTER to add the new partition to the
ALTER TABLE CREATOR.TBNAME
You won't specify a partition number when you add a partition. DB2
will determine the next partition number to be used by examining information in
the DB2 Catalog. You can add partitions up to the maximum limit; the maximum
number of partitions depends on the DSSIZE parameter and page size of the table
You cannot specify attributes like PRIQTY and SECQTY; instead, DB2
uses the values in use for the previous logical partition. Before you begin to
use the new partition, you might want to alter the tablespace to provide
accurate space parameters for the new partition.
Each newly added partition will be immediately available for use,
but you must stop the table space and partitioned index before adding the
If the requirement to add a partition can be satisfied by allowing
an existing partition to be reused, you might be able to rotate the
partition. Rotating partitions allows old data to “roll off,” but the
partition is kept for new data. This is a good option any time old data is
periodically archived and only a limited number of partitions need to be active.
Partition rotation is implemented using ALTER TABLE with the ALTER
PART ROTATE FIRST TO LAST parameter. When rotating, if you specify the RESET
parameter, the data rows in the oldest (or logically first) partition are
deleted and a new table space high boundary is set so that partition becomes the
last logical partition in sequence. This partition will then be ready to hold
the new data as it is added. The partition that was rolled off is immediately
available after the ALTER succeeds; a REORG is not required.
The aftermath of rotating a partition can be confusing. This is
especially the case if you are trying to match partitions to physical data sets.
The .A001 data set is now the last logical partition, not the first. You will
need to use the new LOGICAL_PART column in the SYSTABLEPART table to match
partitions to data sets. Additionally, you can use the DISPLAY command to list
the status of table space partitions by logical partition.
Also, steps need to be taken if you need to keep the rolled off
data for archival purposes. Be sure to unload the data immediately before
rotating the partition using either an UNLOAD utility or a user-written program.
You can change partition boundaries relatively easily, too. DB2 V6
introduced the ability to modify limit keys for partitions. DB2 V8 adds the same
capability for table-based partitioning with the ALTER PART VALUES parameter of
ALTER TABLE . The affected data partitions are placed into the REORP
pending state until they have been reorganized.
Finally, you can rebalance partitions when running DB2 V8. Unlike
the schema changes previously discussed, partition rebalancing is accomplished
using the REORG utility with the new REBALANCE parameter. During the REORG, DB2
will rebalance the data such that it is evenly distributed across the
partitions. Rebalancing is most practical when the data isn't greatly skewed.
The REORG utility will set new partition boundaries so that all
the rows participating in the reorganization are evenly distributed across the
partitions being reorganized. DB2 will update the SYSTABLEPART and SYSINDEXPART
tables to record the new limit key values. The values previsously set in the DDL
are no longer valid -- so don't try to reference them there.
DB2 V8 greatly simplifies the maintenance of partitioned tablespaces. Be sure
you understand the options at your disposal when you move to V8 to reduce the
administrative burden of partitioning.