PI30005: 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.
  See PI23083 for DB2 10 for z/OS.
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 for z/OS users who use IBM DB2    *
  *                 Analytics Accelerator.                       *
  ****************************************************************
  * PROBLEM DESCRIPTION: Currently, IBM DB2 Analytics            *
  *                      Accelerator customers can use the DB2   *
  *                      accounting and statistics traces to     *
  *                      evaluate metrics per DB2 subsystem or   *
  *                      per thread.  However, customers would   *
  *                      like the ability to track metrics at    *
  *                      the statement granularity to better     *
  *                      manage and plan their workloads.        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  This new function APAR now enables Accelerator Statement Level
  Monitoring support in IFCID 316 and 401.
  
  This APAR has functional dependency on the IBM DB2 Analytics
  Accelerator V4 or higher.  Without the server support, no
  statement level monitoring metric will be reported.
  
  This APAR enables DB2 to report SQL statement's eligibility to
  be executed on the accelerators and to report SQL Statement
  Level Monitoring statistics for queries that are accelerated.
  The statement's eligibility and statistics will be reported in
  the IFCID 316, 401 trace records and the
  DSN_STATEMENT_CACHE_TABLE.
  
  The DSN_STATEMENT_CACHE_TABLE changes:
  1.  The ACCEL_OFFLOAD_ELIGIBLE column will be set to 'Y' when
      DB2 determines a dynamic cached query is eligible to be
      executed on the IBM DB2 Analytics Accelerator, when zparm
      or special register for accelerator modeling is enabled.
      'N' is the default value.  The statement is not eligible
      for acceleration.
  
  2.  The following columns in DSN_STATEMENT_CACHE_TABLE are
      filled with execution statistics as the dynamic cached
      statement is actually accelerated (column ACCELERATED is
      'YES').  These columns' description is listed below:
  
      Column         Data Type   Description
      -----------------------------------------------------------
      STAT_ACC_ELAP  BIGINT      Accumulated accelerator Elapsed
                                 time
      -----------------------------------------------------------
      STAT_ACC_CPU   BIGINT      Accumulated accelerator CPU time
      -----------------------------------------------------------
      STAT_ACC_ROW   BIGINT      Accumulated # of rows returned
                                 from accelerator
      -----------------------------------------------------------
      STAT_ACC_BYTE  BIGINT      Accumulated # of bytes returned
                                 from accelerator
      -----------------------------------------------------------
      STAT_ACC_1ROW  BIGINT      Time waited for 1st row of query
                                 result to be available from
                                 accelerator
      -----------------------------------------------------------
      STAT_ACC_DB2   BIGINT      Total time accelerator waited
                                 for DB2 to request query results
      -----------------------------------------------------------
      STAT_ACC_EXEC  BIGINT      Accumulated accelerator
                                 Execution time
      -----------------------------------------------------------
      STAT_ACC_WAIT  BIGINT      Accumulated accelerator queue
                                 Wait time
      -----------------------------------------------------------
  
  3.  The ACCELERATOR_NAME column contains the concatenated
      name(s) of accelerator(s) where the query is accelerated.
  
  Instrumentation changes:
  
  1.  IFCID 316 is changed to report when a cached statement is
      eligible for execution on an accelerator.  When a cached
      statement is accelerated, this trace record also contains
      statistics of the dynamic statement's acceleration in
      QW0316's R20 section per accelerator.  The 316 changes are
      reflected in the DSNDQW04 copy file and related DB2
      documentation on IFCID 316.
  
  2.  IFCID 401 is changed to report when a static statement is
      eligible for execution on an accelerator.  When a statement
      is accelerated, this trace record also contains statistics
      of the static statement's acceleration in QW0401's R20
      section per accelerator.  The 401 changes are reflected in
      the DSNDQW05 copy file and related DB2 documentation on
      IFCID 401.
  
  Reference:
    IDAAV4R1/K
  

Problem conclusion

Temporary fix

Comments

• DB2 will now support Accelerator Statement Level Monitoring.
  

APAR Information

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

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

  UI29032

Modules/Macros

•    DSNDQW04 DSNDQW05 DSNGEPLC DSNGEPSH DSNLXCQH
  DSNLXHOC DSNLXMRF DSNLXRBF DSNLXRFT DSNLXROP DSNLZHC1 DSNLZHP2
  DSNLZMON DSNLZRPA DSNLZR00 DSNWARDS DSNXECL  DSNXECLF DSNXECSS
  DSNXECST DSNXECW  DSNXECWA DSNXECWU DSNXEDP  DSNXEDSC DSNXEDS1
  DSNXEIST DSNXELX  DSNXEPM  DSNXERDS DSNXERT  DSNXERT2 DSNXEUCS
  DSNXEUFP DSNXOD12 DSNX8EKG
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI29032

     UP15/07/17 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.