IBM Support

PI10814: ACCELERATED QUERY INTERMITTENTLY DOES NOT FAILBACK TO DB2 ON OPEN ERROR WHEN USING QUERYACCELERATION ENABLEWITHFAILBACK

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • when using special register CURRENT QUERY ACCELERATION value
    ENABLEWITHFAILBACK or bind option QUERYACCELERATION value
    ENABLEWITHFAILBACK to accelerate a dynamic or static SQL query
    to the IBM DB2 Analytics Accelerator, failback execution of
    a local query intermittently does not occur for OPEN failure on
    the accelerator, when failback behavior is expected. Instead,
    DB2 returns the SQLCODE901 / SQLCODE904 failure from accelerator
    to the user application or client.  Note that this failure to
    failback to DB2 is intermittent and failback will most often
    occur.
    For a remote query that is accelerated, failback to DB2 occurs
    successfully consistently as expected, and this problem does
    not apply.
                                                                   .
    Additional search keywords: IDAAV2R1/K IDAAV3R1/K IDAAV4R1/K
    

Local fix

  • None.  The failure to failback execution to DB2 is intermittent,
    so sometimes it will succeed and sometimes not.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 10 and DB2 11 for z/OS users of      *
    *                 the IBM DB2 Analytics Accelerator and DB2    *
    *                 acceleration behavior ENABLEWITHFAILBACK     *
    ****************************************************************
    * PROBLEM DESCRIPTION: When accelerating DB2 queries for       *
    *                      the IBM DB2 Analytics Accelerator,      *
    *                      the first OPEN for the accelerated      *
    *                      query failed as follows, even though    *
    *                      the user specified DB2 acceleration     *
    *                      behavior ENABLEWITHFAILBACK (EWF):      *
    *                        - 1st OPEN for local query            *
    *                          'intermittently' failed and         *
    *                          returned SQLCODE -901 or -904       *
    *                        - 1st OPEN for a dynamic query bound  *
    *                          for Workload Balancing acceleration *
    *                          failed with SQLCODE -4742 when      *
    *                          the accelerator was terminated      *
    *                          after the dynamic query was         *
    *                          prepared                            *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    When users request DB2 acceleration behavior ENABLEWITHFAILBACK
    for running queries on the IBM DB2 Analytics Accelerator,
    the user application should not see the acceleration failure
    that occurs on the first/initial OPEN for the accelerated query.
    Instead, the application should see the successful OPEN that
    occurs from implicitly re-preparing and running query on DB2
    (i.e., FAILBACK of the query to DB2), if the initial OPEN for
    the 'accelerated' query fails.
                                                                   .
    However in the following cases, after the failed 1st OPEN for
    the accelerated query DB2 did not FAILBACK the query to DB2 as
    expected when ENABLE WITH FAILBACK acceleration behavior is
    requested:
      - 1st OPEN for a local query 'intermittently' failed and
        returned SQLCODE -901 or -904
      - 1st OPEN for a dynamic query bound for Workload Balancing
        ( WLB )acceleration failed with SQLCODE -4742 when
        the accelerators eligible to run the query were all no
        longer active after the dynamic query was prepared.
                                                                  .
    For the case of 'intermittent' failure on 1st OPEN of a local
    query, DB2 Development determined that after the OPEN failure,
    DB2 accessed an internal DB2 control block only intended for
    use with a 'remote' query that was accelerated and the control
    block was not applicable to the local query. As a result,
    invalid information was considered and DB2 'sometimes' did not
    perform the FAILBACK to DB2 after the 1st OPEN failure for
    the accelerated query. This unexpected non-FAILBACK behavior on
    1st OPEN failure did not always occur -- it was intermittent.
                                                                   .
    For the case of the query bound for WLB acceleration,
    DB2 Development saw that during the OPEN after DB2 detected
    that all accelerators eligible to run the WLB-bound query were
    now no longer active after the PREPARE of the query, DB2 did
    not consider ENABLEWITHFAILBACK for this case.  Before DB2
    sends an OPEN for a WLB-bound query to the IBM DB2 Analytics
    Accelerator, DB2 verifies that there is an eligible accelerator
    active to run the query.  DB2 normally issues SQLCODE -4742
    for this 1st OPEN failure. However, if FAILBACK behavior is
    requested, DB2 should not return SQLCODE -4742 but instead
    should FAILBACK the query to DB2 and re-prepare and run it on
    DB2 for z/OS.
    

Problem conclusion

  • DB2 code for query acceleration was changed to properly
    consider FAILBACK to DB2 for the 2 failing cases previously
    described.
                                                                   .
    Additional Keywords: IDAAV3R1/K IDAAV4R1/K
                         SQLCODE901 SQLCODE905 SQLCODE4742
    

Temporary fix

Comments

APAR Information

  • APAR number

    PI10814

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2014-01-31

  • Closed date

    2014-04-01

  • Last modified date

    2014-05-02

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

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

    UI16638 UI16639

Modules/Macros

  •    DSNXERT  DSNXERT2
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UI16638

       UP14/04/17 P F404

  • RB10 PSY UI16639

       UP14/04/17 P F404

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.

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"DB2 for z\/OS"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"A10","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 March 2024