|
Problem IC75035
|
Status: Closed |
POSITIONED/SEARCHED UPDATE AND DELETE IN DB2 CONTROL CENTER MIGHT CORRUPT DATA. |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| 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. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All users of version 9.7 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.7 FixPak 5 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' |
| available fix packs: |
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
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
|
| Solution |
First fixed in DB2 LUW Version 9.7 FixPak 5. |
| 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' |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 15.03.2011
17.01.2012
17.01.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
