Scenario: How to cancel activities queued for more than an hour

Using the example scripts described here, you can create a procedure to cancel activities that have been queued for more than an hour. In addition, an example script is provided that can be used to schedule the queued-activity-cancelling procedure to run every 10 minutes using the Db2® Administrative Task Scheduler.

The queued-activity-cancelling procedure also captures information about the cancelled activities (if an activity event monitor is active), and maintains a small history table of cancelled activities. Both of these informational components are optional and comments in the example script indicate where to comment out the components, if they are not required.

The statements contained in the example procedure are themselves activities and subject to threshold control (depending on how thresholds are configured on your system). Consider running the example queued-activity-cancelling procedure in a service class that does not have any queuing thresholds applied.

Copy the following example script, that creates the procedure to cancel activities queued for more than 1 hour, into a file you have created (for example, a file named x.clp):

-- Simple history table to track cancelled
-- activities

CREATE TABLE SAMPLE.CANCELED_ACTIVITIES(
   APPLICATION_HANDLE BIGINT,
   UOW_ID BIGINT,
   ACTIVITY_ID BIGINT )@

-- Cancel any activities that have been queued
-- for more than 1 hour

CREATE PROCEDURE SAMPLE.CANCEL_QUEUED_ACTIVITIES()
  LANGUAGE SQL
  BEGIN
     DECLARE APPHANDLE   BIGINT; 
     DECLARE UOWID       BIGINT; 
     DECLARE ACTIVITYID  BIGINT;
     DECLARE QUEUETIME   BIGINT;
     DECLARE AT_END      INT DEFAULT 0; 

     DECLARE QUEUEDAPPS CURSOR WITH HOLD FOR SELECT APPLICATION_HANDLE, UOW_ID, ACTIVITY_ID
        FROM TABLE(WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES(NULL,-2)) AS T
        WHERE ACTIVITY_STATE = 'QUEUED' AND LOCAL_START_TIME IS NULL; 

     DECLARE QTIMECUR CURSOR FOR SELECT TIMESTAMPDIFF(8, CHAR(CURRENT TIMESTAMP - TIMESTAMP(VALUE)))
        FROM TABLE(WLM_GET_ACTIVITY_DETAILS(APPHANDLE ,
        UOWID ,  ACTIVITYID , -2)) AS T WHERE NAME = 'ENTRY_TIME';
  
     DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET AT_END = 1;

     -- Ignore errors for activity not found and activity event 
     -- monitor does not exist. 
     DECLARE CONTINUE HANDLER FOR SQLSTATE '5U035', SQLSTATE '01H53'
        BEGIN
        END; 

     -- Find all activities that are queued by WLM 
     -- thresholds where (ACTIVITY_STATE = 'QUEUED')
     OPEN QUEUEDAPPS; 
     FETCH QUEUEDAPPS INTO APPHANDLE, UOWID, ACTIVITYID; 
   
     WHILE AT_END = 0 DO

        -- Now use activity entry time to estimate the time spend queued. 
        -- Queuing occurs before an activity begins execution, so queue
        -- time is approximated using current time - entry time
        OPEN QTIMECUR; 
        FETCH QTIMECUR INTO QUEUETIME;
        CLOSE QTIMECUR;   
   
        IF ( QUEUETIME >= 1 ) THEN

           -- Optional: Insert a record into a table to record the
           -- cancellation of the statement (for monitoring purposes, to
           -- understand how many statements were cancelled). Modify this
           -- insert as required to capture more info such as the name of
           -- the application that submitted the cancelled query. Comment out
           -- these 2 lines if the monitoring is not important to you. 
           INSERT INTO SAMPLE.CANCELED_ACTIVITIES VALUES ( APPHANDLE,
                       UOWID, ACTIVITYID );

           -- Optional: Send details about activity to any activity activities
           -- event monitor before cancelling. Comment out
           -- this line if you don't care about the details of the
           -- statements that were cancelled
           CALL WLM_CAPTURE_ACTIVITY_IN_PROGRESS( APPHANDLE, UOWID, ACTIVITYID ); 

           -- Cancel the activity
           CALL WLM_CANCEL_ACTIVITY( APPHANDLE, UOWID, ACTIVITYID ); 

           -- Explicit commit, required for the insert statement above. The
           -- admin task scheduler will not perform a commit. Comment out this
           -- line if the insert statement is removed. 
           COMMIT; 

        END IF; 

        FETCH QUEUEDAPPS INTO APPHANDLE, UOWID, ACTIVITYID; 

     END WHILE; 

     CLOSE QUEUEDAPPS; 

  END@

Create the queued-activity-cancelling procedure by executing script x.clp using the following command:
```
db2 -td@ -f x.clp
```
Execute the queued-activity-cancelling procedure by issuing the following command:
```
db2 "call sample.cancel_queued_activities()"
```
Any activities that have been queued for more than 1 hour will be cancelled.

The following example script schedules the queued-activity-cancelling procedure to run every 10 minutes using the Db2 Administrative Task Scheduler. Copy the example script into a file you have created (for example, a file named y.clp):

---------------------------------------
-- Enable Db2 Admin Task Scheduler if
-- not already enabled.
---------------------------------------

!db2set DB2_ATS_ENABLE=YES@

---------------------------------------
-- Create SYSTOOLSPACE tablespace.
-- Enable if SYSTOOLSPACE does not already
-- exist on your database.
---------------------------------------

-- CREATE TABLESPACE SYSTOOLSPACE IN IBMCATGROUP MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 4@

---------------------------------------
-- Add a task to automatically cancel
-- activities that have been queued
-- for more than 1 hour. Task is scheduled
-- to run every 10 minutes. Adjust the
-- schedule as necessary using the
-- schedule input parameter (specified in
-- cron format).
---------------------------------------

CALL SYSPROC.ADMIN_TASK_ADD(
   'CANCEL ACTIVITIES QUEUED FOR MORE 1 HOUR',
   NULL,
   NULL,
   NULL,
   '*/10 * * * *',
   'SAMPLE',
   'CANCEL_QUEUED_ACTIVITIES',
   NULL,
   NULL,
   NULL )@

Schedule the queued-activity-cancelling procedure to run every 10 minutes by executing script y.clp using the following command:
```
db2 -td@ -f y.clp
```