We, as DBAs, have all encountered a situation where a bad query just kept running and sucked up the database resources while holding onto logs. It’s always a “simple select” that seems to run over 12 hours. Db2 used to have the governor. This nifty little tool was used to perform this task, but it fell out of favor and eventually was deprecated. But lo and behold, IBM implemented Workload Manager (WLM) for Db2 to pick up where the governor left off.
Introducing Thresholds With Workload Manager
There are a lot of different criteria, such as the number of queries running simultaneously or the time it takes a query to run (see the CREATE THRESHOLD statement). When these thresholds are exceeded, the system needs to respond. For example, new queries can be queued if a certain number of queries are running simultaneously. The objectives are to maintain stability and prevent overutilization of system resources. Another way to reduce the impact of long-running is to identify these queries and force them to stop.
For example, you can decide that queries that take more than two hours to run should be stopped. You can create a threshold, as shown in the following example:
CREATE THRESHOLD "LONG_RUNNING_THRESHOLD" FOR WORKLOAD JCC_WORKLOAD ACTIVITIES ENFORCEMENT DATABASE WHEN ACTIVITYTOTALTIME > 2 HOURS COLLECT ACTIVITY DATA ON COORDINATOR DATABASE PARTITION WITH DETAILS, SECTION AND VALUES STOP EXECUTION; ALTER THRESHOLD "LONG_RUNNING_THRESHOLD" ENABLE; COMMIT;
When a query from the workload JCC_WORKLOAD exceeds the two-hour threshold, the offending application will receive an error code such as the one in the following example:
SQL Exception(s) Encountered: [State: 5U026][Code: -4712]: The activity or request was stopped because the threshold "LONG_RUNNING_THRESHOLD" has been exceeded. Reason code: "9".. SQLCODE=-4712, SQLSTATE=5U026, DRIVER=3.72.24
You can view details about the queries that were stopped by querying the THRESHOLDVIOLATIONS_DB2THRESHOLDS event monitor table. To create this table, run the following DDL:
CREATE EVENT MONITOR DB2THRESHOLDS FOR THRESHOLD VIOLATIONS WRITE TO TABLE THRESHOLDVIOLATIONS ( TABLE THRESHOLDVIOLATIONS_DB2THRESHOLDS IN BIGSQLCATSPACE ), CONTROL ( TABLE CONTROL_DB2THRESHOLDVIOLATIONS IN BIGSQLCATSPACE );
To ensure that information is recorded in the event monitor table, run the SQL statements:
ALTER SERVICE CLASS SYSDEFAULTSUBCLASS UNDER SYSDEFAULTUSERCLASS COLLECT ACTIVITY DATA ON COORDINATOR MEMBER WITH DETAILS AND VALUES; ALTER SERVICE CLASS SYSDEFAULTMANAGEDSUBCLASS UNDER SYSDEFAULTUSERCLASS COLLECT ACTIVITY DATA ON COORDINATOR MEMBER WITH DETAILS AND VALUES;
To retrieve the application name and some basic threshold and violation information, you can query the event monitor table, as shown in the following example:
SELECT APPLICATION_NAME, CONNECTION_START_TIME, THRESHOLD_ACTION, THRESHOLD_MAXVALUE, THRESHOLD_PREDICATE, TIME_OF_VIOLATION FROM THRESHOLDVIOLATIONS_DB2THRESHOLDS;
For more information or any questions you may have, please contact us!