I have a procedure that generates reports basis user requests. The request is such that at a time only one user can call the procedure. Is there any way I can handle this using DB2? Like place a lock on a procedure similar to how we lock tables?
If you are appropriately licensed (Advanced Workgroup or Advanced Enterprise edition -- or Performance Management Option, HT to @IanBjorhovde) you can use a DB2 Workload Manager threshold to accomplish something like that. First you create a service class for your procedure:
CREATE SERVICE CLASS sc_report
You then define a workload that will assign the appropriate activities to the service class:
CREATE WORKLOAD sp_workload CURRENT CLIENT_APPLNAME('SP_REPORT') ENABLE SERVICE CLASS sc_report
Here I'm identifying activities by the value of the client application name, which can be set by calling the system stored procedure
WLM_SET_CLIENT_INFO() in the session, e.g.
CALL WLM_SET_CLIENT_INFO(null,null,'SP_REPORT',null,null); COMMIT;
You can use other parameters, such as the user ID, client IP address etc. to identify the workload -- check the manual for
CREATE WORKLOAD. After that you define the threshold:
CREATE THRESHOLD report_thresh FOR SERVICE CLASS sc_report ACTIVITIES ENFORCEMENT DATABASE ENABLE WHEN CONCURRENTDBCOORDACTIVITIES > 1 STOP EXECUTION
which says that, if an activity falls into the previously defined service class
sc_report and there is already one such activity in the database, this new incoming activity should be stopped. The application will then receive
SQLCODE -4712. Alternatively, you can define the threshold to queue concurrent requests instead of stopping them:
CREATE THRESHOLD report_thresh FOR SERVICE CLASS sc_report ACTIVITIES ENFORCEMENT DATABASE ENABLE WHEN CONCURRENTDBCOORDACTIVITIES > 1 AND QUEUEDACTIVITIES > 10 STOP EXECUTION
The above means that up to 10 concurrent activities matching the workload definition will be queued and executed one after another.
You will need to make sure that the workload definition identifies your report stored procedure and nothing else, because everything that falls into the defined service class will be subject to the threshold.
I'm curious why you only want one call at a time? If that is the case, perhaps this would be better off not called by users and scheduled to run.
Because I don't think you can truly prevent a stored procedure from being called more than once. But you could set things up to make it more difficult.
Off the top of my head, I think you could handle it with roles and another stored procedure. Set your procedure up to only be executable via a role. Then create another stored procedure that can call that one. Inside this new procedure first check to see if any IDs are assigned to the role. If not, then assign the role. Then check again (since we don't have synchronization to prevent only one ID from getting the role). Then execute the procedure you want from that procedure. After it returns, revoke the role from the ID that executed it.
Again, this is in no way fool proof. There is pretty much no way to 100% guarantee that a stored proc can't be executed by two IDs (or even the same ID) simultaneously. And it is a lot of hokey hoops to jump through. Perhaps you are best off pushing this stored proc up into an application where locking/synchronization could be used to better guarantee only one "user" calling it?