IBM Support

PI52619: DYNAMIC CACHE PERFORMANCE DEGRADATION WHEN USING DB2 SYSTEM PARM GET_ACCEL_ARCHIVE = YES + 'CONCENTRATE STATEMENTS WITH LITERALS'

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Performance degradation can occur for the DB2 Dynamic Statement
    Cache when ALL of the following conditions are true:
     (1) DB2 subsystem parameters CACHEDYN = YES and
         GET_ACCEL_ARCHIVE = YES (for SQL query acceleration)
     (2) An SQL query references a table that has been accelerated
         to an accelerator but *not* archived on the accelerator,
         and the accelerator is *not* started when the PREPARE for
         the query occurs
     (3) The PREPARE attribute CONCENTRATE STATEMENTS WITH LITERALS
         is specified but no literals are used in the query
    .
    A query may be prepared incorrectly for acceleration
    when;
    (1) QUERY ACCELERATION = NONE
    (2) DB2 subsystem parameters CACHEDYN = YES and
    GET_ACCEL_ARCHIVE = YES
    (3) Same statement has been prepared previously for
        acceleration.
    .
    

Local fix

  • Reset DB2 zparm GET_ACCEL_ARCHIVE to NO.
    The zparm GET_ACCEL_ARCHIVE=YES should not be used on
    a permanent or long-term basis because of its DB2 subsystem-wide
    effect on ALL queries that reference accelerated tables that are
    *not* archived. Instead, set special register CURRENT
    GET_ACCEL_ARCHIVE = YES for those workloads or queries that
    reference an accelerated table you know has been archived to
    an accelerator.
                                                         .
    Search keywords: IDAAV3R1/K IDAAV4R1/K
                     SQLDYNSTMTCACHE SQLPERFORMANCE
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: Users of DB2 10 and 11 and IBM DB2 Analytics *
    *                 Accelerator for z/OS V4 and V5.              *
    ****************************************************************
    * PROBLEM DESCRIPTION: 1. Performance degradation may occur    *
    *                         for DB2 Dynamic Statement Cache      *
    *                         when All the following are true:     *
    *                       a. GET_ACCEL_ARCHIVE behavior for      *
    *                          acceleration is enabled             *
    *                       b. The SQL query references a table    *
    *                          has been accelerated but not        *
    *                          archived on an accelerator          *
    *                       c. The PREPARE attribute CONCENTRATE   *
    *                          STATEMENTS WITH LITERALS is         *
    *                          specified but no literals are       *
    *                          replaced in the statement.          *
    *                                                          .   *
    *                      2. When the following conditions        *
    *                         occur during a PREPARE, the query    *
    *                         may be prepared for acceleration     *
    *                         incorrectly when it should either    *
    *                         be prepared without acceleration     *
    *                         or get SQL error -4742 on the        *
    *                         PREPARE statement:                   *
    *                       a. QUERY ACCELERATION behavior is      *
    *                          NONE                                *
    *                       b. GET_ACCEL_ARCHIVE behavior is YES   *
    *                       c. The same statement was previously   *
    *                          prepared for acceleration.          *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    1. The potential performance degradation is due to incorrect
       code path where a cache match is not attempted when DB2
       evaluates the statement to not be prepared for acceleration
       and should instead be prepared for execution in DB2 while
       considering the CONCENTRATE STATEMENTS WITh LITERALS
       prepare attribute.
                                                                 .
    2. The incorrect behavior on prepare is due to incorrect code
       path where a match to an entry to the same statement
       prepared for acceleration is attempted and successful.
    

Problem conclusion

  • 1. DB2 code is modified to correctly attempt to cache match
       the correct entry.
                                                              .
    2. DB2 code is modified to not match an entry prepared for
       acceleration when QUERY ACCELERATION behavior is NONE
       and GET_ACCEL_ARCHIVE behavior is YES.
                                                              .
    Additional Keywords: IDAAV4R1/K IDAAV5R1/K
                         SQLDYNSTMTCACHE SQLPERFORMANCE
    

Temporary fix

Comments

APAR Information

  • APAR number

    PI52619

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2015-11-17

  • Closed date

    2016-05-21

  • Last modified date

    2016-07-04

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

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

    UI38054 UI38055

Modules/Macros

  •    DSNXEDSC
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UI38054

       UP16/06/07 P F606

  • RB10 PSY UI38055

       UP16/06/07 P F606

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.



Document information

More support for: DB2 for z/OS

Software version: A10

Reference #: PI52619

Modified date: 04 July 2016


Translate this page: