DB2 FOR LUW / DB2FORLUW / 970 - 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:                                              * 
* ALTER TABLE OPERATION AGAINST MDC TABLE                      * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* 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.              * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to Version 9.7 Fix Pack 4                            * 
****************************************************************

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.7 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 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
 

Problem was first fixed in Version 9.7 Fix Pack 4

see LOCAL FIX

forerunner  : APAR is sysrouted TO one or more of the following: IC74708 
follow-up : 

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

01.02.2011
21.04.2011
21.04.2011

9.7.FP4