DB2 Version 9.7 for Linux, UNIX, and Windows

Workload management table functions and snapshot monitor integration

You can use DB2® workload manager table functions with the snapshot monitor table functions when performing problem determination or performance tuning.

The DB2 workload manager table functions and the snapshot monitor table functions share the following fields. You can perform joins on these fields to derive data that you need to perform diagnostic and performance-tuning activities. Note that, unlike the snapshot table functions, the WLM table functions do not get their information from the snapshot monitor, so that the information available in the WLM table functions is not available from the snapshot monitor.

Table 1. Fields shared between the DB2 workload manager and snapshot monitor table functions
Workload manager table function field	Snapshot monitor table function field
agent_tid	agent_pid
application_handle	agent_id agent_id_holding_lock
session_auth_id	session_auth_id
dbpartitionnum	node_number
utility_id	utility_id
workload_id	workload_id

As an example of a reason to use a join between different table functions, assume that you want to obtain basic information about all of the utilities running in the BATCH service superclass. You might issue the following query:

SELECT SUBSTR(UTILITY_TYPE,1,4) TYPE,
       UTILITY_PRIORITY PRIORITY,
       SUBSTR(UTILITY_DESCRIPTION,1,12) AS UTILITY_DESCRIPTION,
       SUBSTR(UTILITY_DBNAME,1,8) AS DBNAME,
       UTILITY_STATE,
       SUBSTR(UTILITY_INVOKER_TYPE,1,7) INVOKER,
       SUBSTR(CHAR(WLM.DBPARTITIONNUM),1,4) PART,
       SUBSTR(CLASSES.PARENTSERVICECLASSNAME,1,19) SUPERCLASS_NAME,
       SUBSTR(CLASSES.SERVICECLASSNAME,1,18) SUBCLASS_NAME
FROM SYSIBMADM.SNAPUTIL SNAP,
     TABLE(WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES_V97(CAST(NULL AS BIGINT), -2)) WLM,
     SYSCAT.SERVICECLASSES CLASSES
WHERE SNAP.UTILITY_ID = WLM.UTILITY_ID
  AND WLM.SERVICE_CLASS_ID = CLASSES.SERVICECLASSID
  AND CLASSES.SERVICECLASSNAME = 'SYSDEFAULTSUBCLASS'
  AND CLASSES.PARENTSERVICECLASSNAME = 'BATCH'
ORDER BY WLM.DBPARTITIONNUM

The output might resemble the following output:

TYPE PRIORITY    UTILITY_DESCRIPTION DBNAME   UTILITY_STATE INVOKER PART SUPERCLASS_NAME     SUBCLASS_NAME
---- ----------- ------------------- -------- ------------- ------- ---- ------------------- ------------------
LOAD           - OFFLINE LOAD        SAMPLE   EXECUTE       USER    1    BATCH               SYSDEFAULTSUBCLASS
LOAD           - OFFLINE LOAD        SAMPLE   EXECUTE       USER    1    BATCH               SYSDEFAULTSUBCLASS
LOAD           - OFFLINE LOAD        SAMPLE   EXECUTE       USER    1    BATCH               SYSDEFAULTSUBCLASS
LOAD           - OFFLINE LOAD        SAMPLE   EXECUTE       USER    2    BATCH               SYSDEFAULTSUBCLASS
LOAD           - OFFLINE LOAD        SAMPLE   EXECUTE       USER    2    BATCH               SYSDEFAULTSUBCLASS
LOAD           - OFFLINE LOAD        SAMPLE   EXECUTE       USER    2    BATCH               SYSDEFAULTSUBCLASS
LOAD           - OFFLINE LOAD        SAMPLE   EXECUTE       USER    3    BATCH               SYSDEFAULTSUBCLASS
LOAD           - OFFLINE LOAD        SAMPLE   EXECUTE       USER    3    BATCH               SYSDEFAULTSUBCLASS
LOAD           - OFFLINE LOAD        SAMPLE   EXECUTE       USER    3    BATCH               SYSDEFAULTSUBCLASS