DB2 FOR LUW / DB2FORLUW / 970 - DB2

This problem only occurs in a DPF or SMP parallelism enabled 
environment. 
 
You might encounter an incorrect result set for a SQL statement 
that contains two or more OLAP functions with compatible 
partition-by clauses wherein at least one OLAP function contains 
a partition-by clause where all columns equate to constant 
values. A column can equate to a constant value by specifying 
the constant values explicitly in the partition by clause or the 
columns in the partition by clause are referenced in equality 
predicates that equate to constants. 
 
The following example specifies the constant value explicitly 
SELECT ... SUM(X) OVER (PARTITION BY 1 ...) ... 
 
and the following example references the partition by column in 
an equality predicate 
SELECT ... SUM(X) OVER (PARTITION BY Y ...) ... WHERE Y = 1 ...

**************************************************************** 
* USERS AFFECTED:                                              * 
* DPF or SMP parallelism enabled environments                  * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 version 9.7.0.11.                             * 
****************************************************************

You can rewrite your query to move the OLAP function with 
constant partition-by clause into a nested table expression. 
For example, if you have two compatible OLAP functions in a 
query as follows: 
 
SELECT SUM(X) OVER (PARTITION BY A), SUM(Y) OVER (PARTITION BY 
A,B) FROM T1 WHERE T1.A=1 
 
then you can rewrite it as a nested table expression to avoid 
the incorrect result: 
 
SELECT SUMX, SUM(Y) OVER (PARTITION BY A,B) FROM (SELECT A,B,Y, 
SUM(X) OVER (PARTITION BY A) SUMX FROM T1 WHERE A=1) AS TMP

The problem is first fixed in DB2 version 9.7.0.11.

not known / see Local fix

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

27.05.2015
06.10.2015
06.10.2015

9.7.0.11