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

The results of truncating the last partition in a partitioned table space depend on the table space type, whether there is any possibility that data could fall outside the truncated partition, and whether the limit key value of the last partition before truncation is MAXVALUE, MINVALUE, less than MAXVALUE, or greater than MINVALUE.

If the partition that you truncate is empty or there is no possibility that data could fall outside of the new boundary, and the last partition and the previous partition have no pending definition changes, the definition change occurs immediately. No restrictive or pending status is necessary.

For all other cases, the following table shows the results of truncating the last partition.

Table 1. Results of truncating the last partition
Table space type Limit key of last partition is <MAXVALUE or >MINVALUE Limit key of last partition is MAXVALUE or MINVALUE
A partitioned table space with table-controlled partitioning The change is a pending definition change. The affected partition is placed in advisory REORG-pending (AREOR) status. The data is still available, but the definition change is not materialized until REORG is run. The change is an immediate definition change. The affected partition is placed in REORG-pending (REORP) status. The data in the partition is unavailable until REORG is run.
Any partitioned table space with index-controlled partitioning The change is an immediate definition change. The affected partition is placed in REORG-pending (REORP) status. The data in the partition is unavailable until REORG is run. The change is an immediate definition change. The affected partition is placed in REORG-pending (REORP) status. The data in the partition is unavailable until REORG is run.

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 advisory REORG-pending status or 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.

The following steps assume that the data is in ascending order. The process is similar if the columns are in descending order.

Procedure

  • To split a partition into two when the limit key of the last partition is less than MAXVALUE:
    1. Suppose that p1 is the limit key for the last partition. Issue the ALTER TABLE statement with the ADD PARTITION clause to add a partition with a limit key that is greater than p1.
    2. Issue the ALTER TABLE statement with the ALTER PARTITION clause to specify a limit key that is less than p1 for the partition that is now the second-to-last partition.
      For more details on this process, see Changing the boundary between partitions.
    3. Issue the ALTER TABLE statement with the ALTER PARTITION clause to specify p1 for the limit key of the new last partition.
    4. Issue the REORG TABLESPACE utility on the new second-to-last and last partitions to remove the REORG-pending status on the last partition, and materialize the changes and remove the advisory REORG-pending status on the second-to-last partition.
  • To split a partition into two when the limit key of the last partition is MAXVALUE, and the last partition and the previous partition have no pending definition changes:
    1. Issue the ALTER TABLE statement with the ALTER PARTITION clause to specify limit key p1, which is less than MAXVALUE, 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 new last partition, with a limit key that is greater than p1.
    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 to create a partition to include the data for the last six months of 2015 (from 07/01/2015 to 12/31/2015). You also want partition P001 to include only the data for the first six months of 2015 (through 06/30/2015).

Table 2. 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 create a new last partition, issue the following statement:

ALTER TABLE TRANS ADD PARTITION ENDING AT ('01/31/2016');

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');

To preserve the last partition key limit of 12/31/2015, issue the following statement: 

ALTER TABLE TRANS ALTER PARTITION 12 ENDING AT ('12/31/2015');

The following table shows a portion of the table space and the modified partitions:

Table 3. 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.