DB2 FOR LUW / DB2FORLUW / 970 - DB2

When a query uses Fetch First N Rows Only, and has a series of 
lossless Referential Integrety (RI) Joins, the optimizer may 
choose to use a truncated operator, such as a Truncated SORT or 
a Truncated Table Queue. The optimizer may then incorrectly 
choose to truncate the join of a series of dimension tables, 
leading to incorrect results. 
 
This can most commonly be identified by looking for the 
following argument on either a SORT: 
  TRUNCSRT: (Truncated sort (for Fetch First n Rows Only)) 
   TRUE 
 
or a TABLE QUEUE (TQ) operator: 
  TRUNCTQ : (Truncated Table Queue (for Fetch First n Rows 
Only)) 
   <value> 
where <value> could be any one of "INPUT", "OUTPUT" or "INPUT 
AND OUTPUT" 
 
If either of these operators occur in a plan that contains only 
the dimension tables, then the plan may return incorrect 
results, if the join between the dimension tables produces more 
tuples then the truncated operator will allow to be produced.

APAR fix was provided in Fixpack 4.

Remove Fetch First N Rows from the query

DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 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
 

The fix ensures correct result set is returned for the case in 
question.

not known / see Local fix

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

22.10.2010
09.05.2011
09.05.2011