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:
- 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.
- 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
You
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](KC_ROOT/images/ddita/deltaend.gif)
- 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.