Re: RPG SQL and Cursors -- RPG400-L

so, I take it that there is really no way for SQL update to handle the same
thing that I've described in RLA. As for design problem with interactive
job that has long lasting lock, well, I agreed that it is not the best
design. But record lock is a funny thing, you never know when it will
happen. But I do know that when it is time for you to about to sit down to
enjoy your thanksgiving dinner, baam, you get a call from the operator about
a record lock problem.... Besides the application that I'm dealing with is
more than 15 years old.... Time for rewrite, sure, now who is going to cough
out the money :).

But, here is the basic problem with using SQL vs RPG RLA or Java vs. RPG
(Time to go to war): When RLA/RPG encountered an unexpected error, the
program goes into MSGW and programmer can study the caused of problem and
handle the error appropriately. When SQL/Java encountered an unexpected
error, well, it either kills the entire process or keep trucking on
(Beautiful stuff, why wait for people, just more on).
So, a lot of time, I see a lot of code that programmer added to SQL/Java
program to just to handle this type of unexpected exceptions that probably
will never occur. Great stuff, all kind of sophisticated exception handling
code that will just make you get lost on what exactly the business function
that the program is trying to achieve.

I do love SQL and Java and I use them quite a bit. But RPG/RLA is just much
more... hm what is the word....

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

I don't use bulk updates for transactional data, one transaction, normaly
is a unit of work from one user and if it doesn't succeed, rollback and
message to user. In a batch process, same thing: rollback , working further
on and try it later (automatically). To get speed to Batch processes,
parallelisation is the main thing!!!
If an interactive Job helds long lasting record locks, you have a problem
with your design; before each EXFMT or read of a 5250 just a rollback and
there will be no locks.

Dieter

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

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.

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