IBM Support

PI83466: AB04E RC00E70005 AT DSNXODML :M100 WHEN QUERY IS NOT ELIGIBLE FOR ACCELERATION W/ QUERY ACCEL=ENABLE & GET_ACCEL_ARCHIVE=YES

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • ABEND04E RC00E70005 at DSNXODML :M100 occurred when all of the
    following are true:
    - User requested QUERY ACCELERATION = ENABLE or ENABLE WITH
      FAILBACK
    - User requested GET_ACCEL_ARCHIVE = YES
    - DB2 Dynamic Statement Caching (DSC) is active
    - the dynamic query references an accelerated table that is
      *not* archived on the accelerator, but the query is not
      eligible for acceleration because the query uses a function,
      feature, or expression that is not supported by the
      Accelerator (e.g., for Accelerator V4 & V5, the bif HEX, or
      CAST AS VARCHAR with an unsupported data type argument, etc.)
      To determine if the query is not eligible for acceleration,
      change QUERY ACCELERATION to ALL just for the abending query;
      and if the query is not eligible, then the PREPARE for
      the query will fail w/ SQLCODE -4742.
    

Local fix

  • You can avoid the abend by doing any one of the following:
    1.Given that the query references an accelerated table that is
      *not* archived on the accelerator, then you can
      use GET_ACCEL_ARCHIVE = NO (either by resetting that DB2 zparm
      to NO and explicitly issuing SET CURRENT GET_ACCEL_ARCHIVE =
      YES for those workloads or applications that reference
      'archived' accelerated tables; or by issuing SET CURRENT
      GET_ACCEL_ARCHIVE=NO for the failing application or query) OR
    2.Specify QUERY ACCELERATION = ELIGIBLE instead of ENABLE (or
      ENABLE WITH FAILBACK )
    .
    Additional search keywords: IDAAV4R1/K IDAAV5R1/K
                                SQLDYNSTMTCACHE
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 for z/OS users of DB2 QUERY          *
    *                 ACCELERATION = ENABLE with                   *
    *                 GET_ACCEL_ARCHIVE = YES, and DB2 Dynamic     *
    *                 Statement Caching is active.                 *
    ****************************************************************
    * PROBLEM DESCRIPTION: ABEND04E DSNXGRDS.DSNXODML :M100 may    *
    *                      occur for a dynamic query if all        *
    *                      of the following conditions are         *
    *                      true:                                   *
    *                       o QUERY ACCELERATION = ENABLE or       *
    *                         ENABLE WITH FAILBACK                 *
    *                       o GET_ACCEL_ACHIVE = YES               *
    *                       o Dynamic Statement Caching is active  *
    *                       o Query references an accelerated      *
    *                         table that is *not* archived, but    *
    *                         the query is not eligible for        *
    *                         acceleration                         *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    ABEND04E RC00E70005 at DSNXODML :M100 occurred for a dynamic
    query when all of the following are true:
    o User requested QUERY ACCELERATION = ENABLE or ENABLE WITH
     FAILBACK
    o User requested GET_ACCEL_ARCHIVE = YES (either via DB2
     subsystem parameter / zparm or special register)
    o DB2 Dynamic Statement Caching ( DSC ) is active
    o the dynamic query references an accelerated table that is
     *not* archived on the accelerator, but the query is *not*
     eligible for acceleration because the query uses a function,
     feature, or expression that is not supported by the
     Accelerator (e.g., for Accelerator V4 & V5, built-in function
     HEX, or usage of CAST AS VARCHAR with an unsupported data type
     argument, etc.).
                                   .
    DB2 Development determined that the abend occurred because DB2
    did not properly handle the very specific combination described
    above, which resulted in the query being 'partially' prepared
    for acceleration when it should not have been accelerated
    (because the query was not eligible for acceleration).
    Instead, the query should have been prepared to run only in DB2.
                                   .
    To determine if the query is not eligible for acceleration,
    set QUERY ACCELERATION = ALL just for the abending query;
    and if the query is not eligible, then the PREPARE for
    the query will fail w/ SQLCODE -4742.
    To confirm if the user table is archived on the accelerator,
    check the DB2 table SYSACCEL.SYSACCELERATEDTABLES column
    ARCHIVE for that user table entry.
    

Problem conclusion

  • DB2 code has been changed to correctly handle the 'not eligible'
    combination case previously described and not prepare the query
    for acceleration. Instead, DB2 will prepare the query to run
    only in DB2.
                                                                 .
    Additional search keywords: IDAAV4R1/K IDAAV5R1/K IDAAV6R1/K
                  AB04E SQLDYNSTMTCACHE
    

Temporary fix

Comments

APAR Information

  • APAR number

    PI83466

  • 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

    2017-06-21

  • Closed date

    2017-07-28

  • Last modified date

    2017-09-02

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

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

    UI49174

Modules/Macros

  • DSNXONZO
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RB10 PSY UI49174

       UP17/08/16 P F708

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:
02 September 2017