IBM Support

PI55686: A QUERY WITH AN ACCELERATOR ONLY TABLE EXECUTES ON AN ACCELERATOR WHEN ACCELMODEL=YES AND ACCELERATION=NONE

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • A query with an accelerator-only table executes on an
    accelerator even when ACCELMODEL=YES and ACCELERATION=NONE.
    The query should not be offloaded.
    
    ADDITIONAL KEYWORDS / SYMPTOMS:
    =======================================
    ABEND04E RC00E70005 at DSNXOIN M110 when
    ACCELMODEL=YES & SQL REFRESH TABLE
    

Local fix

  • n/a
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All users of DB2 for z/OS who have           *
    *                 subsystem parameter ACCELMODEL set to YES.   *
    *                 All users of DB2 for z/OS who use query      *
    *                 acceleration.                                *
    ****************************************************************
    * PROBLEM DESCRIPTION: This APAR contains the following fixes: *
    *                      (1) (V10 and V11)                       *
    *                      When subsystem parameter ACCELMODEL     *
    *                      is set to YES and QUERY ACCELERATION is *
    *                      set to NONE, an SQL statement           *
    *                      referencing an accelerator-only table   *
    *                      may incorrectly execute on an           *
    *                      accelerator. Instead the SQL statement  *
    *                      should fail with SQLCODE -4742 reason   *
    *                      code 2.                                 *
    *                      (2) (V10 and V11)                       *
    *                      When subsystem parameter ACCELMODEL     *
    *                      is set to YES, QUERY ACCELERATION is    *
    *                      set to ENABLE, ENABLE WITH FAILBACK,    *
    *                      ELIGIBLE, or ALL, and an SQL statement  *
    *                      is an UPDATE or DELETE that references  *
    *                      an accelerator-only table, EXPLAIN      *
    *                      table DSN_QUERYINFO_TABLE is not        *
    *                      populated.                              *
    *                      (3) (V10 and V11)                       *
    *                      When subsystem parameter ACCELMODEL     *
    *                      is set to YES, QUERY ACCELERATION is    *
    *                      set to ENABLE, ENABLE WITH FAILBACK,    *
    *                      or ELIGIBLE, subsystem parameter        *
    *                      QUERY_ACCEL_OPTIONS may incorrectly     *
    *                      implicity set the values 1,2 and 3      *
    *                      for an SQL statement when an            *
    *                      accelerator is already defined.         *
    *                      (4) (V10 and V11)                       *
    *                      When subsystem parameter ACCELMODEL     *
    *                      is set to YES, QUERY ACCELERATION is    *
    *                      NONE and an SQL statement references    *
    *                      an accelerator-only table, EXPLAIN      *
    *                      table DSN_QUERYINFO_TABLE is not        *
    *                      populated.                              *
    *                      (5) (V10 and V11)                       *
    *                      When subsystem parameter ACCELMODEL     *
    *                      is set to YES, QUERY ACCELERATION is    *
    *                      set to ENABLE, ENABLE WITH FAILBACK,    *
    *                      or ELIGIBLE,  and explain table         *
    *                      DSN_QUERYINFO_TABLE is not created,     *
    *                      an ABEND04E RC00C90101 at DSNIBCTD:5007 *
    *                      occurs.                                 *
    *                      (6) (V11)                               *
    *                      When an accelerator is paired with      *
    *                      UTF16 sort order and a query that       *
    *                      contains the function                   *
    *                      CHARACTER_LENGTH(), EXPLAIN table       *
    *                      DSN_QUERYINFO_TABLE.QI_DATA will have   *
    *                      query text that does not match the      *
    *                      query text that is actually sent to     *
    *                      an accelerator.                         *
    *                      (7) (V11)                               *
    *                      When subsystem parameter ACCELMODEL     *
    *                      is set to YES, accelerator modeling     *
    *                      does not implicitly consider            *
    *                      SQL statements that would be            *
    *                      enabled for acceleration when           *
    *                      subsystem parameter                     *
    *                      QUERY_ACCEL_OPTIONS includes values     *
    *                      6 and 7.                                *
    *                      (8) (V11)                               *
    *                      A query may incorrectly fail on         *
    *                      an accelerator with SQLCODE -901.       *
    *                      (9) (V11)                               *
    *                      When an accelerator is paired with      *
    *                      UTF16 sort order and a query that       *
    *                      contains the function                   *
    *                      CHARACTER_LENGTH() with a numeric       *
    *                      expression and unit OCTETS, incorrect   *
    *                      results will be returned from an        *
    *                      accelerator.                            *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    This APAR contains the following fixes:
    (1) (V10 and V11)
    When subsystem parameter ACCELMODEL is set to YES and
    QUERY ACCELERATION is set to NONE, an SQL statement
    referencing an accelerator-only table may incorrectly execute
    on an accelerator. Instead the SQL statement should fail
    with SQLCODE -4742 reason code 2.
    (2) (V10 and V11)
    When subsystem parameter ACCELMODEL is set to YES,
    QUERY ACCELERATION is set to ENABLE, ENABLE WITH FAILBACK,
    ELIGIBLE, or ALL, and an SQL statement is an UPDATE or
    DELETE that references an accelerator-only table, EXPLAIN
    table DSN_QUERYINFO_TABLE is not populated.
    (3) (V10 and V11)
    When subsystem parameter ACCELMODEL is set to YES,
    QUERY ACCELERATION is set to ENABLE, ENABLE WITH FAILBACK,
    or ELIGIBLE, subsystem parameter QUERY_ACCEL_OPTIONS may
    incorrectly implicity set the values 1,2 and 3 for an SQL
    statement when an accelerator is already defined.
    (4) (V10 and V11)
    When subsystem parameter ACCELMODEL is set to YES,
    QUERY ACCELERATION is NONE and an SQL statement references
    an accelerator-only table, EXPLAIN table DSN_QUERYINFO_TABLE
    is not populated.
    (5)
    When subsystem parameter ACCELMODEL is set to YES,
    QUERY ACCELERATION is set to ENABLE, ENABLE WITH FAILBACK,
    or ELIGIBLE, and explain table DSN_QUERYINFO_TABLE is not
    created, an ABEND 04E 00C90101 at DSNIBCTD:5007 occurs.
    (6) (V11)
    When an accelerator is paired with UTF16 sort order and a
    query that contains the function CHARACTER_LENGTH(), EXPLAIN
    table DSN_QUERYINFO_TABLE.QI_DATA will have query text that
    does not match the query text that is actually sent to an
    accelerator.
    (7) (V11)
    When subsystem parameter ACCELMODEL is set to YES, accelerator
    modeling does not implicitly consider SQL statements that
    would be enabled for acceleration when subsystem parameter
    QUERY_ACCEL_OPTIONS includes values 6 and 7.
    (8) (V11)
    An SQL statement may incorrectly fail on an accelerator with
    SQLCODE -901,'SQLSTATE = 58004, SYSTEM ERROR FROM IBM DB2
    Analytics Accelerator TOKENS 42S02: ERROR:  Function
    DB2_ACTION'.
    (9) (V11)
    When an accelerator is paired with UTF16 sort order and a query
    that contains the function CHARACTER_LENGTH() with a numeric
    expression and unit OCTETS, incorrect results will be
    returned from an accelerator.
    
    Keywords:
    IDAAV4R1/K IDAAV5R1/K ACCELMODEL SQLCHARACTERLENGTH SQLCODE901
    SQLINCORR INCORROUT SQLINCORROUT DB2INCORR/K SQLEXPLAIN
    ABEND04E RC00C90101 DSNIBCTD ERQUAL5007
    

Problem conclusion

  • (1) DB2 code is fixed so that the SQL statement in the above
    scenario fails with SQLCODE -4742 reason code 2.
    (2) DB2 code is fixed so that DSN_QUERYINFO_TABLE is
    populated in the above scenario.
    (3) DB2 code is fixed to implicity set QUERY_ACCEL_OPTIONS
    for an SQL statement only when an accelerator is not defined.
    (4) DB2 code is fixed so that DSN_QUERYINFO_TABLE is
    populated in the above scenario.
    (5) DB2 code is fixed so that the abend no longer occurs.
    (6) DB2 code is fixed so that DSN_QUERYINFO_TABLE.QI_DATA
    matches the query text sent to an accelerator.
    (7) DB2 code is fixed so that accelerator modeling implicitly
    includes QUERY_ACCEL_OPTIONS 6 and 7 for an SQL statement
    only when an accelerator is not defined.
    (8) DB2 code is fixed so that the SQL statement executes
    properly on an accelerator.
    (9) DB2 code is fixed to return the correct result.
    

Temporary fix

Comments

APAR Information

  • APAR number

    PI55686

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2016-01-19

  • Closed date

    2016-04-06

  • Last modified date

    2016-06-01

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

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

    UI36854 UI36855

Modules/Macros

  • DSNXODML DSNXOEX1 DSNXOIN  DSNXOLTD DSNXOMPS DSNXONZO DSNXONZS
    DSNXOOS1
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UI36854

       UP16/04/23 P F604 ¢

  • RB10 PSY UI36855

       UP16/04/26 P F604 ¢

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

Modified date: 01 June 2016


Translate this page: