PK49348: IMPROVE PERFORMANCE OF FEATURE REOPT(AUTO)

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Improve performance of feature REOPT(AUTO).
  When using REOPT(AUTO) with dynamic statement caching,
  an increased number of short prepares are observed.
  .
  This apar also addresses the following abend when using
  REOPT(AUTO):
  ABEND0C4 at DSNXORRA + 0E00
  AB0C4
  

Local fix

• Don't use REOPT(AUTO) if the performance degradation is
  significantly noticeable.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 9 for z/OS users of REOPT(AUTO)      *
  *                 bind option with DB2 Dynamic Statement Cache *
  ****************************************************************
  * PROBLEM DESCRIPTION: PK49348 improves the performance of     *
  *                      using bind option REOPT(AUTO) by        *
  *                      reducing the number of cache implicit   *
  *                      SHORT PREPARES for dynamic SQL that     *
  *                      use host-variables when both Dynamic    *
  *                      Statement Caching and REOPT(AUTO) are   *
  *                      in effect.                              *
  *                                                           .  *
  *                      PK49348 also includes fixes for         *
  *                      the following REOPT(AUTO) problems:     *
  *                        -  AB0C4 at DSNXORRA + 0E00           *
  *                        -  Message DSNECP44 KEEPDYNAMIC IS    *
  *                           INVALID WITH REOPT  issued when    *
  *                           specifying both bind options       *
  *                           REOPT(AUTO) and KEEPDYNAMIC(YES)   *
  *                           on BIND PACKAGE subcommand         *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  IBM performance testing revealed that the number of DB2 Dynamic
  Statement Cache implicit SHORT PREPAREs was higher than expected
  for running dynamic SQL that use host-variables in applications
  bound with REOPT(AUTO).
                                                                 .
  For REOPT(AUTO) behavior, 'implicit' PREPAREs are those dynamic
  PREPAREs that result when DB2 itself determines that, in order
  to get a better access path using current application
  host-variable values, a new PREPARE should be done 'implicitly'
  on an SQL OPEN or EXECUTE statement after the application
  successfully issued the original PREPARE for the dynamic
  statement.  A Dynamic Statement Cache (DSC) SHORT PREPARE is so
  named when a PREPARE request (either explicit or DB2 implicit)
  results in a cache match for a dynamic statement already
  prepared in the DSC, thus avoiding the DB2 FULL PREPARE process
  and resulting in improved overall dynamic SQL performance.
                                                                 .
  For REOPT(AUTO), even though these DB2 implicit prepares became
  cache SHORT PREPAREs and avoided the FULL PREPARE process,
  IBM noted that the number of SHORT PREPAREs was higher than
  expected for usage of REOPT(AUTO) and that performance could be
  improved even more for REOPT(AUTO) by reducing that number of
  SHORT PREPAREs required. This apar, PK49348, was created to do
  that.
                                                                 .
  In addition to providing a performance improvement for
  REOPT(AUTO), this apar also addresses the following additional
  REOPT ( AUTO ) issues:
   -- When using REOPT(AUTO), sometimes ABEND0C4 occurred in
      DB2 csect DSNXORRA at offset 0E00
   -- When specifying bind options  REOPT(AUTO) and
      KEEPDYNAMIC(YES) together on BIND PACKAGE subcommand,
      message ' DSNECP44 KEEPDYNAMIC IS INVALID WITH REOPT '
      is issued invalidly.
      REOPT(AUTO) with KEEPDYNAMIC(YES) should be allowed.
                                                                 .
  

Problem conclusion

• DB2 REOPT(AUTO) support was changed as described above to
   (1) reduce number of DSC implicit SHORT PREPAREs required
   (2) prevent ABEND0C4 at DSNXORRA +0E00
   (3) support REOPT(AUTO) with KEEPDYNAMIC(YES) on BIND PACKAGE
       subcommand.
  

Temporary fix

• AK49348
  

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UK43584

Modules/Macros

• DSNECP44 DSNXECLC DSNXECLF DSNXEDP  DSNXEDSC
  DSNXEDS1 DSNXERT  DSNXORRA DSNXRDA  DSNXSLT
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R910 PSY UK43584

     UP09/02/14 P F902

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.