Midrange News for the IBM i Community


Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
Stored procedure issue when called by more than one process
has no ratings.
Published: 03 Dec 2012
Revised: 23 Jan 2013 - 1585 days ago
Last viewed on: 27 May 2017 (3402 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Stored procedure issue when called by more than one process Published by: Chris Proctor on 03 Dec 2012 view comments(15)

Good morning. I'm hoping that someone has dealt with this error and can shed some light on how to keep it from happening. I have a stored procedure that is called to extract 100 rows from an order header file, as well as it's associated detail.  The stored procedure does select the order header based on the processed flag being equal to 'N' and it also updates the processed flag to 'Y' once it's selected.

The problem is that there are multiple processes running simultaneously that call the same sproc and occassionally the same order is grabbed by more than one stored procedure. I was thinking about adding a batch# to the order header record and updating it when the record is selected. I would also include "where batch# = 0" to the selection of the header record, preventing the second stored procedure from selecting the same order.

Is this a viable solution, or does anyone have another way of doing it that might be more effecient? Any help would be greatly appreciated.

 

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: DaleB
Premium member *
Reading, PA
Comment on: Stored procedure issue when called by more than one process
Posted: 4 years 5 months 23 days 13 hours 1 minutes ago

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)?

Posted by: Ringer
Premium member *
Comment on: Stored procedure issue when called by more than one process
Posted: 4 years 5 months 23 days 10 hours 53 minutes ago

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

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Stored procedure issue when called by more than one process
Posted: 4 years 5 months 23 days 8 hours 51 minutes ago

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

Posted by: Ringer
Premium member *
Comment on: Stored procedure issue when called by more than one process
Posted: 4 years 5 months 23 days 7 hours 51 minutes ago

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

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Stored procedure issue when called by more than one process
Posted: 4 years 5 months 23 days 6 hours 3 minutes ago

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

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.

Posted by: Ringer
Premium member *
Comment on: Stored procedure issue when called by more than one process
Posted: 4 years 5 months 23 days 3 hours 21 minutes ago

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

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Stored procedure issue when called by more than one process
Posted: 4 years 5 months 23 days 2 hours 49 minutes ago

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! Yell

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

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Stored procedure issue when called by more than one process
Posted: 4 years 5 months 15 days 9 hours 59 minutes ago

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

Posted by: Ringer
Premium member *
Comment on: Stored procedure issue when called by more than one process
Posted: 4 years 5 months 15 days 7 hours 17 minutes ago

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

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Stored procedure issue when called by more than one process
Posted: 4 years 5 months 15 days 6 hours 51 minutes ago

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

Posted by: Ringer
Premium member *
Comment on: Stored procedure issue when called by more than one process
Posted: 4 years 5 months 15 days 6 hours 42 minutes ago
Edited: Wed, 12 Dec, 2012 at 13:15:45 (1627 days ago)

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

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Stored procedure issue when called by more than one process
Posted: 4 years 5 months 14 days 6 hours 5 minutes ago

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

Posted by: Ringer
Premium member *
Comment on: Stored procedure issue when called by more than one process
Posted: 4 years 5 months 13 days 11 hours 57 minutes ago

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

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Stored procedure issue when called by more than one process
Posted: 4 years 5 months 10 days 9 hours 6 minutes ago
Edited: Mon, 17 Dec, 2012 at 11:58:26 (1622 days ago)

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;

Posted by: Ringer
Premium member *
Comment on: Stored procedure issue when called by more than one process
Posted: 4 years 5 months 9 days 10 hours 25 minutes ago

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