DB2 Version 10.1 for Linux, UNIX, and Windows

Workload management table functions and snapshot monitor integration

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

The DB2 workload management 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 management and snapshot monitor table functions
Workload management 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
member	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) DESCRIPTION,
       SUBSTR(UTILITY_DBNAME,1,8) DBNAME,
       UTILITY_STATE STATE,
       SUBSTR(UTILITY_INVOKER_TYPE,1,7) INVOKER,
       SUBSTR(CHAR(WLM.MEMBER),1,4) MEMB,
       SUBSTR(CLASSES.PARENTSERVICECLASSNAME,1,19) SUPERCLASS_NAME,
       SUBSTR(CLASSES.SERVICECLASSNAME,1,18) SUBCLASS_NAME
FROM SYSIBMADM.SNAPUTIL SNAP,
     TABLE(WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES(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.MEMBER

The output might resemble the following output:

TYPE PRIORITY DESCRIPTION  DBNAME STATE   INVOKER MEMB 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