Learning Visually with Examples [Electronic resources] نسخه متنی

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 .

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.

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

SQL statement 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.

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

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

[View full size image]

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

[View full size image]

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

[View full size image]

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.