DB2 Version 9.7 for Linux, UNIX, and Windows

MONREPORT module

The MONREPORT module provides a set of procedures for retrieving a variety of monitoring data and generating text reports.

The schema for this module is SYSIBMADM.

The MONREPORT module includes the following built-in routines.

Table 1. Built-in routines available in the MONREPORT module
Routine name	Description
CONNECTION procedure	The Connection report presents monitor data for each connection.
CURRENTAPPS procedure	The Current Applications report presents the current instantaneous state of processing of units of work, agents, and activities for each connection. The report starts with state information summed across connections, followed by a section for details for each connection.
CURRENTSQL procedure	The Current SQL report lists the top activities currently running, as measured by various metrics.
DBSUMMARY procedure	The Summary report contains in-depth monitor data for the entire database, as well as key performance indicators for each connection, workload, service class, and database member.
LOCKWAIT procedure	The Lock Waits report contains information about each lock wait currently in progress. Details include lock holder and requester details, plus characteristics of the lock held and the lock requested.
PKGCACHE procedure	The Package Cache report lists the top statements accumulated in the package cache as measured by various metrics.

Usage notes

Monitor element names are displayed in upper case (for example, TOTAL_CPU_TIME). To find out more information about a monitor element, search the DB2® Information Center for the monitor name.

For reports with a monitoring_interval input, negative values in a report are inaccurate. This may occur during a rollover of source data counters. To determine accurate values, re-run the report after the rollover is complete.

Note: The reports are implemented using SQL procedures within modules, and as such can be impacted by the package cache configuration. If you observe slow performance when running the reports, inspect your package cache configuration to ensure it is sufficient for your workload. For further information, see "pckcachesz - Package cache size configuration parameter".

The following examples demonstrate various ways to call the MONREPORT routines. The examples show the MONREPORT.CONNECTION(monitoring_interval, application_handle) procedure. You can handle optional parameters for which you do not want to enter a value in the following ways:

You can always specify null or DEFAULT.
For character inputs, you can specify an empty string (' ').
If it is the last parameter, you can omit it.

To generate a report that includes a section for each connection, with the default monitoring interval of 10 seconds, make the following call to the MONREPORT.CONNECTION procedure:

call monreport.connection()

To generate a report that includes a section only for the connection with application handle 32, with the default monitoring interval of 10 seconds, you can make either of the following calls to the MONREPORT.CONNECTION procedure:

call monreport.connection(DEFAULT, 32)

call monreport.connection(10, 32)

To generate a report that includes a section for each connection, with a monitoring interval of 60 seconds, you can make either of the following calls to the MONREPORT.CONNECTION procedure:

call monreport.connection(60)

call monreport.connection(60, null)

By default, the reports in this module are generated in English. To change the language in which the reports are generated, change the CURRENT LOCALE LC_MESSAGES special register. For example, to generate the CONNECTION report in French, issue the following commands:

SET CURRENT LOCALE LC_MESSAGES = 'CLDR 1.5:fr_FR'
CALL MONREPORT.CONNECTION

Ensure that the language in which the reports are generated is supported by the database code page. If the database code page is Unicode, you can generate the reports in any language.