PM92528: OLAP FUNCTION ROW_NUMBER() DOES NOT SUPPLY UNIQUE ROW NUMBERS

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• INCORROUT may occur when using the ROW_NUMBER() OLAP
  clause and HYBRID JOIN is also being used.  The incorrout is
  that the row numbers are not unique.  In the reported case,
  rows 1-626 was returned, and then next row (the 627th row) was
  numbered 1 and the sequence started over (1-626,1-626, etc.)
  
  DB2INCORR/K
  

Local fix

• Disable Hybrid Join
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 and DB2 11 for z/OS users of OLAP *
  *                 specifications with hybrid join.             *
  ****************************************************************
  * PROBLEM DESCRIPTION: An incorrect result can be returned for *
  *                      a query using an OLAP specification     *
  *                      with hybrid join.                       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An incorrect result can be returned for a query using an OLAP
  specification with hybrid join.  If an OLAP specification such
  as ROW_NUMBER is used in a query that has an access path with
  hybrid join and the intermediate table overflows, the row number
  result is incorrectly reset to zero which causes the row number
  number values to start over at 1 again.
  
  The following example illustrates a failing case.
  
    SELECT ROW_NUMBER() OVER() AS RN
    FROM T1 A, T2 B, T5 C
    WHERE A.C1 = B.C1
    AND   C.C1 = B.C1 ;
  
  If the intermediate table overflows while processing row number
  5000, the value returned for row 5001 is 1 instead of the
  expected value 5001.
  
  This problem can occur for any OLAP specification and has
  various incorrect results reported dependent on the OLAP spec
  that is used.  The problem can also occur if the rid pool
  overflows (instead of the intermediate table) while processing
  the hybrid join.
  

Problem conclusion

• The code in DB2 has been modified to return the correct result.
  The OLAP specification is no longer reset during overflow
  processing.
  
  Additional Keywords: SQLOLAP SQLHYBRID SQLHYBRIDJOIN
  

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:

  UK96317 UK96318

Modules/Macros

• DSNXRCLS
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK96317

     UP13/08/15 P F308 ½

• RB10 PSY UK96318

     UP13/08/15 P F308 ½

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.