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:
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.
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:
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.
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 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.
There are two ways to access the monitor data collected by the database manager:
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.
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.