Converting table spaces to use table-controlled partitioning

Before you can convert a partitioned (non-UTS) table space that uses index-controlled partitioning to a partition-by-range table space, you must convert it to use table controlled partitioning. Table spaces that use index-controlled partitioning, like all non-UTS table spaces are deprecated.

About this task

For partitioned (non-UTS) table spaces, Db2 supports both table-controlled partitioning and index-controlled partitioning. Table-controlled partitioning provides more options and flexibility compared to index-controlled partitioning. Index-controlled partitioning requires partitioning index to control the partitioning.

Non-UTS table spaces for base tables are deprecated and likely to be unsupported in the future.

Table 1. Differences between table-controlled and index-controlled partitioning

Table-controlled partitioning	Index-controlled partitioning
Requires no partitioning index or clustering index.	Requires a partitioning index and a clustering index.
Multiple partitioned indexes can be created in a table space.	Only one partitioned index can be created in a table space.
A table space partition is identified by both a physical partition number and a logical partition number.	A table space partition is identified by a physical partition number.
The high-limit key is always enforced, which means that key values that are out of range might result in errors or discarded data, depending on the operation involved.	The high-limit key is not enforced for a non-large table space.

The conversion is also required before you can convert a partitioned (non-UTS) table space, to a partition-by-range table space.

Learn more: For comprehensive background, how-to information, and examples for various paths for converting your deprecated classic partitioned (non-UTS) table spaces to partition-by-range table spaces, see the white paper Conversion from index-controlled partitioning to Universal Table Space (UTS).

Certain index operations always cause Db2 to automatically convert an index-controlled partitioned table space to a table-controlled partitioned table space. To convert a table that uses index-controlled partitioning to use table-controlled partitioning, use one of the following actions:

• Issue an ALTER INDEX statement with the NOT CLUSTER clause on the partitioning index.
  For example, you can issue the following two ALTER INDEX statements in the same commit scope to convert a table to use table-controlled partitioning and reactivate clustering for the index.

  ALTER INDEX index-name NOT CLUSTER;

  Db2 issues SQLCODE +20272 to indicate that the associated table space no longer uses index-controlled partitioning .

  ALTER INDEX index-name CLUSTER;

  This statement reactivates explicit clustering for the index.
• Issue an 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.
• Issue a CREATE INDEX statement that specifies a PARTITIONED clause to create a partitioned index on the table space.
• Issue a DROP INDEX statement to drop the partitioning index.
  When you drop a partitioning index, Db2 automatically converts the associated index-controlled partitioned table space to a table-controlled partitioned table space.

Example

For example, assume that you have a very large transaction table named TRANS that contains one row for each transaction. The table includes the following columns:

• ACCTID is the customer account ID
• POSTED is the date of the transaction

The table space that contains TRANS is divided into 13 partitions, each containing one month of data. Two existing indexes are defined as follows:

• A partitioning index is defined on the transaction date by the following CREATE INDEX statement with a PARTITION ENDING AT clause: The partitioning index is the clustering index, and the data rows in the table are in order by the transaction date. The partitioning index controls the partitioning of the data in the table space.
• A nonpartitioning index is defined on the customer account ID:

  CREATE INDEX IX2 ON TRANS(ACCTID);

Db2 usually accesses the transaction table through the customer account ID by using the nonpartitioning index IX2. The partitioning index IX1 is not used for data access and is wasting space. In addition, you have a critical requirement for availability on the table, and you want to be able to run an online REORG job at the partition level with minimal disruption to data availability.

1. Drop the partitioning index IX1.

   DROP INDEX IX1;

   When you drop the partitioning index IX1, Db2 converts the table space from index-controlled partitioning to table-controlled partitioning. Db2 changes the high limit key value that was originally specified to the highest value for the key column.

2. Create a partitioned clustering index IX3 that matches the 13 data partitions in the table, as a replacement for IX2.

   CREATE INDEX IX1 ON TRANS(POSTED)
      CLUSTER
      (PARTITION 1  ENDING AT ('01/31/2002'),
       PARTITION 2  ENDING AT ('02/28/2002'),
       ...
       PARTITION 13 ENDING AT ('01/31/2003'));

   When you create the index IX3, Db2 creates a partitioned index with 13 partitions that match the 13 data partitions in the table. Each index partition contains the account numbers for the transactions during that month, and those account numbers are ordered within each partition. For example, partition 11 of the index matches the table partition that contains the transactions for November, 2002, and it contains the ordered account numbers of those transactions.

3. Drop index IX2,which was replaced IX3.

   
   DROP INDEX IX2;
   COMMIT;