PM96478: NEW FUNCTION FOR DB2 V11

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

Local fix

  • n/a
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All users of DB2 11 for z/OS who are         *
    *                 using or interested in the IBM DB2           *
    *                 Analytics Accelerator.                       *
    ****************************************************************
    * PROBLEM DESCRIPTION: This APAR provides new function to      *
    *                      allow a DB2 subsystem to model          *
    *                      the existence of an accelerator to      *
    *                      evaluate the CPU and elapsed time spent *
    *                      in DB2 for static SQL queries that      *
    *                      would potentially be eligible for       *
    *                      acceleration if an accelerator were     *
    *                      active. No accelerator is required or   *
    *                      needs to be active for this modeling to *
    *                      occur.                                  *
    *                      This APAR also fixes SQLCODE -950 for   *
    *                      an INSERT with subselect involving      *
    *                      a three part name for remote tables.    *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    This APAR provides new function to allow a DB2 subsystem to
    model the existence of an accelerator to evaluate the CPU
    and elapsed time spent in DB2 for dynamic and static SQL
    queries that would be eligible for acceleration if an
    accelerator were active.  No accelerator is required or
    needs to be active for this modeling to occur.
    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.  Also the special register and
    zparm GET_ACCEL_ARCHIVE must be set to NO 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.
    
    For SQL INSERT with a subselect statement, even though only
    the SELECT itself may be eligible for acceleration,
    the reported CPU and elapsed time spent in DB2 includes
    the INSERTs performed in DB2 as part of the SQL statement.
    The CPU and elapsed time starts from the beginning of
    the SQL statement execution and ends when the entire SQL
    statement is completed.  DB2 does not breakdown the CPU and
    eligible time for 'parts' of an SQL statement execution
    
    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.
    
    This APAR also has the following fix:
    An SQLCODE -950 is incorrectly returned for an INSERT
    with subselect that has a three part name for remote
    tables when QUERY ACCELERATION=ENABLE,
    ENABLE WITH FAILBACK, ELIGIBLE or ALL.  SQLCODE -950 is
    also incorrectly returned when GET_ACCEL_ARCHIVE = YES.
    Execution of an SQL statement with a three part name on
    an accelerator is not supported, so the SQL statement
    will either execute on DB2 or return SQLCODE -4742
    depending on the values of QUERY ACCELERATION and
    GET_ACCEL_ARCHIVE.
    
    Keywords:
    IDAAV3R1/K IDAAV2R1/K SQLACCELMODEL SQLINSERT SQLCODE950
    IDAAV4R1/K
    

Problem conclusion

Temporary fix

Comments

APAR Information

  • APAR number

    PM96478

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    B10

  • Status

    CLOSED UR1

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    YesSpecatt / New Function

  • Submitted date

    2013-09-05

  • Closed date

    2013-12-12

  • Last modified date

    2014-03-28

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

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

    UI13488

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 DSNWZIFB DSNXECT  DSNXEDP
    DSNXEDS1 DSNXEET  DSNXEPM  DSNXEPP  DSNXERT  DSNXERT2 DSNXESTR
    DSNXESTS DSNXODML DSNXOD4  DSNXOD5  DSNXOIN  DSNXOMPS DSNXONZO
    DSNXOOP  DSNXRCUF DSNXRRTN DSNXTAD  DSNX8SMF DSNX9WCA DSN3EOT0
    DSN6SPRM
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RB10 PSY UI13488

       UP14/01/10 P F401

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.



Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for z/OS

Software version:

B10

Reference #:

PM96478

Modified date:

2014-03-28

Translate my page

Machine Translation

Content navigation