Stored procedure issue when called by more than one processPublished 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.

COMMENTS

Comment on: Stored procedure issue when called by more than one processPosted: 6 years 3 months 17 days 4 hours 58 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 processPosted: 6 years 3 months 17 days 2 hours 50 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 processPosted: 6 years 3 months 17 days 48 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 processPosted: 6 years 3 months 16 days 23 hours 48 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 processPosted: 6 years 3 months 16 days 21 hours 59 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.

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 processPosted: 6 years 3 months 16 days 19 hours 18 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:

Comment on: Stored procedure issue when called by more than one processPosted: 6 years 3 months 16 days 18 hours 46 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!

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 processPosted: 6 years 3 months 9 days 1 hours 56 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 processPosted: 6 years 3 months 8 days 23 hours 14 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 processPosted: 6 years 3 months 8 days 22 hours 48 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 processPosted: 6 years 3 months 8 days 22 hours 39 minutes agoEdited: Wed, 12 Dec, 2012 at 13:15:45 (2290 days ago)

Comment on: Stored procedure issue when called by more than one processPosted: 6 years 3 months 4 days 1 hours 3 minutes agoEdited: Mon, 17 Dec, 2012 at 11:58:26 (2285 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.

Comment on: Stored procedure issue when called by more than one processPosted: 6 years 3 months 3 days 2 hours 22 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.