IZ17292: DB2LOOK ONLY CREATES ONE INDEX IF UNIQUE CONSTRAINT AND ANOTHER INDEX HAVE THE SAME COLUMNS IN A DIFFERENT ORDER

APAR status

• Closed as duplicate of another APAR.

Error description

• db2look extracts index and unique key creation information from
  the system catalogs but not their order of their creation.
  Under certain circumstances this can result in only one index
  being created from the dblook output when the original table had
  a unique contraint being different.
  
  Example:
  
   CREATE TABLE tab999  (
  
                    col1 INTEGER NOT NULL ,
                    col2 INTEGER NOT NULL ,
                    col3 VARCHAR(128) NOT NULL,
                    constraint tab999_uk
                        unique (col1, col2, col3)
                  )  IN "USERS" INDEX IN "INDX" ;
  
  CREATE INDEX tab999_idx1 ON tab999
                  (col3 ASC,
                    col2 ASC,
                   col1 ASC
                   ) ALLOW REVERSE SCANS;
  
  
  ===> extract table definition with db2look:
  
          db2look -d wpc52db -z DB2INST1 -t tab999 -p -e
  
  
  this generates:
  
  
  
   CREATE TABLE "DB2INST1"."TAB999"  (
                    "COL1" INTEGER NOT NULL ,
                    "COL2" INTEGER NOT NULL ,
                    "COL3" VARCHAR(128) NOT NULL )
                   IN "USERS" INDEX IN "INDX" ;
  
  
  -- DDL Statements for indexes on Table "DB2INST1"."TAB999"
  
  CREATE INDEX "DB2INST1"."TAB999_IDX1" ON "DB2INST1"."TAB999"
                  ("COL3" ASC,
                   "COL2" ASC,
                   "COL1" ASC) ALLOW REVERSE SCANS;
  
  -- DDL Statements for unique constraints on Table
  "DB2INST1"."TAB999"
  
  ALTER TABLE "DB2INST1"."TAB999"
          ADD CONSTRAINT "TAB999_UK" UNIQUE
                  ("COL1",
                   "COL2",
                   "COL3");
  
  
  ----------------------------------------------------------------
  AFTER EXECUTING THE ABOVE IT RESULTS IN THE FOLLOWING:
  ----------------------------------------------------------------
  
  
  drop table tab999
  DB20000I  The SQL command completed successfully.
  
  CREATE TABLE "DB2INST1"."TAB999"  ( "COL1" INTEGER NOT NULL ,
  "COL2"
  INTEGER NOT NULL , "COL3" VARCHAR(128) NOT NULL ) IN "USERS"
  INDEX IN
  "INDX"
  DB20000I  The SQL command completed successfully.
  
  CREATE INDEX "DB2INST1"."TAB999_IDX1" ON "DB2INST1"."TAB999"
  ("COL3"
  ASC, "COL2" ASC, "COL1" ASC) ALLOW REVERSE SCANS
  DB20000I  The SQL command completed successfully.
  
  ALTER TABLE "DB2INST1"."TAB999" ADD CONSTRAINT "TAB999_UK"
  UNIQUE
  ("COL1", "COL2", "COL3")
  SQL0598W  Existing index "DB2INST1.TAB999_IDX1" is used as the
  index for
  the
  primary key or a unique key.  SQLSTATE=01550
  
  ----------------------------------------------------------------
  
  When you run the resulting commands in this order, there is only
  one index created and it is then used to enforce the
  UNIQUE key constraint with the above message.
  
  This is expected behavior for db2 when there is an index with
  the same columns to satisfy the unique key irrespective of
  column order.
  

Local fix

• CREATE TABLE "EMANGLAN"."TAB999"  (
                    "COL1" INTEGER NOT NULL ,
                    "COL2" INTEGER NOT NULL ,
                    "COL3" VARCHAR(128) NOT NULL )
                   IN "USERSPACE1" ;
  
  
  -- DDL Statements for indexes on Table "EMANGLAN"."TAB999"
  
  CREATE INDEX "EMANGLAN"."TAB999_IDX1" ON "EMANGLAN"."TAB999"
                  ("COL3" ASC,
                   "COL2" ASC,
                   "COL1" ASC) ;
  
  -- DDL Statements for indexes on Table "EMANGLAN"."TAB999"
  
  CREATE INDEX "EMANGLAN"."TAB999_IDX2" ON "EMANGLAN"."TAB999"
                  ("COL1" ASC,
                   "COL2" ASC,
                   "COL3" ASC) ;
  
  -- DDL Statements for unique constraints on Table
  "EMANGLAN"."TAB999"
  
  ALTER TABLE "EMANGLAN"."TAB999"
          ADD CONSTRAINT "TAB999_UK" UNIQUE
                  ("COL1",
                   "COL2",
                   "COL3");
  

Problem summary

Problem conclusion

Temporary fix

Comments

• This APAR is a duplicate of IZ37225
  

APAR Information

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

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

Fix information

Applicable component levels