MON_GET_UNIT_OF_WORK_DETAILS table function - Get unit of work metrics as an XML document

Authorization

Default PUBLIC privilege

None

Syntax

The schema is SYSPROC.

Routine parameters

application_handle

An input argument of type BIGINT that specifies a valid application handle in the same database as the one currently connected to when calling this function. If the argument is null, metrics are retrieved for units of work running in all superclasses in the database.

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 active database members. If the NULL value is specified, -1 is set implicitly.

system_appls

An input parameter of data type SMALLINT that specifies whether information for system applications is returned. The following values are valid:

• 0 or NULL: System application information is not returned
• 1: User and system application information is returned

Information returned

<db2_unit_of_work xmlns="http://www.ibm.com/xmlns/prod/db2/mon" release="90700000">
   <service_superclass_name>SYSDEFAULTUSERCLASS</service_superclass_name>
   <service_subclass_name>SYSDEFAULTSUBCLASS</service_subclass_name>
   <service_class_id>13</service_class_id>
   <workload_name>SYSDEFAULTUSERWORKLOAD</workload_name>
   <member>0</member>
   <coord_member>0</coord_member>
   <application_handle>21</application_handle>
   <workload_occurrence_id>1</workload_occurrence_id>
   <uow_id>2</uow_id>
   <workload_occurrence_state>UOWEXEC</workload_occurrence_state>
   <system_metrics>
   <act_aborted_total>5</act_aborted_total>
   ...
   <wlm_queue_assignments_total>3</wlm_queue_assignments_total>
   </system_metrics>
</db2_unit_of_work_metrics>

Usage notes

Metrics are rolled up periodically during the unit of work. 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_UNIT OF WORK_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 unit of work that are mapped to that service superclass stop increasing (or remain at 0 if request metrics were disabled at database activation time).

The MON_GET_UNIT_OF_WORK_DETAILS table function returns one row of data per unit of work and per member. No aggregation across units of work (on a member), or across members (for a service class or more), is performed. However, aggregation can be achieved through SQL queries. The input parameters have the effect of being ANDed together.

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.

Example

Identify the units of work that are consuming the highest amount of CPU time on the system.

SELECT detmetrics.application_handle, 
    detmetrics.uow_id, 
    detmetrics.total_cpu_time, 
    detmetrics.app_rqsts_completed_total, 
    detmetrics.rqsts_completed_total 
FROM TABLE(MON_GET_UNIT_OF_WORK_DETAILS(NULL,-2)) AS UOWMETRICS,
XMLTABLE (
    XMLNAMESPACES( DEFAULT 'http://www.ibm.com/xmlns/prod/db2/mon'),
        '$detmetric/db2_unit_of_work' PASSING
        XMLPARSE(DOCUMENT UOWMETRICS.DETAILS) 
        as "detmetric"
COLUMNS 
    "APPLICATION_HANDLE" INTEGER PATH 'application_handle',
    "UOW_ID" INTEGER PATH 'uow_id',
    "TOTAL_CPU_TIME" INTEGER PATH 'system_metrics/total_cpu_time',
    "APP_RQSTS_COMPLETED_TOTAL" INTEGER 
        PATH 'system_metrics/app_rqsts_completed_total',
    "RQSTS_COMPLETED_TOTAL" INTEGER 
        PATH 'system_metrics/rqsts_completed_total'
) AS DETMETRICS
ORDER BY total_cpu_time DESC

The following is an example of output from this query.

APPLICATION_HANDLE   UOW_ID      TOTAL_CPU_TIME       ... 
-------------------- ----------- -------------------- ... 
                  46           5                27959 ... 

  1 record(s) selected.

Output for query (continued).

... APP_RQSTS_COMPLETED_TOTAL RQSTS_COMPLETED_TOTAL
... ------------------------- ---------------------
...                        72                    48