PI83893: DROP FUNCTION/PROCEDURE OF A ROUTINE REFERENCED BY A NATIVE SQL PROCEDURE SUCCEEDS. SQLCODE478 IS EXPECTED.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• DROP FUNCTION/PROCEDURE of a routine referenced by a native SQL
  procedure succeeds instead of issuing SQLCODE -478.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All DB2 11 and 12 for z/OS users of                          *
  * DROP FUNCTION, DROP PROCEDURE, and REVOKE.                   *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * DROP FUNCTION / DROP PROCEDURE /                             *
  * REVOKE on an object that has migrated                        *
  * objects dependent on it incorrectly                          *
  * completes with SQLCODE0.                                     *
  *                                                              *
  * Additionally, SQLCODE478's error                             *
  * message does not print the dependent                         *
  * type when the dependent object's                             *
  * type is a trigger package or                                 *
  * native SQL routine package.                                  *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  Beginning in DB2 11, object dependencies, specifically
  functions and procedures that have dependent functions,
  procedures, or triggers, are recorded in SYSIBM.SYSDEPENDENCIES.
  
  Prior to DB2 11, dependencies of those objects were only
  recorded in SYSIBM.SYSPACKDEP. Dependency records inserted
  before DB2 11 are still kept in SYSIBM.SYSPACKDEP when
  migrating to DB2 11. However, DB2 does not automatically
  record relevant existing dependencies in SYSIBM.SYSDEPENDENCIES
  during the migration process. Only a REBIND of the migrated
  packages will do so.
  
  In DB2 11 and 12, object dependencies are only checked in
  SYSIBM.SYSDEPENDENCIES when executing DROP FUNCTION /
  DROP PROCEDURE / REVOKE. Migrated objects with dependencies in
  SYSIBM.SYSPACKDEP are not accounted for when executing
  DROP FUNCTION / DROP PROCEDURE / REVOKE.
  
  Additionally, SQLCODE478's error message does not show the
  dependent type when the dependent object's type is a trigger
  package or native SQL routine package.
  

Problem conclusion

• DB2 has been modified to issue SQLCODE478 when attempting
  DROP FUNCTION / DROP PROCEDURE / REVOKE on an object that has
  migrated objects dependent on it.
  
  Additionally, SQLCODE478 correctly prints the dependent type
  for trigger packages and native SQL routine packages.
  
  After applying this APAR, objects with dependencies on
  previously dropped functions / procedures will reference a
  function / procedure that no longer exists. Symptoms such as
  SQLCODE904, SQLCODE204, SQLCODE440, or SQLCODE556 may occur.
  
  ADDITIONAL KEYWORDS:
  SQLCODE204
  SQLCODE440
  SQLCODE478
  SQLCODE556
  SQLCODE904
  SQLDROP
  SQLFUNCTION
  SQLPROCEDURE
  SQLTRIGGER
  SQLUDF
  

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:

  UI51063 UI51064

Modules/Macros

• DSNXA08  DSNXA09  DSNXISB1 DSNXIDRT DSNXIRDP
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI51063

     UP17/10/30 P F710

• RB10 PSY UI51064

     UP17/10/30 P F710

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.