A fix is available
APAR status
Closed as program error.
Error description
This APAR addresses the following 3 DB2 IDAA offload related issues: 1. If a complex query contains CTE and many subqueries, when DB2 offloads query to IDAA, a syntax error and SQLCODE901 could occur. 2. When user requests DB2 IDAA offload for a query run from a DB2 package that is bound REOPT(AUTO) and the query validly does not qualify for IDAA offload but is run in DB2 instead, the following DB2 application abend is possible during a second or later OPEN cursor for that query : ABEND04E RC00E70005 at DSNXGRDS . DSNXERT :P115 3. DB2 does not support IDAA offload for a dynamic SQL query that is on the same input line that begins with an SQL simple comment (--) and a linefeed is used to separate the SQL simple comment from the SQL query. Additional search keywords. OFFSETP115 AB04E REOPT AUTO IDAAV2R1/K Additional dump symptoms: 0C4 DSNXONZC OFFSET8568 856E
Local fix
N/A
Problem summary
**************************************************************** * USERS AFFECTED: All users of DB2 9 and DB2 10 for z/OS who * * are also using IDAA ( IBM DB2 Analytics * * Accelerator ) * **************************************************************** * PROBLEM DESCRIPTION: This APAR contains the following fixes * * for DB2 IDAA support: * * * * 1. Before this APAR, a query with at * * least one common table expression * * (CTE) and converted query length * * greater than 32K bytes would return * * SQLCODE -901 when offloaded to IDAA. * * * * 2.Before this APAR, a query with at * * least one UNION, UNION ALL, * * INTERSECT, INTERSECT ALL, EXCEPT, * * EXCEPT ALL with a SELECT list item * * that involves an aggregate function * * for a decimal, date, time, or * * timestamp result column would * * return SQLCODE -904 when offloaded to * * IDAA. * * * * 3. When user requests DB2 IDAA offload * * for a query run from a DB2 package * * that is bound REOPT(AUTO) and * * the query validly does not qualify * * for IDAA offload but is run in DB2 * * instead, the following DB2 application * * abend is possible during a second or * * later OPEN cursor when DB2 does an * * implicit reoptimization re-prepare of * * the query for the Dynamic Statement * * Cache: * * ABEND04E RC00E70005 at DSNXERT :P115 * * * * 4. When using DB2 DSNTEP4 to run * * dynamic SQL queries and user requested * * IDAA offload for those queries, and * * Dynamic Statement Caching is not * * active, the following SQL error * * invalidly occurred: * * SQLCODE -249 SQLERRP = DSNLXRSQ * * * * 5. DB2 does not support IDAA offload * * for a dynamic SQL query that is * * on the same input line that begins * * with an SQL simple comment (--) * * and a linefeed is used to separate * * the SQL simple comment from the * * SQL query. * * * **************************************************************** * RECOMMENDATION: * **************************************************************** This APAR contains the following fixes for DB2 IDAA offload support of queries: . 1. Before this APAR, a query with at least one common table expression (CTE) and converted query length greater than 32K bytes would return SQLCODE -901 when offloaded to IDAA. To determine the converted query length, EXPLAIN the query and select LENGTH(QI_DATA) from DSN_QUERYINFO_TABLE. . 2.Before this APAR, a query with at least one UNION, UNION ALL, INTERSECT, INTERSECT ALL, EXCEPT, EXCEPT ALL with a SELECT list item that involves an aggregate function for a decimal, date, time, or timestamp result column would return SQLCODE -904 when offloaded to IDAA. For a date, time or timestamp result column, the SQLCODE -904 is returned when offloaded to IDAA only if one of the SELECT list items invovles a constant. For example: SELECT MAX('02/24/2012') FROM T1 UNION ALL SELECT MAX(C15DATE) FROM T2; For a decimal result column, the SQLCODE -904 is always returned when offloaded to IDAA. For example: SELECT MAX(C8DECIMAL) FROM T1 UNION ALL SELECT MAX(C8DECIMAL) FROM T2; . 3. The user requested DB2 IDAA offload for a query from a DB2 package that is bound with bind option REOPT(AUTO) and DB2 Dynamic Statement Caching ( DSC ) is active. For whatever valid reason the query is not eligible for IDAA offload and is cached in the DSC and run in DB2 instead. During the second or later OPEN cursor for the query running in DB2, the following DB2 application abend is possible when DB2 does an implicit reoptimization re-prepare of the query for the DB2 DSC (due to the REOPT(AUTO) behavior): ABEND04E RC00E70005 at DSNXGRDS . DSNXERT :P115 The DB2 abend occurred because DB2 did not correctly handle the implicit reopt re-prepare for this query that was not offloaded to IDAA. This REOPT(AUTO) problem only occurs when the user has requested IDAA offload and DB2 validly does not offload the query. . 4. When using DB2 DSNTEP4 to run dynamic SQL queries and user requested IDAA offload for those queries, and Dynamic Statement Caching (DSC) is not active, the following SQL error occurred: DSNT408I SQLCODE = -249, ERROR: DEFINITION OF ROWSET ACCESS FOR CURSOR <unknown> IS INCONSISTENT WITH THE FETCH ORIENTATION CLAUSE SPECIFIED DSNT418I SQLSTATE = 24523 SQLSTATE RETURN CODE DSNT415I SQLERRP = DSNLXRSQ SQL PROCEDURE DETECTING ERROR . This error is issued by DB2. The error does not occur when DSC is active. . DSNTEP4 default cursor behavior is ROWSET and 100 rows for a fetch. DB2 IDAA offload is not supported for ROWSET queries and multi-row fetching, so DB2 does not offload such queries (by design). Instead, DB2 runs the query in DB2. However, the query should not have failed with SQLCODE249 but should have run successfully in DB2. If DSC is active in this scenario, the query runs successfully. The SQLCODE249 occurred invalidly because, when DSC is not active DB2 did not correctly handle the ROWSET query and the SQLCODE249 resulted. It is not a TEP4 error or problem. . This SQLCODE249 can also occur if another application or vendor product attempted to run a query with ROWSET in this same IDAA offload request scenario. . 5. An SQL simple comment starts with the characters '--', and any characters that follow '--' on the same input line are considered as a comment, even if what follows is an SQL statement. However, if a linefeed character is embedded or used on that input line to separate the comment from the SQL statement specified, then the SQL statement is not considered part of the SQL simple comment. In this linefeed case, if that simple comment line is input for a dynamic SQL statement and the SQL statement specified is a query, DB2 does not consider the dynamic query for IDAA offload.
Problem conclusion
1. Code is fixed to offload a query with at least one CTE and converted query length greater than 32K bytes if there are no other restrictions preventing the query from offloading. . 2. Code is fixed to offload a query involving a UNION, UNION ALL, INTERSECT, INTERSECT ALL, EXCEPT, EXCEPT ALL with a SELECT list item that is a decimal, date, time, or timestamp result column. . 3. DB2 REOPT(AUTO) support was changed to correctly handle the DB2 implicit reoptimization re-prepare on OPEN for a query that did not qualify for DB2 IDAA offload. . 4. DB2 was changed to correctly run a ROWSET query in DB2 when the user requested IDAA offload and Dynamic Statement Caching is not active. The ROWSET query is correctly *not* offloaded to IDAA. . 5. DB2 now supports IDAA offload for a dynamic SQL query that is on the same input line that begins with an SQL simple comment (--) and a linefeed is used to separate the SQL simple comment from the SQL query. . Additional Search Keywords: IDAAV2R1/K SQLCODE904 SQLCODE901 SQLUNION SQLUNIONALL SQLINTERSECT SQLINTERSECTALL SQLEXCEPT SQLEXCEPTALL REOPT AUTO SQLDYNSTMTCACHE AB04E OFFSETP115 00E70005
Temporary fix
Comments
APAR Information
APAR number
PM58732
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
910
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2012-02-22
Closed date
2012-04-11
Last modified date
2012-08-13
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UK77857 UK77858
Modules/Macros
DSNXEDP DSNXONZC
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":"9.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":"9.1","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
13 August 2012