RE: DB2 for i, Isolation Level = *NONE, and ACID assumptions vs. other DBs

Charles,

DB2 for i wants to ensure transaction isolation by default, but most of
the legacy databases never implemented database journaling, let alone
commitment control. In DB2, when you CREATE SCHEMA, the system will
create journals and enable commitment control.

If you can journal the files, then your .net developer should be able to
proceed with transaction isolation working as he expects.

IMO, this is one reason for the "legacy" stigma attached to our
platform. From the .net developer's perspective, it appeared that the
platform was limited in basic functionality, or unable to comply with
SQL standards... Either way, he will feel less confident about his work
with DB2i than for Microsoft SQL Server or Oracle.

I still hear tremendous opposition to journaling, and can only assume
that these folk had problems with it 25 years ago, and are unwilling to
look at it again. Sad but true...

-Eric DeLong

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Tuesday, July 21, 2009 1:53 PM
To: Midrange Systems Technical Discussion
Subject: DB2 for i, Isolation Level = *NONE, and ACID assumptions vs.
other DBs

All,

I'm working with a .NET developer and a DBA who are familiar with
non-i RDBMS', namely Oracle and MS SQL Server.

I'm trying to get them to understand the the i allows for a commitment
control / isolation level of *NONE. Which means, for instance if you
run the statement:
update mytable
set someColumn = 'A'
where someOtherColumn = 'B'

and say for instance 100 records have someOtherColumn = 'B' but that
one of those other records is currently locked by another job, the i
will happily update the first X number of records it can, stopping
when it hits the locked record, leaving the locked record and any
remaining records unchanged.

Both the developer and the DBA are confused by this, their first
comments..
"Doesn't DB2 guarantee ACID compliance?"
"In every other db, a single statement like the above is done
Atomically."

I guess I'm not smart enough to explain it right, as all I get is
shock and appalled looks when I try to explain that we need our .NET
code to do an explicit BeginTransaction() and a Commit() or
Rollback().

Can anybody on the list provide information/links I can use to help
counter the idea that DB2 for i is "weird".

Thanks!
Charles