DB2 Version 9.7 for Linux, UNIX, and Windows

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.

Note: If your database was created in Version 9.7 before Fix Pack 1, to run this routine you must have already run the db2updv97 command. If your database was created before Version 9.7 , it is not necessary to run the db2updv97 command (because the catalog update is automatically taken care of by the database migration). If you downgrade to Version 9.7 , this routine will no longer work.

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

This view represents the coordinator perspective, and not that of individual 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

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
STMT_TEXT	CLOB(2MB)	stmt_text - SQL statement text