DB2 FOR LUW / DB2FORLUW / B10 - 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 11.1 Mod 2 Fix Pack 2 or higher               * 
****************************************************************

DB2 Version 11.1 Mod 2 Fix Pack 2 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod2 Fix Pack2 iFix001 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod2 Fix Pack2 iFix002 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod 3 Fix Pack 3 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod3 Fix Pack3 iFix001 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod3 Fix Pack3 iFix002 for Linux, UNIX, and Windows
 

First fixed in DB2 11.1 Mod 2 Fix Pack 2

not known / see Local fix

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

23.11.2016
23.06.2017
23.06.2017