A fix is available
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 number
PK27981
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
810
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2006-07-11
Closed date
2008-01-30
Last modified date
2008-03-03
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
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":"8.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":"8.1","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
03 March 2008