PM53243: STORED PROCEDURE MONITORING SUPPORT

A fix is available

Subscribe

You can track all active APARs for this component.

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.



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM53243

Modified date:

2012-06-03

Translate my page

Machine Translation

Content navigation