IBM Support

PI55807: QUERY MAY LOOP IF ACCELERATING QUERY USING ENABLE WITH FAILBACK AND ZPARM ACCELMODEL=YES AND THE OFFLOADED QUERY FAILS ON OPEN.

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • An accelerated query may loop between DB2 and the Accelerator
    if accelerated query fails on the first OPEN, and acceleration
    behavior is ENABLE WITH FAILBACK and DB2 zparm ACCELMODEL=YES
    (zparm = subsystem parameter ).
    .
    Externally this will appear as a ' hang ' situation for
    the application or workload, but trace recs for DB2 and
    the accelerator show the query is repeatedly accelerated
    between DB2 and the accelerator, where each acceleration
    attempt fails.  This problem can also exhibit as a DB2z
    " DRDA connection limit exceeded " situation after
    the accelerator is overloaded resulting in repeated
    " denial of service " responses from the accelerator.
    .
    When you specify ENABLE WITH FAILBACK acceleration behavior
    and the first OPEN for the accelerated query fails, DB2 should
    rerun the query only in DB2 instead. DB2 should not repeatedly
    send the query to the accelerator for that OPEN.
    .
    Another symptom that can be observed is a query fails with
    SQLCODE4742 even when it fails on the first OPEN with an
    error such as 'ERROR:  (2) This form of correlated query is
    not supported - consider rewriting'. ABEND0C4 could also
    happen to application. The query should run in DB2 instead
    with ENABLE WITH FAILBACK acceleration behavior if OPEN for
    the query fails.
    .
    ABEND0C4 RC00000004 at DSNLXRSQ+7F82
    .
    Additional search keywords: IDAAV4R1/K DB2HANG
    

Local fix

  • Change DB2 subsystem parameter to ACCELMODEL=NO or use a QUERY
    ACCELERATION value other than ENABLE WITH FAILBACK
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 for z/OS users of QUERY ACCELERATION *
    *                 behavior ENABLE WITH FAILBACK                *
    ****************************************************************
    * PROBLEM DESCRIPTION: User specified QUERY ACCELERATION       *
    *                      behavior ENABLE WITH FAILBACK and       *
    *                      one of the following problems occurred  *
    *                      in the situations described:            *
    *                      (1) User also requested Accelerator     *
    *                          Modeling via DB2 for z/OS zparm     *
    *                          ACCELMODEL = YES . When a certain   *
    *                          query was run, the query appeared   *
    *                          to 'hang' and the DB2z connection   *
    *                          limit was exceeded as the target    *
    *                          Accelerator repeatedly responded    *
    *                          with " denial of service " replies. *
    *                      (2) User also requested Accelerator     *
    *                          Archive behavior via DB2 zparm      *
    *                          or special reg GET_ACCEL_ARCHIVE .  *
    *                          When a certain query was run, one   *
    *                          of following DB2 application abends *
    *                          occurred:                           *
    *                           AB04E RC00E70005 at DSNXUTL :M100  *
    *                           AB04E RC00E2000C at DSNSVSVB +0B06 *
    *                          The accelerated table used in       *
    *                          the failing query is not archived.  *
    *                          Other abends may be possible.       *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    APAR PI55807 addresses the following two problem scenarios when
    using DB2 for z/OS QUERY ACCELERATION behavior ENABLE WITH
    FAILBACK :
                                                                   .
    (1) When using QUERY ACCELERATION behavior ENABLE WITH FAILBACK
        and DB2 subsystem parameter / zparm ACCELMODEL = YES (to
        request Accelerator Modeling output), an accelerated query
        appears to hang and the DB2z connection limit was exceeded
        as the target Accelerator repeatedly responded with
        "denial of service" to the query requests.
                                                                   .
        Trace recs for DB2z and the accelerator show that a single
        query was repeatedly accelerated between DB2 and the
        accelerator for a single SQL OPEN from the user application,
        where each acceleration attempt actually fails on the
        accelerator.  However, DB2 repeatedly attempted to
        accelerate the query even though acceleration behavior
        ENABLE WITH FAILBACK was requested. From user application
        perspective, that single SQL OPEN never ends and appears to
        'hang'.
                                                                   .
        When ENABLE WITH FAILBACK acceleration behavior is requested
        and the first SQL OPEN for the accelerated query fails, DB2
        should implicitly re-prepare that query and rerun it only in
        DB2 instead -- i.e., "failback" the query to DB2. DB2 should
        not repeatedly resend the query to the accelerator for that
        same single SQL OPEN.
                                                                   .
        From analysis of the problem, DB2z Development determined
        that during the implicit "failback" re-prepare to run the
        query in DB2z, when accelerator modeling was requested
        DB2 re-prepared the query for sending to the accelerator
        again, instead of re-preparing it to run only on DB2z.
                                                                   .
        If accelerator modeling is not requested (zparm
        ACCELMODEL=NO), then the "failback" re-prepare to DB2z
        occurs correctly and the query is run only in DB2. The query
        is not resent to the accelerator.  So to circumvent the
        problem, set zparm ACCELMODEL=NO (note that ACCELMODEL is
        an online-changeable zparm and does not require a DB2
        STOP and reSTART).
                                                                   .
                                                                   .
    (2) When using QUERY ACCELERATION behavior ENABLE WITH FAILBACK
        and Accelerator archive behavior is also requested as
        GET_ACCEL_ARCHIVE = YES (via either DB2 subsystem parameter/
        zparm or special register), one of the following DB2
        application abends occurred when a certain query was
        accelerated:
           ABEND04E RC00E70005 at DSNXGRDS . DSNXUTL :M100
           ABEND04E RC00E2000C at DSNXEAB . DSNSVSVB +0B06
        The accelerated table referenced in the failing query is
        *not* archived ("failback" to DB2z is not possible for
        a query whose accelerated table is 'archived').
        Other abends may be possible.
                                                                   .
        DB2z Development determined the abend(s) occurred during
        the implicit "failback" re-prepare to DB2z after the query
        failed on the accelerator during the application's SQL OPEN.
        The abend only occurred when archive acceleration is
        requested, because in this case DB2 did not correctly
        perform the re-prepare of the query to run only on DB2z.
        If GET_ACCEL_ARCHIVE = NO , the abend(s) does not occur and
        the query "failback" re-prepare to DB2 succeeds and
        the query runs only in DB2z.
                                                                   .
        To circumvent the problem, use GET_ACCEL_ARCHIVE=NO  via
        either special register or DB2 zparm (note that this zparm
        is online-changeable and does not require a DB2 stop and
        reSTART).
    

Problem conclusion

  • DB2 code to perform the implicit "failback" re-prepare to DB2
    for ENABLE WITH FAILBACK behavior was changed to perform the
    re-prepare correctly in both of the failing scenarios previously
    described, and the problems reported no longer occur.
                                                                   .
                                                                   .
    Additional search keywords: IDAAV4R1/K DB2HANG LOOP
                                ENABLEWITHFAILBACK EWF
                                SQLSPECIALREG
                                ABNDID04E OFFSETM100 OFFSET0B06
    

Temporary fix

  • AI55807
    

Comments

APAR Information

  • APAR number

    PI55807

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2016-01-21

  • Closed date

    2016-05-12

  • Last modified date

    2016-11-09

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

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

    UI37811 UI37812

Modules/Macros

  • DSNXODML DSNXOMPS DSNXOOP
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UI37811

       UP16/06/01 P F605

  • RB10 PSY UI37812

       UP16/06/01 P F605

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"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
09 November 2016