Monitor routines
are routines with names that begin with "MON", such as MON_GET_SERVICE_SUBCLASS,
or "EVMON", such as EVMON_FORMAT_UE_TO_TABLES.
These
routines perform a variety of different functions related to querying
the status of your system, or manipulating monitoring data. Some routines
are table functions that return data in the form of a table. For example,
several table functions provide access to monitor elements that are
available from the monitoring infrastructure introduced in DB2® Version 9.7. Some are views.
Views are very similar to table functions; they return data in table
format, but unlike table functions, they do not require any input
parameters. Some monitor routines, notably, the ones that begin "EVMON" with transform data in one format to another. 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
- MON_GET_PKG_CACHE_STMT
and MON_GET_PKG_CACHE_STMT_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_EXTENDED_LATCH_WAIT
- MON_GET_INDEX
- MON_GET_LOCKS
- MON_GET_PAGE_ACCESS_INFO
- MON_GET_TABLE
- MON_GET_TABLESPACE
Use these table functions to investigate performance issues associated
with a particular data object.
Other table
functions are useful for subsystem monitoring:
- MON_GET_FCM
- MON_GET_FCM_CONNECTION_LIST
- MON_GET_HADR
- MON_GET_SERVERLIST
- MON_GET_TRANSACTION_LOG
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.
- MON_GET_INDEX_USAGE_LIST returns information
from a usage list defined for an index.
- MON_GET_TABLE_USAGE_LIST returns information
from a usage list defined for a table.
In
addition, the following table functions serve a progress monitoring
role:
- MON_GET_AUTO_MAINT_QUEUE returns information
about all automatic maintenance jobs that are currently queued for
execution by the autonomic computing daemon (db2acd).
- MON_GET_AUTO_RUNSTATS_QUEUE returns information
about all objects which are currently queued for evaluation by automatic
statistics collection in the currently connected database.
- MON_GET_EXTENT_MOVEMENT_STATUS returns the status of the extent
movement operation.
- MON_GET_REBALANCE_STATUS returns the status of a rebalance operation
on a table space.
- MON_GET_RTS_RQST returns information about all
real-time statistics requests that are pending in the system, and
the set of requests that are currently being processed by the real
time statistics daemon.
- MON_GET_USAGE_LIST_STATUS returns current status
on a usage list.
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
Event monitor 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
The event monitor 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.