PM69672: ALT ALTER TABLE DROP FOREIGN KEY ENDS SQLCODEN204

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• The DB2 Administration Tool for z/OS' ALT function's APPLY job
  ALTER TABLE DROP FOREIGN KEY ends with a SQLCODE -204
  SQLCODEN204 as earlier ALTER TABLE DROP PRIMARY KEY statements
  had removed the FOREIGN KEY.
  ADB5044I *** INPUT STATEMENT:
     ALTER TABLE >schema<.>table<
       DROP FOREIGN KEY >fkey<;
  sqlerror on ALTER      command, EXECUTE    function
   DSNT408I SQLCODE = -204, ERROR:  >schema<.>table<.>fkey<
  IS AN UNDEFINED NAME
   DSNT418I SQLSTATE   = 42704 SQLSTATE RETURN CODE
   DSNT415I SQLERRP    = DSNXISB3 SQL PROCEDURE DETECTING ERROR
   DSNT416I SQLERRD    = 13  0  0  -1  0  0 SQL DIAGNOSTIC
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of the DB2 Administration Tool and     *
  *                 DB2 Object Comparison Tool for z/OS who      *
  *                 alter a column which is part of a primary    *
  *                 key of the table, and the primary key is     *
  *                 involved in more than one referential        *
  *                 constraint.                                  *
  ****************************************************************
  * PROBLEM DESCRIPTION: When altering a column which is part    *
  *                      of a primary key of the table, and      *
  *                      the primary key is involved in more     *
  *                      than one referential constraint, the    *
  *                      APPLY job may fail with a               *
  *                      SQLCODE= -204 / SQLCODEN204:            *
  *                                                              *
  *                      ADB5044I *** INPUT STATEMENT:           *
  *                         ALTER TABLE tablename                *
  *                           DROP RESTRICT ON DROP              *
  *                           DROP FOREIGN KEY FKname;           *
  *                      DSNT408I SQLCODE = -204,                *
  *                      ERROR:  >schema<.>table<.>fkey<         *
  *                       IS AN UNDEFINED NAME                   *
  *                      DSNT418I SQLSTATE   = 42704             *
  *                      SQLSTATE RETURN CODE                    *
  *                      ADB5185E The run is ending due to       *
  *                      errors.                                 *
  *                      ADBT010W - Rollback performed due       *
  *                      to previous error                       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  When altering a column which is part of the primary key of
  a table, and the primary key is involved in more than one
  referential constraint, the APPLY job may fail with a
  SQLCODE= -204 / SQLCODEN204.  This is caused by a redundant
  DROP FOREIGN KEY statement to drop a referential constraint
  in the changes file, but the referential constraint is
  already dropped implicitly when dropping its parent key.
  
  ADB5044I *** INPUT STATEMENT:
     ALTER TABLE tablename
       DROP RESTRICT ON DROP
       DROP FOREIGN KEY FKname;
  DSNT408I SQLCODE = -204,
       ERROR:   >schema<.>table<.>fkey<
   IS AN UNDEFINED NAME
  DSNT418I SQLSTATE   = 42704
  SQLSTATE RETURN CODE
  ADB5185E The run is ending due to errors.
  ADBT010W - Rollback performed due to previous error
  

Problem conclusion

• Problem has been resolved.
  

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:

Modules/Macros

•    ADB2CMF  ADB2CMP  ADB2CMR  ADB2CMT
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RA10 PSY UK92575

     UP13/03/19 P F303

• RA20 PSY UK92576

     UP13/03/19 P F303

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.