DB2 FOR LUW / DB2FORLUW / A50 - DB2

The db2look generates unique constraint DDL followed by unique 
index DDL, even if the constraint reuses the index originally. 
This invalid order causes SQL0605W when executing the DDL, and 
unique index cannot be created. 
 
If the unique index has specified non-default index options like 
'DISALLOW REVERSE SCANS', users cannot get the required index 
due to SQL0605W. 
 
The following example illustrates the problem. 
 
1. Create a table with unique index and unique constraint. 
 
create table T1 (c1 int not null, c2 int) 
create unique index ix1 on t1(c1) disallow reverse scans 
alter table t1 add constraint cons1 unique (c1) 
-- cons1 reuses ix1 to maintain the constraint. 
 
2. Generate the table DDL by db2look. 
 
db2look -d <DBNAME> -t T1 -a -o db2look.out 
 
------------------------------------------------ 
-- DDL Statements for Table "DB2INST1"."T1" 
------------------------------------------------ 
CREATE TABLE "DB2INST1"."T1"  ( 
                  "C1" INTEGER NOT NULL , 
                  "C2" INTEGER ) 
                 IN "USERSPACE1" ; 
 
-- DDL Statements for Unique Constraints on Table 
"DB2INST1"."T1" 
 
ALTER TABLE "DB2INST1"."T1" 
        ADD CONSTRAINT "CONS1" UNIQUE 
                ("C1"); 
 
-- DDL Statements for Indexes on Table "DB2INST1"."T1" 
 
CREATE UNIQUE INDEX "E101Q3B "."IX1" ON "DB2INST1"."T1" 
                ("C1" ASC) 
                COMPRESS NO DISALLOW REVERSE SCANS; 
 
3. The DDL produces +605, and we cannot get required index. 
 
db2 -tvf db2look.out 
 
SQL0605W  The index was not created because an index 
"DB2INST1.CONS1" with a 
matching definition already exists.  SQLSTATE=01550

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to Db2 10.5 Fix Pack 9 or higher                     * 
****************************************************************

Please use -ct option to align the DDL order by create time, or 
manually edit the generated DDL.

First fixed in Db2 10.5 Fix Pack 9

not known / see Local fix

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

20.01.2017
29.09.2017
29.09.2017

9.0.