Re: RPG SQL and Cursors -- RPG400-L

agree that commitment control should be used. But how do you resolve the
lock problem. If you roll out the RBAR, and some one've locked the very
last record that you want to update, how do you resolve this one tinny lock
problem then? Also, if your bulk update takes a long time (Say more that 30
secs) to complete, it could be locking up a lot of records for that 30 secs.

For a RLA, it is quite simple, the "Program Infor Feed back area" will tell
you what job locks the record. So, if the job is an interactive job, the
program can send a message to the user to get out of the record or his job
will be killed in 1 min. For a batch job, the program will normally be
coded to wait for it or error out and let programmer decide what to do. The
programmer can look at joblog to tell what job is causing the lock and
provide appropriate action. Now... how do you do that using sql update?

<dieter.bender@xxxxxxxxxxxx> wrote in message
news:mailman.15443.1290183748.2702.rpg400-l@xxxxxxxxxxxxxxx

... in OLTP database with concurrent read and write access to the
database, you would have the requirement of transaction isolation. (You
should always use commit with SQL updates anyway!!!) The bigger the
transaction gets, the slower it will be and if a SQL bulk operation dies
at the very last record, caused by a lock conflict, you would have to
issue a rollback, if you need consistent data at every time. Throwing out
RBAR is shooting a little bit short...

D*B

--------------------------------------------------
From: "hockchai Lim" <lim.hock-chai@xxxxxxxxxxxxxxx>
Sent: Friday, November 19, 2010 4:19 PM
Newsgroups: midrange.rpg400-l
To: <rpg400-l@xxxxxxxxxxxx>
Subject: Re: RPG SQL and Cursors

The only issue I have with using one SQL update statement to update
multiple rows is that there is no way to handle record lock easily. So,
for a one time deal, may be is a good idea. For a production process
that gets run thru out the day, it could become a headache to handle when
encountering recordlock.

<Clip>
"Charles Wilt" <charles.wilt@xxxxxxxxx> wrote in message
news:mailman.15339.1290142252.2702.rpg400-l@xxxxxxxxxxxxxxx
Scott,

You could declare a cursor that selects all items, fetch the data from
the cursor and the update the row through the cursor. The other way
is to simply issue use a single UPDATE statement.
</Clip>
Charles

On Thu, Nov 18, 2010 at 3:56 PM, Scott Klement
<rpg400-l@xxxxxxxxxxxxxxxx> wrote:

hi Charles,

The point being SQL is designed for sets or records, but to many
people do things row by row since that what they are used to.

If this is what you're trying to express, the phrase "if you're
using a cursor you're probably doing something wrong" is not a good way
to express it! It just confuses the issue.

Look at all the confusion it's already caused.

1) People thinking they have to use native I/O anytime a cursor would be
involved! (Whey might even insert to a temp file from SQL, then read
the temp file via F-specs... )
2) People thinking they should use CLI instead of embedded.
3) People thinking they should never use a cursor.

A better approach might be to say something like:

Don't use is a cursor if:
1) You only plan to read a single row, then close it.
2) Your program doesn't need the data (i.e. reading the file purely in
order to write/update something.)

Do use a cursor if:
1) You need to read a list of rows into your program (for example: build
a subfile, print a report, generate a spreadsheet, build a web page.)

Sometimes cursors are the right or the only way to do something...but
too often they are a poor choice.

Every tool has good and bad uses. That doesn't make cursors "bad"!

For example, I can use a (big) wrench to pound in nails if I want to.
But often times, it screws up. I shouldn't say "if you use a wrench,
you're doing it wrong" that would be misleading when taken out of
context. Even worse would be "wrenches are bad." Instead, I should say
something like "don't use a wrench to do a hammer's job."

So...
1) Don't use a cursor where SELECT INTO or VALUES INTO would do.
2) Don't use a cursor where UPDATE or INSERT (possibly with a subselect)
would do.

Grrr... who put that soap box under my feet?!
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.