Monitoring table functions and administrative views
Database monitoring is a vital activity that provides continuous feedback on the health of a database system. To facilitate monitoring, DB2 collects information from the database manager, its databases, and any connected applications. This collected information can be examined using one or more of the various monitoring interfaces provided with DB2.
There are two distinct methods for monitoring operations in a database. The first involves monitoring table functions and administrative views, and allows you to view information that shows the state of various aspects of the database at a specific point in time. Event monitors, on the other hand, capture and log historical information as specific types of database events occur.
Monitor elements are data structures that store information about a particular aspect of database system status. Data for monitored elements is continually accumulated in memory and available for querying by one or more of the various monitoring interfaces provided with DB2, such as table functions or event monitors. Each monitor element reflects one of the following types of data:
- Counter — A counter keeps a total count of the number of times an activity or event has occurred, increasing throughout the life of the monitor. Monitoring element counters are never reset, starting with database activation and continuing to accumulate until the database is deactivated. An example of a counter element would be the total number of SQL statements that have been executed against a database.
- Gauge — A gauge keeps a count of the number times an activity or event has occurred at a specific point in time. Unlike counter values, gauge values can go up or down, and their value at any given point in time is usually dependent upon the level of database activity. An example of a gauge element would be the number of applications that are currently connected to a database.
- Watermark — A watermark indicates the highest (maximum) value an item has reached since monitoring began. An example of a watermark element would be the longest-running unit of work since the database was activated.
- Text — As the name implies, text elements provide reference-type details of all monitoring activities performed. Examples of information elements would include text of an SQL statement, buffer pool names, database names and aliases, path details, etc.
- Timestamp — A timestamp monitor element indicates the time an activity or event took place. Timestamp values are provided as the number of seconds and microseconds that have elapsed since 1 Jan 1970. An example of a timestamp element would be the date and time the first connection to a database was established. Compared to gauges that measure elapsed time (such as the time spent performing a sort), timestamps measure the exact point in time that something begins or ends.
It is important to note that monitoring elements are not exclusive to particular monitoring interfaces. Different table functions and event monitors collect monitoring data from sets of monitoring elements that can generally overlap. Monitor elements are categorized into three main categories:
- Request monitor elements — Request monitor elements measure the work carried out by the database server as a whole to process different types of requests. This includes overall database system processing and processing between server and connected clients. Examples of request monitor elements include the number of completed requests by the system, the total CPU usage time, and time spent waiting for the next request from an open connection.
- Activity monitor elements — Activity monitor elements are a subset of request monitor elements. These elements measure data server processing related to executing activities, in particular, work involved with SQL statement execution such as locking, sorting and row processing.
- Data object monitor elements — Data object monitor elements provide information about operations performed on particular data objects such as tables, indices, buffer pools, table spaces and containers.
DB2 offers hundreds of metrics across these three categories, but collecting the entire set can be counter-productive and misleading. Proper choice of relevant metrics for analysis leads the DBA to accurate deductions about system health or problems being investigated. Some examples of useful metrics are listed below:
- The ratio of database rows read to rows selected (ROWS_READ/ROWS_RETURNED) gives an indication of whether indices are being used properly to optimize reading operations.
- The time an agent waits for log records to be flushed to disk (LOG_WRITE_TIME/TOTAL_COMMITS) gives an indication of the efficiency of database transaction logging.
Monitoring table functions are designed to collect information about the state of the database manager, its databases, and any connected applications at a specific point in time. Monitoring table functions use the newer, lightweight, high-speed monitoring infrastructure introduced in DB2 9.7. Prior to DB2 9.7, access to point in time monitoring data was achieved through snapshot monitoring, which uses a different monitoring infrastructure. Generally speaking, snapshot monitoring facilities are no longer being enhanced in the product and usage of monitoring table functions is recommended. Monitoring table functions report information at the system, activity, and data object levels. These table functions collect data from request, activity and data object monitor elements, respectively.
The system monitoring perspective includes information about all work carried out by the database to process application requests. Request monitor elements continuously accumulate and aggregate this information from requests handled at various levels of the workload management (WLM) hierarchy. System monitoring information can be accessed at the unit of work, workload, service class, and connection:
MON_GET_UNIT_OF_WORK_DETAILS— Return request metrics for one or more units of work
MON_GET_WORKLOAD_DETAILS— Return request metrics for one or more workloads
MON_GET_SERVICE_SUBCLASS_DETAILS— Return request metrics for one or more service subclasses
MON_GET_CONNECTION_DETAILS— Return request metrics for one or more connections
Table functions for monitoring system information are provided in pairs. One function allows relational access to commonly used data, having each request monitor data in one column. The DETAILS table function returns an XML document encapsulating the full detailed set of available request monitor elements.
The database configuration parameter
controls whether data is collected by request monitor elements across
the entire data server, and can be set to the following values:
NONE— No request monitor elements are collected.
BASE— All request monitor elements are collected. This is the default value.
EXTENDED— Similar to BASE, but additionally the values for some monitor elements are determined with more granularity.
Alternatively, collection can be controlled for individual WLM service
classes through the
COLLECT REQUEST METRICS clause while creating or
altering service classes (
METRICS can be set to NONE, BASE, or EXTENDED).
If disabling of request metrics collection over a database named PAYROLL is desired, except for the service class ACCOUNTING, the following commands can be issued:
DB2 UPDATE DB CFG FOR PAYROLL USING MON_REQ_METRICS NONE ALTER SERVICE CLASS ACCOUNTING COLLECT REQUEST METRICS EXTENDED
Accordingly, request metrics will only be collected for agents that run in the ACCOUNTING service class.
To identify the units of work that are consuming the highest amount of CPU time on the system, you could do so by executing the following command:
SELECT APPLICATION_HANDLE, TOTAL_WAIT_TIME, TOTAL_RQST_TIME FROM TABLE(MON_GET_CONNECTION(NULL,NULL))
When this command is executed from the command-line processor, you should see something like the output shown below.
Listing 1. Sample output from
APPLICATION_HANDLE TOTAL_WAIT_TIME TOTAL_RQST_TIME -------------------- -------------------- -------------------- 39 179 269 78 0 0 51 207 316 77 0 21 50 1014 1408 40 109 351 79 89 167 7 record(s) selected.
The output produced by this command would help you decide if the percentage of time spent waiting for each application is acceptable. The SQL query can also be modified to produce a fourth column with the calculated percentage for time spent waiting.
The activity monitoring perspective focuses on the subset of data server processing related to executing activities (SQL statements execution, in particular). Metrics are accumulated in memory and package cache for running activities and SQL statements. The following table functions access current data for activities:
MON_GET_ACTIVITY_DETAILSreturns data about individual activities in progress. Data is returned in a relational form, but detailed metrics are returned in an XML document in the DETAILS column of the result table.
MON_GET_PKG_CACHE_STMTreturns a current view of static and dynamic SQL statements in the database package cache. Data is returned in a relational form.
MON_GET_PKG_CACHE_STMT_DETAILSreturns detailed metrics for one or more package cache entries. Data is returned in a relational form, but the detailed metrics are returned in an XML document in the DETAILS column of the results table.
The database configuration parameter
mon_act_metrics controls activity metrics collection in all workloads. Alternatively,
collection can be controlled for individual WLM workloads through the
COLLECT ACTIVITY METRICS clause while creating or altering workloads.
Both can be set to
The data object monitoring perspective provides information about operations carried out on database objects: tables, indices, buffer pools, table spaces, and containers. Each data object has monitoring elements incremented each time a request involves processing that object. The following table functions access current data for database objects:
MON_GET_BUFFERPOOLreturns monitor metrics for one or more buffer pools, allowing analysis of buffer pool activity, efficiency, hit ratio, etc.
MON_GET_TABLESPACEreturns monitor metrics for one or more table spaces, allowing analysis of table space and associated buffer pool activities.
MON_GET_CONTAINERreturns monitor metrics for one or more table space containers, allowing analysis of reading and writing activities.
MON_GET_TABLEreturns monitor metrics for one or more tables, including information about reads, inserts, updates, overflow activity, etc.
MON_GET_INDEXreturns monitor metrics for one or more indices, including information about the number of index scans, number of index-only scans, etc.
The database configuration parameter
mon_obj_metrics controls data object metrics on the entire database for monitoring
elements associated with
MON_GET_CONTAINER (WLM service class and workload clauses do not
apply). Unlike their peers, monitoring elements associated with
MON_GET_INDEX table functions always collect data.
Collection for tables and indices cannot be disabled by database
To list aggregated reading activity on all tables accessed since the database was activated, and ordered by highest reads, the following query can be issued.
Listing 2. Query to list the aggregated reading activity on all tables accessed
SELECT varchar(tabschema,20) as tabschema, varchar(tabname,20) as tabname, sum(rows_read) as total_rows_read, FROM TABLE(MON_GET_TABLE('','',-2)) AS t GROUP BY tabschema, tabname ORDER BY total_rows_read DESC
Listing 3. Sample output from
TABSCHEMA TABNAME TOTAL_ROWS_READ -------------------- -------------------- -------------------- SYSIBM SYSHISTO 113 SYSIBM SYSWORKL 22 SYSIBM SYSROUTI 13 SYSIBM SYSSERVI 13 SYSIBM SYSTHRES 6 SYSIBM SYSTABLE 3 SYSIBM SYSCONTE 2 SYSIBM SYSDBAUT 2 SYSIBM SYSEVENT 2 SYSIBM SYSPLAN 1 SYSIBM SYSSURRO 1 SYSIBM SYSVERSI 1 SYSIBM SYSXMLST 1 SYSIBM SYSAUDIT 0 SYSIBM SYSROLEA 0 SYSIBM SYSROLES 0 SYSIBM SYSTASKS 0 SYSIBM SYSWORKA 0 SYSIBM SYSXMLPA 0 19 record(s) selected.
The table below provides a summary of table functions with corresponding database configuration parameters that control monitoring data collection.
Table 1. Monitoring table functions
|Table functions||Database configuration control|
|Not applicable (always collected)|
Administrative views are similar to table functions; they return
data in table format. But unlike table functions, they do not require
any input parameters. Administrative views belong to the SYSIBMADM
schema and usually start with
MON (monitoring table functions belong
to the SYSPROC schema and usually start with
MON_GET). The following
table lists administrative views based on the new monitoring
Table 2. Administrative views
|MON_BP_UTILIZATION||Returns key monitoring metrics, including hit ratios and average read and write times, for all buffer pools and all database partitions in the currently connected database.|
|MON_CONNECTION_SUMMARY||Returns key metrics for all connections in the currently connected database. It is designed to help monitor the system in a high-level manner, showing incoming work per connection.|
|MON_CURRENT_SQL||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.|
|MON_CURRENT_UOW||Returns key metrics for all units of work submitted on all members of the database. It can identify long-running units of work and to help prevent performance problems.|
|MON_DB_SUMMARY||Returns key metrics aggregated over all service classes in the currently connected database. It is designed to help monitor the system in a high-level manner by providing a concise summary of the database.|
|MON_LOCKWAITS||Returns information about agents working on behalf of applications that are waiting to obtain locks in the currently connected database. It is a useful query for identifying locking problems.|
|MON_PKG_CACHE_SUMMARY||Returns key metrics for both static and dynamic SQL statements in the cache, providing a high-level summary of the database package cache.|
|MON_SERVICE_SUBCLASS_SUMMARY||Returns key metrics for all WLM service subclasses in the currently connected database. It is designed to help monitor the system in a high-level manner, showing work executed per service class.|
|MON_TBSP_UTILIZATION||Returns key monitoring metrics, including hit ratios and utilization percentage, for all table spaces and all database partitions in the currently connected database.|
|MON_WORKLOAD_SUMMARY||Returns key metrics for all WLM workloads in the currently connected database. It is designed to help monitor the system in a high-level manner, showing incoming work per workload.|
NOTE: Some of the monitoring administrative views
above are designed to replace snapshot administrative views that use
the pre-DB2 9.7 monitoring infrastructure, such as
TBSP_UTILIZATION). Like their new counterparts, snapshot
administrative views belong to the SYSIBMADM schema. For more
information about snapshot monitoring and snapshot administrative
views, refer to Part 4 of the previous edition of this series: "DB2 9 DBA certification exam 731 prep, Part 4: Monitoring DB2
activity" or the DB2 10.1 for Linux, UNIX, and Windows Database Monitoring Guide and Reference.
To identify and retrieve information on all applications that executed
units of work exceeding one minute, you can issue the following query,
which employs the
Listing 4. Query to identify and retrieve information on all applications exceeding 1 minute
SELECT APPLICATION_HANDLE AS APPL_HANDLE, UOW_ID, ELAPSED_TIME_SEC, TOTAL_ROWS_MODIFIED AS TOTAL_READ, TOTAL_ROWS_MODIFIED AS TOTAL_MODIFIED FROM MON_CURRENT_UOW WHERE ELAPSED_TIME_SEC > 60 ORDER BY ELAPSED_TIME_SEC DESC
Listing 5. Sample output from
APPL_HANDLE UOW_ID ELAPSED_TIME_SEC TOTAL_READ TOTAL_MODIFIED ----------- ------ ---------------- ---------- -------------- 254 1 750 87460 0 61 1 194 108 0 145 4 82 0 34 3 record(s) selected.