DB2 Version 9.7 for Linux, UNIX, and Windows

Reports generated using the MONREPORT module

The MONREPORT module generates text reports of monitoring data that you can use to troubleshoot SQL performance problems.

You can generate the following reports using the MONREPORT module:

Table 1. List of reports generated using the MONREPORT module
Report Name	Procedure to create report	Main data source / table functions
Summary report	MONREPORT.DBSUMMARY	MON_GET_SERVICE_SUBCLASS and selected details from MON_GET_CONNECTION and MON_GET_WORKLOAD
Connection report	MONREPORT.CONNECTION	MON_GET_CONNECTION
Current Applications report	MONREPORT.CURRENTAPPS	Includes fields from MON_GET_CONNECTION, MON_GET_UNIT_OF_WORK, WLM_GET_SERVICE_CLASS_AGENTS_V97, WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES_V97
Current SQL report	MONREPORT.CURRENTSQL	WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES_V97 and statement text obtained from MON_GET_PKG_CACHE_STMT for the specified executable_id values
Package Cache report	MONREPORT.PKGCACHE	MON_GET_PKG_CACHE_STMT
Current Lock Wait report	MONREPORT.LOCKWAIT	Most data from MON_GET_APPL_LOCKWAIT; additional data from MON_GET_CONNECTION, WLM_GET_SERVICE_CLASS_AGENTS_V97, WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES_V97, MON_GET_PKG_CACHE_STMT, MON_GET_TABLE

To generate a report, issue a command such as call monreport.connection(60, 32). The first parameter is the monitoring interval; the subsequent parameters are report-specific. Use the same monitoring interval for all reports to establish accurate baseline values and consistent results.

Most reports start with a summary section that provides one line of key information for each item in the report. For example, the Connection report contains a one-line summary of each connection. The main body of the report consists of a detailed section for each item in the summary.

Each metric in the report is labeled with the underlying monitor element name (for example: CLIENT_IDLE_WAIT_TIME = 44). To determine what the metric represents, search the Information Center for the monitor element name.

You can customize the reports generated by the MONREPORT module. The MONREPORT module is implemented entirely using SQL and you can obtain the module code from the database catalog and create a customized version.

Reports for initial diagnosis

An important use of these reports is to troubleshoot SQL performance slowdowns. Each report is designed to answer certain diagnosis questions. Some reports support initial diagnosis, while others support subsequent detailed diagnosis of particular types of problems.

Initial diagnosis involves:

Determining the problem category, by narrowing the problem down to the aspect or stage of processing that has slowed down.
Identifying the SQL statements involved in the problem and collecting information about the SQL statements for further analysis.

Table 2. MONREPORT module reports suitable for initial diagnosis
Procedure name	Information provided and usage
MONREPORT.DBSUMMARY Part 1: System Performance	Part 1 of the Summary report provides monitor data for most aspects of processing aggregated across the entire database. This information is useful for answering questions about the aspect or stage of processing that has slowed down. For example: Is the problem inside or outside the data server? Is there a computing resource bottleneck? Are requests in a wait state? If so, for what resource? Is the slowdown located in a particular data server processing component?
MONREPORT.DBSUMMARY Part 2: Application Performance	Part 2 of the Summary report provides key performance indicators for each connection, workload, and service class. This information is useful for answering questions about the scope of application requests involved in the slowdown. For example: Is this slowdown a general system slowdown that affects much or all the workload? Is this slowdown limited to SQL statements issued from a particular source such as particular connections, DB2® workloads or DB2 service classes?
MONREPORT.DBSUMMARY Part 3: Member level information	Part 3 of the Summary report provides key performance indicators for each member. This information is useful for determining whether the slowdown is isolated to one or a few members.
MONREPORT.CURRENTSQL	The Current SQL report provides information about statements that are currently running, in the form of several lists of the top N activities. Each list ranks activities by a different metric, such as processing resources, rows processed, number of direct reads and direct writes. This information is useful for determining whether the slowdown is isolated to one or a few SQL statements. If the slowdown is isolated to one or a few SQL statements, those statements are likely to appear in this report of top statements.
MONREPORT.PKGCACHE	The Package Cache report provides information about statements that have run recently and are stored in the package cache. This report shows several summaries, each listing the top N activities. The activities are ranked by the following metrics: CPU wait time rows processed number of executions I/O wait time For each of CPU, wait time, rows processed, and I/O wait time, the report shows two summaries: one for the metric aggregated across all executions of a statement, and one for the metric averaged by the number of executions of the statement. This information is useful for determining whether the slowdown is isolated to one or a few SQL statements. If so, those statements are likely to appear at the top in this report. The information per execution can help identify the most costly statements while the information summed across executions can help identify statements with the most impact on the system cumulatively considering both the statement cost and frequency of execution.
MONREPORT.CURRENTAPPS	The Current Applications report shows the current processing state for units of work, agents, and activities. The report starts with a summary section showing the number of current connections and activities, as well as a series of summaries, such as the summary of current units of work by workload occurrence state. The body of the report consists of one section for each connection that provides the details of the connection. This information is useful for viewing all the work currently running on the system. This allows you to check for patterns that might identify the problem category.

Reports for detailed diagnosis

After completing the initial diagnosis, you might need to pursue a specialized or detailed set of troubleshooting analyses for the problem category you identified during the initial diagnosis phase.

Table 3. MONREPORT module reports suitable for detailed diagnosis
Procedure name	Information provided and usage
MONREPORT.CONNECTION	If the Summary report showed that the slowdown is limited to SQL statements issued from a particular connection, then you can view detailed information about the affected connection. This report contains the same metrics as Part 1 of the Summary report, but it presents this information for each connection.
MONREPORT.LOCKWAIT	If the reports viewed during the initial diagnosis suggest there is a lock wait problem, then you can view detailed information about each lock wait currently in progress. This information includes lock holder and lock requestor details, as well as characteristics of the lock held and the lock requested.