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. You should note that setting switches in this way means that they cannot be turned off without stopping the database management system. Switches are explained in "Resetting Monitor Data". For more information on configuration see the dft_monswitches configuration parameters in the Administration Guide.
To see if your database manager is currently collecting any monitor data issue the command:
db2 get database manager monitor switchesThe 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.