PI10639: DB2 10 AND 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 10 and DB2 11 for z/OS new function.
    

Local fix

  • n/a
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 for z/OS  users of the following     *
    *                 dynamic query acceleration features:         *
    *                 (1) A DB2 'virtual' accelerator for queries  *
    *                 (2) Either the dynamic SQL statement EXPLAIN *
    *                 or the special register CURRENT EXPLAIN      *
    *                 MODE = EXPLAIN to evaluate a dynamic SQL     *
    *                 query's eligibility for acceleration         *
    ****************************************************************
    * PROBLEM DESCRIPTION: This APAR provides new function that    *
    *                      allows either the dynamic EXPLAIN       *
    *                      statement or the special register       *
    *                      CURRENT EXPLAIN MODE = EXPLAIN to be    *
    *                      used with DB2 Profile Monitor to        *
    *                      evaluate a dynamic SQL query's          *
    *                      eligibility for acceleration against    *
    *                      a specific accelerator named in the DB2 *
    *                      Profile table DSN_PROFILE_ATTRIBUTES .  *
    *                      This is most useful when you want to    *
    *                      use a 'virtual' accelerator for query   *
    *                      evaluation instead of a 'real'          *
    *                      accelerator.                            *
    *                                                              *
    *                      This APAR also addresses the following  *
    *                      abends when DB2 unexpectedly executes   *
    *                      an accelerated dynamic SQL query that   *
    *                      was prepared using CURRENT EXPLAIN      *
    *                      MODE = EXPLAIN :                        *
    *                      (1) ABEND04E RC00C90101 DSNIBCTD :5007  *
    *                      (2) ABEND04E RC00E20009                 *
    *                          DSNXEDC3 . DSNSVSFB +0898           *
    *                      Other abend symptoms may be possible.   *
    *                      The abends can occur with or without    *
    *                      using a virtual accelerator.            *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    Currently, the DB2 dynamic SQL statement EXPLAIN or the DB2
    special register CURRENT EXPLAIN MODE = EXPLAIN can be used to
    evaluate a dynamic SQL query's eligibility for acceleration.
    However, DB2, not the user, chooses the target accelerator used
    for this acceleration evaluation.  If the user wants a 'virtual'
    accelerator to be used for the query's acceleration evaluation
    instead of a 'real' accelerator when both are 'active' for the
    DB2 subsystem, DB2 will not choose the 'virtual' accelerator.
    .
    This APAR provides new function that allows either the dynamic
    EXPLAIN statement or the special register CURRENT EXPLAIN
    MODE = EXPLAIN to be used with DB2 Profile Monitor to evaluate
    a dynamic SQL query's eligibility for acceleration against
    a specific accelerator named in the DB2 Profile table
    SYSIBM.DSN_PROFILE_ATTRIBUTES .  In this way, the user can
    direct DB2 to use instead a virtual accelerator for evaluating
    a query for acceleration.
    .
    A new function keyword, ACCEL_NAME_EXPLAIN, for
    SYSIBM.DSN_PROFILE_ATTRIBUTES has been added. The column
    ATTRIBUTE1 contains the real or virtual accelerator
    name. This profile monitoring attribute is supported
    for the following profile scopes:
      Global
      AUTHID+LOCATION
      PLANNAME+COLLID+PKGNAME
    .
    This new attribute directs a dynamic EXPLAIN statement
    to evaluate an SQL query for acceleration to the accelerator
    defined in ACCEL_NAME_EXPLAIN. When using the special register
    CURRENT EXPLAIN MODE = EXPLAIN, during the PREPARE of
    a dynamic SQL query the query is evaluated for acceleration
    to the accelerator defined in ACCEL_NAME_EXPLAIN.
    .
    The new function ACCEL_NAME_EXPLAIN will not be used for the
    following scenarios:
    (1) static SQL queries during BIND/REBIND PACKAGE EXPLAIN(YES)
        or EXPLAIN(ONLY)
    (2) dynamic SQL queries PREPAREd when using CURRENT EXPLAIN
        MODE=YES
    .
    ACCEL_NAME_EXPLAIN will not cause an SQL query to be actually
    accelerated to a specific accelerator at execution time.
    .
    Example
    -------
    The following example assumes there is an existing
    virtual accelerator with the name VACCEL.  The INSERT
    statements below create a global profile such that all dynamic
    EXPLAIN statements with CURRENT QUERY ACCELERATION=ENABLE,
    ENABLE WITH FAILBACK, ELIGIBLE or ALL will use the
    accelerator VACCEL.
    .
      INSERT INTO SYSIBM.DSN_PROFILE_TABLE (PROFILEID)
       VALUES (1);
    .
      INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES
       (PROFILEID,KEYWORDS,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3)
       VALUES
       (1, 'ACCEL_NAME_EXPLAIN', 'VACCEL',NULL,NULL);
    .
    After executing the INSERT statements above, the
    -START PROFILE command must be issued so that the profile
    is activated. At this time all dynamic EXPLAIN statements
    will use the virtual accelerator 'VACCEL'.
    .
    SET CURRENT QUERY ACCELERATION=ENABLE;
    SET CURRENT EXPLAIN MODE=EXPLAIN;
    SELECT * FROM T1 WHERE C1>2 FOR FETCH ONLY;
    .
    The SELECT statement above will populate the EXPLAIN tables
    since CURRENT EXPLAIN MODE=EXPLAIN and will use the
    virtual accelerator 'VACCEL' as defined in the profile tables.
    ---------------------------------------------------------
    This APAR also addresses the following abends when DB2
    unexpectedly executes an accelerated dynamic SQL query that
    was prepared using CURRENT EXPLAIN MODE = EXPLAIN :
     (1) AB04E RC00C90101 DSNIBCTD :5007
     (2) AB04E RC00E20009 DSNXEDC3 . DSNSVSFB +0898
    Other abend symptoms may be possible. The abends can occur
    with or without uing a virtual accelerator.
    
    Keywords:
    IDAAV4R1/K IDAAV3R1/K ABEND04E SQLEXPLAIN ERQUAL5007
    OFFSET0898 00C90101 00E20009
    

Problem conclusion

Temporary fix

Comments

APAR Information

  • APAR number

    PI10639

  • 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-01-29

  • Closed date

    2014-05-22

  • Last modified date

    2014-07-01

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

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

    UI18239 UI18240

Modules/Macros

  • DSNT1RSP DSNXODML DSNXOMPS DSNXONZA
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UI18239

       UP14/06/07 P F406

  • RB10 PSY UI18240

       UP14/06/07 P F406

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:

A10

Reference #:

PI10639

Modified date:

2014-07-01

Translate my page

Machine Translation

Content navigation