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" <ian.msnews...@ avatopia.comwro te:

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.indivi dual.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='CENSORE D', CTY@CM='CENSORE D',
ST@@CM='ON', ZIP@CM='CeNsOr' , HDLCCM=390029, TIMCCM=1616, UINCCM='censore d'

[joe.weinstein]

On Nov 10, 11:02 am, "Ian Boyd" <ian.msnews...@ avatopia.comwro te:

"Serge Rielau" <[e-mail] .comwrote 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='CENSORE D', CTY@CM='CENSORE D',
ST@@CM='ON', ZIP@CM='CeNsOr' , HDLCCM=390029, TIMCCM=1616, UINCCM='censore d'

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.