PI23083: DB2 10 FOR Z/OS NEW FUNCTION

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• DB2 10 for z/OS new function.
  See PI30005 for DB2 11 for z/OS.
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 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.
  ×**** PE15/08/21 FIX IN ERROR. SEE APAR PI46967  FOR DESCRIPTION
  

APAR Information

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

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

  UI24713

Modules/Macros

•    DSNDQW04 DSNDQW05 DSNGEPLC DSNGEPSH DSNLXCQH
  DSNLXHIM DSNLXHNO DSNLXHOC DSNLXHPR DSNLXHSE DSNLXHSP DSNLXMRF
  DSNLXRAX DSNLXRBF DSNLXRCL DSNLXRCM DSNLXRCN DSNLXRDT DSNLXRFT
  DSNLXRLS DSNLXROP DSNLXRSL DSNLXRSQ DSNLXRUW DSNLXSET DSNLZHC0
  DSNLZHC1 DSNLZHC2 DSNLZHP0 DSNLZHP1 DSNLZHP2 DSNLZHP3 DSNLZHR0
  DSNLZHR1 DSNLZMON DSNLZRPA DSNLZR00 DSNTBAR  DSNTBCM  DSNWARDS
  DSNXECL  DSNXECLF DSNXECLN DSNXECNT DSNXECSS DSNXECST DSNXECW
  DSNXECWA DSNXECWU DSNXEDS1 DSNXEIST DSNXELX  DSNXEPM  DSNXERDS
  DSNXERT  DSNXERT2 DSNXESET DSNXEUCS DSNXEUFP DSNXEUF0 DSNXMIT2
  DSNXOD12 DSNXRCUF DSNXRRTN DSNX8EKG
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI24713

     UP15/02/12 P F502

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.