home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

DB2 - Problembeschreibung

Problem IC82715 Status: Geschlossen

SELECT FROM AN UPDATE/DELETE/INSERT IN COMMON TABLE EXPRESSION GIVES WRONG
RESULTS.

Produkt:
DB2 FOR LUW / DB2FORLUW / A10 - DB2
Problembeschreibung:
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)
Problem-Zusammenfassung:
**************************************************************** 
* USERS AFFECTED:                                              * 
* All                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Please upgrade to DB2 version 101 fix pack 1                 * 
****************************************************************
Local-Fix:
fetch first n rows should be removed from the CTE and put in the 
actual query
verfügbare FixPacks:
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

Lösung
Problem is fixed in DB2 version 101 fix pack 1
Workaround
keiner bekannt / siehe Local-Fix
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
17.04.2012
21.11.2012
21.11.2012
Problem behoben ab folgender Versionen (IBM BugInfos)
Problem behoben lt. FixList in der Version
10.1.0.1 FixList
10.5.0.1 FixList