IT29891: INCORRECT ORDER OF INDEX AND PRIMARY KEY GENERATED DURING DB2LOOK -CT PROCESSING

APAR status

• Closed as program error.

Error description

• 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
  

Local fix

Problem summary

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

Problem conclusion

• First fixed in Db2 11.1 Mod 4 Fixpack 5
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RB10 PSN

     UP