PM62259: INCORROUT (FEWER ROWS THAN EXPECTED) CAN BE RETURNED WHEN USING OLAP AND A SUBQUERY PREDICATE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Incorrect output may occur when a query contains a subquery
  predicate and olap specifications and no sort is needed for
  olap processing.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 9 and DB2 10 for z/OS users of OLAP  *
  *                 specifications.                              *
  ****************************************************************
  * PROBLEM DESCRIPTION: (1) V9 and V10:                         *
  *                      Incorrect output may occur for a query  *
  *                      that contains an OLAP specification and *
  *                      a subquery predicate.                   *
  *                      (2) V10:                                *
  *                      ABENDOC4 RC00000038 DSNXOWIN+104A may   *
  *                      occur when a query contains multiple    *
  *                      OLAP specifications other than RANK,    *
  *                      DENSE_RANK, and ROW_NUMBER.             *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  (1) V9 and V10:
  Incorrect output may occur for a query that contains an OLAP
  specification and a subquery predicate.
  The following is an example of such a failing scenario:
  SELECT ROW_NUMBER() OVER(ORDER BY C1)
    FROM T1
   WHERE T1.C2 IN (SELECT ...)
  Note: There is an index on T1.C1 and no sort miniplan for the
  OLAP specification.  The subquery is transformed into a
  correlated virtual table.
  
  (2) V10:
  OC4 ABEND RC00000038 DSNXOWIN+104A may occur when a query
  contains multiple OLAP specifications other than RANK,
  DENSE_RANK, and ROW_NUMBER.
  The following is an example of such a failing scenario:
  SELECT SUM(C1) OVER (PARTITION BY..ORDER BY..ROWS BETWEEN..),
         AVG(C2) OVER (PARTITION BY..ORDER BY..ROWS BETWEEN..),
         MIN(C1) OVER (PARTITION BY..ORDER BY..ROWS BETWEEN..),
         MIN(C2) OVER (PARTITION BY..ORDER BY..ROWS BETWEEN..),
         MAX(C1) OVER (PARTITION BY..ORDER BY..ROWS BETWEEN..),
         MAX(C2) OVER (PARTITION BY..ORDER BY..ROWS BETWEEN..),
         ....
     FROM (SELECT ...) TX;
  

Problem conclusion

• DB2 is modified to process OLAP specifications correctly.
  
  Additional keywords: SQLSUBQUERY SQLRANK SQLDENSERANK
                       SQLROWNUMBER SQLINCORR INCORROUT
                       SQLINCORROUT DB2INCORR/K SQLOLAP
                       ABENDOC4 RC00000038 in DSNXOWIN
  

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:

  UK79062 UK79063

Modules/Macros

• DSNXOGA  DSNXOWIN
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK79062

     UP12/06/11 P F206

• R910 PSY UK79063

     UP12/06/11 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.