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



Thanks Charles. Yes, heard of RBAR...avoid it in my MS SQL code at all
costs. You see it here for two reasons:

1) The loop is usually 10 records and no more than 30 (with no reasonable
expectation it would be more in the future) so we're talking miliseconds in
this case. I usually would use numbers tables, set/relational approach,
etc. but for now I'm keeping it simple until I understand how to lock
resources properly (not only the target table but also the darned next
number table this ERP uses (no autoid's). Once I have it functional I'll go
back and look at the appropriate DB2/400 functions for doing in-set next
numbers, etc.

In the MS SQL world we recently got a new sys function that will allow for
doing an autonumber directly in a select statement...prior to that I would
have eaten up any milleseconds saved on 10 loops by setting up number
tables, CTE's etc. to join to and hit everything at once.

I'll definitely check out your link though.

Thanks! RH


"Charles Wilt" <charles.wilt@xxxxxxxxx> wrote
in message
news:l2hfa303d751004200742j2718d714r7cb58f8edee280a5@xxxxxxxxxxxxxxxxx
You can indeed to COMMITs and or ROLLBACKs in a stored procedure...

Your application design will dictate if you should or not. :)

Ryan, another good resource is the "Stored Procedures, Triggers, and
User Defined Functions on DB2 UDB for iSeries" REDBOOK. Chapter 8 is
"Stored procedure Error handling."
http://www.redbooks.ibm.com/abstracts/sg246503.html?Open

Finally a word of advice...

Have you heard the term RBAR? "Row By Agonizing Row", it's about the
worst thing you can do on any DB... and you're doing it here.
http://www.simple-talk.com/sql/t-sql-programming/rbar--row-by-agonizing-row/

I haven't looked at your code in detail, but it seems that you should
be able to get rid of the cursor.

HTH,
Charles Wilt



On Tue, Apr 20, 2010 at 5:14 AM, Dan Kimmel
<dkimmel@xxxxxxxxxxxxxxx> wrote:
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.





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

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.