DB2 10.5 for Linux, UNIX, and Windows

Conditions for matching a source table index with a target table partitioned index

If you want to reuse the indexes on the source table as index partitions on a target table when attaching data partitions, all index key columns or expressions for the indexes on the source table must match the index key columns or expressions for the partitioned indexes on the target table.

If the source table has an expression-based index, the system-generated statistical view and package that are associated with the index are dropped as part of the process to attach the partition. If the target table has a partitioned expression-based index, the expression is used when determining whether the source table has a matching index. If all other properties of the two indexes are the same, the index on the source table is considered to match the partitioned index on the target table. That is, the index on the source table can be used as an index on the target table.

The following table applies only when the target index is partitioned. The target index property is assumed by the source index in all cases where they are considered to be a match.

Table 1. Determining whether the source index matches the target index when the target index property is different from the source index property
Rule number	Target index property	Source index property	Does the source index match?
1.	Non-unique	Unique	Yes, if the index is not an XML index.
2.	Unique	Non-unique	No.
3.	Column X is descending	Column X is ascending	No.
4.	Column X is ascending	Column X is descending	No.
5.	Column X is random	Column X is ascending	No.
6.	Column X is ascending	Column X is random	No.
7.	Column X is random	Column X is descending	No.
8.	Column X is descending	Column X is random	No.
9.	Partitioned	Nonpartitioned	No. It is assumed that the source table is partitioned.
10.	pctfree n1	pctfree n2	Yes
11.	level2pctfree n1	level2pctfree n2	Yes.
12.	minpctused n1	minpctused n2	Yes.
13.	Disallow reverse scans	Allow reverse scans	Yes, the physical index structure is the same irrespective of whether reverse scans are allowed.
14.	Allow reverse scans	Disallow reverse scans	Yes, the same reason as 9.
15.	pagesplit [L\|H\|S]	pagesplit [L\|H\|S]	Yes.
16.	Sampled statistics	Detailed statistics	Yes.
17.	Detailed statistics	Sampled statistics	Yes.
18.	Not clustered	Clustered	Yes.
19.	Clustered	Not clustered	Yes. The index becomes a clustering index, but the data is not clustered according to this index until the data is reorganized. You can use a partition-level reorganization after attaching the data partition to cluster the data according to this index partition.
20.	Ignore invalid values	Reject invalid values	Yes.
21.	Reject invalid values	Ignore invalid values	No. The target index property of rejecting invalid values must be respected, and the source table might have rows that violate this index constraint.
22.	Index compression enabled	Index compression not enabled	Yes. Compression of the underlying index data does not occur until the index is rebuilt.
23.	Index compression not enabled	Index compression enabled	Yes. Decompression of the index data does not occur until the index is rebuilt.

Note: With rule number 9, an ALTER TABLE ... ATTACH PARTITION statement fails and returns error message SQL20307N, SQLSTATE 428GE if both of the following conditions are true:

You attempt to attach a multidimensional clustering (MDC) table (with nonpartitioned block indexes) that you created by using DB2® Version 9.7 or earlier to a new MDC partitioned table (with partitioned block indexes) that you created by using DB2 Version 9.7 Fix Pack 1 or later.
You specify the ERROR ON MISSING INDEXES clause.

Removing the ERROR ON MISSING INDEXES clause allows the attachment to be completed because the database manager maintains the indexes during the attach operation. If you receive error message SQL20307N, SQLSTATE 428GE, consider removing the ERROR ON MISSING INDEXES clause. An alternative is to use the online table move procedure to convert an MDC partitioned table that has nonpartitioned block indexes to a table that has partitioned block indexes.