Automatic conversion to table-controlled partitioning
Some index operations cause DB2® to automatically convert an index-controlled partitioned table space to a table-controlled partitioned table space.
- Use the CREATE INDEX statement with the PARTITIONED clause to create a partitioned index on an index-controlled partitioned table space.
- Use the CREATE INDEX statement with a PART VALUES clause and without
a CLUSTER clause to create a partitioning index.
DB2 stores the specified high limit key value instead of the default high limit key value.
- Use the ALTER INDEX statement with the NOT CLUSTER clause on a partitioning index that is on an index-controlled partitioned table space.
- Use the DROP INDEX statement to drop a partitioning index on an index-controlled partitioned table space.
Use the ALTER TABLE statement to add
a new partition, change a partition boundary, or rotate a partition
to last on an index-controlled partitioned table space.
In these cases, DB2 automatically converts to table-controlled partitioning, but does not automatically drop any indexes. DB2 assumes that any existing indexes are useful.
After the conversion to table-controlled partitioning, DB2 changes the existing high-limit key value for non-large table spaces to the highest value for the key. DB2 enforces the high-limit key value. By default, DB2 does not put the last partition of the table space into a REORG-pending (REORP) state. Exceptions to this rule are:
- When adding or rotating a new partition, DB2 stores the original high-limit
key value instead of the default high-limit key value. DB2 puts the last partition into a REORP state,
unless the high-limit key value is already being enforced, or the
last partition is empty.
- When altering a partition that converts a
table space from index-controlled partitioning to table-controlled
partitioning, DB2 changes the
existing high-limit key to the highest value that is possible for
the data types of the limit key columns. After the conversion to table-controlled
partitioning, DB2 changes the
high-limit key value back to the user-specified value and puts the
last partition into a REORP state.
After the conversion to table-controlled partitioning, the SQL statement that created the partitioning index is no longer valid. For example, after dropping a partitioning index on an index-controlled partitioned table space, an attempt to re-create the index by issuing the same CREATE INDEX statement that you originally used would fail. This failure happens because the boundary partitions are now under the control of the table.
You can use the IX_TB_PART_CONV_EXCLUDE
subsystem parameter to specify whether to exclude trailing columns
from the table-controlled partitioning keys when table spaces are
converted from index-controlled partitioning to table-controlled partitioning.
The default value is YES, which means the DB2 subsystem
excludes trailing columns from the original partitioning key in the
definition of the new partitioning key. The trailing columns are the
columns that do not affect the partitioning. Trailing columns have
all X'FF' values in the LIMITKEY column value of the SYSIBM.SYSINDEXPART
catalog table. A value of NO means the DB2 subsystem
uses all columns of the original partitioning index to define the
new partitioning key.
The IX_TB_PART_CONV_EXCLUDE
subsystem parameter does not affect the automatic conversion to table-controlled
partitioning in the following cases:
- When issuing the ALTER TABLE statement to add a new partition, change a partition boundary, or rotate a partition to last on an index-controlled partitioned table space, In these cases, the DB2 subsystem uses all columns of the original partitioning index to define the new partitioning key.
- When issuing the CREATE INDEX statement with a PART VALUES clause and without a CLUSTER clause. In this case, the DB2 subsystem uses the partitioning key that is specified by the CREATE INDEX statement.
