MON_CURRENT_UOW - Retrieve metrics for all units of work

The MON_CURRENT_UOW administrative view returns key metrics for all units of work that were submitted on all members of the database.

This view identifies long running units of work and can therefore be used to prevent performance problems.

The MON_CURRENT_UOW view represents the coordinator perspective, and not individual members.

The schema is SYSIBMADM.

Authorization

One of the following authorizations is required:

SELECT privilege on the MON_CURRENT_UOW administrative view
CONTROL privilege on the MON_CURRENT_UOW administrative view
DATAACCESS authority
DBADM authority
SQLADM authority

Default PUBLIC privilege

None

Information returned

Table 1. Information returned by the MON_CURRENT_UOW administrative view
Column name	Data type	Description or Monitor element
COORD_MEMBER	SMALLINT	coord_member - Coordinator member
UOW_ID	INTEGER	uow_id - Unit of work ID
APPLICATION_HANDLE	BIGINT	application_handle - Application handle
APPLICATION_NAME	VARCHAR(128)	appl_name - Application name
SESSION_AUTH_ID	VARCHAR(128)	session_auth_id - Session authorization ID
CLIENT_APPLNAME	VARCHAR(255)	CURRENT CLIENT_APPLNAME special register
ELAPSED_TIME_SEC	INTEGER	The time elapsed since this unit of work began, in seconds.
ELAPSED_TIME_MSEC	INTEGER	The time elapsed since the unit of work began, in milliseconds.
WORKLOAD_OCCURRENCE_STATE	VARCHAR(32)	workload_occurrence_state - Workload occurrence state
TOTAL_CPU_TIME	BIGINT	total_cpu_time - Total CPU time
TOTAL_ROWS_MODIFIED	BIGINT	The total number of rows inserted, updated or deleted.
TOTAL_ROWS_READ	BIGINT	The total number of rows read from tables.
TOTAL_ROWS_RETURNED	BIGINT	The total number of rows that have been selected and returned to the application.

Example

The following example retrieves the application handle, the unit of work ID, the elapsed time, and the total number of rows read and rows returned, for all units of work that have been executed for more than 10 seconds.

SELECT APPLICATION_HANDLE AS APPL_HANDLE,
       UOW_ID, ELAPSED_TIME_SEC,
       TOTAL_ROWS_READ AS TOTAL_READ,
       TOTAL_ROWS_MODIFIED AS TOTAL_MODIFIED
  FROM SYSIBMADM.MON_CURRENT_UOW
 WHERE ELAPSED_TIME_SEC > 10
 ORDER BY ELAPSED_TIME_SEC DESC

The following is an example of output for this query.

APPL_HANDLE UOW_ID ELAPSED_TIME_SEC TOTAL_READ TOTAL_MODIFIED
----------- ------ ---------------- ---------- --------------
        254      1              750      87460              0
         61      1              194        108              0
        145      4               82          0             34

  3 record(s) selected.