Section 4.6. Monitoring Tools | Understanding DB2: Learning Visually with Examples (2nd Edition)

4.6. Monitoring Tools

As a database administrator, it is important to have a good understanding of your database environment. Some of the things you need to know are the kinds of activities that are happening on the system, the database workload, and memory usage. The Monitoring tools watch the database based on the criteria you provide and generate reports.

The graphical Monitoring tools consist of the Activity Monitor, Event Analyzer, Health Center, Indoubt Transaction manager, and the Memory Visualizer. In addition, DB2 Version 8.2 introduces a new command line Monitoring tool called db2pd.

4.6.1. The Activity Monitor

The Activity Monitor helps you monitor the performance of your application and database by tracking monitor data. Among other things, it can help you monitor application concurrency, resource consumption, SQL statement usage, and lock-waiting situations.

You can start the Activity Monitor either from the DB2 menu or using the db2am command. When you start the Activity Monitor, the Set Up Activity Monitor Wizard appears. This wizard prompts you to select from a set of predefined monitoring tasks, as shown in Figure 4.34.

Figure 4.34. The Set Up Activity Monitor Wizard

The data that is monitored depends on the monitoring task you select, which can be either predefined by DB2 or user-defined. After setting up your Activity Monitor, you can produce a report. The report shows the results of the selected monitoring task.

Figure 4.35 shows a sample report produced by the Activity Monitor, based on the selected monitoring task Tuning the dynamic SQLstatement cache . In Figure 4.35, each executed dynamic SQL statement is listed in the left-most column. The other columns display the system resources that have been consumed. Click on the Details and Recommendations tab to get more information on the selected report.

Figure 4.35. An Activity Monitor report

To use the Activity Monitor, you must set the following database manager configuration (DBM CFG) parameters to ON: DFT_MON_BUFPOOL, DFT_MON_LOCK, DFT_MON_SORT, DFT_ MON_ STMT, DFT_MON_ TIMESTAMP, AND DFT_MON_UOW.

4.6.2. Event Analyzer

You use Event Monitors for troubleshooting performance problems. After creating an Event Monitor with the CREATE EVENT MONITOR statement, the output file produced (normally with an .EVT extension) can be analyzed with the Event Analyzer. You can also create Event Monitors in the Control Center by right-clicking on the Event Monitors folder for a selected database and choosing Create. They can later be analyzed with the Event Analyzer, as shown in Figure 4.36.

Figure 4.36. The Event Analyzer

In Figure 4.36, the Event Monitor evmon1 was created to detect deadlock events. The Event Analyzer is showing two time periods for which this type of information was collected. Event Monitors are discussed in more detail in Chapter 16, Database Performance Considerations.

4.6.3. Health Center

Use the Health Center GUI tool to set up thresholds for key performance and database health indicators, and to notify you when these thresholds have been exceeded. This tool provides recommendations and allows you to take action to correct the problem. In other words, you can have the database manage itself! Figure 4.37 shows the Health Center.

Figure 4.37. The Health Center

4.6.4. Indoubt Transaction Manager

You can use the Indoubt Transaction Manager to help resolve transactions left in an indoubt state during two-phase commit processing. An indoubt transaction is one that has been prepared but has not been committed or rolled back. This can happen if an error occurs while processing statements against any database in a transaction that updates multiple databases. You can right-click on a transaction and then choose to Forget, Commit, or Rollback the transaction.

Figure 4.38 shows the Indoubt Transaction Manager with no indoubt transactions listed.

Figure 4.38. The Indoubt Transaction Manager

4.6.5. The Memory Visualizer

The Memory Visualizer tool helps you track and graphically display the memory used by DB2. The tool can help you detect memory problems. You can also modify the memory settings within the tool. Chapter 15, The DB2 Memory Model, discusses the memory usage by DB2 and the db2mtrk memory tracker tool, which is a text-based tool equivalent to the Memory Visualizer. Figure 4.39 shows the Memory Visualizer. In the figure, the buffer pools and Lock Manager Heap are being plotted in the graph at the bottom of the window every five seconds. In both cases, the graph shows a straight line indicating the memory usage for these two is not increasing.

Figure 4.39. The Memory Visualizer

4.6.6. The db2pd Tool

The db2pd tool is a command line tool that retrieves statistics, including memory usage statistics, from a running DB2 instance or database. For example:

To display the operating system information, issue
```
 db2pd osinfo 
```
To display all instance-related information, issue
```
 db2pd inst 
```
To display all database-related information to the sample database, issue
```
 db2pd db sample 
```

Use the db2pd help command to display all the available options. This tool is not available through the graphical interface.