PH15410: INCORRECT MAY HPPEN FOR QUERY CONTAINS OLAP FUNCTIONS AND REFERENCES TABLE EXPRESSION/VIEW/SQL TABLE FUNCTION OR CTE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Incorrect output may happen for an SQL statement that satisfies
  all of the following conditions:
  
  1. The statement contains a nested table expression, view or
  SQL table function.
  2. The statement contains an OLAP specification.
  3. The nested table expression, view or SQL table function
  contains an expression that is referenced more
  than one time in the select list of the SQL statement.
  
  
  
  
  
  
  
  Additional keywords:
  DB2INCORR/K INCORROUT SQLINCORR SQLINCORROUT ZSA2
  

Local fix

• Please contact Db2 for z/OS Client Success team to guide about
  setting the query rewrite zparm
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 12 for z/OS users of an SQL                          *
  * statement containing a table                                 *
  * expression, view or SQL table                                *
  * function and an OLAP                                         *
  * specification.                                               *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Incorrect output may happen for an                           *
  * SQL statement that satisfies all                             *
  * of the following conditions:                                 *
  * 1. The statement contains a nested                           *
  * table expression, view or SQL table                          *
  * function.                                                    *
  * 2. The statement contains an OLAP                            *
  * specification.                                               *
  * 3. The nested table expression,                              *
  * view or SQL table function contains                          *
  * an expression that is referenced more                        *
  * than one time in the select list of                          *
  * the SQL statement.                                           *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  For example,
  
  SELECT  CX, MAX(CX) OVER() FROM (
    SELECT CX, DEC(CX, 5,1) FROM
       (SELECT C1+1 FROM T1)TX(CX)
  )TY(CX,CY);
  
  Incorrect output may occur for the SELECT
  statement.
  

Problem conclusion

• Db2 has been modified to correctly process the
  aforementioned SQL statement, and correct result
  will be returned.
  
  Additional keywords: SQLTABLEEXPR SQLVIEW SQLSETFUNCTION
                       SQLINCORR INCORROUT SQLINCORROUT
                       DB2INCORR/K
                       SQLRANK SQLDENSERANK SQLROWNUMBER
                       SQLOLAP
  

Temporary fix

Comments

APAR Information

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

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

  UI65716

Modules/Macros

• DSNXOOS2
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI65716

     UP19/10/21 P F910

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.