PI28660: DB2 11 FOR Z/OS NEW FUNCTION

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• DB2 11 for z/OS new function.
  IDAAV4R1/K
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 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.
  3 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'.
  3 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.
  3 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:
  3 The QUERY_ACCELERATION subsystem parameter
  3 The QUERYACCELERATION bind option, if specified
  3 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
  

APAR Information

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

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

  UI28976

Modules/Macros

•    DSN@DMEM DSNADMEM DSNTESC  DSNTINS3 DSNTXTA
  DSNTXTB  DSNXECL  DSNXECLF DSNXECW  DSNXECWA DSNXECWU DSNXEDP
  DSNXEDSC DSNXEDS1 DSNXEPM  DSNXERT  DSNXERT2 DSNXEUFP DSNXODML
  DSNXOD12 DSNXOIN  DSNXONZA DSNXONZB DSNXRFFL DSNXRIWS
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI28976

     UP15/07/16 P F507

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.