DB2 Version 9.7 for Linux, UNIX, and Windows

Converting existing indexes to partitioned indexes

System-created and user-created indexes might need to be migrated from nonpartitioned to partitioned. User-created indexes can be converted while maintaining availability to the table and indexes for most of the migration. System-created indexes used to enforce primary key constraints or unique constraints will not be able to have the constraints maintained while the conversion is done.

About this task

Indexes created by you can be converted from nonpartitioned to partitioned while having continuous availability to the data using the index. You can create a partitioned index with the same keys as the corresponding nonpartitioned index. While the partitioning index is created, you can still use the current indexes and the table where the index is being created. Once the partitioned index is created, you can drop the corresponding nonpartitioned index and rename the new partitioned index if desired.

Before you begin

Indexes created in an earlier release of the product might be nonpartitioned. This could include both indexes created by you, or system-created indexes created by the database manager. Examples of system-created indexes are indexes to enforce unique and primary constraints and the block indexes of an MDC table.

Results

The following examples demonstrate how to convert existing nonpartitioned indexes into partitioned indexes.

Example

Here is an example of converting a nonpartitioned index created by you to one that is a partitioned index:

UPDATE COMMAND OPTIONS USING C OFF;
CREATE INDEX data_part ON sales(sale_date) PARTITIONED;
DROP INDEX dateidx;
RENAME INDEX data_part TO dateidx;
COMMIT;

Here is an example of converting a nonpartitioned index created by the database manager to one that is a partitioned index. In this case, there will be a period of time between the dropping of the original constraint, and the creation of the new constraint.

ALTER TABLE employees DROP CONSTRAINT emp_uniq;
ALTER TABLE employees ADD CONSTRAINT emp_uniq UNIQUE (employee_id);

MDC tables created using DB2® Version 9.7 and earlier releases have nonpartitioned block indexes. To take advantage of partitioned table data availability features such as data roll in and roll out and partition level reorganization of table data and indexes, the data in the multidimensional clustering (MDC) table created using DB2 V9.7 and earlier releases must be moved to a partitioned MDC table with partitioned block indexes created using DB2 V9.7 Fix Pack 1 or a later release.

Online move of a partitioned MDC table to use partitioned block indexes

You can move data from a MDC table with nonpartitioned block indexes to an MDC table with partitioned block indexes using an online table move.

In the following example, company1.parts table has region and color as the MDC key columns; and the corresponding block indexes are nonpartitioned.

CALL SYSPROC.ADMIN_MOVE_TABLE(
 'COMPANY1',  --Table schema
 'PARTS',     --Table name
 ' ',         --null; No change to columns definition
 ' ',         --null; No additional options
 'MOVE');     --Move the table in one step

Offline move of a partitioned MDC table to use partitioned block indexes

To minimize data movement, you can move data from a MDC table with nonpartitioned block indexes to an MDC table with partitioned block indexes when the table is offline. The process uses the following steps:

Create a new, single-partition MDC table with the same definition as the table to be converted. When specifying the range for the partition, use a range outside the ranges of the partitioned MDC table to be converted.
The block indexes of new, single-partition MDC table are partitioned. The partition created when specifying the range is detached in a later step.
Detach each partition of the MDC table. Each partition becomes a stand-alone MDC table.
When a partition is detached, the partition data is attached to a new, target table without moving the data in the partition.
Note: The last partition of the MDC table cannot be detached. It is a single-partition MDC table with nonpartitioned block indexes.
For each stand-alone table created by detaching the MDC table partitions, and the single-partition MDC table with nonpartitioned block indexes, attach the table to the new partitioned MDC table created in Step 1.
When the table is attached, the table data is attached to the new partitioned MDC table without moving the data, and the block indexes are created as partitioned block indexes.
After attaching the first stand-alone MDC table, you can detach the empty partition created when you created the new MDC table.
Issue SET INTEGRITY statement on the new partitioned MDC table.

Converting existing indexes to partitioned indexes

About this task

Before you begin

Results

Example

What to do next