A fix is available
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 number
PI83893
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
B10
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2017-06-30
Closed date
2017-10-12
Last modified date
2017-11-01
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
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.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
01 November 2017