IBM Support

PI30005: DB2 11 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 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 number

    PI30005

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    B10

  • Status

    CLOSED UR1

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2014-11-19

  • Closed date

    2015-07-01

  • Last modified date

    2015-08-03

  • 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.



Document information

More support for: DB2 for z/OS

Software version: B10

Reference #: PI30005

Modified date: 03 August 2015


Translate this page: