MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics

Syntax

>>-MON_GET_WORKLOAD_DETAILS--(--workload_name--,--member--)----><

The schema is SYSPROC.

Table function parameters

workload_name

An input argument of type VARCHAR(128) that specifies a specific workload for which the metrics are to be returned. If the argument is NULL or an empty string, metrics are returned for all workloads.

member

An input argument of type INTEGER that specifies a valid member in the same instance as the currently connected database when calling this function. Specify -1 for the current database member, or -2 for all database members. If the NULL value is specified, -1 is set implicitly.

Authorization

EXECUTE privilege on the MON_GET_WORKLOAD_DETAILS function.

Example

Display lock information for each workload, aggregated across members, ordered by highest lock wait time.

SELECT varchar(wlmetrics.workload_name,30) as workload_name,
       sum(detmetrics.lock_wait_time) as total_lock_wait_time,
       sum(detmetrics.lock_waits) as total_lock_waits,
       sum(detmetrics.lock_timeouts) as total_lock_timeouts,
       sum(detmetrics.lock_escals) as total_lock_escals
FROM TABLE(MON_GET_WORKLOAD_DETAILS('',-2)) AS WLMETRICS,
XMLTABLE (XMLNAMESPACES( DEFAULT 'http://www.ibm.com/xmlns/prod/db2/mon'),
           '$detmetric/db2_workload' PASSING 
           XMLPARSE(DOCUMENT WLMETRICS.DETAILS) 
           as "detmetric"
COLUMNS "LOCK_WAIT_TIME" INTEGER PATH 'system_metrics/lock_wait_time',
        "LOCK_WAITS" INTEGER PATH 'system_metrics/lock_waits',
        "LOCK_TIMEOUTS" INTEGER PATH 'system_metrics/lock_timeouts',
        "LOCK_ESCALS" INTEGER PATH 'system_metrics/lock_escals'
) AS DETMETRICS
GROUP BY workload_name
ORDER BY total_lock_wait_time desc;

The following example is a sample output from this query.

WORKLOAD_NAME                  TOTAL_LOCK_WAIT_TIME TOTAL_LOCK_WAITS ... 
------------------------------ -------------------- ---------------- ... 
SYSDEFAULTADMWORKLOAD                             0                0 ... 
SYSDEFAULTUSERWORKLOAD                            0                0 ... 

  2 record(s) selected.

Output for query (continued).

... TOTAL_LOCK_TIMEOUTS TOTAL_LOCK_ESCALS
... ------------------- -----------------
...                   0                 0
...                   0                 0

Usage notes

Metrics are rolled up to a workload on unit of work boundaries, and periodically during the execution of requests. Therefore, the values reported by this table function reflect the current state of the system at the time of the most recent rollup. Metrics are strictly increasing in value. To determine the value of a given metric for an interval of time, use the MON_GET_WORKLOAD_DETAILS table function to query the metric at the start and end of the interval, and compute the difference.

Request metrics are controlled through the COLLECT REQUEST METRICS clause on service superclasses and the mon_req_metrics database configuration parameter at the database level. Metrics are only collected for a request if the request is processed by an agent in a service subclass whose parent service superclass has request metrics enabled, or if request metrics collection is enabled for the entire database. By default request metrics are enabled at the database level. If request metrics have been disabled at the database level, and for a service superclass, the metrics reported for each workload mapped to that service superclass stop increasing (or remain at 0 if request metrics were disabled at database activation time).

The MON_GET_WORKLOAD_DETAILS table function returns one row of data per workload and per member. No aggregation across workloads (on a member), or across members (for a service class or more), is performed. However, aggregation can be achieved through SQL queries as shown in the example.

The schema for the XML document that is returned in the DETAILS column is available in the file sqllib/misc/DB2MonRoutines.xsd. Further details can be found in the file sqllib/misc/DB2MonCommon.xsd.

Information returned

<db2_workload xmlns="http://www.ibm.com/xmlns/prod/db2/mon" release="90700000">
   <workload_name>SYSDEFAULTADMWORKLOAD</workload_name>
   <workload_id>11</workload_id>
   <member>0</member>
   <system_metrics release="90700000">
   <act_aborted_total>5</act_aborted_total>
    ...
   <wlm_queue_assignments_total>3</wlm_queue_assignments_total>
   </system_metrics>
</db2_workload>