|
midrange.com
The problem I have with this, is what if the user left and forgot to log
off. Now your in an infinite loop.
Michael Schutte
Work 614-492-7419
email michael_schutte@xxxxxxxxxxxx
"Lim Hock-Chai"
<Lim.Hock-Chai@us
amobility.com> To
Sent by: "RPG programming on the AS400 /
rpg400-l-bounces@ iSeries" <rpg400-l@xxxxxxxxxxxx>
midrange.com cc
Subject
03/30/2006 05:48 RE: SQL Update in RPGLE.
PM
Please respond to
RPG programming
on the AS400 /
iSeries
<rpg400-l@midrang
e.com>
Generally speaking, I'm against using sql to do mass update in
production mode.
Reason: There is no easy way to handle record lock.
Using you program as an example:
If you change it to use sql, it would look like what Tommy has post:
C/EXEC SQL
C+ UPDATE TESTING/CUST SET CUSSTA = 'TX'
C+ WHERE CUSSTA = 'TN' with nc
C/END-EXEC
Let same you got 200,000 customers with CUSSTA='TN'. If one of the
record is locked when you run this sql, it will fail. Of course, you
can code it to check the sql status code and try to re-run it or tell
you there is a problem. However, AFAIK, there is no way to know who
lock the record and force that user to get out of the record. Even if
there is a way to do that, when you rerun the sql, the entire query
start over again. Is fine if you have a logical by CUSSTA. Otherwise,
the program will probably recreate the index.
If you use RPG, here is what normally done:
- Assume that CUSTNO field is the unique key for CUST file
- Also assume that your system already has a procedure kickUserOut that
send system message to user to get out of the record or his/her job will
be endded:
FCUST UF E K DISK RENAME(CUSTR:CSREC)
C read(n) CUST
C dow not %eof
C if CUSSTA = 'TN'
* lock the record
C CUSTNO chain(e) CUST
C dow %status = 1218
C callp kickUserOut()
C CUSTNO chain(e) CUST
C enddo
* update the record
C eval CUSSTA = 'TX'
C update CSREC
C endif
C read(n) CUST
C enddo
C eval *inlr = *on
Note: Even if your system do not have kickUserOut procedure, the RPG
will simply send a *inq message to you when there is a record lock
problem and you can then do a retry.
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Mike Troxclaire
Sent: Thursday, March 30, 2006 2:57 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: RE: SQL Update in RPGLE.
All the inputs are correct and I agree that there is no need for SQL if
only one file is involved but I already have a ready made complicated
SQL query so I wanted to go the SQL way. I agree that F specs were not
needed at all.
The update query ran just fine when I added "WITH NC" at the end of the
SQL statement.
It would be very interesting to see some input on performance when it
comes to SQL compared to RPGLE. RPGLE would definitely be much faster
but just wondering by how much. I have atleast 12 files in my UPDATE
statement.
Cheers,
Mike.
_________________________________________________________________
Don't just search. Find. Check out the new MSN Search!
http://search.msn.click-url.com/go/onm00200636ave/direct/01/
--
This is the RPG programming on the AS400 / iSeries (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 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.
