System Monitor Guide and Reference

Database Manager Maintains Operation and Performance Data

Built into the database manager is the ability to collect data about its operation and performance, and that of the applications using it. The database manager maintains information at the following levels:

• Database manager
• Database
• DCS database
• Remote database
• Application (database connection)
• DCS application
• Remote application
• Table
• Table space
• Buffer pool
• Transaction
• DCS transaction
• Statement
• DCS statement
• Subsection
• Dynamic SQL package

Collecting some of this data introduces some processing overhead. For example, in order to calculate the execution time of an SQL statement, the database manager must make a call to the operating system to obtain timestamps before and after statement execution. These types of system calls are generally expensive. In order to minimize the overhead involved in maintaining monitoring information, monitor switches control the collection of potentially expensive data by the database manager.

Monitor Switches Control Data Collected by the Database Manager

The database system monitor will always collect some basic information, but you can use the switches to govern the amount of expensive data collected. Monitor switches can be set:

• Explicitly, this is usually done using the UPDATE MONITOR SWITCHES command.

  You can also set these switches in the database manager configuration file if you want data collection to start from the moment the server is started.

  Switches can be changed without stopping the database management system. This dynamic updating of switches requires that the application doing the update must be explicitly attached to the instance for the updates to take effect.

  Note:

  Any existing snapshot applications will not be affected by a dynamic update. In order to pick up the new default values for the switches, a monitoring application must terminate and re-establish its connection

  Switches are explained in Resetting Monitor Data. For more information on configuration see the dft_monswitches configuration parameters in the Administration Guide.

  Updating the switches in the database manager configuration file will update the switches for all nodes in a partitioned database if the user is attached to it.

• Implicitly, when an event monitor is activated. Event monitors are explained in Event Monitors.

To see if your database manager is currently collecting any monitor data issue the command:

     db2 get database manager monitor switches

The resulting output indicates the database manager switch settings and the time that they were turned on.

          DBM System Monitor Information Collected
 
Switch list for node 1
Buffer Pool Activity Information (BUFFERPOOL) = OFF
Lock Information                       (LOCK) = OFF
Sorting Information                    (SORT) = ON   04-18-1997 10:11:01.738400
SQL Statement Information         (STATEMENT) = OFF
Table Activity Information            (TABLE) = OFF
Unit of Work Information                (UOW) = OFF

In this example, in addition to collecting basic-level information, the database manager is collecting all information under control of the sort switch.

Note:

In a partitioned database, if a global retrieval is requested, then a switch list is returned for each node in the system (see GET DATABASE MANAGER MONITOR SWITCHES).

Accessing Monitor Data

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

• Snapshot monitoring

  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.

• Event monitors

  You can request the database manager to automatically log monitor data to files or a named pipe when specific events occur. This allows you to collect information about transient events that are difficult to monitor through snapshots, such as deadlocks and transaction completions.