PK73452: ABEND04E RC00E70005 AT DSNXIAB2: P068 WHEN ALTER TABLE WITH DROPFOREIGN KEY

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Customer got ABEND04E RC00E70005 at DSNXIAB2: P068 when trying
  to ALTER TABLE to DROP FOREIGN KEY:
  
  ==SQL Statement
     -------------
   ALTER TABLE "UT001"."SXX_STAT_OTHER" DROP FOREIGN KEY "FKSXX"
  
  DB2 abend because it was looking for the index UT001.XSTA04
  which is exsiting in SYSIBM.SYSINDEXES that was associated with
  UT001.SXX_STAT_OTHER but DM return an error code for could not
  find it.
  
  From REPAIR DBD TEST report, there is an inconsistance of DBD in
  directory and catalog for the particular index.
  
  But REPAIR DBD with REBUILT option couldn't solve issue.
  
  Even both DSN1CHKR & REPAIR REBUILD jobs ran OK, REPAIR TEST for
  the database after the REBUILD shows the inconsistance is
  gone,  customer still got the same error when they alter the
  table to drop foreign key.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 users of constraints.                *
  ****************************************************************
  * PROBLEM DESCRIPTION: ABEND04E RC00E70005 at DSNXIAB2:P068 on *
  *                      ALTER TABLE DROP FOREIGN KEY.           *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  DB2 is not correctly handling some error conditions when
  processing constraints, which may result in unexpected symptoms,
  such as ABEND04E RC00E70005 at DSNXIAB2:P068 on ALTER TABLE DROP
  FOREIGN KEY.
  
  Affected SQL statements:
  CREATE TABLE PRIMARY KEY
  CREATE TABLE UNIQUE
  CREATE TABLE FOREIGN KEY
  CREATE TABLE CHECK
  ALTER TABLE ADD PRIMARY KEY
  ALTER TABLE ADD UNIQUE
  ALTER TABLE ADD FOREIGN KEY
  ALTER TABLE ADD CHECK
  ALTER TABLE DROP PRIMARY KEY
  ALTER TABLE DROP UNIQUE
  ALTER TABLE DROP FOREIGN KEY
  ALTER TABLE DROP CHECK
  ALTER TABLE DROP CONSTRAINT
  

Problem conclusion

• DB2 code has been fixed to handle these error conditions
  properly.
  
  ADDITIONAL KEYWORDS:
  SQLALTER
  SQLCREATE
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

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

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

  UK47862 UK47863

Modules/Macros

• DSNXISB3
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R810 PSY UK47862

     UP09/07/14 P F907

• R910 PSY UK47863

     UP09/07/14 P F907

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.