A fix is available
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
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":"10.1","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":"10.1","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
01 June 2016