WLM_GET_SERVICE_CLASS_AGENTS_V97 table function - List agents running in a service class
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.
Syntax
The schema is SYSPROC.
Table function parameters
- service_superclass_name
- An input argument of type VARCHAR(128) that specifies the name of a service superclass in the currently connected database. If the argument is null or an empty string, data is retrieved for all the superclasses in the database.
- service_subclass_name
- An input argument of type VARCHAR(128) that refers to a specific subclass within a superclass. If the argument is null or an empty string, data is retrieved for all the subclasses in the database.
- application_handle
- An input argument of type BIGINT that specifies the application handle for which agent information is to be returned. If the argument is null, data is retrieved for all applications in the database. An application handle of 0 returns the system entities only.
- member
- An input argument of type INTEGER that specifies the member number in the same instance as the currently connected database. Specify -1 for the current database member, or -2 for all database members. If a null value is specified, -1 is set implicitly.
Authorization
- EXECUTE privilege on the routine
- DATAACCESS authority
- DBADM authority
- SQLADM authority
- WLMADM authority
Default PUBLIC privilege
None
Examples
- Example 1
- The following query returns a list of agents that are associated with application handle 1 for all database members. You can determine the application handle by using the LIST APPLICATIONS command or the WLM_GET_SERVICE_CLASS_WORKLOAD_OCCURRENCES_V97 table function.
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
Sample output is as follows:
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. TheAPPHANDLE 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
AGENTTYPE
column with a value ofCOORDINATOR
has a value ofFETCH
for theREQTYPE
column (which indicates the main or initial request type). This means that the type of request is a fetch request for the coordinator agent. - Example 2
- The following query determines which lock an agent is waiting on:
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
Sample output is as follows:
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 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.
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.
Usage note
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.
Information 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 |