PM63218: SQLCODEN478 DROP ON OBJECT TYPE PROCEDURE CANNOT BE PROCESSED BECAUSE OBJECT OF TYPE TRIGGER IS DEPENDENT ON IT.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• The DB2 Object Comparison Tool for z/OS APPLY jobs fails
  with a return code / RC=8 and a message:
  DROP PROCEDURE <procedure_name>
  error on DROP command, EXECUTE function
  DNT408I SQLCODE = -478, ERROR: DROP OR REVOKE ON OBJECT TYPE
  PROCEDURE CANNOT BE PROCESSED BECAUSE OBJECT .
  OF TYPE TRIGGER IS DEPENDENT ON IT
  DSNT418I SQLSTATE = 42893 SQLSTATE RETURN CODE
  DSNT415I SQLERRP = DSNXIDRT SQL PROCEDURE DETECTING ERROR
  
  Additional symptoms:
  .
  When RUNID used for COMPARE for unqualified object is the same
  as SQLID it may result in SQLCODE -204 (SQLCODEN204)
  
  DSNT408I SQLCODE = -204, ERROR: sqlid.tbname
   IS AN UNDEFINED NAME
  
  DSNT418I SQLSTATE   = 42704 SQLSTATE RETURN CODE
  DSNT415I SQLERRP    = DSNXOFE SQL PROCEDURE DETECTING ERROR
  DSNT416I SQLERRD    = -200  0  0  -1  0  0 SQL DIAGNOSTIC
  INFORMATION
  DSNT416I SQLERRD    = X'FFFFFF38'  X'00000000'  X'00000000'
  X'FFFFFFFF'
           X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of the DB2 Object Comparison Tool for  *
  *                 z/OS who compare DB2 stored procedures,      *
  *                 user defined functions, and triggers.        *
  ****************************************************************
  * PROBLEM DESCRIPTION: A compare of triggers may result in a   *
  *                      SQLCODE=-204 / SQLCODEN204.  DROPs of   *
  *                      stored procedures and user defined      *
  *                      functions may fail with a SQLCODE=-478  *
  *                      / SQLCODEN478, and CREATEs of stored    *
  *                      procedures and user defined functions   *
  *                      may fail with a SQLCODE=-440 /          *
  *                      SQLCODEN440. A dependent object may be  *
  *                      dropped and not recreated if the        *
  *                      dependent object is in the source DDL.  *
  *                                                              *
  *                      DSNT408I SQLCODE = -204,                *
  *                      ERROR: sqlid.tbname IS AN UNDEFINED     *
  *                      NAME                                    *
  *                                                              *
  *                      DNT408I SQLCODE = -478,                 *
  *                      ERROR: DROP OR REVOKE ON OBJECT         *
  *                      TYPE PROCEDURE CANNOT BE PROCESSED      *
  *                      BECAUSE OBJECT OF TYPE TRIGGER IS       *
  *                      DEPENDENT ON IT                         *
  *                                                              *
  *                      DNT408I SQLCODE = -440,                 *
  *                      NO AUTHORIZED routine-type BY           *
  *                      THE NAME routine-name HAVING            *
  *                      COMPATIBLE ARGUMENTS WAS                *
  *                      FOUND IN THE CURRENT PATH               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  The DB2 Object Comparison Tool used the current SQLID instead
  of the current SCHEMA to qualify unqualified objects in the
  trigger body.  This caused identification of unqualified
  objects in the trigger body to fail with a SQLCODE=-204 /
  SQLCODEN204 during a CREATE. Support added in a prior APAR
  to ensure that stored procedures, functions, and objects
  depending on stored procedures and functions were dropped
  and created in the order required by their dependencies did
  not include a test for version files that contained newly
  added dependent object information required to implement the
  support. The ability to recreate a dropped dependent object
  was lost because information from the target catalog was
  lost due to the dependent object being present in the source
  DDL.
  

Problem conclusion

• The DB2 Object Comparison Tool has been corrected.  The current
  SCHEMA is used to qualify unqualified objects in the trigger
  body. The version files will be examined for the presence of
  dependent object information.  If dependent object information
  is needed but not available, one of two warning messages,
  ADB7152W or ADB7154W will be issued.
  
  ADB7152W The dependent object information is needed.  This
           information is not available from DDL version files.
  
  ADB7154W The dependent object information is needed.  The
           version files must have a release marker of at least
           814.  Generate new version files with the current
           product JCL.
  

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

•    ADBCDTS  ADBCVEX  ADBCVEY  ADB2CMF  ADB2CMP
  ADB2CMR  ADB2CMS  ADB2CMT  ADB2REE  ADB7000
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RA10 PSY UK80590

     UP12/08/01 P F207

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.