DB2 FOR LUW / DB2FORLUW / 970 - DB2

If you are using the database partitioning feature (DPF) and 
your query satisfies the following conditions, then the results 
returned might be incorrect if the DB2 query plan optimizer 
chose a right outer hash join operation: 
1. there is an outer join between two tables, T1 and T2; 
2. one or both T1 and T2 are hash distributed across 2 or more 
database partitions; 
3. the join is not performed on the hash distribution key; 
4. there is a group by clause on the columns the join is 
performed on. 
 
For example, consider tables T1 and T2, both hash distributed on 
column (A).  The following query satisfies the conditions and 
might return incorrect results, depending on the data in the 
tables and if a right outer hash join operation is used: 
 
SELECT T2.B, COUNT(*) 
FROM T1 LEFT JOIN T2  ON T1.B = T2.B 
GROUP BY T2.B 
 
You can identify if the results are incorrect if the expected 
groups includes duplicate null entries. 
You can identify if an right outer hash join operation is used 
by capturing an EXPLAIN of the query and verifying the 
following: 
1.  a HSJOIN operation is chosen to perform the outer join 
2.  the OUTERJN (Outer Join Type) argument of the HSJOIN is set 
to "RIGHT"

**************************************************************** 
* USERS AFFECTED:                                              * 
* All users with database partitioning feature                 * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to version 9.7 Fix Pack 6                            * 
****************************************************************

You can workaround this defect by using one of the following 
options: 
1. an optimization class that does not consider hash join, which 
includes classes 0, 1, and 3; or 
2. create an optimization profile to use a different join 
method, such as NLJOIN or MSJOIN, or reverse the order of the 
inputs to the HSJOIN operator which will produce a "LEFT" outer 
join type.

DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Problem was first fixed in version 9.7 Fix Pack 6

See Local Fix

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

05.03.2012
13.06.2012
13.06.2012

9.7.FP6