Monitoring current queries in a customized adaptive WLM environment

Use the MON_GET_ACTIVITY table function to better understand the current state of service class activities, such as how many queries are currently running or queued per service class.

A query in the EXECUTING state is currently consuming resources and is being processed by the database engine.
A query in the IDLE state is currently consuming resources, but is blocked on the client and waiting for the next client request.
A query in the QUEUED state is waiting for resources.

The following monitoring query returns the total number of queries and the number of queries that bypassed the adaptive workload manager and so are not eligible to be queued. Results are summarized by service class.

SELECT SUBSTR(PARENTSERVICECLASSNAME, 1, 30) AS SERVICE_CLASS, 
       ACTIVITY_STATE, 
       SUM(ADM_BYPASSED) AS BYPASSED, 
       COUNT(*)
FROM TABLE(MON_GET_ACTIVITY(NULL,-1)) AS T,
     SYSCAT.SERVICECLASSES AS Q
WHERE T.SERVICE_CLASS_ID = Q.SERVICECLASSID
GROUP BY PARENTSERVICECLASSNAME,
         ACTIVITY_STATE

The following output shows that there is 1 query executing in the default user service class, 10 queries executing in the SC1 service class, and 2 queries queued in the SC1 service class.

SERVICE_CLASS                  ACTIVITY_STATE                   BYPASSED             COUNT                            
------------------------------ -------------------------------- -------------------- ---------------------------------
SYSDEFAULTUSERCLASS            EXECUTING                                           1                                 1
SC1                            EXECUTING                                           1                                10
SC1                            QUEUED                                              1                                 2