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.