PK27981: IMPROVE PERFORMANCE OF DESCRIBE FUNCTION FOR DYNAMIC SQL WHEN BOTH REOPT(ONCE) AND DYNAMIC STATEMENT CACHE ARE USED

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Improve the performance of the DESCRIBE function for dynamic
  SQL when dynamic statement cache feature is active along
  with REOPT ( ONCE ).  With REOPT(ONCE), a DESCRIBE may require
  an implicit prepare of the dynamic statement due to
  the deferred prepare feature of REOPT(ONCE) that defers
  the prepare until OPEN or EXECUTE of the dynamic statement.
  The DESCRIBE prepare does not benefit from the dynamic
  statement cache short prepare.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: DB2 Users of REOPT(ONCE) or REOPT(AUTO),     *
  *                 DESCRIBE, and DB2 dynamic statement caching  *
  ****************************************************************
  * PROBLEM DESCRIPTION: When using the bind options REOPT(ONCE) *
  *                      or REOPT(AUTO) with dynamic statement   *
  *                      caching active and executing a DESCRIBE *
  *                      for a dynamic SQL statement,            *
  *                      DB2 performance was impacted by a full  *
  *                      "non-cache" prepare for the DESCRIBE.   *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  When using bind options REOPT(ONCE) or REOPT(AUTO) with dynamic
  statement caching, the prepare does not actually occur via
  the PREPARE statement.  Instead, the actual prepare occurs
  implicitly on the OPEN or EXECUTE of the dynamic statement.
  The REOPT options require that the values of the host-variables
  specified in the SQL statement be known during the prepare of
  the statement.  Given that the EXEC SQL PREPARE only uses
  parameter markers for where the host-vars would appear in
  the SQL statement to be prepared, the host-var values are not
  known until the OPEN or EXECUTE which specifies
  the host-variables.  So the actual prepare is deferred until
  the time of the OPEN or EXECUTE.  However, the EXEC SQL PREPARE
  statement will return a SQLCODE0 to the application, even though
  an actual prepare was not done.
                                                                 .
  Given that no prepare occurs via the PREPARE statement,
  if an SQL DESCRIBE is requested after the PREPARE but before
  the OPEN or EXECUTE, there is no information to return about
  the statement. So as part of the DESCRIBE process,
  DB2 implicitly performs a temporary "full prepare" of dynamic
  SQL so that DESCRIBE info can be returned as requested.
  Given that the full prepare is temporary, DB2 does not want to
  potentially cause a "cache" prepare that would insert a prepared
  statement into the cache, consequently, the DESCRIBE does not
  benefit from the cache "short prepare".  Therefore the temporary
  full prepare is a regular non-cache dynamic SQL prepare that is
  later destroyed when the OPEN or EXECUTE is requested and the
  statement is re-prepared as required for REOPT processing.
                                                                .
  This then shows up as two prepares for one statement which can
  be noticeable via performance measurement.  This is working as
  designed for the combination of DESCRIBE and REOPT where two
  prepares are unavoidable (there is no code error for this),
  however, DB2 decided to improve upon the prepare performance for
  the DESCRIBE temporary prepare.
  

Problem conclusion

• DB2 code was changed to allow a DESCRIBE prepare for a dynamic
  SQL statement with REOPT(ONCE) or REOPT(AUTO) and dynamic stmt
  caching active to take advantage of the cache " short prepare "
  to find a matching dynamic statement already prepared in
  the cache, instead of always doing a non-cache " full prepare ",
  when the statement has not been prepared in the application
  thread before the DESCRIBE request is made.
  However, if a matching cached dynamic statement is not found,
  then the DESCRIBE will do a non-cache full prepare as before to
  build the statement data required for the DESCRIBE, and
  this prepare will NOT cause a new insert into the dynamic
  statement cache.
  

Temporary fix

• AK27981
  

Comments

APAR Information

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

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

  UK33370 UK33371

Modules/Macros

• DSNXEDP  DSNXEDSC
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R810 PSY UK33370

     UP08/02/16 P F802

• R910 PSY UK33371

     UP08/02/16 P F802

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.