IT26008: DB2LOOK HAS DUPLICATE/INCORRECT COLUMN IN REFERENCES CLAUSE OF FOREIGN KEY DDL.

Db2 Version 11.1 Mod4 Fix Pack4 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 4 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 6 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 5 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 7 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• Starting in Db2 v10.5 fp9 and v11.1 fp3, db2look might have
  extra/incorrect columns in references clause of foreign key ddl.
  This can happen when a different schema exists with same name
  and constraint name. When recreating an environment with
  db2look, it will fail.
  
  This issue is to be fixed in the next released fixpack
  
  
  ----------------- db2look
  ----------------------------------------------
  ALTER TABLE "PROD    "."RE_RESS_COMP"
      ADD CONSTRAINT "R3R02R01" FOREIGN KEY
          ("MATRIC_RETR",
           "LF_RETRAITE")
      REFERENCES "PROD    "."RE_SUIVI_AS"
          ("MATRIC_RETR",
           "MATRIC_RETR",
           "LF_RETRAITE",
           "LF_RETRAITE")    --> Duplicate column.
      ON DELETE CASCADE
      ON UPDATE RESTRICT
      ENFORCED
      ENABLE QUERY OPTIMIZATION;
  
  
  Another symptom is SYSPROC.ADMIN_COPY_SCHEMA creating duplicate
  columns because it relies on db2look under the covers.
  
  Before calling SYSPROC.ADMIN_COPY_SCHEMA the constraint is
  defined as
  
  ALTER TABLE "DB2INST1  "."TAB1"
          ADD CONSTRAINT "F1" FOREIGN KEY
                  ("TAB2_ID")
          REFERENCES "DB2INST1  "."TAB2"
                  ("TAB2_ID")
          ON DELETE CASCADE
          ON UPDATE NO ACTION;
  
  After calling SYSPROC.ADMIN_COPY_SCHEMA constraint is defined as
  
  
  ALTER TABLE "DB2INST1 "."TAB1"
         ADD CONSTRAINT "F1" FOREIGN KEY
                 ("TAB2_ID")
         REFERENCES "DB2INST1 "."TAB2"
                 ("TAB2_ID",
                  "TAB2_ID")        <<<< additional duplicate
  column
         ON DELETE CASCADE
         ON UPDATE NO ACTION;
  

Local fix

• Manually Correct it.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * db2look                                                      *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply v11.1 M4 FP4 to DB2 server                             *
  ****************************************************************
  

Problem conclusion

• First fixed in v11.1 M4 FP4
  

Temporary fix

• See LOCAL FIX
  

Comments

APAR Information

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

  IT24545

• 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