Using Workload Manager (WLM) to End Long-Running Queries

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!