PI70476: HANG OR LOOP FOR ACCELERATED QUERY USING MEDIAN AND ENABLE WITH FAILBACK BEHAVIOR. SQLCODE -802. DB2 V10 & V11 ONLY

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• HANG OR LOOP occurs for an accelerated query that uses built-in
  function (bif) MEDIAN and accel behavior ENABLE WITH FAILBACK is
  in effect. This occurs for DB2 V10 and V11 only.
  .
  Examination of trace recs between DB2z and the accelerator
  showed that DB2z repeatedly re-drives the query to be run on
  on the accelerator, but each time the query with bif MEDIAN
  failed on the accelerator with SQLCODE -802 .
  DB2 incorrectly and continuously re-drives the failing query
  to the accelerator when accel behavior ENABLE WITH FAILBACK is
  in effect.  The external symptom of this problem appears as
  a hang or loop for the application or query.
  .
  This problem only occurs if the query with MEDIAN fails on
  the accelerator and ENABLE WITH FAILBACK is used. The problem
  only applies to DB2 V10 and V11.
  .
  Additional search keywords: SQLCODE802 SQLMEDIAN
  

Local fix

• To avoid the hang and unlimited re-drive of the failing query
  with MEDIAN, use acceleration behavior ENABLE or ELIGIBLE, or,
  if possible, remove the bif MEDIAN from the query.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 V10 and V11 for z/OS users of QUERY  *
  *                 ACCELERATION behavior ENABLE WITH FAILBACK   *
  *                 for a query that specifies the DB2 built-in  *
  *                 function MEDIAN                              *
  ****************************************************************
  * PROBLEM DESCRIPTION: User specified QUERY ACCELERATION       *
  *                      behavior ENABLE WITH FAILBACK for       *
  *                      a query that uses the built-in function *
  *                      MEDIAN, and the query did not terminate *
  *                      but instead, appeared to hang .         *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  User specified QUERY ACCELERATION behavior ENABLE WITH FAILBACK
  (EWF) for a query that referenced built-in function (bif) MEDIAN
  and the query did not terminate but instead, appeared to 'hang'.
  Another possible external symptom of the problem is the DB2z
  connection limit being exceeded as the target accelerator
  repeatedly responded with "denial of service" to the query
  requests.
                                                                 .
  Examination of trace records for both DB2z and the IBM DB2
  Analytics Accelerator showed that DB2z repeatedly and
  continuously sent the same MEDIAN query to run on the same
  accelerator on a single SQL OPEN for the query from the user
  application. However, each attempt to run the query failed on
  the accelerator server, returning SQLCODE -802 to DB2z.
  The ENABLE WITH FAILBACK behavior resulted in an infinite run-
  loop situation between DB2z and the accelerator server for
  the single query, appearing externally as a 'hang'.  The failure
  on the accelerator was valid a failure, however, the SQLCODE802
  was never returned to the user application, because DB2z
  repeatedly and incorrectly tried to run that query on
  the accelerator -- not allowing the application or the query to
  terminate.
                                                                 .
  When using acceleration behavior ENABLE WITH FAILBACK, if
  an accelerated query fails on the first OPEN (within a COMMIT
  scope), the normal DB2z behavior is to temporarily implicitly,
  re-PREPARE the query to run only in DB2z -- i.e, "failback"
  the query to DB2z, where the query then returns DB2z-specific
  results to the user application instead of the accelerator
  failure.  So the user would normally see the query succeed
  instead of failing, if no error occurred when the query was run
  only on DB2z.
                                                                 .
  However, for DB2 V10 and V11, the built-in function MEDIAN is
  only supported in queries that are accelerated to an accelerator
  server; such queries cannot be run on DB2z itself. So when using
  behavior EWF for these queries, instead of DB2z implicitly
  re-preparing and running the query only in DB2z, DB2z should
  terminate the query and return the accelerator failure to
  the user's application. In this case, DB2z did not do that, but
  instead repeatedly re-prepared the failing query to run on
  the accelerator, because the user table was also defined on that
  accelerator and the query could not be prepared to run only in
  DB2z.  This resulted in the 'hang' or infinite loop situation
  between DB2z and the accelerator server for this query.
                                                                 .
  This problem can occur for either dynamic or static queries that
  specify MEDIAN and are accelerated using ENABLE WITH FAILBACK
  behavior, but fail on the accelerator server. If the query
  succeeds on the accelerator, no hang or loop between DB2z and
  the accelerator server occurs.
                                                                 .
  Note that this problem does not occur on DB2 V12 because the
  built-in function MEDIAN "is" supported on V12. So in this
  failing scenario under EWF for DB2 V12, the query is implicitly
  re-prepared to run only in DB2z and is not accelerated again for
  that COMMIT scope.
  

Problem conclusion

• DB2 V10 and V11 code for ENABLE WITH FAILBACK behavior was
  corrected to not allow the implicit 'failback' to DB2z for query
  specifying bif MEDIAN, if that query fails on the accelerator
  server. Thus preventing the hang or infinite run-loop situation
  between DB2z and the accelerator server for this query.
                                                                 .
  For an application bound with bind option QUERYACCELERATION
  (ENABLEWITHFAILBACK) and containing a static query that
  uses bif MEDIAN, to make this APAR fix effective for that static
  query, that application must be rebound after application of
  this PTF.
                                                                 .
  Additional search keywords: IDAAV4R1/K IDAAV4R1/K DB2HANG LOOP
                              ENABLEWITHFAILBACK EWF SQLMEDIAN
                              SQLSPECIALREG
  

Temporary fix

• AI70476
  

Comments

APAR Information

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

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

  UI44242 UI44243

Modules/Macros

• DSNXECLF DSNXECW  DSNXECWA DSNXECWU DSNXEDP
  DSNXEDSC DSNXEDS1 DSNXEPP  DSNXERT  DSNXERT2 DSNXODML
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI44242

     UP17/02/10 P F702 Ž

• RB10 PSY UI44243

     UP17/02/10 P F702 Ž

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.