Monitor (MON) routines

Monitor (MON) routines return a diverse set of monitor elements describing system behavior and performance that can be used for understanding, tuning, and troubleshooting applications on the database server.

Characteristics of monitor (MON) routines

  • The MON routines are strategically important, so the names of these routines do not change across releases. As enhancements are made, new output columns are added to these functions. Therefore, when you issue a query to retrieve information using a built-in routine, 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.

  • Some routines 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.
  • The metrics returned by the monitoring routines are never reset. They start at 0 when the database is activated and continue to accumulate until the database is deactivated.
  • With most routines, you can choose to receive data for a single object (for example, service class "A") or for all objects.
  • As with most routines, when using these routines in a Db2® pureScale® environment or a partitioned database environment, you can choose to receive data for a single member or for all members. If you choose to receive data for all members, the routines return one row for each member. You can add the values across members to obtain the value of a monitor element across members.

Monitor (MON) routines

Monitor routines return a diverse set of monitor elements describing system behavior and performance that can be used for understanding, tuning, and troubleshooting applications on the database server. 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 routines 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_DATABASE and MON_GET_DATABASE_DETAILS
Other routines return data for a specific type of 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 routines to investigate performance issues associated with a particular data object.
Other routines are useful for subsystem monitoring:
  • MON_GET_FCM
  • MON_GET_FCM_CONNECTION_LIST
  • MON_GET_HADR
  • MON_GET_SERVERLIST
  • MON_GET_TRANSACTION_LOG
Other routines are useful for examining activities and statements:
  • MON_GET_ROUTINE
  • MON_GET_AGENT
Other routines are useful for examining details of individual activities and statements:
  • MON_GET_ACTIVITY 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.
  • MON_GET_PKG_CACHE_STMT and MON_GET_PKG_CACHE_STMT_DETAILS
  • MON_GET_WLM_ADMISSION_QUEUE returns a list of all activities in the admission queue and their position in the queue.
In addition, the following routines 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 routines 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 routines that begin with MON_FORMAT_XML_ take as input an XML metrics document returned by one of the MON_GET_*_DETAILS routines (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.