Db2 – How to ensure that only one user at a time can call a stored procedure? Can I lock an SP

db2locking

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?

Best Answer

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.

Related Question