Exercise 7: Cancelling an ongoing activity

Step 1: Issue a long running query

From a CLP window, run the following script that issues a long running query

db2 -tvf longquery.db2

Step 2: Get the application handle

From another CLP window, call the WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES to get the application handle, unit of work ID and activity ID of the cursor activity.

SELECT T.APPLICATION_HANDLE, T.UOW_ID, T.ACTIVITY_ID, T.ACTIVITY_TYPE
FROM  TABLE(WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES
              (CAST(NULL AS BIGINT), -2)) T
      WHERE T.CLIENT_APPLNAME = 'CLP longquery.db2';

By joining the result of the table function with the APPLICATIONS administrative view, we can find the cursor activity that is run from within longquery.db2. The output would look something such as the following:

APPLICATION_HANDLE   UOW_ID      ACTIVITY_ID ACTIVITY_TYPE
-------------------- ----------- ----------- ----------------------------
----
                 267           1           1 READ_DML                       

  1 record(s) selected.

Step 3: Cancel the activity

From the same CLP window, call the WLM_CANCEL_ACTIVITY stored procedure to cancel the cursor activity obtained previously, using the application handle, unit of work ID, and activity ID obtained from the previous step:

CONNECT TO SAMPLE

CALL WLM_CANCEL_ACTIVITY (267, 1, 1)

CONNECT RESET

Note that in your case, the application handle, unit of work ID, and activity ID will be different.

In the first CLP window, you will see the following output returned by the long running query issued by longquery.db2.

SQL4725N  The activity has been cancelled. SQLSTATE=57014