Re: RPG SQL and Cursors

["hockchai Lim" <lim.hock-chai@xxxxxxxxxxxxxxx>]

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.