DB2 Version 10.1 for Linux, UNIX, and Windows

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. It 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

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.

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.
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.