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

Found this statement which confirms what I'm seeing in the Redbook
"Integrating DB2 Universal
Database for iSeries with Microsoft ADO .NET" (SG24-6440)

By default, the IBM.Data.DB2.iSeries provider runs with an isolation
level of No commit or
*NONE. This means that all changes made to the database are permanent;
no explicit
commit or rollback can be performed.

Autocommit
When you are not in transaction mode (that is, before you call
BeginTransaction() on the
connection object, and after you call Commit() or Rollback() on the
transaction object), the
IBM.Data.DB2.iSeries provider runs with an isolation level of No
commit or *NONE. This
means that all changes made to the database are permanent; no explicit
commit or rollback
can be performed. *NONE enables you to update non-journaled files. The
provider does not
currently allow you to specify an isolation level to be used while in
autocommit mode.


Charles



On Tue, Jul 21, 2009 at 3:34 PM, Charles Wilt<charles.wilt@xxxxxxxxx> wrote:

Eric & Lucas,

The files are journalled, that's not the issue.

The issue is that the .NET developer has to code an explicit
BeginTransaction(), even with the connections DefaultIsolationLevel
set to ReadCommited.

As I recall, the OLEDB provider had an autocommit property.  When
true, you didn't need explict Bend/End transactions.  It appears that
.NET doesn't do this.

Charles

On Tue, Jul 21, 2009 at 3:21 PM, DeLong, Eric<EDeLong@xxxxxxxxxxxxxxx> wrote:

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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.