DB2 FOR LUW / DB2FORLUW / 970 - 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. Install release and fixpak that has   *
* this fix.                                                    *
****************************************************************
* RECOMMENDATION:                                              *
* Install release and fixpak that has this fix.                *
****************************************************************

none available. Install release and fixpak that has this fix.

not known / see Local fix

forerunner  : IT17851 
follow-up : 

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

23.11.2016
30.05.2017
30.05.2017