Splitting the last partition into two
To allow for future growth, you can truncate the last partition of a table space and move some of the data into a new partition.
About this task
If you truncate a partition and some of the data could fall outside of the new boundary that is defined by the limit key value, the partition is placed in REORG-pending (REORP) status.
You can reset the advisory REORG-pending or REORG-pending status in one of the following ways:
- Run REORG with the DISCARD option to reset the REORG-pending status, set the new partition boundary, and discard the data rows that fall outside of the new boundary.
- Add a partition for the data rows that fall outside of the current partition boundaries.
The topic describes the procedure for the second choice.
Procedure
- Issue the ALTER TABLE statement with the ALTER PARTITION clause to specify a new boundary for the last partition. For more details on this process, see Changing the boundary between partitions.
- Issue the ALTER TABLE statement with the ADD PARTITION clause to add a partition.
- Issue the REORG TABLESPACE utility on the new second-to-last and last partitions to remove the REORG-pending status.
Example
![Begin general-use programming interface information.](../cmn/../art/gupi_opn.gif)
For example, the following table shows a representation of a table space through the year 2015, where each year of data is saved in separate partitions. Assume that you want to split the data for 2015 into two partitions.
You want partition P001 to include only the data for the first six months of 2015 (through 06/30/2015). Then, you want to create a partition to include the data for the last six months of 2015 (from 07/01/2015 to 12/31/2015).
Partition | Limit value | Data set name that backs the partition |
---|---|---|
P002 | 12/31/2005 | catname.DSNDBx.dbname.psname.I0001.A002 |
P003 | 12/31/2006 | catname.DSNDBx.dbname.psname.I0001.A003 |
P004 | 12/31/2007 | catname.DSNDBx.dbname.psname.I0001.A004 |
P005 | 12/31/2008 | catname.DSNDBx.dbname.psname.I0001.A005 |
P006 | 12/31/2009 | catname.DSNDBx.dbname.psname.I0001.A006 |
P007 | 12/31/2010 | catname.DSNDBx.dbname.psname.I0001.A007 |
P008 | 12/31/2011 | catname.DSNDBx.dbname.psname.I0001.A008 |
P009 | 12/31/2012 | catname.DSNDBx.dbname.psname.I0001.A009 |
P010 | 12/31/2013 | catname.DSNDBx.dbname.psname.I0001.A010 |
P011 | 12/31/2014 | catname.DSNDBx.dbname.psname.I0001.A011 |
P001 | 12/31/2015 | catname.DSNDBx.dbname.psname.I0001.A001 |
To truncate partition P001 to include data only through 06/30/2015, issue the following statement:
ALTER TABLE TRANS ALTER PARTITION 1 ENDING AT ('06/30/2015');
Next, to create a partition that holds the remainder of the year 2015, issue the following statement:
ALTER TABLE TRANS ADD PARTITION ENDING AT ('12/31/2015');
The following table shows a portion of the table space and the modified partitions:
Partition | Limit value | Data set name that backs the partition |
---|---|---|
P011 | 12/31/2014 | catname.DSNDBx.dbname.psname.I0001.A011 |
P001 | 06/30/2015 | catname.DSNDBx.dbname.psname.I0001.A001 |
P012 | 12/31/2015 | catname.DSNDBx.dbname.psname.I0001.A012 |
![End general-use programming interface information.](../cmn/../art/gupi_cls.gif)