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

Obtain the fix for this APAR.

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 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.