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



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.