I want to be able to set things up so that my end-users can monitor and cancel their own queries without having to come to me. Is there a way I can do this?
This question often pops up from customers who want to encourage self-service/self-management of SQL by their user community and there is a fairly simple approach you can follow to make it possible.
My starting assumption is that you want to control what information that the users can see when they monitor SQL because you don't know what sensitive information might be revealed in the information being shown (e.g. literals in SQL statement text) so it is best to restrict which SQL statements that people can see to those they actually submitted.
The easiest way to implement this restriction is to define a view on the SYSIBMADM.MON_CURRENT_SQL with a predicate ensuring that the session authorization ID which submitted the SQL statement matches the one of the current session. This will let people only see SQL executed by the same authorization ID as the connection doing the monitoring.
You do this by taking advantage of the SESSION_USER special register like this:
CREATE VIEW JUST_MY_SQL AS SELECT * FROM SYSIBMADM.MON_CURRENT_SQL WHERE SESSION_AUTH_ID = SESSION_USER;
if you want to show additional or different information, you can do the same thing on top of a view of your own devising. Here is a more complicated one based on some of the DB2 monitoring table functions:
CREATE VIEW JUST_MY_SQL2 (APPLICATION_HANDLE, UOW_ID, ACTIVITY_ID, STMT_TEXT)
WITH FIND_MY_SQL AS (SELECT A.APPLICATION_HANDLE, A.UOW_ID, A.ACTIVITY_ID, A.EXECUTABLE_ID,
A.DBPARTITIONNUM AS MEMBER
FROM TABLE(MON_GET_ACTIVITY(NULL,-2)) AS A,
TABLE(WLM_GET_SERVICE_CLASS_WORKLOAD_OCCURRENCES(NULL,NULL,-2)) AS B
WHERE A.APPLICATION_HANDLE = B.APPLICATION_HANDLE
AND A.DBPARTITIONNUM = B.DBPARTITIONNUM
AND B.DBPARTITIONNUM = B.COORD_PARTITION_NUM
AND B.SESSION_AUTH_ID = SESSION_USER)
SELECT APPLICATION_HANDLE, UOW_ID, ACTIVITY_ID,
(SELECT STMT_TEXT FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL, FIND_MY_SQL.EXECUTABLE_ID,
Finally, don't forget that you need to make this view accessible to everyone so you need to make sure that the schema used for the new view is one that is in everyone's default PATH setting or that all SQL references to the view are fully-qualified. And, of course, you have to grant SELECT privilege on this view to your users (or to PUBLIC).
Once this is done, your users will be able to look at their, and only their, currently active SQL through this view.
If you are unable to discern between different end-users based on the session authorization ID (e.g, they share the same authorization ID for their connection, perhaps because they access the server through an application server), then you can implement a similar approach by pushing down more client information into the DB2 server using the client information fields and creating appropriate predicates in your view on those fields. (Of course, you should also be figuring out how to avoid sharing authorization IDs since this a very bad security practice! :)
If you want to also extend the ability to cancel their own queries to your users, then you need to ensure that they can only cancel their own statements and not just any statement that is executing. The simplest way to do this is to define a wrapper function around the SYSPROC.WLM_CANCEL_ACTIVITY function to ensure that the target SQL statement is a valid one.
There are many ways to do this so I will leave this as an exercise to the reader after offering an example to guide the way:
CREATE PROCEDURE TEST.CANCEL_MY_ACTIVITY (IN APPHANDLE BIGINT,
IN UOWID BIGINT,
IN ACTIVITYID BIGINT)
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE ERRMSG VARCHAR(1024);
DECLARE ACTCOUNT BIGINT DEFAULT 0;
DECLARE C1 CURSOR FOR (SELECT COUNT(*)
FROM TABLE(SYSPROC.WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES_V97( NULL, -2 )) AS A,
TABLE(SYSPROC.WLM_GET_SERVICE_CLASS_WORKLOAD_OCCURRENCES(NULL,NULL,-2)) AS B
WHERE A.APPLICATION_HANDLE = APPHANDLE
AND A.UOW_ID = UOWID
AND A.ACTIVITY_ID = ACTIVITYID
AND A.APPLICATION_HANDLE = B.APPLICATION_HANDLE
AND A.DBPARTITIONNUM = B.DBPARTITIONNUM
AND B.SESSION_AUTH_ID = SESSION_USER);
FETCH C1 INTO ACTCOUNT;
IF (ACTCOUNT > 0)
CALL WLM_CANCEL_ACTIVITY( APPHANDLE, UOWID, ACTIVITYID );
SET ERRMSG = 'Activity not found for current session user: ' || SESSION_USER;
SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = ERRMSG;
Again, as with the monitoring view, you have to give EXECUTE privilege on the new procedure to your users (or to PUBLIC) and also ensure that the schema used is in the default PATH setting or make sure that calls to the wrapper routine are fully-qualified.
Hope this helps,