A fix is available
APAR status
Closed as program error.
Error description
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.
Local fix
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
Problem summary
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 10.1 Fix Pack 6 * ****************************************************************
Problem conclusion
First fixed in DB2 10.1 Fix Pack 6
Temporary fix
Comments
APAR Information
APAR number
IT17851
Reported component name
DB2 FOR LUW
Reported component ID
DB2FORLUW
Reported release
A10
Status
CLOSED PER
PE
NoPE
HIPER
YesHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2016-11-08
Closed date
2017-03-02
Last modified date
2017-03-02
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Fixed component name
DB2 FOR LUW
Fixed component ID
DB2FORLUW
Applicable component levels
[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1"}]
Document Information
Modified date:
26 September 2021