DB2 FOR LUW / DB2FORLUW / A10 - 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 Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Please upgrade to DB2 version 101 fix pack 1                 * 
****************************************************************

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

DB2 Version 10.1 Fix Pack 1 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
 

Problem is fixed in DB2 version 101 fix pack 1

not known / see Local fix

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

17.04.2012
21.11.2012
21.11.2012