Question & Answer
Question
How to find out which application is consuming more system temporary space than the configured limit?
Cause
Applications exhausting temporary tablespaces.
Answer
You can create a threshold to collect information about any application that consumes more than X bytes of system temp space.
Steps to create this threshold :
1. Create a threshold violations event monitor to capture threshold violations (it records 1 event each time the threshold rule is
exceeded by an application) :
CREATE EVENT MONITOR T FOR THRESHOLD VIOLATIONS WRITE TO TABLE SET EVENT MONITOR T STATE 2
2. Create an activity event monitor to capture additional data for each threshold violation including the text of the statement that violates
the threshold :
CREATE EVENT MONITOR A FOR ACTIVITIES WRITE TO TABLE SET EVENT MONITOR A STATE 1
3. Define a threshold to capture information about statements that use more that X bytes (using 100 MB in example below) of system temp space :
CREATE THRESHOLD TH1 FOR DATABASE ACTIVITIES ENFORCEMENT DATABASE WHEN SQLTEMPSPACE > 100 M COLLECT ACTIVITY DATA ON COORDINATOR WITH DETAILS
CONTINUE
Now you can query the THRESHOLDVIOLATIONS_T table to check if any statement has exceeded this threshold i.e. used more than 100 M of system temp space. If any record shows up you can check the ACTIVITYSTMT_A table for statement text for the statement that exceeded the threshold.
The advantage of the threshold is that it will provide the exact statement that exceeded the temp space limit.
Considerations :
* Requires WLM license
* Only captures information about statements that use system temp space; does not capture information about utilities (e.g. REORG) which use system temp space.
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg22002370