DB2 Version 9.7 for Linux, UNIX, and Windows

Monitor routines and views

Monitor table functions and views are routines with names that begin with "MON", such as MON_GET_SERVICE_SUBCLASS. These table functions and views provide access to monitor elements that are available from the monitoring infrastructure introduced in DB2® Version 9.7. Certain other routines, such as snapshot functions, also return monitoring information.

The "MON" routines are strategically important, therefore the names of these routines will not change in future releases. However, they will have new output columns added when enhancements are made in future releases. Therefore, when you issue a query to retrieve information using a built-in routine or view, do not use a statement of the form SELECT * .... Instead, name the result columns in the SELECT statement. This gives the application control over the number of result columns and the sequence in which they are returned.

Monitor (MON) table functions

All table functions include a common set of monitor elements. These elements provide information about a diverse set of system performance indicators that can affect application response time. You can also obtain monitor data for a subset of the workload you are interested in.

Some monitor table functions report on various aspects of the overall system workload, for example:

MON_GET_CONNECTION and MON_GET_CONNECTION_DETAILS
MON_GET_SERVICE_SUBCLASS and MON_GET_SERVICE_SUBCLASS_DETAILS
MON_GET_UNIT_OF_WORK and MON_GET_UNIT_OF_WORK_DETAILS
MON_GET_WORKLOAD and MON_GET_WORKLOAD_DETAILS

These table functions have two versions, one of which has a _DETAILS suffix. The version without the _DETAILS suffix provides a relational SQL interface that returns the most commonly used data. The version with the _DETAILS suffix provides XML-based access to the monitor data, and returns a more comprehensive data set.

Other table functions return data for a specific type of data object, for example:

MON_GET_APPL_LOCKWAIT
MON_GET_BUFFERPOOL
MON_GET_CONTAINER
MON_GET_INDEX
MON_GET_LOCKS
MON_GET_TABLE
MON_GET_TABLESPACE
MON_GET_PKG_CACHE_STMT

Use these table functions to investigate performance issues associated with a particular data object.

The following table functions, which return data about fast communication manager (FCM), have been added with the Version 9.7 Fix Pack 2 release:

MON_GET_FCM
MON_GET_FCM_CONNECTION_LIST

Other table functions are useful for examining details of individual activities and statements:

MON_GET_ACTIVITY_DETAILS returns details for a specific activity currently running on the system; these details include general activity information (like statement text) and a set of metrics.

In addition, this table function serves a progress monitoring role:

MON_GET_EXTENT_MOVEMENT_STATUS returns the status of the extent movement operation.

The table functions that begin with MON_FORMAT_ return information in an easy-to-read row-based format. The MON_FORMAT_LOCK_NAME takes the internal binary name of a lock and returns detailed information about the lock. The table functions that begin with MON_FORMAT_XML_ take as input an XML metrics document returned by one of the MON_GET_*_DETAILS table functions (or from the output of statistics, activity, unit of work, or package cache event monitors) and returns formatted row-based output.

MON_FORMAT_XML_COMPONENT_TIMES_BY_ROW returns formatted row-based output on component times.
MON_FORMAT_XML_METRICS_BY_ROW returns formatted row-based output for all metrics.
MON_FORMAT_XML_TIMES_BY_ROW returns formatted row-based output on the combined hierarchy of wait and processing times.
MON_FORMAT_XML_WAIT_TIMES_BY_ROW table function returns formatted row-based output on wait times.

Characteristics of monitor (MON) table functions

The metrics returned by the monitoring table functions are never reset. They start at 0 when the database is activated and continue to accumulate until the database is deactivated.
With most table functions, you can choose to receive data for a single object (for example, service class "A") or for all objects.
As with most table functions, when using these table functions in a partitioned database environment, you can choose to receive data for a single partition or for all partitions. If you choose to receive data for all partitions, the table functions return one row for each partition. You can add the values across partitions to obtain the value of a monitor element across partitions.

Monitor (MON) views

The monitor views return metrics on various database activities, for example:

MON_CURRENT_SQL returns 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.
MON_DB_SUMMARY returns metrics aggregated over all service classes.
MON_LOCKWAITS returns information about agents working on behalf of applications that are waiting to obtain locks in the currently connected database.
MON_SERVICE_SUBCLASS_SUMMARY returns metrics for all service subclasses, showing work executed per service class.
MON_CURRENT_UOW returns metrics for all units of work.
MON_WORKLOAD_SUMMARY returns metrics for all workloads, showing incoming work per workload.

Event monitor (EVMON) routines

The DB2 Version 9.7 release also introduces two new routines whose purpose is somewhat different than the other "MON" table functions. These routines extract and format data from event monitors that write events to an unformatted event table. The LOCKING and UNIT OF WORK event monitor types use unformatted event tables. The routines names are as follows:

EVMON_FORMAT_UE_TO_XML table function
EVMON_FORMAT_UE_TO_TABLES procedure

These routines allow you to access event monitor data, either through an XML document, by using the EVMON_FORMAT_UE_TO_XML table function; or through relational tables, by using the EVMON_FORMAT_UE_TO_TABLES procedure.