DB2 Version 9.7 for Linux, UNIX, and Windows

Example: Investigating agent usage by service class

DB2® workload manager provides the WLM_GET_SERVICE_CLASS_AGENTS_V97 table function, which you can use to determine the relative distribution of agents among service classes.

Situations can arise in which a data server resource, such as an agent, is overutilized by a group of users or an application. For example, assume that a group of users is using almost all of the available agents and that a user from outside this group voices a concern about that to you.

The first step to take is to determine how many agents are working for each service class. You might use a query such as the following one:

SELECT SUBSTR(AGENTS.SERVICE_SUPERCLASS_NAME,1,19) AS SUPERCLASS_NAME,
       SUBSTR(AGENTS.SERVICE_SUBCLASS_NAME,1,19) AS SUBCLASS_NAME,
       COUNT(*) AS AGENT_COUNT
FROM TABLE(WLM_GET_SERVICE_CLASS_AGENTS_V97('', '', CAST(NULL AS BIGINT), -2))
AS AGENTS
WHERE AGENT_STATE = 'ACTIVE'
GROUP BY SERVICE_SUPERCLASS_NAME, SERVICE_SUBCLASS_NAME
ORDER BY SERVICE_SUPERCLASS_NAME, SERVICE_SUBCLASS_NAME

SUPERCLASS_NAME     SUBCLASS_NAME       AGENT_COUNT
------------------- ------------------- -----------
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS            7
TEST                SYSDEFAULTSUBCLASS           20

If you conclude that a particular service class is using more than its fair share of agents, you can take actions to restrict the number of activities permitted for a workload or a service class. Alternatively, you can restrict the number of connections for a service class.