Changing the boundary between partitions

You can change the boundary of a partition by explicitly specifying a new value for the limit key. The limit key is the highest value of the partitioning key for a partition. The partitioning key is the column or columns that are used to determine the partitions.

About this task

Alternatively, you can let DB2® determine any appropriate limit key changes to more evenly distribute the data across partitions. If you want DB2 to determine any limit key changes, follow the instructions in Redistributing data across partitions by using REORG.

Procedure

To change the boundary between partitions:

  1. Use an ALTER statement to modify the limit key value for each partition boundary that you want to change.

    If the partitioned table space uses table-controlled partitioning, use an ALTER TABLE statement with the ALTER PARTITION clause to alter the limit key. If the partitioned table space uses index-controlled partitioning, use an ALTER INDEX statement with the ALTER PARTITION clause.

    You can change the limit key values of all or most of the partitions. You can apply the changes to one or more partitions at a time, which allows for relatively small parts of the data to be unavailable at a time.

    After you alter the limit keys, the partitions on either side of the boundary are placed in REORG-pending (REORP) status. This status means that the data is unavailable until the affected range of partitions are reorganized.

  2. Run the REORG TABLESPACE utility to redistribute data in the partitioned table space based on the new limit key values.

    This action also resets the REORP status and makes the data available. The following example specifies options that help maximize performance while reorganizing the data: 

    REORG TABLESPACE DSN8S10E PART 2:3
  NOSYSREC  COPYDDN SYSCOPY  STATISTICS TABLE INDEX(ALL)

    This example reorganizes a range of partitions and includes the STATISTICS keyword, which means that REORG collects statistics about the specified range of partitions.

    You can reorganize a range of partitions, even if the partitions are not in REORP status. However, you cannot reorganize only a subset of the range of partitions that are in REORP status. You must reorganize the entire range to reset the restrictive status.

    If you run REORG on partitions that are in REORP or advisory REORG-pending (AREOR) status, consider the values that you set for the following options:

    SHRLEVEL
    Start of changeYou can specify SHRLEVEL REFERENCE or SHRLEVEL CHANGE when objects are in the REORP status. REORG materializes any pending definition changes at the table space level and for the indexes.End of change
    KEEPDICTIONARY
    REORG ignores the KEEPDICTIONARY option for any partition that is in REORP status. REORG automatically rebuilds the dictionaries for the affected partitions. However, if you specify a range of partitions that includes some partitions that are not in REORP status, REORG accepts the KEEPDICTIONARY option for those nonrestricted partitions.
    DISCARDDN and PUNCHDDN
    Specify the DISCARDDN and PUNCHDDN data sets when the limit key for the last partition was reduced for a table space that is defined as LARGE or DSSIZE. Otherwise, REORG terminates and issues message DSNU035I and return code 8.

    REORG writes SYSCOPY records as follows:

    • If any partition is in REORP status when REORG runs, DB2 writes a SYSCOPY record with STYPE=A for each partition that is specified on the REORG job.
    • If you take an inline image copy of a range of partitions, DB2 writes one SYSCOPY record with ICTYPE=F for each partition. Each record has the same data set name.