DB2 FOR LUW / DB2FORLUW / 970 - DB2

There are two problems here: 
 
1) This problem results in an empty index being created on the 
detach partition, despite the partition not being empty.  To hit 
this problem you need to detach a partition that has partitioned 
index(es) that require rebuilding.  If the asynchronous 
partition detach task does not fully complete and a crash occurs 
and then restart database is then executed and the database is 
configured to have indexes rebuilt on restart (controlled by the 
indexrec Database and Database Manager configuration 
parameters), it will then rebuild the problem indexes on each 
partition, but on the partially detached partition (still in the 
logically detached state), the index will be created empty. 
Later, once the database has been restarted the asynchronous 
partitioned detach task will complete the detach and the 
standalone table may have data with an empty index. 
 
2) This problem can result in an index on a non-partitioned 
table to be marked as unique in the catalogs but not in the 
actual index object (or the reverse, non-unique in the catalogs 
but unique in the actual index).  As a result it does not 
enforce uniqueness correctly.  The following is an example of 
how this can occur: 
- partitioned table with a non-unique partitioned index on not 
nullable columns where the index key columns are a super-set of 
the table partitioning key columns 
- a partition is detached and before the asynchronous portion of 
the partition detach completes 
- the table is altered to add a primary or unique key using the 
same columns, in the same order, as the partitioned index 
mentioned above (i.e. we will convert the existing index from 
non-unique to unique).  If at this point you dropped a primary 
or unique constraint, you may see experience the reverse of this 
issue (non-unique in the catalogs but unique in the actual 
index) provide that the index existed prior to the primary key 
or unique constraint being added to the table. 
- the asynchronous partitioned detach task completes and the 
detached partition is now a fully accessible standalone 
non-partitioned table where the catalogs and index object are 
inconsistent

**************************************************************** 
* USERS AFFECTED:                                              * 
* All users                                                    * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 version 9.7.0.6.                              * 
****************************************************************

Recreate the affected indexes.

DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

The problem was first fixed in DB2 version 9.7.0.6.

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

29.03.2012
05.06.2012
05.06.2012

9.7.0.6