IBM Support

PI22531: DB2 10 FOR Z/OS NEW FUNCTION

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as new function.

Error description

  • DB2 10 for z/OS new function.
    
    
    Customers may also see
    ABND=04E-00D35011
    LOC=DSNLZGLM.DSNLZRCD:0057
    
    
    Additional keywords and symptoms:
    ********************************
    ABND04E 00D35011 RC00D35011
    DSNLZRCD ABNDID0057 0057
    

Local fix

  • n/a
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 10 for z/OS users of DB2             *
    *                 acceleration for dynamic queries who         *
    *                 also what to use DB2 Dynamic Statement       *
    *                 Caching for those accelerated dynamic        *
    *                 queries.                                     *
    *                                                              *
    *                                                              *
    *                                                              *
    ****************************************************************
    * PROBLEM DESCRIPTION: The use of an accelerator enables high  *
    *                      performance for executing complex       *
    *                      analytic queries. Prior to this new     *
    *                      function APAR, DB2 for z/OS did not     *
    *                      support Dynamic Statement Caching for   *
    *                      accelerated dynamic queries so a        *
    *                      PREPARE of an accelerated dynamic       *
    *                      query required the "full" non-cache     *
    *                      prepare process without the benefit     *
    *                      of statement caching that is available  *
    *                      to dynamic queries that do not run      *
    *                      accelerated.                            *
    *                                                              *
    *                                                              *
    *                                                              *
    *                                                              *
    *                                                              *
    *                                                              *
    *                                                              *
    *                                                              *
    *                                                              *
    *                                                              *
    *                                                              *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    This new function APAR now extends Dynamic Statement Cache
    (DSC) support to dynamic queries that are accelerated to an
    accelerator. This significantly improves performance of
    PREPAREs for accelerated dynamic queries because these
    PREPAREs can now benefit from the usage of the DSC as do
    queries that are not accelerated.
                                                                   .
    No changes are needed to enable Dynamic Statement Cache support
    for accelerated dynamic queries if Dynamic Statement Caching is
    already enabled for the DB2 subsystem.
                                                                   .
    This APAR introduces the following changes to the DB2 table
    DYNAMIC_STATEMENT_CACHE_TABLE:
    (1) New CHAR(10) column ACCELERATED is added.
    ACCELERATED identifies whether a cached dynamic query was
    prepared for acceleration to an accelerator server.
                                                                   .
    Possible values for column ACCELERATED are:
    NO
    The cached statement was not prepared for acceleration.
    This is the default value for a cached dynamic statement.
    This is also the value for a cached query under the following
    conditions:
    - The QUERY ACCELERATION behavior was not specified or was
      explicitly set to NONE when the dynamic query was prepared.
    - A QUERY ACCELERATION behavior other than ALL was specified
      when the dynamic query was prepared, but DB2 did not prepare
      the query for acceleration because the query did not
      qualify for acceleration according to the QUERY ACCELERATION
      behavior specified.
    DB2 will consider this cache entry as a possible cache match
    during any of these prepare scenarios previously described,
    unless a QUERY ACCELERATION behavior is specified but the query
    can never be accelerated (see the description of the column
    value NEVER below).
                                                                   .
    YES
    The cached query was prepared for acceleration to an accelerator
    server when a QUERY ACCELERATION behavior was specified. If a
    QUERY ACCELERATION behavior is specified when a dynamic query
    is prepared, DB2 can consider this cache entry for a possible
    cache match during the prepare of the dynamic query. If the
    QUERY ACCELERATION behavior is not specified or is explicitly
    set to NONE when the query is prepared, DB2 will not consider
    this cache entry for a cache matched during the prepare.
                                                                   .
    NEVER
    The cached query was not prepared for acceleration when a QUERY
    ACCELERATION behavior was specified for the prepare, because
    the query can never be accelerated.
      If the QUERY ACCELERATION behavior is set to ENABLE, ENABLE
      WITH FAILBACK, or ELIGIBLE for the prepare of the query,
      DB2 will consider this cache entry first as a possible cache
      match during the prepare, to verify if the query has been
      previously cached as 'never can be accelerated'.
      If the QUERY ACCELERATION behavior is set to ALL for the
      prepare of the query, DB2 will not consider this cache
      entry as a possible cache match during the prepare.
      If the QUERY ACCELERATION behavior is not specified or
      is explicitly set to NONE for the prepare of the query,
      DB2 will not consider this cache entry as a possible cache
      match during the prepare.
                                                                   .
    With this APAR/PTF applied, when QUERY ACCELERATION behavior is
    used for a DB2 subsystem that has DB2 Dynamic Statement Caching
    (DSC) active, it is possible for a cached query to have
    multiple entries in the DSC, which is then reflected in the
    table DSN_STATEMENT_CACHE_TABLE. This can occur if the same
    dynamic query is run by applications that do not use QUERY
    ACCELERATION, as well as, by applications that "do" use QUERY
    ACCELERATION on the same DB2 subsystem.
                                                                   .
    Query acceleration behavior is specified by either the
    DB2 subsystem parameter QUERY_ACCELERATION, the
    QUERYACCELERATION  bind option, or the CURRENT QUERY
    ACCELERATION special register. The precedence order
    (lowest to highest) is:
      The QUERY_ACCELERATION subsystem parameter
      The QUERYACCELERATION bind option, if specified
      An explicit SET CURRENT QUERY ACCELERATION statement
                                                                   .
    (2) This APAR also introduces these additional columns to the
    table DYNAMIC_STATEMENT_CACHE_TABLE. These columns are
    for future DB2 use and will be described and used
    in a future DB2 APAR/PTF:
    EXPANSION_REASON   CHAR(2)      NOT NULL WITH DEFAULT
    ...
    STAT_ACC_ELAP      BIGINT       NOT NULL WITH DEFAULT
    STAT_ACC_CPU       BIGINT       NOT NULL WITH DEFAULT
    STAT_ACC_ROW       BIGINT       NOT NULL WITH DEFAULT
    STAT_ACC_BYTE      BIGINT       NOT NULL WITH DEFAULT
    STAT_ACC_1ROW      BIGINT       NOT NULL WITH DEFAULT
    STAT_ACC_DB2       BIGINT       NOT NULL WITH DEFAULT
    STAT_ACC_EXEC      BIGINT       NOT NULL WITH DEFAULT
    STAT_ACC_WAIT      BIGINT       NOT NULL WITH DEFAULT
    ACCEL_OFFLOAD_ELIGIBLE CHAR(1)  NOT NULL WITH DEFAULT 'N'
    ACCELERATOR_NAME   VARCHAR(128)          WITH DEFAULT NULL
    

Problem conclusion

Temporary fix

Comments

  • new function support of dsc for idaa
    Additional Keywords:
    IDAAV4R1/K
    

APAR Information

  • APAR number

    PI22531

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED UR1

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    YesSpecatt / New Function

  • Submitted date

    2014-07-22

  • Closed date

    2015-01-13

  • Last modified date

    2015-12-09

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

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

    UI24359

Modules/Macros

  •    DSNTESC  DSNTXTA  DSNTXTB  DSNXEADW DSNXECL
    DSNXECLF DSNXECLN DSNXECW  DSNXECWA DSNXECWU DSNXEDP  DSNXEDSC
    DSNXEDS1 DSNXEPM  DSNXERT  DSNXERT2 DSNXEUFP DSNXEUF0 DSNXMIT2
    DSNXODML DSNXOD12 DSNXOIN  DSNXONZA DSNXONZB DSNXRFF  DSNXRFN
    DSNXRIWS
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UI24359

       UP15/01/29 P F501

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 #: PI22531

Modified date: 09 December 2015


Translate this page: