PI20247: WHEN SUBSYSTEM PARAMETER ACCELMODEL = YES, A BIND OR REBIND WITH QUERYACCELERATION DOES NOT BIND A STATIC QUERY FOR ACCELERATION

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • When DB2 subsystem parameter/ zparm ACCELMODEL = YES, a BIND or
    REBIND PACKAGE specifying bind option QUERYACCELERATION does
    not bind a static query for acceleration. DB2 will instead
    bind the static query to be run only in DB2 without
    acceleration.
    .
    Keywords:
    IDAAV4R1/K SQLACCELMODEL
    

Local fix

  • n/a
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 for z/OS users of static SQL query   *
    *                 acceleration and DB2 subsystem parameter     *
    *                 ACCELMODEL = YES                             *
    ****************************************************************
    * PROBLEM DESCRIPTION: When DB2 subsystem parameter / zparm    *
    *                      ACCELMODEL=YES, a BIND or REBIND        *
    *                      PACKAGE specifying bind option          *
    *                      QUERYACCELERATION does not bind         *
    *                      a qualified static SQL query for        *
    *                      acceleration. DB2 instead binds         *
    *                      the static query to be run only in DB2  *
    *                      without acceleration, allowing          *
    *                      DB2 'accelerator modeling' behavior to  *
    *                      take precedence over the requested      *
    *                      QUERYACCELERATION behavior.             *
    *                                                              *
    *                      This is incorrect DB2 acceleration      *
    *                      behavior and DB2 should bind            *
    *                      the qualified static SQL query for      *
    *                      acceleration as the user requested,     *
    *                      even when zparm ACCELMODEL=YES.         *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    This APAR corrects a problem for static SQL query acceleration,
    where if DB2 subsystem parameter / zparm ACCELMODEL = YES
    (requesting DB2 to bind or prepare queries to run only in DB2
    as normal but do 'accelerator modeling' for those queries),
    a BIND or REBIND PACKAGE specifying bind option
    QUERYACCELERATION does not bind a qualified static SQL query for
    acceleration. DB2 incorrectly ignores the QUERYACCELERATION
    behavior that the user specified in the bind option and binds
    the static query to be run only in DB2 without acceleration.
    As a result, DB2 'accelerator modeling' behavior incorrectly
    took precedence over the user-requested QUERYACCELERATION
    behavior.  When a BIND or REBIND PACKAGE specifies bind option
    QUERYACCELERATION, DB2 should honor the requested
    QUERYACCELERATION behavior over 'accelerator modeling' (i.e.,
    zparm ACCELMODEL=YES) and bind the qualified static query for
    acceleration.
                                                              .
    One quick way to confirm whether the static query was bound for
    acceleration during the BIND or REBIND PACKAGE, is to examine
    the catalog table SYSIBM.SYSPACKSTMT column STATUS value for
    the particular static query in the package. If that query's
    STATUS column does not contain the letter value 'O', then
    that static query was not bound for acceleration.
                                                                  .
    Another way to confirm the invalid precedence order of
    'accelerator modeling' (ACCELMODEL=YES) is to REBIND the
    package with bind option EXPLAIN(ONLY) (this does not produce
    a new package or replace or change the previous package) and
    examine the bind-generated output in the DB2 table
    DSN_QUERYINFO_TABLE. If any rows have the value 'ACCELMDL' in
    column QINAME1, then the query was bound to run only in DB2 and
    with 'accelerator modeling'.
                                                                  .
    If you have confirmed, by using either of the above methods,
    that your static queries were not bound for acceleration due to
    the invalid precedence of zparm ACCELMODEL, you can do one of
    the following:
     - To circumvent the problem without applying this PTF,
       temporarily reset the zparm ACCELMODEL to NO (ACCELMODEL is
       online changeable), then REBIND the affected packages.
       After the REBINDs have completed and the packages'
       SYSIBM.SYSPACKSTMT column STATUS = 'O' for those static
       queries you intended to accelerate, restore zparm
       ACCELMODEL to YES if you want to continue with 'accelerator
       modeling' for other applications.
     - Or, after applying this PTF, REBIND the affected packages.
       No reset of zparm ACCELMODEL is needed.
       After the REBINDs have completed, confirm that the packages'
       SYSIBM.SYSPACKSTMT column STATUS = 'O' for those static
       queries you intended to accelerate.
                                                                  .
    Additional search keywords:
    IDAAV4R1/K SQLACCELMODEL
    

Problem conclusion

  • DB2 bind code is corrected so that when a BIND or REBIND
    PACKAGE specifying bind option QUERYACCELERATION is issued,
    DB2 honors the requested QUERYACCELERATION behavior as
    precedent over 'accelerator modeling' when DB2 subsystem
    parameter ACCELMODEL=YES, and DB2 binds the qualified static
    query for acceleration.
    

Temporary fix

  • *********
    * HIPER *
    *********
    

Comments

APAR Information

  • APAR number

    PI20247

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2014-06-17

  • Closed date

    2014-07-25

  • Last modified date

    2014-09-03

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

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

    UI20000 UI20001

Modules/Macros

  •    DSNXODML DSNXOMPS
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UI20000

       UP14/08/12 P F408

  • RB10 PSY UI20001

       UP14/08/12 P F408

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

PI20247

Modified date:

2014-09-03

Translate my page

Machine Translation

Content navigation