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

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Looks like IBM iAccess 6.1 has some enhancements:
ftp://ftp.software.ibm.com/systems/support/system_i/techoverviews/v6r1/V6R1_iAccessFamily.pdf

(p.24) Ability to specify the IsolationLevel for autocommit

Well, wonder if I can get my hands on iAccess v6r1...

Charles

On Tue, Jul 21, 2009 at 3:58 PM, Charles Wilt<charles.wilt@xxxxxxxxx> wrote:
> 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.