IBM Support

PI36746: OFFLOADING A QUERY TO THE ACCELERATOR RETURNS SQLCODE4742 RC04 EVEN IF THE PACKAGE IS BOUND WITH ISOLATION(UR)

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • An attempt to offload / accelerate a query in a package bound
    with ISOLATION(UR) and using QUERY ACCELERATION ALL, failed
    with SQLCODE4742 RC04 (REASON=4). REASON=4 means that DB2
    did not bind/prepare the query for acceleration/offload
    because "the query is not read only".
    However, when using bind option ISOLATION UR , DB2 should
    have classified the query as READ ONLY, thus allowing the query
    to be bound for acceleration/offload.
    

Local fix

  • To circumvent the problem, add one of the following clauses
    to the SQL query text (or PREPARE ATTRIBUTES for dyn query):
    1. FOR READ ONLY or FOR FETCH ONLY clause, or
    2. WITH UR clause
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All users of DB2 query acceleration and      *
    *                 the bind option ISOLATION(UR)                *
    ****************************************************************
    * PROBLEM DESCRIPTION: The user specified bind options         *
    *                      ISOLATION(UR) and QUERYACCELERATION     *
    *                      when binding a package (or SET CURRENT  *
    *                      QUERY ACCELERATION at run-time) and     *
    *                      validly expected an SQL query within    *
    *                      the package to                          *
    *                       (1) be classified by DB2 as a READ     *
    *                           ONLY query by default without      *
    *                           needing WITH UR or FOR FETCH ONLY  *
    *                           specified in the query text, and   *
    *                       (2) therefore satisfy the acceleration *
    *                           criteria READ ONLY, allowing DB2   *
    *                           to bind/prepare the query for      *
    *                           acceleration.                      *
    *                      However, DB2 did not bind/prepare the   *
    *                      query for acceleration.                 *
    *                      When the user also specified EXPLAIN    *
    *                      YES, the PLAN table DSN_QUERYINFO_TABLE *
    *                      entry for the query shows value 4 for   *
    *                      column REASON_CODE and reason text      *
    *                      'The query is not read only' for column *
    *                      QI_DATA.                                *
    *                                                              *
    *                      If QUERYACCELERATION ALL is used,       *
    *                      for a static query the BIND PACKAGE     *
    *                      fails with SQLCODE4742 REASON=4, or     *
    *                      a dynamic query executed from that      *
    *                      package fails with SQLCODE4742 REASON=4 *
    *                      at run-time.                            *
    *                                                              *
    *                      When bind option ISOLATION(UR) is used, *
    *                      DB2 should consider the query as        *
    *                      satisfying the acceleration criteria    *
    *                      READ ONLY.                              *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    An SQL query using ISOLATION UR (via either bind option
    ISOLATION(UR) or SQL clause WITH UR) is a READ ONLY query by
    default and satisfies the acceleration criteria READ ONLY for
    an SQL query, without requiring WITH UR or FOR FETCH ONLY to be
    specified in the query text.  However, in this reported case
    the user bound a package with bind option ISOLATION(UR) and
    specified QUERY ACCELERATION (either via bind option or
    SET CURRENT QUERY ACCELERATION), but DB2 did not bind the query
    for acceleration.  If QUERYACCELERATION ALL is used, for
    a static query the BIND PACKAGE fails with SQLCODE4742 REASON=4,
    or a dynamic query executed from that package fails with
    SQLCODE4742 REASON=4 at run-time.  REASON=4 means the query
    cannot be accelerated because "the query is not read only".
                                                                   .
    In addition,
     - for a static query, when the user also specified bind option
       EXPLAIN(YES), or
     - for a dynamic query, when the user issued SET CURRENT EXPLAIN
       MODE = YES before PREPARE of the query,
    the PLAN table DSN_QUERYINFO_TABLE entry for the query shows
    value 4 for column REASON_CODE and reason text 'The query is not
    read only' for column QI_DATA.
                                                                   .
    When using bind option ISOLATION(UR), the query should be
    classified as READ ONLY and should meet acceleration criteria
    READ ONLY for a query. DB2 should bind that query for
    acceleration if all other qualifying acceleration criteria is
    satisfied for the QUERY ACCELERATION behavior requested.
    However, DB2 did not bind the query for acceleration because
    when verifying acceleration criteria READ ONLY, DB2 did not
    consider the bind option ISOLATION(UR).
    Note that for a query run in DB2 (i.e., not accelerated),
    DB2 *does* consider the bind option ISOLATION(UR) for the READ
    ONLY attribute for a query, so this issue only applies when
    the user requests query acceleration.
                                                                   .
    

Problem conclusion

  • DB2 code for verifying acceleration criteria of a query was
    modified to consider the bind option ISOLATION(UR) as the READ
    ONLY attribute for the query, thus allowing the query to
    satisfy the DB2 acceleration criteria READ ONLY and be bound for
    acceleration if all other criteria are met.
                                                                   .
    To make this fix effective for a static query, the DB2 package
    must be rebound after application of this PTF.
    For a dynamic query, no REBIND is necessary.
                                                                   .
    Additional search keywords: IDAAV3R1/K IDAAV4R1/K
                                SQLCODE4742 RC04
    

Temporary fix

  • AI36746
    

Comments

APAR Information

  • APAR number

    PI36746

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2015-03-12

  • Closed date

    2015-06-24

  • Last modified date

    2015-08-03

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

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

    UI28802 UI28803

Modules/Macros

  •    DSNXONZO
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UI28802

       UP15/07/10 P F507

  • RB10 PSY UI28803

       UP15/07/10 P F507

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

Modified date: 03 August 2015


Translate this page: