PM63022: QUERY LOOPS / HANGS WHEN USING IDAA WITH REOPT AUTO AND CURRENT QUERY ACCELERATION ENABLE WITH FAILBACK

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• When using IBM DB2 Analytics Accelerator (IDAA) and special
  register CURRENT QUERY ACCELERATION = ENABLE WITH FAILBACK ,
  a query appears to loop or hang when bound REOPT(AUTO).
                                                              .
  The IDAA and DB2 DRDA traces show that the query fails on
  IDAA at SQL OPEN, and DB2 repeatedly tries to offload the query
  to IDAA after each failure, instead of running the query in DB2
  after the failure as directed by special reg setting
  CURRENT QUERY ACCELERATION = ENABLE WITH FAILBACK .
  This only occurs when the application is bound REOPT(AUTO).
                                                            .
  Additional search keywords: IDAAV2R1/K
  

Local fix

• To avoid the loop or hang, REBIND the application REOPT(NONE) or
  use CURRENT QUERY ACCELERATION = ENABLE   (without FAILBACK).
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: DB2 9 and 10 for z/OS users of IBM DB2       *
  *                 Analytics Accelerator (IDAA) and CURRENT     *
  *                 QUERY ACCELERATION ENABLE WITH FAILBACK with *
  *                 bind option REOPT(AUTO)                      *
  ****************************************************************
  * PROBLEM DESCRIPTION: When using IDAA and CURRENT QUERY       *
  *                      ACCELERATION ENABLE WITH FAILBACK,      *
  *                      a dynamic query appears to loop or hang *
  *                      when bound REOPT(AUTO).                 *
  *                                                              *
  *                      IDAA and DB2 traces show that the query *
  *                      failed on IDAA at SQL OPEN as DB2       *
  *                      repeatedly tried to offload the query   *
  *                      to IDAA.                                *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  When using IBM DB2 Analytics Accelerator (IDAA) and special
  register CURRENT QUERY ACCELERATION = ENABLE WITH FAILBACK
  (or DB2 system parameter QUERY ACCELERATION),
  a query appears to loop or hang when bound REOPT(AUTO).
                                                                 .
  The IDAA and DB2 DRDA traces show that the query OPEN fails on
  IDAA, but after each OPEN failure on IDAA, DB2 repeatedly
  re-prepares the query for offload and then tries to offload
  the query to IDAA again, instead of re-preparing the query to
  run in DB2 as directed by special register
     CURRENT QUERY ACCELERATION = ENABLE WITH FAILBACK .
  This problem only occurs when the query SQL OPEN fails when
  offloaded to IDAA and the application is bound REOPT(AUTO).
                                                                 .
  DB2 continuously tried to prepare and offload the failing query
  to IDAA because when using bind option REOPT(AUTO), DB2 reset
  the internal indicator that marked the query OPEN offload as
  having failed, so DB2 did not correctly FAILBACK the query to be
  re-prepared and run in DB2. Instead DB2 continued to try and
  re-prepare the query to be offloaded to IDAA. This resulted in
  an infinite 'PREPARE + OPEN query' offload process loop for
  the application that was running the query.
                                                                 .
  To avoid the loop or hang, REBIND the application as REOPT(NONE)
  or use CURRENT QUERY ACCELERATION = ENABLE   (without FAILBACK),
  if appropriate.
  

Problem conclusion

• DB2 code for IDAA offload was changed to correctly FAILBACK the
  query to be prepared and run in DB2 when the query fails on IDAA
  at OPEN and the bind option REOPT(AUTO) is used with
  CURRENT QUERY ACCELERATION = ENABLE WITH FAILBACK.
                                                                 .
  Additional search keywords: IDAAV2R1/K  REOPT AUTO
                              LOOPDSNXERT LOOPDSNXERT2 LOOPDSNXEDP
                              LOOPDSNXOOP LOOPDSNXEPM
  

Temporary fix

• AM63022
  

Comments

APAR Information

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

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

  UK78886 UK78887

Modules/Macros

• DSNXEDP  DSNXERT2
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK78886

     UP12/06/05 P F206

• R910 PSY UK78887

     UP12/06/05 P F206

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.