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

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.