Rotating partitions
You can use the ALTER TABLE statement to rotate any logical partition to become the last partition. Rotating partitions is supported for partitioned (non-UTS) table spaces and partition-by-range table spaces, but not for partition-by-growth table spaces.
About this task
Nullable partitioning columns: Db2 lets you use nullable columns as partitioning columns. But with table-controlled partitioning, Db2 can restrict the insertion of null values into a table with nullable partitioning columns, depending on the order of the partitioning key. After a rotate operation, if the partitioning key is ascending, Db2 prevents an INSERT of a row with a null value for the key column. If the partitioning key is descending, Db2 allows an INSERT of a row with a null value for the key column. The row is inserted into the first partition.
Procedure
To rotate a partition to be the last partition:
- Issue the ALTER TABLE statement and specify the ROTATE PARTITION option.
- Optional: Run the RUNSTATS utility.
Example
Partition | Limit value | Data set name that backs the partition |
---|---|---|
P008 | 12/31/2004 | catname.DSNDBx.dbname.psname.I0001.A008 |
P009 | 12/31/2005 | catname.DSNDBx.dbname.psname.I0001.A009 |
P010 | 12/31/2006 | catname.DSNDBx.dbname.psname.I0001.A010 |
To rotate the first partition for table TRANS to be the last partition, issue the following statement:
ALTER TABLE TRANS ROTATE PARTITION FIRST TO LAST
ENDING AT ('12/31/2007') RESET;
For a table with limit values in ascending order, the data in the ENDING AT clause must be higher than the limit value for previous partitions. Db2 chooses the first partition to be the partition with the lowest limit value.
For a table with limit values in descending order, the data must be lower than the limit value for previous partitions. Db2 chooses the first partition to be the partition with the highest limit value.
The RESET keyword specifies that the existing data in the first logical partition is deleted, and no delete triggers are activated. Because the oldest (or first) partition is P001, Db2 assigns the new limit value to P001. This partition holds all rows in the range between the new limit value of 12/31/2007 and the previous limit value of 12/31/2006. The RESET operation deletes all existing data. You can use the partition immediately after the ALTER completes. The partition is not placed in REORG-pending (REORP) status, if the table is large, or if the last partition before the rotation is empty.
The following table shows a representation of the table space after the first partition is rotated to become the last partition.
Partition | Limit value | Data set name that backs the partition |
---|---|---|
P002 | 12/31/1997 | catname.DSNDBx.dbname.psname.I0001.A002 |
P003 | 12/31/1998 | catname.DSNDBx.dbname.psname.I0001.A003 |
P004 | 12/31/1999 | catname.DSNDBx.dbname.psname.I0001.A004 |
P005 | 12/31/2000 | catname.DSNDBx.dbname.psname.I0001.A005 |
P006 | 12/31/2001 | catname.DSNDBx.dbname.psname.I0001.A006 |
P007 | 12/31/2002 | catname.DSNDBx.dbname.psname.I0001.A007 |
P008 | 12/31/2003 | catname.DSNDBx.dbname.psname.I0001.A008 |
P009 | 12/31/2004 | catname.DSNDBx.dbname.psname.I0001.A009 |
P010 | 12/31/2005 | catname.DSNDBx.dbname.psname.I0001.A010 |
P011 | 12/31/2006 | catname.DSNDBx.dbname.psname.I0001.A011 |
P001 | 12/31/2007 | catname.DSNDBx.dbname.psname.I0001.A001 |