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 - 1979 days ago
Last viewed on: 24 Jun 2018 (4127 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