Scenario: Disconnecting all applications either mapped to or executing activities in a service class

To create the FORCEALLINSC procedure, execute the following steps:

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

   CREATE PROCEDURE FORCEALLINSC ( IN INSCID BIGINT )
           SPECIFIC FORCEALLINSC
           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
           FROM TABLE(SYSPROC.WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES( NULL, -2 )) 
           AS T WHERE T.SERVICE_CLASS_ID = INSCID);
   
   
      DECLARE C2 CURSOR FOR  (SELECT APPLICATION_HANDLE
           FROM TABLE(SYSPROC.WLM_GET_SERVICE_CLASS_WORKLOAD_OCCURRENCES(NULL,NULL, -2 )) 
           AS T, SYSCAT.SERVICECLASSES AS S
           WHERE T.SERVICE_SUPERCLASS_NAME = S.PARENTSERVICECLASSNAME AND
           T.SERVICE_SUBCLASS_NAME = S.SERVICECLASSNAME AND
           S.SERVICECLASSID = INSCID);
                                  
      DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
        BEGIN 
        END;
   
   -- First force any applications that have an activity running in
   -- the specified service class
   
      OPEN C1; 
   
      FETCH_LOOP: 
        LOOP
   
      FETCH C1 INTO APPHNDL;  
   
      IF (SQLSTATE <> '00000') 
      THEN LEAVE FETCH_LOOP;
      END IF;
   
      CALL ADMIN_CMD( 'FORCE APPLICATION (' || CHAR(APPHNDL) || ')' ); 
   
      END LOOP FETCH_LOOP;
   
   -- Now force any connections that are mapped to the service class, but which
   -- don't currently have any activities running
   
      OPEN C2; 
   
      FETCH_LOOP2: 
        LOOP
   
      FETCH C2 INTO APPHNDL;  
   
      IF (SQLSTATE <> '00000') 
      THEN LEAVE FETCH_LOOP2;
      END IF;
   
      CALL ADMIN_CMD( 'FORCE APPLICATION (' || CHAR(APPHNDL) || ')' ); 
   
      END LOOP FETCH_LOOP2;
    
   END@

2. Run the following CLP command:

   db2 -td@ -f forceall.ddl

After the procedure has been created, execute the procedure (for example, disconnecting all applications that are either mapped to or currently executing activities in a particular service class with ID = 15) using the following statement:

CALL FORCEALLINSC( 15 )