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.

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.

   Recommendation: If the table space uses index-controlled partitioning, alter it to use table-controlled partitioning before you alter the limit key. You can follow the example in Converting table spaces to use table-controlled partitioning.

   If you attempt to alter a limit key by using ALTER TABLE, the statement fails if both of the following conditions are true:

   • The table space uses index-controlled partitioning.
   • The PREVENT_ALTERTB_LIMITKEY subsystem parameter is set to YES.

   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.

   The effect of altering the limit keys depends on the type of table space:

   • For partition-by-range table spaces and partitioned (non-UTS) table spaces with table-controlled partitioning, the data remains available after the limit keys are altered.

     In most cases, altering the limit keys for those table spaces is a pending definition change that causes the partitions on either side of the boundary to be placed in advisory REORG-pending (AREOR) status.

     In some cases, a change to a limit key value is immediately materialized, and AREOR status is not set. Immediate materialization occurs when Db2 determines that both of the following conditions are true:

     
     • The alteration does not move any data between partitions
     • No other pending definition change exists on the identified partition
     • No pending definition changes exist on the adjacent logical partition toward which the alteration moves the limit key of the identified partition.
     
   • For partitioned (non-UTS) table spaces with index-controlled partitioning, altering the limit keys is an immediate definition change. In these cases, the partitions on either side of the boundary are placed in REORG-pending (REORP) status, and 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 any AREOR or REORP states. The following example specifies options that help maximize performance while reorganizing the data:

   REORG TABLESPACE DSN8S12E 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 AREOR or REORP status. However, you cannot reorganize only a subset of the range of partitions that are in AREOR or REORP status. You must reorganize the entire range to reset the restrictive status and materialize any pending limit key changes.

   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

   You can specify SHRLEVEL REFERENCE or SHRLEVEL CHANGE when objects are in AREOR or REORP status. REORG materializes any pending definition changes. If you specify SHRLEVEL NONE, REORG does not materialize any pending limit key changes and any restrictive states are not reset.

   KEEPDICTIONARY

   REORG ignores the KEEPDICTIONARY option for any partition that is in REORP or AREOR 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.

   If REORG materialized any pending limit key changes, the related plans and packages are invalidated.