Administration Guide

Using the Database System Monitor

The DB2 database manager maintains data about its operation, its performance, and the applications using it. This data is maintained as the database manager runs, and can provide important performance and troubleshooting information. For example, you can find out:

• The number of applications connected to a database, their status, and which SQL statements each application is executing, if any.

• Information that shows how well the database manager and database are configured, and helps you to tune them.

• When deadlocks occurred for a specified database, which applications were involved, and which locks were in contention.

• The list of locks held by an application or a database. If the application cannot proceed because it is waiting for a lock, there is additional information on the lock, including which application is holding it.

Because collecting some of this data introduces overhead on the operation of DB2, monitor switches are available to control which information is collected. To set monitor switches explicitly, use the UPDATE MONITOR SWITCHES command or the sqlmon() API. (You must have SYSADM, SYSCTRL, or SYSMAINT authority.)

There are two ways to access the data maintained by the database manager:

• Taking a snapshot

  Use the GET SNAPSHOT command from the command line; the Control Center on the OS/2, Windows 95, or Windows NT operating systems for a graphical interface; or write your own application, using the sqlmonss() API call.

  The Control Center, available from the DB2 folder or with the db2cc command, provides a performance monitor tool that samples monitor data at regular intervals by taking snapshots. This graphical interface provides either graphs or textual views of the snapshot data, in both detail and summary form. You can also define performance variables using data elements returned by the database monitor.

  The Control Center's Snapshot Monitor tool also allows you to define exception conditions by specifying threshold values on performance variables. When a threshold value is reached, you can predefine any of the following actions to occur: notification through a window or audible alarm, logging of a record in a database, and/or execution of a script or program.

• Using an event monitor

  An event monitor captures system monitor information after particular events have occurred, such as the end of a transaction, the end of a statement, or the detection of a deadlock. This information can be written to files or to a named pipe.

  To use an event monitor:

  1. Create its definition with the Control Center or the SQL statement CREATE EVENT MONITOR. This statement stores the definition in database system catalogs.

  2. Activate the event monitor through the Control Center, or with the SQL statement:

       SET EVENT MONITOR evname STATE 1
     

     If writing to a named pipe, start the application reading from the named pipe before activating the event monitor. You can either write your own application to do this, or use db2evmon. Once the event monitor is active and starts writing events to the pipe, db2evmon will read them as they are being generated and write them to standard output.

  3. Read the trace. If using a file event monitor, you can view the binary trace that it creates in either of the following ways:

     • Use the db2evmon tool to format the trace to standard output.

     • Click on the Event Analyzer icon in the Control Center (on the Windows 95, Windows NT, or OS/2 systems) to use a graphical interface to view the trace, search for keywords, and filter out unwanted data.

For information on the system database monitor and the event monitor, see the System Monitor Guide and Reference. For a scenario of how to use them from the Control Center, see the Administration Getting Started.

[ DB2 List of Books | Search the DB2 Books ]