A fix is available
APAR status
Closed as program error.
Error description
An accelerated query may loop between DB2 and the Accelerator if accelerated query fails on the first OPEN, and acceleration behavior is ENABLE WITH FAILBACK and DB2 zparm ACCELMODEL=YES (zparm = subsystem parameter ). . Externally this will appear as a ' hang ' situation for the application or workload, but trace recs for DB2 and the accelerator show the query is repeatedly accelerated between DB2 and the accelerator, where each acceleration attempt fails. This problem can also exhibit as a DB2z " DRDA connection limit exceeded " situation after the accelerator is overloaded resulting in repeated " denial of service " responses from the accelerator. . When you specify ENABLE WITH FAILBACK acceleration behavior and the first OPEN for the accelerated query fails, DB2 should rerun the query only in DB2 instead. DB2 should not repeatedly send the query to the accelerator for that OPEN. . Another symptom that can be observed is a query fails with SQLCODE4742 even when it fails on the first OPEN with an error such as 'ERROR: (2) This form of correlated query is not supported - consider rewriting'. ABEND0C4 could also happen to application. The query should run in DB2 instead with ENABLE WITH FAILBACK acceleration behavior if OPEN for the query fails. . ABEND0C4 RC00000004 at DSNLXRSQ+7F82 . Additional search keywords: IDAAV4R1/K DB2HANG
Local fix
Change DB2 subsystem parameter to ACCELMODEL=NO or use a QUERY ACCELERATION value other than ENABLE WITH FAILBACK
Problem summary
**************************************************************** * USERS AFFECTED: All DB2 for z/OS users of QUERY ACCELERATION * * behavior ENABLE WITH FAILBACK * **************************************************************** * PROBLEM DESCRIPTION: User specified QUERY ACCELERATION * * behavior ENABLE WITH FAILBACK and * * one of the following problems occurred * * in the situations described: * * (1) User also requested Accelerator * * Modeling via DB2 for z/OS zparm * * ACCELMODEL = YES . When a certain * * query was run, the query appeared * * to 'hang' and the DB2z connection * * limit was exceeded as the target * * Accelerator repeatedly responded * * with " denial of service " replies. * * (2) User also requested Accelerator * * Archive behavior via DB2 zparm * * or special reg GET_ACCEL_ARCHIVE . * * When a certain query was run, one * * of following DB2 application abends * * occurred: * * AB04E RC00E70005 at DSNXUTL :M100 * * AB04E RC00E2000C at DSNSVSVB +0B06 * * The accelerated table used in * * the failing query is not archived. * * Other abends may be possible. * **************************************************************** * RECOMMENDATION: * **************************************************************** APAR PI55807 addresses the following two problem scenarios when using DB2 for z/OS QUERY ACCELERATION behavior ENABLE WITH FAILBACK : . (1) When using QUERY ACCELERATION behavior ENABLE WITH FAILBACK and DB2 subsystem parameter / zparm ACCELMODEL = YES (to request Accelerator Modeling output), an accelerated query appears to hang and the DB2z connection limit was exceeded as the target Accelerator repeatedly responded with "denial of service" to the query requests. . Trace recs for DB2z and the accelerator show that a single query was repeatedly accelerated between DB2 and the accelerator for a single SQL OPEN from the user application, where each acceleration attempt actually fails on the accelerator. However, DB2 repeatedly attempted to accelerate the query even though acceleration behavior ENABLE WITH FAILBACK was requested. From user application perspective, that single SQL OPEN never ends and appears to 'hang'. . When ENABLE WITH FAILBACK acceleration behavior is requested and the first SQL OPEN for the accelerated query fails, DB2 should implicitly re-prepare that query and rerun it only in DB2 instead -- i.e., "failback" the query to DB2. DB2 should not repeatedly resend the query to the accelerator for that same single SQL OPEN. . From analysis of the problem, DB2z Development determined that during the implicit "failback" re-prepare to run the query in DB2z, when accelerator modeling was requested DB2 re-prepared the query for sending to the accelerator again, instead of re-preparing it to run only on DB2z. . If accelerator modeling is not requested (zparm ACCELMODEL=NO), then the "failback" re-prepare to DB2z occurs correctly and the query is run only in DB2. The query is not resent to the accelerator. So to circumvent the problem, set zparm ACCELMODEL=NO (note that ACCELMODEL is an online-changeable zparm and does not require a DB2 STOP and reSTART). . . (2) When using QUERY ACCELERATION behavior ENABLE WITH FAILBACK and Accelerator archive behavior is also requested as GET_ACCEL_ARCHIVE = YES (via either DB2 subsystem parameter/ zparm or special register), one of the following DB2 application abends occurred when a certain query was accelerated: ABEND04E RC00E70005 at DSNXGRDS . DSNXUTL :M100 ABEND04E RC00E2000C at DSNXEAB . DSNSVSVB +0B06 The accelerated table referenced in the failing query is *not* archived ("failback" to DB2z is not possible for a query whose accelerated table is 'archived'). Other abends may be possible. . DB2z Development determined the abend(s) occurred during the implicit "failback" re-prepare to DB2z after the query failed on the accelerator during the application's SQL OPEN. The abend only occurred when archive acceleration is requested, because in this case DB2 did not correctly perform the re-prepare of the query to run only on DB2z. If GET_ACCEL_ARCHIVE = NO , the abend(s) does not occur and the query "failback" re-prepare to DB2 succeeds and the query runs only in DB2z. . To circumvent the problem, use GET_ACCEL_ARCHIVE=NO via either special register or DB2 zparm (note that this zparm is online-changeable and does not require a DB2 stop and reSTART).
Problem conclusion
DB2 code to perform the implicit "failback" re-prepare to DB2 for ENABLE WITH FAILBACK behavior was changed to perform the re-prepare correctly in both of the failing scenarios previously described, and the problems reported no longer occur. . . Additional search keywords: IDAAV4R1/K DB2HANG LOOP ENABLEWITHFAILBACK EWF SQLSPECIALREG ABNDID04E OFFSETM100 OFFSET0B06
Temporary fix
AI55807
Comments
APAR Information
APAR number
PI55807
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
2016-01-21
Closed date
2016-05-12
Last modified date
2016-11-09
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI37811 UI37812
Modules/Macros
DSNXODML DSNXOMPS DSNXOOP
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":"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:
09 November 2016