Partial Transactional Integrity?

[Ian Boyd]

i accidentally ran an UPDATE statement without the WHERE clause on a
customer's live customers table. After saying "oh shit" many many times, i
closed the ad-hoc query tool, hoping DB2 would see my disconnect and
rollback the implicit transaction. (Either that or DB2 would have completed
the updated before it saw my disconnect).
So, there were two possibilities:
a) 0 row(s) affected
b) 1,398,287 row(s) affected

What actually happened is disturbing, and is my question. When queried to
see how many rows were affected, it was

c) 832,284 row(s) affected

How is it that an aborted UPDATE statement would affect only 60% of the
rows. Using a modern transactional RDMS means that the update was atomic,
and either all rows were affected, or no rows were affected.
So, there are probably three answers that could be posted in response:

a) You must be mistaken; it didn't only affect 60% of the rows.

b) Of course updates in DB2 are not atomic. That would lead to performance
problems that are not acceptable in production environments.

c) Your customer must have set some database option that disables all
transactional integrity. And DB2 has to have this option, becuase otherwise
there could be performance problems that are not acceptable in production
environments.
So pick your answer, or add your own.

Ian

[joe.weinstein]

On Nov 10, 6:26 am, "Ian Boyd" <[e-mail]:

i accidentally ran an UPDATE statement without the WHERE clause on a
customer's live customers table. After saying "oh shit" many many times, i
closed the ad-hoc query tool, hoping DB2 would see my disconnect and
rollback the implicit transaction. (Either that or DB2 would have completed
the updated before it saw my disconnect).

So, there were two possibilities:
a) 0 row(s) affected
b) 1,398,287 row(s) affected

What actually happened is disturbing, and is my question. When queried to
see how many rows were affected, it was

c) 832,284 row(s) affected

How is it that an aborted UPDATE statement would affect only 60% of the
rows. Using a modern transactional RDMS means that the update was atomic,
and either all rows were affected, or no rows were affected.

So, there are probably three answers that could be posted in response:

a) You must be mistaken; it didn't only affect 60% of the rows.

b) Of course updates in DB2 are not atomic. That would lead to performance
problems that are not acceptable in production environments.

c) Your customer must have set some database option that disables all
transactional integrity. And DB2 has to have this option, becuase otherwise
there could be performance problems that are not acceptable in production
environments.

So pick your answer, or add your own.

Ian

It seems that if the update was (as you say) a single SQL statement,
the DBMS's basic ACID guarantees would provide that it either execute
completely or not at all. I would vote for a DBMS bug.
Joe Weinstein at BEA Systems
or

[Phil Sherman]

If you are not executing SQL directly through an IBM supplied interface,
you have another layer of software that possibly could cause what you
are seeing. I'd also check there.

Since this is a production table, I'd assume that recovery logging is
active. If you know approximately when you started the update
(timestamp), you can try using a point-in-time recovery to an offline
database to recover the data that was lost. I've used this on a number
of occasions to fix exactly the same problem you've run into.

Phil Sherman
Ian Boyd wrote:

i accidentally ran an UPDATE statement without the WHERE clause on a
customer's live customers table. After saying "oh shit" many many times, i
closed the ad-hoc query tool, hoping DB2 would see my disconnect and
rollback the implicit transaction. (Either that or DB2 would have completed
the updated before it saw my disconnect).
So, there were two possibilities:
a) 0 row(s) affected
b) 1,398,287 row(s) affected

What actually happened is disturbing, and is my question. When queried to
see how many rows were affected, it was

c) 832,284 row(s) affected

How is it that an aborted UPDATE statement would affect only 60% of the
rows. Using a modern transactional RDMS means that the update was atomic,
and either all rows were affected, or no rows were affected.
So, there are probably three answers that could be posted in response:

a) You must be mistaken; it didn't only affect 60% of the rows.

b) Of course updates in DB2 are not atomic. That would lead to performance
problems that are not acceptable in production environments.

c) Your customer must have set some database option that disables all
transactional integrity. And DB2 has to have this option, becuase otherwise
there could be performance problems that are not acceptable in production
environments.
So pick your answer, or add your own.

Ian

[Serge Rielau]

Ian Boyd wrote:

i accidentally ran an UPDATE statement without the WHERE clause on a
customer's live customers table. After saying "oh shit" many many times, i
closed the ad-hoc query tool, hoping DB2 would see my disconnect and
rollback the implicit transaction. (Either that or DB2 would have completed
the updated before it saw my disconnect).

DB2 is transactional and I don't buy the notion of a bug in that space.
Most likely the UPDATE isn't a simple update.
Perhaps a cursor with a commit count to keep logging down?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

[Ian Boyd]

"Serge Rielau" <sr*****@ca.ibm.comwrote in message
news:4r************@mid.individual.net...

Ian Boyd wrote:
DB2 is transactional and I don't buy the notion of a bug in that space.
Most likely the UPDATE isn't a simple update.
Perhaps a cursor with a commit count to keep logging down?

The statement i ran was:

UPDATE CSPCM
SET SEX@CM='M', DOB@CM=10187, AD1@CM='CENSORED', CTY@CM='CENSORED',
ST@@CM='ON', ZIP@CM='CeNsOr', HDLCCM=390029, TIMCCM=1616, UINCCM='censored'

[joe.weinstein]

On Nov 10, 11:02 am, "Ian Boyd" <[e-mail]:

"Serge Rielau" <[e-mail] in messagenews:4r************@mid.individual.net...

Ian Boyd wrote:
DB2 is transactional and I don't buy the notion of a bug in that space.
Most likely the UPDATE isn't a simple update.
Perhaps a cursor with a commit count to keep logging down?The statement i ran was:

UPDATE CSPCM
SET SEX@CM='M', DOB@CM=10187, AD1@CM='CENSORED', CTY@CM='CENSORED',
ST@@CM='ON', ZIP@CM='CeNsOr', HDLCCM=390029, TIMCCM=1616, UINCCM='censored'

Describe the table and indexes and the number of rows. It should be
easy to test/duplicate the problem at IBM... There is no typical
intermediary between a client and the DBMS that will subvert
the SQL you show. Also say which version of DB2 you are running.

Joe

[Ian Boyd]

Describe the table and indexes and the number of rows.

Wide table, unknown indexes, ~1.3M rows.

It's not my table, and customer would be....hesitent....to talk about it so
soon after 'the incident.'

>Also say which version of DB2 you are running.

Don't know; but it's running on an AS/400, so it's gotta be a million years
old.

[Greg Nash]

... and what client you're using too. Did the SQL run directly on the
server, or via DB2 Connect or Enterprise, or Client Access? I believe
the Client Access driver allows SQL statements (from calling apps) to
run without commitment control, ie it won't necessarily be wrapped in
the notion of a committable transaction. I'm not sure about the others.

I am sure commitment control had to be on to support replication of
AS/400 files using dataPropagator, and I think it's needed for SQL
insert/delete/update from DB2 Connect. SELECT works regardless AFAIK.

If commitment control is off, then you might be stuck with restoring a
backup, or at least comparing backed up data with live to work out which
records to restore..

Ian Boyd wrote:

>>Describe the table and indexes and the number of rows.

Wide table, unknown indexes, ~1.3M rows.

It's not my table, and customer would be....hesitent....to talk about it so
soon after 'the incident.'

>>Also say which version of DB2 you are running.

Don't know; but it's running on an AS/400, so it's gotta be a million years
old.

[aj]

To help w/ this sort of thing, I run a daily cronned script that
unloads/maintains 5 prior ascii versions of critical tables. If
I have an "oh shit" moment, as long as I realize it within 5 days,
I can undo the damage. This has saved my butt a few times.

If you're interested, here's the script. You didn't mention
your OS - mine is Linux. Replace things between <w/ your own.

Cheers
aj
----------------------------------------------------

# This script backs up database tables in ascii format
cd /db2/backups/ascii/<DB>

echo "connect to <DB>;" /db2/backups/ascii/<DB>/backup.sql
echo "set schema <SCHEMA;" >/db2/backups/ascii/<DB>/backup.sql

for table in <LIST YOUR TABLES ON ONE LINE DELIMITED BY A SPACE>
do
if [ -s $table.5.gz ]
then
rm $table.5.gz
fi

if [ -s $table.4.gz ]
then
mv $table.4.gz $table.5.gz
fi

if [ -s $table.3.gz ]
then
mv $table.3.gz $table.4.gz
fi

if [ -s $table.2.gz ]
then
mv $table.2.gz $table.3.gz
fi

if [ -s $table.1.gz ]
then
mv $table.1.gz $table.2.gz
fi

if [ -s $table.txt.gz ]
then
mv $table.txt.gz $table.1.gz
fi

echo $table
done | /usr/bin/awk '
NF == 1 {
printf("EXPORT TO %s.txt OF DEL MODIFIED BY coldel| chardel\"\"
decpt. decplusblank datesiso MESSAGES %s.msg SELECT * FROM <DB>.%s
;\n",$1,$1,$1) ;
}' >/db2/backups/ascii/<DB>/backup.sql

echo "connect reset ;" >/db2/backups/ascii/<DB>/backup.sql
db2 -tvf /db2/backups/ascii/<DB>/backup.sql

#
************************************************** *******************************
# Gzip the newly-created .txt's
for file in `ls *.txt`
do
/bin/gzip $file
done

# Note the ending time
echo `date`

Ian Boyd wrote:

i accidentally ran an UPDATE statement without the WHERE clause on a
customer's live customers table. After saying "oh shit" many many times, i
closed the ad-hoc query tool, hoping DB2 would see my disconnect and
rollback the implicit transaction. (Either that or DB2 would have completed
the updated before it saw my disconnect).
So, there were two possibilities:
a) 0 row(s) affected
b) 1,398,287 row(s) affected

What actually happened is disturbing, and is my question. When queried to
see how many rows were affected, it was

c) 832,284 row(s) affected

How is it that an aborted UPDATE statement would affect only 60% of the
rows. Using a modern transactional RDMS means that the update was atomic,
and either all rows were affected, or no rows were affected.
So, there are probably three answers that could be posted in response:

a) You must be mistaken; it didn't only affect 60% of the rows.

b) Of course updates in DB2 are not atomic. That would lead to performance
problems that are not acceptable in production environments.

c) Your customer must have set some database option that disables all
transactional integrity. And DB2 has to have this option, becuase otherwise
there could be performance problems that are not acceptable in production
environments.
So pick your answer, or add your own.

Ian

[Ian Boyd]

.. and what client you're using too. Did the SQL run directly on the

server, or via DB2 Connect or Enterprise, or Client Access? I believe the
Client Access driver allows SQL statements (from calling apps) to run
without commitment control, ie it won't necessarily be wrapped in the
notion of a committable transaction. I'm not sure about the others.

I am sure commitment control had to be on to support replication of AS/400
files using dataPropagator, and I think it's needed for SQL
insert/delete/update from DB2 Connect. SELECT works regardless AFAIK.

It was a Windows program called WinSQL. It's speciality is connecting to
an ODBC dsn and run ad-hoc queries. This is useful for us because our
connection to the DB2 database is though a ODBC dsn that the customer
setup for us.

The tool does provide "auto-commit transactions" feature. DB2 implicitly
starts a transaction with most statements, and the tool will automatically
commit it after the statement runs. If you turn the feature off, then you
have
to call COMMIT or ROLLBACK manually.

The important thing here is that i closed the tool before the statement
finished.
And even if it did finish, the database should have commited all the
modifications,
or none of them, not 60% of them.

If commitment control is off, then you might be stuck with restoring a
backup, or at least comparing backed up data with live to work out which
records to restore..

This is a post-mortem. Of all the things that conspired to go wrong,
the very last thing (where my killing the application should have rolled
back the
implicit tranasction) is what i'm trying to explain.

[Serge Rielau]

Ian,

Can you post the SQL statement that you killed of?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

[joe.weinstein]

On Nov 14, 8:49 am, Serge Rielau <[e-mail]:

Ian,

Can you post the SQL statement that you killed of?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Hi Serge, he already did:

"The statement i ran was:

UPDATE CSPCM
SET SEX@CM='M', DOB@CM=10187, AD1@CM='CENSORED', CTY@CM='CENSORED',
ST@@CM='ON', ZIP@CM='CeNsOr', HDLCCM=390029, TIMCCM=1616,
UINCCM='censored'

"

so it seems a single unqualified update statement went
to the DBMS, and a hangup while it was being executed
is alleged to have updated a fraction of the (many) qualifying
rows, not zero nor all the rows.

Joe

[Serge Rielau]

jo***********@gmail.com wrote:

so it seems a single unqualified update statement went
to the DBMS, and a hangup while it was being executed
is alleged to have updated a fraction of the (many) qualifying
rows, not zero nor all the rows.

I don't buy it.....anyway
Ian, feel free to open a PMR.
What you observed is unheard of.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

[Knut Stolze]

jo***********@gmail.com wrote:

>

On Nov 14, 8:49 am, Serge Rielau <[e-mail]:

>Ian,

Can you post the SQL statement that you killed of?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Hi Serge, he already did:

"The statement i ran was:

UPDATE CSPCM
SET SEX@CM='M', DOB@CM=10187, AD1@CM='CENSORED', CTY@CM='CENSORED',
ST@@CM='ON', ZIP@CM='CeNsOr', HDLCCM=390029, TIMCCM=1616,
UINCCM='censored'

"

so it seems a single unqualified update statement went
to the DBMS, and a hangup while it was being executed
is alleged to have updated a fraction of the (many) qualifying
rows, not zero nor all the rows.

I concur with Serge. If it was a single UPDATE without where-clause, then
it changed all rows or none at all (after the cancel). The _only_ way how
just a few rows can be affected is that
(a) the update set a value to the same one as before (and, thus, wasn't a
real data change from the application point of view - from DB2's view, it
was a data change), or
(b) some layer in between intercepted the statement and broke it into
several statements.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Ian Boyd]

I concur with Serge. If it was a single UPDATE without where-clause, then

it changed all rows or none at all (after the cancel). The _only_ way how
just a few rows can be affected is that
(a) the update set a value to the same one as before (and, thus, wasn't a
real data change from the application point of view - from DB2's view, it
was a data change), or
(b) some layer in between intercepted the statement and broke it into
several statements.

i know DB2 doesn't have databases and tables, but rather (i think)
"libraries" and "files". And i'm pretty sure it also has views, but are
called "logicals"

So you're thinking that the 'thing' i updated might actually have been a
logical, and only some of those rows were updated? Or perhaps a 'file' with
a trigger that prevented the updating of some of the tuples?
i assume that as/400 db2 still starts implicit transactions, and that a
commit had to have been called. It's *possible* that the tool got the signal
that the update completed before it actually closed when i was mashing X in
the top right corner - and issued it's automatic commit.
The way i knew that only some of the rows were updated was doing

SELECT COUNT(*) FROM CSPCM

and compared it to

SELECT COUNT(*) FROM CSPCM
WHERE AD1@CM = 'THE VALUE I CHANGED IT TO'

and that's how i got the difference.
Maybe when i reopened the tool, reconnecting to DB2, i was given the same
connection, and DB2 was still in the process of finishing the transaction...
i dunno.

[Serge Rielau]

DB2 for iSeries!!!! Now things start to fall into place.
I thought you are still on your last project (which was DB2 for LUW as I
recall).
Anyway DB2 for iSeries does have special table properties that allow it
to behave non-transactional. If this property is set on your target
table then yes... I can see how you saw what you got.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

[Ian Boyd]

i know DB2 doesn't have databases and tables, but rather (i think)

"libraries" and "files". And i'm pretty sure it also has views, but are
called "logicals"

EDIT: DB2 on AS/400. Or at least the version of DB2 they have on their
AS/400

i know that DB2 has databases, tables and views.

[Ian Boyd]

Anyway DB2 for iSeries does have special table properties that allow it to

behave non-transactional.

.....

.....ummm......

......

wtf
....is a hotfix forthcoming to remove this ACID loophole...?
Any other variants of DB2 that don't support what i think they ought to?
Okay, trick question :)
But omg...an option for an update to not be atomic?

[Karl Hanson]

Serge Rielau wrote:

DB2 for iSeries!!!! Now things start to fall into place.
I thought you are still on your last project (which was DB2 for LUW as I
recall).
Anyway DB2 for iSeries does have special table properties that allow it
to behave non-transactional. If this property is set on your target
table then yes... I can see how you saw what you got.

Cheers
Serge

I believe it's a connection property (vs a table property). The
application might be running w/o commitment control, sometimes called
"COMMIT(*NONE)" or "No commit". See Isolation level topic:

http://publib.boulder.ibm.com/infoce...afzmstisol.htm

--
Karl Hanson

[Serge Rielau]

Karl Hanson wrote:

Serge Rielau wrote:

>DB2 for iSeries!!!! Now things start to fall into place.
I thought you are still on your last project (which was DB2 for LUW as
I recall).
Anyway DB2 for iSeries does have special table properties that allow
it to behave non-transactional. If this property is set on your target
table then yes... I can see how you saw what you got.

Cheers
Serge

I believe it's a connection property (vs a table property). The
application might be running w/o commitment control, sometimes called
"COMMIT(*NONE)" or "No commit". See Isolation level topic:

http://publib.boulder.ibm.com/infoce...afzmstisol.htm

I trust the experts, of which I am none, as far as iSeries is concerned.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

[Knut Stolze]

Ian Boyd wrote:

>Anyway DB2 for iSeries does have special table properties that allow it
to behave non-transactional.

....

....ummm......

.....

wtf
...is a hotfix forthcoming to remove this ACID loophole...?

Would an answer be: Don't use this feature??

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[joe.weinstein]

On Nov 20, 5:16 am, Knut Stolze <[e-mail]:

>
....Would an answer be: Don't use this feature??

Is this feature the default behavior? Can
you describe the benefits of this feature?
thanks,
Joe Weinstein at BEA Systems

[Knut Stolze]

jo***********@gmail.com wrote:

>

On Nov 20, 5:16 am, Knut Stolze <[e-mail]:

>>
....Would an answer be: Don't use this feature??

Is this feature the default behavior? Can
you describe the benefits of this feature?

Sorry, I don't know DB2 for iSeries well enough to comment on that.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Serge Rielau]

jo***********@gmail.com wrote:

>
On Nov 20, 5:16 am, Knut Stolze <[e-mail]:

> ....Would an answer be: Don't use this feature??

Is this feature the default behavior? Can
you describe the benefits of this feature?
thanks,
Joe Weinstein at BEA Systems

Joe, keep in mind that SQL is merely one interface to talk to OS/400.
What happens when you copy 100 files from one directory of your FS to
another and you CTRL-C half way through? Non-transactional!
DB2 iSeries == OS/400.
Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

[Karl Hanson]

Serge Rielau wrote:

jo***********@gmail.com wrote:

>>
On Nov 20, 5:16 am, Knut Stolze <[e-mail]:

>> ....Would an answer be: Don't use this feature??

Is this feature the default behavior? Can
you describe the benefits of this feature?
thanks,
Joe Weinstein at BEA Systems

Joe, keep in mind that SQL is merely one interface to talk to OS/400.
What happens when you copy 100 files from one directory of your FS to
another and you CTRL-C half way through? Non-transactional!
DB2 iSeries == OS/400.
Cheers
Serge

Yes - DB2 is an integrated part of i5/OS (formerly OS/400).
Transactional integrity is supported for all isolation levels except
"*NONE". To ensure transactional integrity via ODBC, the correct
isolation level connection property must be established, even when
running with "autocommit" enabled. This technical note provides specifics:
http://www.ibm.com/support/docview.w...2569b200550644

--
Karl Hanson