Are you holding the lock on the updated row (the flag being set to 'Y') long enough? And are the other processes also trying to read that row for update (which would be prevented by the lock)?

Is this native RPG I/O or embedded SQL? Sounds like SQL. Yeah, what Dale said, lock the record for a nanosecond, and the other jobs will queue up. Add "For Update of Field1, Field2, etc" to the Declare.

Then later do (if you need to actually do an update). 

"Update MYFILE Set Field1=x, Field2=y...where current of myCursor".

And finally and this is weird to me, that UPDATE will NOT unlock the row after it does the update. You have to close the cursor or fetch/lock another row or fetch to EOF.

Chris Ringer

Thanks, Dale and Chris. This is actually a stored procedure. I am fairly new to sprocs and have followed what has been done in the past, just because of lack of knowledge in this area. What you guys are proposing is a huge improvement over what we're now doing.

Do either of you know of anywhere I might find examples of how this is used? There will be a significant change to the sprocs to incorporate this. If you could answer a few of my questions, maybe I can figure it out.

• First of all, one of the sprocs has 12 data sets returned based on SELECTs against 12 different files. Selection of 11 of the files would be based on the 100 rows selected from the order header file. Would the additional 11 SELECTs include the "where current of myCursor" (header cursor)?
• Chris, as for your comment on not unlocking the row after an UPDATE, I don't think that would be an issue unless I'm missing something, because once the sproc is finished running it should unlock it, right?
• Lastly, since the sproc is selecting 100 rows of order header records where the processed flag = 'N', would I just need to include the "For Update of Processed" to the Declare?

Anyway, thanks a lot guys for your input. You have no idea how excited I am to find a cleaner solution to this problem.

Chris

You're talking about SPL (Stored Procedure Language), an internal stored proc. I don't have a lot of experience with those. I code RPGLE and define *that* as an external stored proc and have RPG do the I/O and return parms and result sets. I find this way easier to code and debug and support. Just my preference.

For your internal stored proc, call it and when it returns, do a DSPRDCLCK MYFILE to see if it held on to any record locks.

Chris Ringer

I guess it's SPL.... The source type is SQL. I wouldn't even know how to create it from a command line, I'm doing it thru Turnover.

We are using them a lot because we have outside vendors retrieving data by calling our sprocs. I guess all I can do is try it and see if it works. Not sure how we could test it tho. Oh well, always learning something new.

Thanks for the input, Chris.

Is coding it in RPG an option? Or did that horse leave the barn already? You still use the CREATE PROCEDURE command in SQL but point it to the RPG. Then debug it with iSeries Nav and STRSRVJOB/STRDBG. Example:

CREATE PROCEDURE YOURLIB/CD670SP                                        
(INOUT ioPgmSts      CHAR(10),    /* OUT: <> PASS is Call failed! */  
 IN    iRefNum       CHAR(10),    /* AKA Tracking Number */           
 IN    iCompNum   DECIMAL(4,0),   
 IN    iPrdSeries    CHAR(30),    /* Is just plain text */            
 IN    iPrdFam    DECIMAL(3,0),   /* Product Family number */         
 IN    iPrdClass     CHAR(2000),  /* Up to 2a x 1000. Blanks = ALL. */
 INOUT ioMessage     CHAR(80))    /* OUT: Some error text if failed */
SPECIFIC CD670SP                                                     
RESULT SETS 1                                                        
NO SQL                    /* <- Security: is max allowed in CALL stack! */         
LANGUAGE RPGLE                                                    
NOT DETERMINISTIC         /* ALWAYS Call, never Cache */             
EXTERNAL NAME CD670SP     /* Will Find RPGLE in *LIBL */             
PARAMETER STYLE GENERAL ;                                            
                                                                     
COMMENT ON SPECIFIC PROCEDURE YOURLIB/CD670SP                           
IS 'Stored Proc to CALL External RPGLE *LIBL/CD670SP' ; 

 Chris Ringer

Unfortunately, at this point I don't think it's an option. This application already exist in production and this logic to write the selected 100 rows to another file with the next available batch number, then use that file to drive the selection of all the remaining results sets is used all over the place!

I have the opportunity to try and come up with a better solution, but I don't think the sproc can be in RPG.

Hi Chris

I'm curious...in your RPG example of the stored procedure, are the input parms passed to the CD670SP program as *entry parms? Also, can a sproc be written to return a result set, say 100 header records, or is that more difficult this way? I'm looking at all options now, because it appears that the result set will be made up of fields from a number of different files and I'm thinking that maybe it'd be easier in an RPG program rather than a SQL statement.

Also, how does the processing time compare to an SQL sproc?

Thanks for the input.

Chris

Yes the stored proc parms are forwarded to the RPG as *ENTRY PLIST (or a prototype). Yes, RPG can return zero or more result sets to the caller, no problem, either as a SQL OPEN cursor and/or RPG arrays. It runs as fast as normal RPG, because it's still RPG.

Chris Ringer

Awesome. I'll look into it. Thanks, Chris!

Examples:

http://www.ibmsystemsmag.com/ibmi/developer/rpg/rpg_stored_procedures2/

http://ibmsystemsmag.com/ibmi/developer/rpg/rpg_stored_procedures/

The RPG can used adopted authority too, all the usual stuff.

SQL connects through one of the QZDASOINIT prestart jobs usually and does a user profile swap so the job runs as the connected user.

Chris Ringer

I've been playing with it, Chris, and it looks pretty cool. I could definitely see myself using the RPG sprocs. Thanks for all the info!

Chris

You can return *both* SQL cursors and arrays from one RPG program too.

Exec SQL Set Result Sets                   
         Cursor C1,                        
         Array :TheParts For :NumRows Rows ;

 Chris Ringer

Hi Chris. I have a question for you. We have a sproc that is running terribly slow and I'm wondering if it might be possible to speed it up with an RPG sproc. I believe that the problem might be is that it's trying to find the most recent order status date with a subselect within another subselect.

What I was wondering is, is it possible within an RPG sproc to just select all the fields for the order and a blank order status field, then retrieve the order header for the latest update date and update that field before returning the result set? Thanks for all the help, this is awesome! Here's what the cursor currently looks like.

Declare spF78110SS_C Cursorwithreturnfor

Select trim(ORDER_NO) ORDER_NO, trim(CUST_NO) CUST_NO, trim(SITE_ID) SITE_ID, trim(BT_ZIP) BT_ZIP, trim(LANGUAGE) CUST_LANG, trim(ORDER_DAT8) ORDER_DAT8,(select trim(h.order_stat) order_stat from f78100s h where h.order_no = d.order_no and h.site_id = d.site_id and h.updated in (select max(b.updated) from f78100s b where b.order_no = d.order_no and b.site_id = d.site_id)) order_stat, trim(PROD_ID) PROD_ID, trim(LINE_STAT) LINE_STAT, case D_SHIPPED when 'Y' then char(SHIP_DATE) else '' end SHIP_DATE,

case D_SHIPPED when'Y'thentrim(CARRIER_NM) else''end CARRIER_NAME,

case D_SHIPPED when'Y'thentrim(TRACKING) else''end TRACKING

case D_SHIPPED when'Y'thentrim(CARRIER) else''end CARRIER,

trim(CUST_NAME) CUST_NAME From F78110S d Where ((days(now()) - days(UPDATED)) <= p_DAYS or p_DAYS = 0) orderby SITE_ID, CUST_NO, BT_ZIP, ORDER_NO;

Open spF78110SS_C;

Update a result set? I've never seen that done, not sure if that's possible. BUT you could fetch through that cursor in RPG, load an array with all the values and pass the array back as the result set.

Chris Ringer