IBM Support

PI23083: 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.
    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 number

    PI23083

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

  • Closed date

    2015-01-27

  • Last modified date

    2015-09-30

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



Document information

More support for: DB2 for z/OS

Software version: A10

Reference #: PI23083

Modified date: 30 September 2015


Translate this page: