MON_CURRENT_SQL - Retrieve key metrics for all activities on all members

The MON_CURRENT_SQL administrative view returns key metrics for all activities that were submitted on all members of the database and have not yet been completed, including a point-in-time view of currently executing SQL statements (both static and dynamic) in the currently connected database.

You can use the MON_CURRENT_SQL administrative view to identify long running activities and prevent performance problems.

This view returns metrics that are aggregated across all members.

The schema is SYSIBMADM.

Authorization

One of the following authorizations is required:
  • SELECT privilege on the MON_CURRENT_SQL administrative view
  • CONTROL privilege on the MON_CURRENT_SQL administrative view
  • DATAACCESS authority
  • DBADM authority
  • SQLADM authority
  • ACCESSCTRL authority
  • SECADM authority

Default PUBLIC privilege

None

Information returned

Table 1. Information returned by the MON_CURRENT_SQL administrative view
Column name Data type Description or Monitor element
COORD_MEMBER SMALLINT coord_member - Coordinating member
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 activity began, in seconds. The value of this column is null when an activity has entered the system but is in a queue and has not started running.
ACTIVITY_STATE VARCHAR(32) activity_state - Activity state
ACTIVITY_TYPE VARCHAR(32) activity_type - Activity type
TOTAL_CPU_TIME BIGINT total_cpu_time - Total CPU time
ROWS_READ BIGINT rows_read - Rows read
ROWS_RETURNED BIGINT rows_returned - Rows returned
QUERY_COST_ESTIMATE BIGINT query_cost_estimate - Query cost estimate
DIRECT_READS BIGINT direct_reads - Direct reads from database
DIRECT_WRITES BIGINT direct_writes - Direct writes to database
APPLICATION_ID VARCHAR(128) appl_id - Application ID
UOW_ID INTEGER uow_id - Unit of work ID
ACTIVITY_ID INTEGER activity_id - Activity ID
STMT_TEXT CLOB(2MB) stmt_text - SQL statement text