Rotate partitions in DB2 on z

轻轻的

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-universal) table spaces and range-partitioned table spaces, but not for partition-by-growth table spaces.



About this task


Recommendation: 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. 　　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


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

PartitionLimit valueData 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.



Table 2. Rotating the first partition to be the last partition

PartitionLimit valueData 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

• Parent topic: Altering partitions
  

Related tasks:

• Changing the boundary between partitions
  
• Extending the boundary of the last partition
  
• Splitting the last partition into two
  
• Inserting rows at the end of a partition