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.
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.
About this task
Results
Example
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;
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.
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
- 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.