DB2 Version 10.1 for Linux, UNIX, and Windows

Example: Determining which activities are queued by a WLM threshold and their queue order

You can use a DB2® workload manager (WLM) table function, WLM_GET_SERVICE_CLASS_AGENTS, to view which applications or activities are queued by a WLM threshold and the order of the applications or activities in the queue.

Any agent queued by a WLM threshold will have the value WLM_QUEUE in the EVENT_OBJECT column in the corresponding row returned for the agent. In addition, the AGENT_STATE_LAST_UPDATE_TIME column will contain the time that the agent entered the WLM_QUEUE state, which is the time that the agent was queued. Using this information, a simple view can be constructed to list all applications or activities that are queued by a WLM threshold, and to obtain their queue entry time.

To create a view that uses the WLM_GET_SERVICE_CLASS_AGENTS table function to obtain the WLM queue information for the examples provided here, run the following statement:

CREATE VIEW WLM_QUEUE_INFO (APPLICATION_HANDLE, UOW_ID, ACTIVITY_ID, 
    THRESHOLD_NAME, QUEUE_ENTRY_TIME, MEMBER) 
    AS SELECT APPLICATION_HANDLE, UOW_ID, ACTIVITY_ID, 
    VARCHAR(EVENT_OBJECT_NAME, 128), AGENT_STATE_LAST_UPDATE_TIME, 
    MEMBER FROM TABLE(WLM_GET_SERVICE_CLASS_AGENTS(NULL,NULL,NULL,-2)) 
    AS T WHERE EVENT_OBJECT = 'WLM_QUEUE'

Note: For TOTALSCPARTITIONCONNECTIONS thresholds, uow_id and activity_id are not applicable (NA) and, therefore, show up as NULL.

This view can be used to easily answer questions such as the following:

How many applications or activities are currently queued by a WLM threshold?
What is the order of the applications or activities in the WLM threshold queue?

Example 1

To count the number of applications queued by each queuing threshold, run the following statement:

SELECT VARCHAR(THRESHOLD_NAME, 30) AS THRESHOLD, COUNT(*) 
     AS QUEUED_ENTRIES FROM WLM_QUEUE_INFO GROUP BY THRESHOLD_NAME

The following is a sample of the output obtained after running the preceding statement:

THRESHOLD                      QUEUED_ENTRIES
------------------------------ --------------
TH1                                         3

  1 record(s) selected.

Example 2

To list all the activities that are queued in the TH1 CONCURRENTDBCOORDACTIVITIES threshold and show the order of the activities in the queue determined by their queue entry time, run the following statement:

SELECT QUEUE_ENTRY_TIME, APPLICATION_HANDLE, UOW_ID, ACTIVITY_ID FROM 
     WLM_QUEUE_INFO WHERE THRESHOLD_NAME = 'TH1' ORDER BY QUEUE_ENTRY_TIME ASC

The following is a sample of the output obtained after running the preceding statement:

QUEUE_ENTRY_TIME           APPLICATION_HANDLE   UOW_ID      ACTIVITY_ID
-------------------------- -------------------- ----------- -----------
2009-11-09-18.08.32.583286                  145           1           2
2009-11-09-18.08.42.589623                  146           1           1
2009-11-09-18.08.54.607083                  147           1           1

  3 record(s) selected.