Scenario: Moving from index-controlled to table-controlled partitioning

You can change an existing index-controlled partitioned table space to a table-controlled partitioned table space and implement a DPSI.

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, which is the customer account ID
POSTED, which holds the date of the transaction

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

Begin general-use programming interface information.

A partitioning index is defined on the transaction date by the following CREATE INDEX statement with a PARTITION ENDING AT clause:
```
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'));
```
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);
```

End general-use programming interface information.

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.

To save space and to facilitate reorganization of the table space, you can drop the partitioning index IX1, and you can replace the access index IX2 with a partitioned clustering index that matches the 13 data partitions in the table.

Issue the following statements:

DROP INDEX IX1;
CREATE INDEX IX3 ON TRANS(ACCTID)
   PARTITIONED CLUSTER;
COMMIT;

DROP INDEX IX2;
COMMIT;

What happens:

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.
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.
You drop the nonpartitioning index IX2 because it has been replaced by IX3.

You can now run an online REORG at the partition level with minimal impact on availability. For example:

REORG TABLESPACE dbname.tsname PART 11
   SHRLEVEL CHANGE

Running this utility reorganizes the data for partition 11 of dbname.tsname. The data rows are ordered within each partition to match the ordering of the clustering index.

Recommendations:

Drop a partitioning index if it is used only to define partitions. When you drop a partitioning index, DB2 automatically converts the associated index-controlled partitioned table space to a table-controlled partitioned table space.
You can create a data-partitioned secondary index (DPSI) as the clustering index so that the data rows are ordered within each partition of the table space to match the ordering of the keys of the DPSI.
Create any new tables in a partitioned table space by using the PARTITION BY clause and the PARTITION ENDING AT clause in the CREATE TABLE statement to specify the partitioning key and the limit key values.