Scenario: Cancelling all activities being executed in a service subclass

To create the CANCELALL procedure, execute the following steps:

1. Copy the following CREATE PROCEDURE statement to a file (for example, cancelall.ddl):

   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@

2. Run the following CLP command:

   db2 -td@ -f cancelall.ddl

After the procedure has been created, execute the procedure (for example, cancelling all activities in the service subclass which has ID = 15) using the following statement:

CALL CANCELALL( 15 )