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

Recommendation: Begin general-use programming interface information.When you create a partitioned table space, you do not need to allocate extra partitions for expected growth. Instead, you can use the ALTER TABLE ADD PARTITION statement to add partitions as needed. If rotating partitions is appropriate for your application, use the ALTER TABLE ROTATE PARTITION statement to avoid adding another partition. End general-use programming interface information.

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

Begin general-use programming interface information.To rotate a partition to be the last partition:

  1. Issue the ALTER TABLE statement and specify the ROTATE PARTITION option.
  2. Optional: Run the RUNSTATS utility. End general-use programming interface information.

Example

For example, assume that the partition structure of the table space is sufficient through the year 2006. The following table shows a representation of the table space through the year 2006. When another partition is needed for the year 2007, you determined that the data for 1996 is no longer needed. You want to recycle the partition for the year 1996 to hold the transactions for the year 2007.
Table 1. An excerpt of a partitioned table space
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

Begin general-use programming interface information.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.End general-use programming interface information.

The following table shows a representation of the table space after the first partition is rotated to become the last partition.

Table 2. Rotating the first partition to be 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