RE: SQL Update in RPGLE.

["Lim Hock-Chai" <Lim.Hock-Chai@xxxxxxxxxxxxxxx>]

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/