A fix is available
APAR status
Closed as new function.
Error description
DM1578 Stored Procedure Monitoring support.
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: All DB2 users who use DB2 instrumentation * * for Stored Procedure and User-Defined * * Function performance and tuning analysis. * **************************************************************** * PROBLEM DESCRIPTION: Stored Procedure (SP) and User-Defined * * Function (UDF) performance and tuning * * analysis is difficult when multiple * * procedures or functions are involved. * **************************************************************** * RECOMMENDATION: * **************************************************************** Stored Procedure (SP) and User-Defined Function (UDF) performance and tuning analysis has typically been performed via a combination of IFCID3 and IFCID239. IFCID3 provides plan level information and aggregates all executions of SPs or UDFs into common fields. This can create difficulty when tuning multiple procedures or functions that are executed in a given transaction. IFCID239 is also used for performance and tuning analysis at the package level. This provides better granularity than IFCID3 but still may not be sufficient for all transactions. If a procedure or function is executed multiple times, the variation between executions cannot be identified. Instrumentation enhancements are needed.
Problem conclusion
Temporary fix
Comments
Multiple IFCID enhancements are implemented to provide more effective performance and tuning analysis of Stored Procedures and User-Defined Functions. * IFCID233 is written at the beginning and end of a Stored Procedure or User-Defined Function invocation. This record is enhanced with the invoking statement ID, the invoking statement type, the version ID (applies only to versioned procedures), and the routine ID. Note: The routine ID may be zero if a REBIND is not performed for packages containing CALL statements where the stored procedure name is a literal. See DSNDQW02 for mapping details. * New IFCIDs 380 and 381 are created for Stored Procedure and User-Defined Function detail respectively. These records have two data sections. Data section 1 is mapped by QW0233. Data section 2 is mapped by QW0380 which includes CP, specialty engine, and elapsed time details for nested activity. A series of 380 and/or 381 records can be used to determine the amount of class 1 and class 2 CP, specialty engine, and elapsed time relative to the execution of a given Stored Procedure or User-Defined Function. See DSNDQW05 for mapping details. * New IFCIDs 497, 498, and 499 are created for statement level detail. These records track dynamic and static DML statements executed by a transaction, including those executed within a Stored Procedure or User-Defined Function. A series of IFCID 497, 498, and/or 499 records can be used to determine the statements executed for a given transaction. Note: Any packages containing static SQL statements that existed prior to DB2 10 must be rebound in DB2 10 NFM (not necessarily with this APAR applied) in order to obtain a valid statement ID. See DSNDQW05 for mapping details. * A new performance class 24 is created to encapsulate IFCID380 and IFCID499 for stored procedure detail analysis. For users who are interested in exploiting the functions provided via this APAR, the following actions need to be considered: * For a CALL statement to a DB2 for z/OS Stored Procedure, the Stored Procedure name can be identified via a literal or by using a host variable or parameter marker. When using a literal for the stored procedure name, and in order to benefit from the enhancement that provides a valid routine ID in various IFCID records, the packages that contain the CALL statement must be rebound after this APAR is applied. * For an SQL statement that invokes a DB2 for z/OS User-Defined Function, and in order to benefit from the enhancement to provide a valid routine ID in various IFCID records, the packages that contain the SQL statement must be rebound after this APAR is applied. * The mapping of IFCID233 remains compatible with prior versions and no immediate change is required. However, applications that parse this record will need to be changed in order to exploit the new fields.
APAR Information
APAR number
PM53243
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
A10
Status
CLOSED UR1
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2011-11-30
Closed date
2012-05-04
Last modified date
2012-06-03
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UK78514
Modules/Macros
DSNDQWAC DSNDQWHS DSNDQW02 DSNDQW03 DSNDQW05 DSNLCDG2 DSNLCMSL DSNLXOQS DSNLXPRS DSNLXXSS DSNLZMON DSNTXSTA DSNTXSTB DSNWVINT DSNWVZSA DSNXEDP DSNXERT DSNXERT2 DSNXGCAL DSNXGUDF DSNXRCUF DSNXRRTN DSNXRUC9 DSNXRUFC DSNXRUFM DSNXRUF9
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
RA10 PSY UK78514
UP12/05/22 P F205
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":"10.1","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":"10.1","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
03 June 2012