A fix is available
APAR status
Closed as program error.
Error description
When using bind option KEEPDYNAMIC (YES) with Db2 Dynamic Statement Caching, in the scenario shown below the user expected Db2 to do an implicit PREPARE on a new SQL OPEN following SQL ROLLBACK, even though the implicit PREPARE that DB2 attempted on the last SQL OPEN failed before the SQL ROLLBACK was issued: PREPARE dyn cursor query (no HOLD) under KEEPDYNAMIC(YES) OPEN dyn query cursor FETCH cursor COMMIT - DB2 closes query cursor but keeps copy of the prepared- cached query across COMMIT. User only needs to do a new OPEN following the COMMIT instead of issuing new explicit PREPARE for the query. Db2 will do an implicit PREPARE on the new OPEN, using the 'kept' copy of the cached query. OPEN dyn cursor - Db2 attempts implicit PREPARE for query, but PREPARE fails and Db2 returns -SQLCODE for OPEN to user application. Db2 released the copy of the cached query it was keeping and the query is now in an 'unprepared' state. ROLLBACK - User application decides to issue ROLLBACK after receiving -SQLCODE OPEN query cursor - User expected Db2 to do implicit PREPARE for the query, even though the last implicit PREPARE failed leaving dyn query in an 'unprepared' state. Db2 returns SQLCODE -514 because the dyn query is in 'unprepared' state and Db2 no longer has query statement. The same occurs for a dyn DML stmt such as INSERT, UPDATE, DELETE in this scenario where SQL EXECUTE is used instead of OPEN and FETCH. In this scenario case, Db2 returns SQLCODE -518 on the EXECUTE that follows the ROLLBACK. Note that SQLCODEs -514 / -518 can also occur for the last OPEN or EXECUTE if a 2nd COMMIT is used instead of the ROLLBACK. The same results occur for both usages following a failed implicit PREPARE. Additional search keywords: SQLCODE514 SQLCODE518 SQLDYNSTMTCACHE
Local fix
If for the last SQL OPEN the Db2 implicit PREPARE failed, then issue an explicit SQL PREPARE for the query before the OPEN that follows the COMMIT or ROLLBACK.
Problem summary
**************************************************************** * USERS AFFECTED: * * All Db2 12 for z/OS users of Db2 Dynamic * * Statement Caching and Db2 bind option * * KEEPDYNAMIC(YES) for applications that use * * dynamic SQL and ROLLBACK or COMMIT within * * the same transaction. * * * * Especially users of New Function APAR/PTF * * PH00637 / UI58875 that supports improved * * concurrency between DDL and cached dynamic * * SQL transactions that issue ROLLBACK and * * COMMIT * **************************************************************** * PROBLEM DESCRIPTION: * * When Db2 Dynamic Statement Cache(DSC) * * is enabled and user ran dynamic SQL * * application bound KEEPDYNAMIC(YES), * * SQLCODE -518 occurred on an SQL * * EXECUTE for a dyn statement following * * an SQL ROLLBACK, after the last SQL * * EXECUTE for that same dyn statement * * issued "before" the ROLLBACK failed * * with a "prepare-related" error/ * * -SQLCODE. * * * * See more scenario description details * * in the Problem Summary section below. * * * * When SQL OPEN for dynamic cursor * * query/SELECT is used instead of SQL * * EXECUTE in the same scenario, SQLCODE * * -514 can occur for the SQL OPEN * * following the ROLLBACK. * **************************************************************** * RECOMMENDATION: * * Apply corrective PTF when available * **************************************************************** When Db2 Dynamic Statement Caching (DSC) is in effect, for the scenario shown below in an application bound with KEEPDYNAMIC YES and that issues dynamic SQL with a ROLLBACK, SQLCODE -518 is received on the EXECUTE following the ROLLBACK as described here below -- Application transaction bound KEEPDYNAMIC(YES): - DSC PREPARE an INSERT INTO Db2-table - This original prepare of the dyn INSERT on this Db2 thread is successful - SQL EXECUTE the dynamic INSERT - Application issues a COMMIT - At COMMIT because of KEEPDYNAMIC(YES), Db2 "keeps" the thread's prepared copy of the dyn INSERT stmt across the COMMIT, but marks the "cached" INSERT stmt as 'not actively in use' by this Db2 thread. - If there are no other Db2 threads with this same cached INSERT as currently 'in use', then a separate Db2 thread waiting to perform DDL on an object referenced by the dyn INSERT stmt can concurrently proceed, while the KEEPDYNAMIC dyn SQL transaction continues running after the COMMIT. . - SQL EXECUTE the dynamic INSERT again (following COMMIT) - the separate Db2 thread that is performing DDL on a table or object referenced by the INSERT has 'invalidated' the "cached" INSERT stmt, so Db2 must now perform an 'implicit' cache-prepare of the thread's copy of the dyn INSERT before it can be run again. With KEEPDYNAMIC(YES) behavior the user does not have to issue a new explicit PREPARE; instead Db2 does the full-prepare implicitly as part of the SQL EXECUTE. - During this 'implicit' full cache-prepare process for the dyn INSERT, Db2 encounters a SQLCODE -904 (or SQLCODE -911 , e.g.) situation because the DDL operation from the separate Db2 thread has not completed, and the table/object referenced by the dyn INSERT stmt is still in an 'X-locked' state. - So Db2 fails the SQL EXECUTE of the INSERT and returns the SQLCODE -904 to the dyn SQL transaction. - The dyn SQL transaction does not terminate, but continues to run. . - Upon receiving the -904 for the dyn INSERT statement, the KEEPDYNAMIC application issues an SQL ROLLBACK. . - Next, SQL EXECUTE the dyn INSERT again, without a new explicit PREPARE from the same transaction - Db2 returns SQLCODE -518 , indicating that the dynamic INSERT is not 'prepared' for execution. . In the above scenario, because of Db2 V12 support of KEEPDYNAMIC(YES) behavior across a ROLLBACK, the user does not expect Db2 to require a new 'explicit' PREPARE from the application for the new SQL EXECUTE INSERT following the ROLLBACK because the INSERT was originally prepared successfully for this Db2 thread before a COMMIT; even though on the last SQL EXECUTE INSERT before the ROLLBACK, the Db2 'implicit' full cache-prepare failed with SQLCODE -904. . Db2 failed the last SQL EXECUTE of the dyn INSERT stmt because 'before' the ROLLBACK, on the previous SQL EXECUTE INSERT that failed with SQLCODE -904, Db2 completely 'unprepared' this dyn SQL transaction thread's original prepared copy of the cached dyn INSERT stmt, including discarding the thread's link to the cached dyn INSERT statement text. So this Db2 thread no longer even had the statement text to use for any attempted 'implicit' prepare of the INSERT stmt. . The same applies to cached dyn SQL DELETE , UPDATE , etc. and cursor query ( SELECT ) in the same scenario. If the cached stmt is a cursor query , the failures would occur on the SQL OPENs for the cursor, where the OPEN after the ROLLBACK would fail with SQLCODE -514 (instead of -518). . This same problem can also occur if only COMMITs are used in the above scenario, instead of the ROLLBACK after the COMMIT. . Db2 concluded that, given the dynamic INSERT stmt was originally prepared successfully as a cached statement for this Db2 thread before a COMMIT, Db2 V12 should honor the SQL EXECUTE following the ROLLBACK (or COMMIT) even though on the last EXECUTE for the dyn INSERT before the ROLLBACK (or COMMIT), the Db2 implicit-prepare attempt failed for the INSERT stmt. Note that this will only be honored for such a previously failed implicit-prepare *if* the dyn stmt (including a cursor query) had been originally prepared successfully as a cached stmt before a COMMIT for this Db2 thread. .
Problem conclusion
Db2 V12 KEEPDYNAMIC support for cached statements was changed to honor the SQL EXECUTE (or SQL OPEN) following a ROLLBACK (or COMMIT) in the failing scenario as previously described in the above Problem Summary. . Additional search keywords: SQLDYNSTMTCACHE SQLCODE518 SQLCODE514 SQLCODE904 SQLCODE911
Temporary fix
Comments
APAR Information
APAR number
PH09498
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
C10
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2019-03-07
Closed date
2019-08-14
Last modified date
2019-09-01
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI64736
Modules/Macros
DSNXEDSC DSNXERT2
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
RC10 PSY UI64736
UP19/08/22 P F908
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":"12.0","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":"12.0","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
01 September 2019