DB2 FOR LUW / DB2FORLUW / 970 - DB2

Under rare cases, a query satisfying the following conditions 
might produce wrong results: 
1. The query has a common table expression (CTE). 
2. The CTE has a select over an update/delete/insert (UDI) 
3. The select in the CTE has a fetch first n rows. 
4. The value to be set also comes from the same table on which 
UDI is taking place 
 
eg. 
WITH updated AS (  CTE  select * (outer select from final table( 
update db2admin.SE_CUSTOMER_PRODUCT_TEMP_test c 
 UDI 
        set PROCESS_FLAG=(select 11 from 
DB2admin.SE_CUSTOMER_PRODUCT_TEMP_test (select on same table 
                                        fetch first row only) 
where PROCESS_FLAG=0  )s 
fetch first 10 rows only) 
fetch first n rows on outer select 
select * from updated; 
 
This query should update all records in the table but only 
output 10 records. Actual result is updation of 10-12 rows only. 
 
Rewritten version: 
WITH updated AS ( 
select * 
from final table( 
        update db2admin.SE_CUSTOMER_PRODUCT_TEMP_test c 
        set PROCESS_FLAG=(select 11 from 
DB2admin.SE_CUSTOMER_PRODUCT_TEMP_test 
                                        fetch first row only) 
where PROCESS_FLAG=0  )s 
) 
fetch first n rows on outer select removed 
select * from updated fetch first 10 rows only; 
 fetch first n rows placed in query

**************************************************************** 
* USERS AFFECTED:                                              * 
* All                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See problem description                                      * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Please upgrade to DB2 version 970 fix pack 6                 * 
****************************************************************

fetch first n rows should be removed from the CTE and put in the 
actual query

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 problem is first fixed in DB2 version 970 fix pack 6

not known / see Local fix

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

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

02.12.2011
05.06.2012
05.06.2012

9.7.0