Listing query activity

Use the MON_GET_ACTIVITY table function to retrieve information about the current state of queries.

  • 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 query returns the following information:
  • The total number of queries that are in each state
  • The number of queries in each state that bypassed the adaptive workload manager and therefore are not eligible to be queued
SELECT ACTIVITY_STATE, 
       SUM(ADM_BYPASSED) AS BYPASSED, 
       COUNT(*) AS COUNT
FROM TABLE(MON_GET_ACTIVITY(NULL,-1)) AS T
GROUP BY ACTIVITY_STATE
The following sample output indicates that 2 queries are idle, 1 query is queued, and 1 query is executing, and that the executing query bypassed the adaptive workload manager.
ACTIVITY_STATE                   BYPASSED             COUNT                            
-------------------------------- -------------------- ---------------------------------
EXECUTING                                           1                                1
IDLE                                                0                                2
QUEUED                                              0                                1