PM94515: BLOCKING UNSUPPORTED CORRELATED SUBQUERY FOR IBM DB2 ANALYTICS ACCELERATOR

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Blocking unsupported correlated subquery for IBM DB2 Analytics
  Accelerator.
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All users of DB2 11 for z/OS who use         *
  *                 IBM DB2 Analytics Accelerator.               *
  ****************************************************************
  * PROBLEM DESCRIPTION: (1) Certain correlated subqueries       *
  *                      cannot run on the accelerator and issue *
  *                      SQLCODE -904.                           *
  *                                                              *
  *                      (2) Currently DB2 blocks all NOT IN     *
  *                      subqueries since incorrect output is    *
  *                      possible when executed on an            *
  *                      accelerator.                            *
  *                                                              *
  *                      (3) SQLCODE -904 or incorrect output    *
  *                      may be returned for a query with        *
  *                      parameter markers and INNER JOIN is     *
  *                      in the query text or the query          *
  *                      contains an IN-list with more than      *
  *                      32,000 items.                           *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  (1) Certain correlated subqueries cannot run on the
  accelerator and issue SQLCODE -904.
  
  (2) Currently DB2 blocks all NOT IN subqueries since incorrect
  output is possible when executed on an accelerator.
  
  (3) SQLCODE -904 or incorrect output may be returned for a query
  with parameter markers and INNER JOIN is in the query text or
  the query contains an IN-list with more than 32,000 items.
  
  Additional Keywords:
  IDAAV3R1/K IDAAV4R1/K SQLCODE904 SQLNOTIN SQLINCORROUT SQLINCORR
  INCORROUT DB2INCORR/K SQLIN PARAMETERMARKER SQLPMARKER SQLPMK
  

Problem conclusion

• (1) The unsupported correlated subqueries will run on DB2 when
  QUERY ACCELERATION=ENABLE, ENABLE WITH FAILBACK or ELIGIBLE, and
  they will receive SQLCODE -4742 reason 21 when
  QUERY ACCELERATION=ALL or GET_ACCEL_ARCHIVE = YES.
  
  (2) NOT IN subqueries are now eligible for offload if the
  accelerator is at the right version.
  
  (3) The SQLCODE -904 or possible incorrect output is fixed.
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

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

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

  UK98637

Modules/Macros

• DSNXOD5  DSNXOEX1 DSNXONZA DSNXONZC DSNXONZO DSNXOOS1 DSNXOP1
  DSNXOW1
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UK98637

     UP13/11/05 P F311 ½

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.