PM53243: STORED PROCEDURE MONITORING SUPPORT

Obtain the fix for this APAR.

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 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.