DB2 FOR LUW / DB2FORLUW / 950 - 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)                    <its downlaoding 
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:                                              * 
* Upgrade to DB2 version 950 fixpack 10                        * 
****************************************************************

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

The problem was first fixed in DB2 version 950 fixpack 10

not known / see Local fix

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

23.01.2012
30.10.2012
30.10.2012

9.5.0