PI04890: SQLCODE -901 ATTRIBUTE NOT FOUND FOR OFFLOAD OF A COMMON TABLE EXPRESSION WITH SET OPERATION

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQLCODE -901 Attribute not found for offload of a common table
  expression with set operation when using 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: This APAR fixes the following issues    *
  *                      for IBM DB2 Analytics Accelerator.      *
  *                                                              *
  *                      (1) Incorrect output may be returned    *
  *                          from the accelerator for a query    *
  *                          that involves DBCS datatypes.       *
  *                                                              *
  *                      (2) A query with a GROUP BY may have    *
  *                          repeating columns when executed     *
  *                          on an accelerator.                  *
  *                                                              *
  *                      (3) An SQLCODE -901 is returned for     *
  *                          a query that contains a common      *
  *                          table expression inside a set       *
  *                          operation.                          *
  *                                                              *
  *                      (4) Incorrect output may be returned    *
  *                          from the acclerator for a query     *
  *                          that contains a HAVING clause and   *
  *                          no GROUP BY clause.                 *
  *                                                              *
  *                      (5) A query is incorrectly executed on  *
  *                          an accelerator when there is mixed  *
  *                          data column that has a CAST as      *
  *                          VARCHAR and QUERY_ACCEL_OPTIONS     *
  *                          contains the  values 1 or 2 and     *
  *                          DSNHDECP hsa MIXED set to YES.      *
  *                                                              *
  *                      (6) A query with CASE WHEN (NOT EXISTS) *
  *                          correlated subquery is not blocked  *
  *                          from executing on an accelerator.   *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  This APAR fixes the following issues for IBM DB2 Analytics
  Accelerator.
  
  (1) Incorrect output may be returned from the accelerator for a
      query that involves DBCS datatypes.
  
  (2) A query with a GROUP BY may have repeating columns when
      executed on an accelerator.
  
  (3) An SQLCODE -901 is returned for a query that contains a
      common table expression inside a set operation.
  
  (4) Incorrect output may be returned from the acclerator for a
      query that contains a HAVING clause and no GROUP BY clause.
  
  (5) A query is incorrectly executed on an accelerator when it
      should be executed on DB2 when there is a mixed data column
      that has a CAST as VARCHAR, QUERY_ACCEL_OPTIONS does not
      contain the value 3, and DSNHDECP has MIXED set to yes.
      This results in incorrect output.
  
  (6) A query with CASE WHEN (NOT EXISTS) correlated subquery is
      not blocked from executing on an accelerator. This type of
      correlated subquery cannot execute on an accelerator.
  
  For static SQL queries that have been bound for acceleration
  and encounter any of the issues above, after applying this
  PTF, REBIND the package for the failing query.
  
  Additional Keywords:
  IDAAV3R1/K SQLCODE901 SQLINCORR SQLINCORROUT INCORROUT
  DB2INCORR/K SQLCTE SQLGROUPBY SQLHAVING IDAAV4R1/K
  

Problem conclusion

• (1) DB2 code is fixed to return the correct output from the
    accelerator.
  (2) DB2 code is fixed.
  (3) DB2 code is fixed so that the query executes successfully
    on the accelerator.
  (4) DB2 code is fixed to return the correct output from the
    accelerator.
  (5) DB2 code is fixed.
  (6) DB2 code is fixed to block this type of correlated subquery
  

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:

  UI13503

Modules/Macros

• DSNXOEX1 DSNXONZC DSNXONZO DSNXONZQ DSNXONZS DSNXOOS1 DSNXOP1
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI13503

     UP14/01/10 P F401 ½

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.