The WLM_GET_SERVICE_CLASS_AGENTS_V97 table function returns the list of agents, fenced mode processes (db2fmp processes), and system entities on a specified member that are running in a specified service class or on behalf of a specified application. The system entities are non-agent threads and processes, such as page cleaners and prefetchers.
>>-WLM_GET_SERVICE_CLASS_AGENTS_V97--(--service_superclass_name--,--> >--service_subclass_name--,--application_handle--,--member--)--><
The schema is SYSPROC.
None
SELECT SUBSTR(CHAR(APPLICATION_HANDLE),1,7) AS APPHANDLE,
SUBSTR(CHAR(DBPARTITIONNUM),1,4) AS PART,
SUBSTR(CHAR(AGENT_TID),1,9) AS AGENT_TID,
SUBSTR(AGENT_TYPE,1,11) AS AGENTTYPE,
SUBSTR(AGENT_STATE,1,10) AS AGENTSTATE,
SUBSTR(REQUEST_TYPE,1,12) AS REQTYPE,
SUBSTR(CHAR(UOW_ID),1,6) AS UOW_ID,
SUBSTR(CHAR(ACTIVITY_ID),1,6) AS ACT_ID
FROM TABLE(WLM_GET_SERVICE_CLASS_AGENTS_V97(CAST(NULL AS VARCHAR(128)),
CAST(NULL AS VARCHAR(128)), 1, -2)) AS SCDETAILS
ORDER BY APPHANDLE, PART, AGENT_TID
APPHANDLE PART AGENT_TID AGENTTYPE AGENTSTATE REQTYPE UOW_ID ACT_ID
--------- ---- --------- ----------- ---------- -------------- ------ ------
1 0 3 COORDINATOR ACTIVE FETCH 1 5
1 0 4 SUBAGENT ACTIVE SUBSECTION:1 1 5
1 1 2 SUBAGENT ACTIVE SUBSECTION:2 1 5
The output shows a coordinator agent and a subagent on
member 0 and a subagent on member 1 operating on behalf of an activity
with UOW ID 1 and activity ID 5. The AGENTTYPE column
with a value of COORDINATOR has a value of FETCH for
the REQTYPE column (which indicates the main or initial
request type). This means that the type of request is a fetch request
for the coordinator agent. select event_object, event_type, event_state, varchar
(event_object_name, 30)
as event_object_name
from table
(wlm_get_service_class_agents_v97('','',cast(NULL as bigint), -1)) as t
EVENT_OBJECT EVENT_TYPE EVENT_STATE
--------------- ----------------- -------------------
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST WAIT IDLE
LOCK ACQUIRE IDLE
ROUTINE PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
EVENT_OBJECT_NAME
--------------------------
-
-
-
-
-
-
-
-
-
02000500000000000000000054
-
-
-
-
-
-
-
-
-
-
-
21 record(s) selected.
Using the same query at a later
time shows that the WLM threshold has queued an agent:EVENT_OBJECT EVENT_TYPE EVENT_STATE
--------------- ----------------- -------------------
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
WLM_QUEUE WAIT IDLE
ROUTINE PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
REQUEST PROCESS EXECUTING
EVENT_OBJECT_NAME
--------------------------
-
-
-
-
-
-
-
-
-
MYCONCDBCOORDTH
-
-
-
-
-
-
-
-
-
-
-
21 record(s) selected.
The parameters are, in effect, ANDed together. That is, if you specify conflicting input parameters, such as a service superclass SUP_A and a subclass SUB_B such that SUB_B is not a subclass of SUP_A, no rows are returned.
Column name | Data type | Description |
---|---|---|
SERVICE_SUPERCLASS_NAME | VARCHAR (128) | service_superclass_name - Service superclass name monitor element |
SERVICE_SUBCLASS_NAME | VARCHAR (128) | service_subclass_name - Service subclass name monitor element |
APPLICATION_HANDLE | BIGINT | application_handle - Application handle monitor element |
DBPARTITIONNUM | SMALLINT | dbpartitionnum - Database partition number monitor element |
ENTITY | VARCHAR (32) | One of the following values:
|
WORKLOAD_NAME | VARCHAR (128) | workload_name - Workload name monitor element |
WORKLOAD_OCCURRENCE_ID | INTEGER | workload_occurrence_id - Workload occurrence identifier monitor element |
UOW_ID | INTEGER | uow_id - Unit of work ID monitor element |
ACTIVITY_ID | INTEGER | activity_id - Activity ID monitor element |
PARENT_UOW_ID | INTEGER | parent_uow_id - Parent unit of work ID monitor element |
PARENT_ACTIVITY_ID | INTEGER | parent_activity_id - Parent activity ID monitor element |
AGENT_TID | BIGINT | agent_tid - Agent thread ID monitor element |
AGENT_TYPE | VARCHAR (32) | Agent type. The agent types are as follows:
|
SMP_COORDINATOR | INTEGER | Indication of whether the agent is an SMP coordinator: 1 for yes and 0 for no. |
AGENT_SUBTYPE | VARCHAR (32) | Agent subtype. The possible subtypes are as
follows:
|
AGENT_STATE | VARCHAR (32) | Indication of whether an agent is associated
or active. The possible values are:
|
EVENT_TYPE | VARCHAR (32) | Type of event last processed by this agent.
The possible values are as follows:
See Table 2 for more information about possible values for this column. |
EVENT_OBJECT | VARCHAR (32) | Object of the event last processed by this agent.
The possible values are as follows:
See Table 2 for more information about possible values for this column. |
EVENT_STATE | VARCHAR (32) | State of the event last processed by this agent.
The possible values are as follows:
See Table 2 for more information about possible values for this column. |
REQUEST_ID | VARCHAR (64) | Request ID. This value is unique only in combination with the value of application_handle. You can use this combination to distinguish between one request that is taking a long time and multiple requests; for example, to distinguish between one long fetch and multiple fetches. |
REQUEST_TYPE | VARCHAR (32) | Type of request. The possible values are as
follows:
|
REQUEST_TYPE (continued) | VARCHAR (32) |
|
NESTING_LEVEL | INTEGER | nesting_level - Nesting level monitor element |
INVOCATION_ID | INTEGER | invocation_id - Invocation ID monitor element |
ROUTINE_ID | INTEGER | routine_id - Routine ID monitor element |
EVENT_OBJECT_NAME | VARCHAR (1024) | Event object name. If the value of EVENT_OBJECT is LOCK, the value of this column is the name of the lock that the agent is waiting on. If the value of EVENT_OBJECT is WLM_QUEUE, the value of the column is the name of the WLM threshold that the agent is queued on. Otherwise, the value is NULL. |
APPLICATION_NAME | VARCHAR (128) | |
APPLICATION_ID | VARCHAR (128) | |
CLIENT_PID | BIGINT | |
SESSION_AUTH_ID | VARCHAR (128) | |
REQUEST_START_TIME | TIMESTAMP | Time that the agent started processing the request on which it is currently working |
AGENT_STATE_LAST_UPDATE_TIME | TIMESTAMP | The last time that the event, being processed by the agent, was changed. The event currently processed by the agent is identified by the EVENT_TYPE, EVENT_OBJECT, and EVENT_STATE columns. |
EXECUTABLE_ID | VARCHAR (32) FOR BIT DATA | executable_id - Executable ID monitor element |
Event description | EVENT_STATE value | EVENT_TYPE value | EVENT_OBJECT value | EVENT_OBJECT_NAME value |
---|---|---|---|---|
Acquire lock | IDLE | ACQUIRE | LOCK | Lock name |
Escalate lock | EXECUTING | PROCESS | LOCK_ESCALATION | NULL |
Process request | EXECUTING | PROCESS | REQUEST | NULL |
Wait for a new request | IDLE | WAIT | REQUEST | NULL |
Wait for a request to be processed at a remote partition | IDLE | WAIT | REMOTE_REQUEST | NULL |
Wait on a WLM threshold queue | IDLE | WAIT | WLM_QUEUE | Threshold name |
Process a routine | EXECUTING | PROCESS | ROUTINE | NULL |
Re-create an index | EXECUTING | PROCESS | INDEX_RECREATE | NULL |
Build compression dictionary | EXECUTING | PROCESS | COMP_DICT_BLD | NULL |
Implicit rebind | EXECUTING | PROCESS | IMPLICIT_REBIND | NULL |