The following is an example of a stored procedure that can be used to cancel all currently executing activities in a particular service subclass.
CREATE PROCEDURE CANCELALL ( IN INSCID BIGINT )
SPECIFIC CANCELALL
LANGUAGE SQL
BEGIN
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE APPHNDL BIGINT;
DECLARE UOWID INTEGER;
DECLARE ACTIVITYID INTEGER;
DECLARE C1 CURSOR FOR (SELECT APPLICATION_HANDLE,
UOW_ID, ACTIVITY_ID
FROM TABLE(SYSPROC.WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES( NULL, -2 ))
AS T WHERE T.SERVICE_CLASS_ID = INSCID);
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
END;
OPEN C1;
FETCH_LOOP:
LOOP
FETCH C1 INTO APPHNDL, UOWID, ACTIVITYID;
IF (SQLSTATE <> '00000')
THEN LEAVE FETCH_LOOP;
END IF;
CALL WLM_CANCEL_ACTIVITY( APPHNDL, UOWID, ACTIVITYID );
END LOOP FETCH_LOOP;
END@
db2 -td@ -f cancelall.ddl
CALL CANCELALL( 15 )