PM87668: SQLCODE198 FROM DSNHLEX FOR DYN SELECT BOUND KEEPDYNAMIC (YES) WHEN BOTH QUERY ACCELERATION AND DYNAMIC STATEMENT CACHE USED

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • WHEN USING the IBM DB2 Analytics Accelerator for z/OS
    WITH QUERY ACCELERATION not= NONE AND DB2 DYNAMIC
    STATEMENT CACHING IS ENABLED, A DYNAMIC SELECT FROM
    A PACKAGE BOUND KEEPDYNAMIC (YES) INTERMITTENTLY
    FAILS WITH SQLCODE -198 FROM DB2 CSECT DSNHLEX.
    Other negative SQLCODES, INCLUDING SQLCODE301 and
    SQLCODE313 ARE ALSO POSSIBLE.
    THESE FAILURES DO NOT OCCUR UNDER KEEPDYNAMIC(NO).
    .
    Additional search keywords: IDAAV2R1/K IDAAV3R1/K
                                SQLKEEPDYNAMIC
                                SQLDYNSTMTCACHE
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 Users of dynamic SQL queries bound   *
    *                 KEEPDYNAMIC(YES) and both IBM DB2 Analytics  *
    *                 Accelerator and DB2 dynamic statement cache  *
    *                 are active for the same DB2 thread or        *
    *                 application                                  *
    ****************************************************************
    * PROBLEM DESCRIPTION: SQLCODE198 from DB2 csect DSNHLEX for   *
    *                      a dynamic query bound KEEPDYNAMIC YES   *
    *                      when both DB2 QUERY ACCELERATION and    *
    *                      dynamic statement caching are active    *
    *                      for the DB2 thread or application.      *
    *                                                              *
    *                      Other SQLCODEs including SQLCODE104,    *
    *                      SQLCODE301, and SQLCODE313 are possible *
    *                                                              *
    *                      The following DB2 csect abends are also *
    *                      possible:                               *
    *                       ABEND0C4 RC00000038 DSNXEIST +0360     *
    *                       ABEND0C4 RC00000038 DSNXEUSC +034E     *
    *                       ABEND0C4 RC00000038 DSNXEFB  +045A     *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    When using the IBM DB2 Analytics Accelerator for z/OS (IDAA) and
    both CURRENT QUERY ACCELERATION ^= NONE and DB2 dynamic stmt
    caching are active for the running DB2 thread, a dynamic query
    prepared and executed from a package bound KEEPDYNAMIC (YES)
    intermittently fails with SQLCODE -198 from DB2 Parser csect
    DSNHLEX. Other SQLCODEs, including SQLCODE104, SQLCODE301, and
    SQLCODE313 are also possible.
    The problem can also occur when, in addition to all the above
    conditions, PREPARE attribute CONCENTRATE STATEMENTS WITH
    LITERALS is also used.
                                                                   .
    These failures do not occur when using KEEPDYNAMIC(NO) or
    CURRENT QUERY ACCELERATION = NONE (the default).
                                                                   .
    Problem occurred when a query that was not accelerated to IDAA
    (because it did not qualify) but instead was cached in the DB2
    dynamic statement cache (DSC), and that cached query was LRU'd
    or otherwise invalidated out of the global DSC while the query
    was being run by the application, causing DB2 to do an implicit
    re-prepare for the query when it is run after a COMMIT (due to
    KEEPDYNAMIC YES ).
                                                                   .
    The SQLCODES occurred because DB2 incorrectly allowed the
    failing DB2 thread's copy of the statement text and internal
    statement block to be freed after detecting that the cached copy
    of the statement was 'invalidated'. This only occurs when IDAA
    and QUERY ACCELERATION are also active for the same DB2 thread,
    even though the failing query is *not* accelerated to IDAA.
                                                                   .
    Other possible symptoms include, the following abends in DB2
    csects:
     ABEND0C4 RC00000038 DSNXEIST +0360
     ABEND0C4 RC00000038 DSNXEUSC +034E
     ABEND0C4 RC00000038 DSNXEFB  +045A
    

Problem conclusion

  • DB2 was changed to not incorrectly free the DB2 thread's local
    copy of both the cached statement's text and internal control
    block in the scenario previously described.
                                                                   .
    Additional search keywords: IDAAV2R1/K IDAAV3R1/K
                                SQLKEEPDYNAMIC SQLDYNSTMTCACHE
                                SQLCODE198 SQLCODE104 SQLCODE301
                                SQLCODE313
                                AB0C4 RC38
                                OFFSET360 OFFSET34E OFFSET45A
    

Temporary fix

  • AM87668
    

Comments

APAR Information

  • APAR number

    PM87668

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-04-23

  • Closed date

    2013-07-12

  • Last modified date

    2013-08-02

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

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

    UK95797 UK95798

Modules/Macros

  • DSNXEDP  DSNXEDSC
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK95797

       UP13/07/29 P F307 ½

  • R910 PSY UK95798

       UP13/07/29 P F307 ½

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.



Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM87668

Modified date:

2013-08-02

Translate my page

Machine Translation

Content navigation