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 IC75033 Status: Geschlossen

POSITIONED/SEARCHED UPDATE AND DELETE IN DB2 CONTROL CENTER MIGHT CORRUPT
DATA.

Produkt:
DB2 FOR LUW / DB2FORLUW / 950 - DB2
Problembeschreibung:
"positioned UPDATE and DELETE" was introduced in V9.5 DB2 
Control Center by the fix of APAR IZ31390 as a complement of 
searched UPDATE and DELETE. 
However in some scenarios positioned/searched UPDATE and DELETE 
will UPDATE/DELETE rows that you do not want to UPDATE/DELETE, 
therefore corrupt the data. 
 
Here is an example of positioned UPDATE: 
================================ 
db2 "create db us_db using codeset 1252 territory US" 
 
db2set db2codepage=1252 
 
db2 terminate 
db2 "connect to us_db" 
 
db2 "create table test1 (id char(1), name char(8)) 
db2 "insert into test1 values ('1', 'bbb')" 
db2 "insert into test1 values ('2', 'bbb')" 
db2 "insert into test1 values ('3', 'bbb')" 
 
# check the original values in the table 
select * from db2inst1.test1 
 
ID NAME 
-- -------- 
1  bbb 
2  bbb 
3  bbb 
 
  3 record(s) selected. 
 
# Perform following SQL query in DB2 Control Center: 
select name from db2inst1.test1 where id='2' 
# After Control Center returns the result, edit the value from 
'bbb' to 'bbbtest1' and click on COMMIT. 
 
# Check the result of positioned UPDATE 
db2 "select * from db2inst1.test1" 
 
ID NAME 
-- -------- 
1  bbbtest1  -->  this row was wrongly updated 
2  bbb       -->  the expected row was not updated 
3  bbb 
 
  3 record(s) selected. 
================================ 
 
According to current design, DB2 Control Center will not be able 
to know which row(s) to be updated/deleted if the columns in 
predicates are not included in the SELECT clause. 
For above example, column "id" was in the predicate but missed 
from the SELECT clause. As a result, DB2 Control Center could 
not update the row with "id=2". Instead, it updated the 1st row 
with "name='bbb'", i.e. the row with "id=1". 
 
After applying the fix, each time you perform 
positioned/searched UPDATE and DELETE in DB2 Control Center and 
try to COMMIT the result, a warning message will pop out to 
remind you of the possible data corruption and ask for your 
input for committing the changes or not.
Problem-Zusammenfassung:
**************************************************************** 
* USERS AFFECTED:                                              * 
* All users of version 9.5 on Linux, Unix and Windows          * 
* platforms.                                                   * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* "positioned UPDATE and DELETE" was introduced in V9.5 DB2    * 
* Control Center by the fix of APAR IZ31390 as a complement of * 
* searched UPDATE and DELETE.                                  * 
* However in some scenarios positioned/searched UPDATE and     * 
* DELETE will UPDATE/DELETE rows that you do not want to       * 
* UPDATE/DELETE, therefore corrupt the data.                   * 
*                                                              * 
* Here is an example of positioned UPDATE:                     * 
* ================================                             * 
* db2 "create db us_db using codeset 1252 territory US"        * 
*                                                              * 
* db2set db2codepage=1252                                      * 
*                                                              * 
* db2 terminate                                                * 
* db2 "connect to us_db"                                       * 
*                                                              * 
* db2 "create table test1 (id char(1), name char(8))           * 
* db2 "insert into test1 values ('1', 'bbb')"                  * 
* db2 "insert into test1 values ('2', 'bbb')"                  * 
* db2 "insert into test1 values ('3', 'bbb')"                  * 
*                                                              * 
* # check the original values in the table                     * 
* select * from db2inst1.test1                                 * 
*                                                              * 
* ID NAME                                                      * 
* -- --------                                                  * 
* 1  bbb                                                       * 
* 2  bbb                                                       * 
* 3  bbb                                                       * 
*                                                              * 
* 3 record(s) selected.                                        * 
*                                                              * 
* # Perform following SQL query in DB2 Control Center:         * 
* select name from db2inst1.test1 where id='2'                 * 
* # After Control Center returns the result, edit the value    * 
* from 'bbb' to 'bbbtest1' and click on COMMIT.                * 
*                                                              * 
* # Check the result of positioned UPDATE                      * 
* db2 "select * from db2inst1.test1"                           * 
*                                                              * 
* ID NAME                                                      * 
* -- --------                                                  * 
* 1  bbbtest1  -->  this row was wrongly updated               * 
* 2  bbb       -->  the expected row was not updated           * 
* 3  bbb                                                       * 
*                                                              * 
* 3 record(s) selected.                                        * 
* ================================                             * 
*                                                              * 
* According to current design, DB2 Control Center will not be  * 
* able to know which row(s) to be updated/deleted if the       * 
* columns in predicates are not included in the SELECT clause. * 
* For above example, column "id" was in the predicate but      * 
* missed from the SELECT clause. As a result, DB2 Control      * 
* Center could not update the row with "id=2". Instead, it     * 
* updated the 1st row with "name='bbb'", i.e. the row with     * 
* "id=1".                                                      * 
*                                                              * 
* After applying the fix, each time you perform                * 
* positioned/searched UPDATE and DELETE in DB2 Control Center  * 
* and try to COMMIT the result, a warning message will pop out * 
* to remind you of the possible data corruption and ask for    * 
* your input for committing the changes or not.                * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 UDB Version 9.5 FixPak 8 or higher levels.    * 
****************************************************************
Local-Fix:
Including all the necessary columns in the SELECT clause. For 
the example in 'Problem Description', perform following 
statement: 
    select id, name from db2inst1.test1 where id='2' 
to replace the original one: 
    select name from db2inst1.test1 where id='2'
verfügbare FixPacks:
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

Lösung
First fixed in DB2 UDB Version 9.5 FixPak 8.
Workaround
Including all the necessary columns in the SELECT clause. For 
the example in 'Problem Description', perform following 
 
statement: 
    select id, name from db2inst1.test1 where id='2' 
 
to replace the original one: 
    select name from db2inst1.test1 where id='2'
Bug-Verfolgung
Vorgänger  : APAR is sysrouted TO one or more of the following: IC75035 
Nachfolger : 
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
15.03.2011
30.06.2011
30.06.2011
Problem behoben ab folgender Versionen (IBM BugInfos)
9.5.
Problem behoben lt. FixList in der Version
9.5.0.8 FixList