A fix is available
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