IBM Support

PM90886: NEW FUNCTION FOR DB2 V10

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as new function.

Error description

  • New function for DB2 V10.                              (dm1761)
    

Local fix

  • n/a
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: DB2 users that need to evaluate the          *
    *                 CPU and elapsed time spent in DB2 for SQL    *
    *                 that would be eligible for acceleration.     *
    ****************************************************************
    * PROBLEM DESCRIPTION: This APAR allows a subsystem to model   *
    *                      the existence of an accelerator to      *
    *                      evaluate the CPU and elapsed time       *
    *                      spent in DB2 for SQL that would be      *
    *                      eligible for acceleration.              *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    A new zparm and three new accounting fields have been added to
    provide an accelerator modeling function. Accelerator modeling
    allows queries to provide the CPU time and elapsed time spent
    processing SQL in DB2 that may be eligible for execution on an
    accelerator.
    Additionally, EXPLAIN will populate DSN_QUERYINFO_TABLE, which
    shows if a query is eligible for offload or not.
    
    The ACCELMODEL subsystem parameter determines whether to
    enable modeling of query workload for evaluating potential
    savings for both the accumulated elapsed time and CPU time if
    the plan is executed on an accelerator. Only queries that are
    deemed eligible for execution on an accelerator by DB2 will be
    included in accelerator-related fields of accounting  trace
    IFCID 3.
    
    NO
    
    Specifies that no modeling is to be performed.  This is the
    default setting.
    
    YES
    
    Specifies that modeling is to be performed and consider
    acceleration eligibility for an SQL statement and update the
    new accounting fields accordingly
    
    To enable modeling, the IDAA special register CURRENT QUERY
    ACCELERATION and zparm QUERY_ACCELERATION (set by the CURRENT
    QUERY ACCEL field of this panel) must be set to NONE for
    accelerator modeling to be used.  All other values for the
    special register and zparm will cause DB2 to attempt to
    accelerate queries instead of performing accelerator modeling.
    This means that existing queries that already execute on the
    accelerator with CURRENT QUERY ACCELERATION = ENABLE,
    ENABLE WITH FAILBACK, ELIGIBLE, or ALL will not be part of the
    accelerator-related accounting fields.
    
    DB2 does not perform accelerator modeling for SQL queries
    executed under a native stored procedure (aka SQLPL procedure).
    However, DB2 does perform accelerator modeling for SQL queries
    executed under an external stored procedure.  Accelerator
    modeling for native or SQLPL procedures is a consideration for
    the future.
    
    EXPLAIN for a statement that goes through accelerator modeling
    will have the following output:
    (1) If the query is eligible for offload,
    DSN_STATEMNT_TABLE.REASON = 'ACCELMODEL ELIGIBLE'
    
    (2) If the query is not eligible for offload,
    DSN_STATEMNT_TABLE.REASON = 'ACCELMODEL NOT ELIGIBLE'
    
    (3) DSN_QUERYINFO_TABLE will show the accelerated or not
    accelerated output (same as today). Column QINAME1 will have a
    value of 'ACCELMDL', which is normally the accelerator name
    
    (4) PLAN_TABLE and all other explain tables will show the DB2
    access path
    
    Three new accounting fields will be added to the QWAC of the
    IFCID 3. These values do not include times from SQL executed
    in a native SQL stored procedure that are eligible for
    execution on an accelerator.
    QWAC_ACCEL_ELIG_ELA  CL8   The accumulated elapsed time spent
                               processing SQL in DB2 that may be
                               eligible for execution on an
                               accelerator.
    
    QWAC_ACCEL_ELIG_CP   CL8   The accumulated CPU time spent
                               processing SQL  in DB2 that may be
                               eligible for execution on an
                               accelerator.
    
    QWAC_ACCEL_ELIG_SE DS CL8 The accumulated CPU time consumed on
                              an IBM specialty engine while
                              processing SQL in DB2 that may be
                              eligible for execution on an
                              accelerator.
    
    Field QWP4ACMO is added to the IFCID 106 trace record
    to track the ACCELMODEL parameter.
    
    Keywords:
    IDAAV4R1/K IDAAV3R1/K IDAAV2R1/K SQLACCELMODEL
    

Problem conclusion

Temporary fix

Comments

APAR Information

  • APAR number

    PM90886

  • 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

    2013-06-11

  • Closed date

    2013-09-19

  • Last modified date

    2013-11-04

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

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

    UK97693

Modules/Macros

  • DSN@XAZP DSNB1TAD DSNDQWAC DSNDQWPZ DSNDSPRM DSNRUB01 DSNTIDXA
    DSNTIDXB DSNTIJUZ DSNTINST DSNTLRAC DSNTXAZP DSNTXSTB DSNWAACT
    DSNWACDC DSNWACDF DSNWDFIF DSNWVAPR DSNWVSMT DSNWVSR1 DSNWVZCK
    DSNWVZIT DSNWVZPS DSNWVZSA DSNWVZSS DSNWZIFA DSNXECT  DSNXEDP
    DSNXEDS1 DSNXEET  DSNXEPM  DSNXERT  DSNXERT2 DSNXESTR DSNXESTS
    DSNXODML DSNXOD4  DSNXOD5  DSNXOIN  DSNXOMPS DSNXONZO DSNXOOP
    DSNXRCUF DSNXRRTN DSNXTAD  DSNX8SMF DSNX9WCA DSNZCMD1 DSNZINIT
    DSN3EOT0 DSN6SPRM
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK97693

       UP13/10/04 P F310

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 #: PM90886

Modified date: 04 November 2013


Translate this page: