DB2 FOR LUW / DB2FORLUW / 950 - DB2

An ALTER TABLE operation against an MDC table which defines a 
primary key or unique constraint can mistakenly mark a matching 
existing index as unique although it contains duplicated keys. 
 
Once the index is mistakenly marked as unique, queries against 
the table can return incorrect results. 
 
 
You can find potentially affected index(s) by querying the 
system catalogs with the following query: 
 
 
select TABSCHEMA, TABNAME, CONSTNAME, TYPE 
from 
        SYSCAT.TABCONST 
where 
        (TYPE = 'P' or TYPE = 'U') 
and 
        (TABSCHEMA, TABNAME) in 
            ( 
            select 
                    T.TABSCHEMA, 
                    T.TABNAME 
            from 
                    SYSCAT.INDEXES I, 
                    SYSCAT.TABLES T 
            where 
                    I.MADE_UNIQUE = 'Y' 
                    and I.TABNAME = T.TABNAME 
                    and I.TABSCHEMA = T.TABSCHEMA 
                    and T.CLUSTERED ='Y' 
            )

**************************************************************** 
* USERS AFFECTED:                                              * 
* All                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* ALTER TABLE OPERATION AGAINST MDC TABLE MISTAKENLY MARKS     * 
* MATCHING INDEX AS UNIQUE THOUGH IT CONTAINS DUPLICATE KEYS.  * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 LUW Version 9.5 Fix Pack 8                    * 
****************************************************************

Drop the primary key or unique constraint and verify 
uniqueness by selecting the GROUP BY index key columns 
along with columns with the following property: 
HAVING COUNT(*) > 1. Note that this query will 
not return correct values if the invalid unique constraint is 
still on the table; it must be dropped first.  There must 
not be a unique index on the table when the uniqueness 
of the rows is being checked, regardless of whether the 
index is used or not.

DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
 

Problem was first fixed in Version 9.5 Fix Pack 8

see LOCAL FIX

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

25.02.2011
05.07.2011
05.07.2011

9.5.FP8