About cookies on this site Our websites require some cookies to function properly (required). In addition, other cookies may be used with your consent to analyze site usage, improve the user experience and for advertising. For more information, please review your options. By visiting our website, you agree to our processing of information as described in IBM’sprivacy statement. To provide a smooth navigation, your cookie preferences will be shared across the IBM web domains listed here.
Question & Answer
Question
How can I cancel an SQL which may be long running or using a lot of temp DB space?
Cause
SQL fills temp DB space and the server will halt.
Answer
1. Turn on table monitor:
db2 connect to TSMDB1
db2 update monitor switches using table on
db2 "select APPLICATION_HANDLE, substr(STMT_TEXT,1,60) STMT_TEXT from TABLE
(MON_GET_PKG_CACHE_STMT('d', null, null, -1)) AS tf , (select APPLICATION_HANDLE,EXECUTABLE_ID from
TABLE(WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES_V97(null, -2))) ss where
tf.EXECUTABLE_ID=ss.EXECUTABLE_ID"
This will produce an output similar to the following:

The application handle is 15597 in this example.
3. Next, issue the command below:
db2 "force application (application_handle_identified_from_step_2)"
Example:
db2 "force application (15597)"
[{"Product":{"code":"SSGSG7","label":"Tivoli Storage Manager"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Server","Platform":[{"code":"PF016","label":"Linux"},{"code":"PF025","label":"Platform Independent"},{"code":"PF033","label":"Windows"}],"Version":"All Supported Versions","Edition":"","Line of Business":{"code":"LOB26","label":"Storage"}}]
Product Synonym
TSM ITSM ADSM IBM Spectrum Protect
Was this topic helpful?
Document Information
More support for:
Tivoli Storage Manager
Software version:
All Supported Versions
Operating system(s):
Linux, Windows
Document number:
249161
Modified date:
17 June 2018
UID
swg21683049