DB2 FOR LUW / DB2FORLUW / B10 - DB2

If the following Objects are created in the order below :

CREATE TABLE "DB2INST1"."TAB1" ( "COLA" TIMESTAMP NOT NULL WITH
DEFAULT CURRENT TIMESTAMP , "COLB" CHAR(10) NOT NULL WITH
DEFAULT '' ) IN USERSPACE1 ORGANIZE BY ROW
DB20000I The SQL command completed successfully.

CREATE INDEX "DB2INST1"."TAB1_PRIM" ON "DB2INST1"."TAB1" ("COLA"
ASC) COMPRESS NO INCLUDE NULL KEYS ALLOW REVERSE SCANS
DB20000I The SQL command completed successfully.

ALTER TABLE "DB2INST1"."TAB1" ADD CONSTRAINT "TAB1_CPRIM"
PRIMARY KEY ("COLA")
SQL0598W Existing index "DB2INST1.TAB1_PRIM" is used as the
index for the primary key or a unique key. SQLSTATE=01550

DB2LOOK with the -CT option will generate the following DDL :

CREATE TABLE "DB2INST1"."TAB1" ( "COLA" TIMESTAMP NOT NULL WITH
DEFAULT CURRENT TIMESTAMP , "COLB" CHAR(10) NOT NULL WITH
DEFAULT '' ) IN USERSPACE1 ORGANIZE BY ROW;

ALTER TABLE "DB2INST1"."TAB1" ADD CONSTRAINT "TAB1_CPRIM"
PRIMARY KEY ("COLA");

CREATE INDEX "DB2INST1"."TAB1_PRIM" ON "DB2INST1"."TAB1" ("COLA"
ASC) COMPRESS NO INCLUDE NULL KEYS ALLOW REVERSE SCANS;


Where the INDEX and the PRIMARY KEY are not in the same order.


As a consequence, the Index is not created when the DDL is
replayed :

CREATE TABLE "DB2INST1"."TAB1" ( "COLA" TIMESTAMP NOT NULL WITH
DEFAULT CURRENT TIMESTAMP , "COLB" CHAR(10) NOT NULL WITH
DEFAULT '' ) IN USERSPACE1 ORGANIZE BY ROW
DB20000I The SQL command completed successfully.

ALTER TABLE "DB2INST1"."TAB1" ADD CONSTRAINT "TAB1_CPRIM"
PRIMARY KEY ("COLA")
DB20000I The SQL command completed successfully.

CREATE INDEX "DB2INST1"."TAB1_PRIM" ON "DB2INST1"."TAB1" ("COLA"
ASC) COMPRESS NO INCLUDE NULL KEYS ALLOW REVERSE SCANS;
SQL0605W The index was not created because an index
"DB2INST1.TAB1_CPRIM" with a matching definition already exists.
SQLSTATE=01550

****************************************************************
* USERS AFFECTED:                                              *
* ALL                                                          *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to Db2 11.1 Mod 4 Fixpack 5 or higher                *
****************************************************************

not known / see Local fix

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

01.08.2019
16.01.2020
16.01.2020