IBM Support

PH09498: SQLCODE 514 ON OPEN / SQLCODE 518 ON EXECUTE AFTER ROLLBACK WHENUSING KEEPDYNAMIC (YES) W DB2 DYNAMIC STATEMENT CACHING

A fix is available

Subscribe

You can track all active APARs for this component.

 

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