Troubleshooting Guide

Database System Monitor

Built into the database manager is the ability to collect data about its operation and performance, and that of the applications using it. There are two ways to access the monitor data collected by the database manager:

• Snapshot monitoring, which provides you with information for a specific point in time.
• Event monitors, which automatically log monitor data to files or to a named pipe when specific events occur. Event monitors allow you to collect information about transient events that are difficult to monitor through snapshots.

This chapter briefly describes these methods to help you diagnose the cause of poor system and application performance. For more specific information on the Database System Monitor, see the System Monitor Guide and Reference.

Using the Snapshot Monitor

Taking a snapshot gives you information for a specific point in time. A snapshot is a picture of the current state of activity in the database manager for a particular object or group of objects. Using the Snapshot Monitor you can make a specific snapshot requests to assist you with troubleshooting a problem. This section explains the how to use the Snapshot Monitor to capture this information.

The Snapshot Monitor provides two categories of information for each level being monitored:

• State information tells you:
  • The current status of the database
  • Information on the current or most recent unit of work
  • The list of locks being held by an application
  • The status of an application
  • The current number of connections to a database
  • The most recent SQL statement performed by an application
  • Run-time values for configurable system parameters
• Counters accumulate counts for activities from the time monitoring started until the time a snapshot is taken:
  • The number of deadlocks that have occurred
  • The number of transactions performed on a database
  • The amount of time an application has waited for locks

Many snapshot request types and logical data groupings can be created. Snapshots can be requested at different levels from tables through the database manager. For more information, see the System Monitor Guide and Reference. For a listing of snapshot request types and logical data groupings, see "Snapshot Requests" in the System Monitor Guide and Reference.

To make a snapshot request, use the db2GetSnapshot() command or invoke the GetSnapshot() API. The API allows you to specify several snapshot requests. For a coding example of db2GetSnapshot(), see "Making a Snapshot Request," in the System Monitor Guide and Reference.

A snapshot returns its data as a self-describing stream in the user-supplied buffer. Data is returned in logical groupings. Each item returned by a snapshot request contains fields that specify its size and type for processing. For more information, see "Reading the Snapshot" in the System Monitor Guide and Reference.

Using the Event Monitor

You can request the database manager to automatically log data when specific events occur. The event monitor writes out database system monitor data to either a file or a named pipe when one of the following events occurs:

• End of a transaction
• End of a statement
• A deadlock
• Start of a connection
• End of a connection
• Database activation
• Database deactivation
• End of a statement's subsection (when a database is partitioned)

An event monitor effectively provides the ability to obtain a trace of the activity on a database. This section briefly describes how to review the contents and format of the trace produced by an event monitor. Using the CREATE EVENT MONITOR statement, you can modify the trace to help diagnose your problem.

Output Records

The output of an event monitor is a binary stream of logical data groupings that are exactly the same for both pipe and file event monitors. You can format this trace using the db2evmon productivity tool.

Records in a trace can be divided into the following types:

• Monitor information, which identifies the version level of the event monitor
• Prologue information, which is generated when an event monitor is activated
• Actual content information, which is generated as events occur
• Epilog information, which is generated when a database is deactivated

Each record includes the application handle and application ID. These allow you to correlate each record with the application for which the record was generated. The application handle (agent_id) is unique system-wide for the duration of the application. The application ID is a string identifier that includes a time stamp and is guaranteed to remain unique, even after stopping and restarting the database manager.

For more specific information, see "Output Records" in the System Monitor Guide and Reference.

Event monitors return data in a self-describing format. That allows you to perform several useful tasks:

• Read the log stream header
• Read the log header
• Read the data stream
• Swap bytes in numerical values

For more information, see "Reading an Event Monitor Trace" in the System Monitor Guide and Reference.