× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I'm not overly well versed in this, but I was under the impression that
you can't do commitment control within a stored procedure. That keeps
all commitment control within the domain of the application that calls
the procedure. The ATOMIC keyword specifies an all or nothing for the
stored procedure statements within the commitment boundary of the
caller. If you think about it, this makes sense. The caller may be
updating many tables with the stored procedure being only one of the
steps. If the stored procedure works correctly, you don't want it
committing changes it has made and then have the caller rollback from a
later error.

I may be totally wrong, of course. Discuss.


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Ryan Hunt
Sent: Monday, April 19, 2010 9:31 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Transaction Managment DB2/400

OK, I found some info on BEGIN ATOMIC and added that statement while
also removing my explicit COMMIT and ROLLBACK statements. This achieved
my goal of rolling back the transaction upon failure - as opposed to
beginning the transaction but then requiring my connection to end to
rollback the records (more of an implicit rollback functionality).

I'd still love to see some samples of how to manage multiple
specifically scoped transactions within a SQL procedure with multiple
DML statements (I underline SQL because the IBM docs seem to always
offer C, RPG, COBOL, PL SQL, REXX (?), etc. - which are all foreign to
me). I'm reading more now on SQLCA, SQLCODE, SQLSTATE, SAVEPOINTS, etc.
to try and achieve this but so far I'm dying of a thousand syntax paper
cuts.

Thanks.RH



"Luis Rodriguez" <luisro58@xxxxxxxxx> wrote in message
news:i2k902b36c41004190838r57a7260aj5882f9ceb0dccd56@xxxxxxxxxxxxxxxxx
Ryan,

As a (I suppose) newcomer to DB2 for the IBM i, I would recommend you
the
DB2 Database programming Manual, which you can find here:
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/dbp/rbafo.pd
f

Also, there is a nice book about commiment control here:
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzakj/rzakj.
pdf

As in almost all the things related the IBM i, Google and the Infocenter
are your friends. The Infocenter link for V5R4 is:
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp


HTH,

Luis Rodriguez
IBM Certified Systems Expert - eServer i5 iSeries


On Mon, Apr 19, 2010 at 8:12 AM, Ryan Hunt <ryan.hunt@xxxxxxxxxxxxx>
wrote:

When I say transaction management, I mean:

Start a unit of work-->perform work-->check for errors (if none
found)-->commit work (else, rollback to start).

I'm hoping the list can provide me with two things.

1) a good reference for reading the details of transaction management
on DB2/400. I started doing some research and found lots on isolation

levels
-
but that seems more about isolation of reads (whether reads can be
dirty,
etc.) rather than enforcing change control. (please note that I am
primarily an MS SQL DBA/Developer so I may have gleaned something
incorrect
from my reading).

2) a quick primer to get me started, for instance, the real code to
perform
this:

BEGIN TRANSACTION

UPDATE table1
set foo = x, bar = y
WHERE col1 = Z;

IF NO ERROR RETURNED THEN
COMMIT;
ELSE
ROLLBACK;
END IF;

Thanks much. RH



--
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.






As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.