16.2.1 DB2 Monitoring
DB2 should collect information from the database manager, its databases, and any connected applications. With these functionalities, we can achieve:
- Problem determination
- Performance management
Use system resources optimally, and helps ensure that some problems are avoided. By using performance management information and techniques, you can try to avoid some time on problem determination and increase overall user satisfaction.
- Trend analysis
Historical data is kept and used to determine growth and trends in usage. Trends help you identify changes in overall system activity and plan hardware upgrades if they are needed.
In DB2, there are two primary tools with which you can access system monitor information, each serving a different purpose:
- The Snapshot monitor enables you to capture a picture of the state of database activity at a particular point in time (the moment the snapshot is taken).
- The Event monitor logs data as specified database events occur.
The system monitor provides multiple means of presenting monitor data to you. For both Snapshot and event monitors you have the option of storing monitor information in files or SQL tables, viewing it on screen (directing it to standard-out), or processing it with a client application.
Collecting system monitor data introduces processing overhead for the database manager, which not only consumes more CPU resources, but also increases memory consumption to store the collected data.
In order to minimize the overhead involved in maintaining monitoring information, monitor switches control of the collection of potentially expensive data by the database manager. Each switch has only two settings: ON or OFF. If a monitor switch is OFF, the monitor elements under that switch's control do not collect any information. There is a considerable amount of basic monitoring data that is not under switch control, and will always be collected regardless of switch settings.
The typical information that you can get from the DB2 system monitor can be classified as listed in Table 16-1.
| Monitor switches | Information collected |
|---|---|
| Buffer Pool | Buffer pool usage statistics |
| Lock Info | Number of locks that have occurred and deadlocks |
| Sort Info | Sort overflows, number of sorts |
| Statement | Seeing what SQL statements are currently running on the DB2 server |
| Timestamp Info | Timestamp information |
| Unit of Work | Statistics for units of work included in start and stop time and status |
You can use the GET SNAPSHOT command to get snapshot information once you have turned on the switches.
To check the status of the monitor switches, you can use the get monitor switches command.
