1

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?

3

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.

  • Does that require the WLM license? My understanding is the work load manager stuff is a separate license from IBM. – Chris Aldrich Jan 11 '16 at 19:38
  • WLM is included into both Advanced editions (Workgroup and Enterprise), there's also limited support in (non-Advanced) Enterprise. I don't think you can license it separately. – mustaccio Jan 11 '16 at 19:45
  • WLM can be licensed for Express Edition, Workgroup Server Edition and Enterprise Server Edition via the IBM DB2 Performance Management Offering. – Ian Bjorhovde Jan 11 '16 at 23:57
  • I don't want it to stop. I need it to wait till the previous execution is over. – rrrohanb Jan 12 '16 at 2:03
0

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?

  • It's because the ever user gets a report of how many times the report was called earlier. Hence i need it to be sequential and not parallel. I was thinking of keeping a flag which gets set at the start of the procedure and make the next one wait till the flag is not reset. However it doesn't seem like it is full proof. – rrrohanb Jan 11 '16 at 16:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.