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
Modified date:
17 June 2018
UID
swg21683049