DB2 FOR LUW / DB2FORLUW / 970 - DB2

If there is an exists subquery in the head expression, this 
subquery will be pushed down and the original sql statement will 
be converted to a join sql statement. 
 
For example, in the following sql statement, nick1 and nick2 are 
nicknames and there is an exists subquery in the head 
expression: 
select ((case when exists(select * from nick1 where c2='aaa') 
then 'TRUE' else 'FALSE' end)) from nick2; 
 
DB2 compiler will rewrite it to: 
Optimized Statement: 
------------------- 
SELECT 
CASE 
WHEN (1 = Q3.$C0) 
THEN 'TRUE' 
ELSE 'FALSE' END 
FROM 
   (SELECT DISTINCT Q2.$C0 
   FROM 
      (SELECT 1 
      FROM IIDEV22.NICK1 AS Q1 
      WHERE (Q1.C2 = 'aaa       ')) AS Q2) AS Q3, IIDEV22.NICK2 
AS Q4 = 
 
In this scenario, federation pda(pushdown analysis) component 
will push down all the subquery to remote data source which 
cause the wrong result.

The fix is fixed in db2 v97 FP5

Change the exists subquery to "count(*) > 0" clause

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
 

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC81476 
follow-up : 

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

17.08.2011
12.12.2011
12.12.2011