DB2 Version 9.7 for Linux, UNIX, and Windows

Conditions for matching a source table index with a target table partitioned index during ATTACH PARTITION

All index key columns of the partitioned index on the target table must match with the index key columns of the index on the source table. If all other properties of the index are the same, then the index on the source table is considered a match to 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 table here can be used to determine if the indexes are considered a match or not.

The table below is only useful and applicable when the target index is partitioned. The target index property is assumed by the source index in all cases where they are considered a match.

Table 1. Determining whether the source index matches 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.	partitioned	nonpartitioned	No. Note: this assumes the source table is partitioned.
6.	pctfree n1	pctfree n2	Yes
7.	level2pctfree n1	level2pctfree n2	Yes
8.	minpctused n1	minpctused n2	Yes
9.	disallow reverse scans	allow reverse scans	Yes, the physical index structure is the same irrespective of whether reverse scans are allowed or not.
10.	allow reverse scans	disallow reverse scans	Yes, the same reason as (9).
11.	pagesplit [L\|H\|S]	pagesplit [L\|H\|S]	Yes
12.	sampled statistics	detailed statistics	Yes
13.	detailed statistics	sampled statistics	Yes
14.	not clustered	CLUSTER	Yes
15.	CLUSTER	not clustered	Yes. The index will become a clustering index but the data will not be clustered according to this index until the data is reorganized. You can use a partition level reorganization after attaching to cluster the data according to this index partition.
16.	ignore invalid	reject invalid	Yes
17.	reject invalid	ignore invalid	No. The target index property of rejecting invalid values needs to be respected and the source table may have rows that violate this index constraint.
18.	Index compression enabled	Index compression not enabled	Yes. Note: compression of the underlying index data will not occur until the index is rebuilt.
19.	Index compression not enabled	Index compression enabled	Yes. Note: decompressing the index data will not occur until the index is rebuilt.

Note: With rule number 5, an ALTER TABLE ... ATTACH PARTITION statement fails returning error message SQL20307N, SQLSTATE 428GE, if you attempt to attach a multidimensional clustering (MDC) table created using DB2 Version 9.7 or earlier releases (having nonpartitioned block indexes) to a new MDC partitioned table created using DB2® Version 9.7 Fix Pack 1 or later releases (having partitioned block indexes) and the ERROR ON MISSING INDEXES clause is used. Removing the ERROR ON MISSING INDEXES clause allows the attachment to complete because the database manager maintains the indexes during the attach operation. If you received error message SQL20307N, SQLSTATE 428GE, you should 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.