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

To split a partition into two:
  1. 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.
  2. Issue the ALTER TABLE statement with the ADD PARTITION clause to add a partition.
  3. 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.

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).

Table 1. Table space with each year of data in a separate partition
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:

Table 2. Table space with one year split into two 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.