DB2 FOR LUW / DB2FORLUW / A10 - DB2

An sql statement in a partitioned database environment 
containing the ROW_NUMBER() OVER() operation 
might produce inconsistent results because the ROW_NUMBER() 
operation is processed in parallel on the individual partitions 
on the broadcasted result set. 
 
A example statement : 
 
select T1.BIGINT_ID, count(*) FROM 
( SELECT 
    VARCHAR_KEY, 
    ROW_NUMBER() OVER() AS BIGINT_ID 
  FROM 
    ( 
      SELECT DISTINCT 
        VARCHAR_KEY 
      FROM 
        MYTABLE 
    ) ) T1, MYTABLE T2 
where T1.VARCHAR_KEY = T2.VARCHAR_KEY 
group by T1.BIGINT_ID having count(*) > 1 
 
This will have an execution plan showing  : 
 
 
                  11141 
                 BTQ 
                 (  11) 
                   | 
                 1856.83 
                 MDTQ 
                 (  12) 
 
Where the resultset is pushed out to all partitions via the BTQ 
operation and where the row_number() operator is applied. 
A simplified example data set : 
 
TQ11  receives all matching rows and it will be assigning the 
rank to all rows as it receives them. 
say TQ11 on partition 0 receives row a b c d,  it will assign 
these row_number() / rank value : 
 
a rownum 1 
b rownum 2 
c rownum 3 
d rownum 4 
 
but partition 1 might receive the rows in a different order : c 
b a d 
so on partition 1 the assigned rownumbers are different 
 
a rownum 3 
b rownum 2 
c rownum 1 
d rownum 4 
 
hence when the data is then rejoined with the base table, the 
result might be 
a 1 
b 2 
c 1 
d 4 
 
with incorrect duplicate values for the rownumber() column.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 10.1 Fix Pack 6                               * 
****************************************************************

If there is a unique column in the stream that row_number() over 
() runs on, then add the unique column into row_number: 
row_number() over (order by <unique-columns>) 
 
Example: 
SELECT VARCHAR_KEY, 
       ROW_NUMBER() OVER (ORDER BY VARCHAR_KEY) + 1000000000 AS 
BIGINT_ID

First fixed in DB2 10.1 Fix Pack 6

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

08.11.2016
02.03.2017
02.03.2017