IBM Books

Administration Getting Started

Monitoring Performance

This section provides a brief overview of the Snapshot Monitor and the Event Monitoring tools. It then describes how to set up a simple monitoring session using the Snapshot Monitor, and how to setup a simple monitoring session using an Event Monitor and the Event Analyzer.

The Snapshot Monitor and Event Analyzer provide the following benefits:

You use the Snapshot Monitor when you have a problem that is occurring now. It lets you take a snapshot of database activity and performance data at a point in time. You create an event monitor when you need to know completion information such as how long a transaction took or how much CPU a statement used. You then use the Event Analyzer to read the data recorded from the event monitor.

Figure 25 illustrates these concepts. With the Snapshot Monitor, for a given point in time, a snapshot is returned for a performance variable. The Snapshot Monitor displays these points for comparison over time. Each point on the graph represents a data value. The steps for using the Snapshot Monitor are provided in "Monitoring Performance at a Point In Time".

With an event monitor, two events are being monitored for a given database, connections and statements. For each database connection, there is one connection event record produced. For each statement executed in that connect, there is a statement record produced. Each connection event record maps to one row in the Connections View window of the Event Analyzer. This window shows information for each application that connected during the monitored period, including:

Each statement event record maps to one row in the Statements View window in the Event Analyzer.

The steps for creating an event monitor and viewing the resultant data using the Event Analyzer are provided in "Analyzing an Event for a Period of Time".

Figure 25. Comparison: Snapshot Monitor and Event Monitor Tools (Event Monitor, Event Analyzer)


* Figure SQLK0SNP not displayed.

Considerations for Monitoring and Tuning a Database

Consider the following before monitoring and tuning a database:

The next section describes how to set up a simple monitor session using the Snapshot Monitor, and how to use the Alert Center to keep track of any performance-related problems.

For details of how to obtain the monitor data, analyze the monitor information, determine what changes are required, and implement the changes, see the online help for the DB2 administration tools.

The monitoring tools are optionally installable wherever you can install the Administration Tools. Chapter 1. "DB2 Administration Tools and Basic Concepts" describes where you can install the Administration Tools.

Monitoring Performance at a Point In Time

Use the Snapshot Monitor if you want to do complex data collection and analyze the data to pinpoint potential problems. You can watch performance data change over time.

The Snapshot Monitor lets you:

The following types of information are captured:

Taking snapshots at predefined intervals provides a picture of the current state of the activity in the database manager and its applications. This information can be used to:

Performance information is available for the following database objects:

For each, a variety of performance variables can be monitored. The Performance Variable Reference Help, available from the Help menu of any Snapshot Monitor window, provides a description of all the performance variables. These variables are organized into categories. By default, all performance variables are monitored, but the categories can be turned on and off through the administration tools. The following categories have been set on by default:

From the Control Center, you can have only one Snapshot Monitor capturing snapshots from an instance of a database manager. This means that the API that is used to get snapshot information is issued only once for all monitored database objects in a database manager. This decreases the overhead on a database manager by the Snapshot Monitor.

For detailed information on how to use the Snapshot Monitor, see its online help.

Defining a Simple Monitor Scenario

To monitor the SAMPLE database for the total number of connections:

  1. From the Control Center, open the Command Center using the icon on the toolbar and enter the following command to connect to the SAMPLE database: 
    connect to sample

  2. From the Control Center, click with mouse button 2 on the SAMPLE database and select Snapshot monitoring -> Show monitor profile. The Monitor Profile notebook opens.

  3. On the Definition page, if this is the first time you are using the monitor, accept the default profile, db2smpv, that is supplied with the Snapshot Monitor.

    A profile is associated with an instance only, not a specific database.

  4. Accept the default of 20 seconds for the Profile sampling interval. The more frequent the sampling, the more data you will collect. However, the more frequent the sampling, the higher the chance that performance will be affected.

  5. Select the Perform monitor actions you prefer. When a threshold is reached, the alert action that you specify later in this scenario will occur either once per warning or alarm, or at every sampling interval during the warning or alarm condition, depending on what you select here.

  6. Move through the notebook pages until you reach the Database page.

  7. In the Category group box, select the Connections category. It should say (On) beside it to show that this category is turned on for monitoring.

  8. In the Performance variable list, select the performance variable called Total Connections. This is the performance variable that you will monitor specifically in this example.

  9. In the Threshold group box, select the upper left check box. This activates the upper alarm threshold entry box.

  10. Type the number 0 in the upper alarm threshold entry box. You are telling the monitor that your upper threshold value for the total number of connections to the SAMPLE database is zero.

  11. When you selected the upper left threshold check box, you also activated the Upper alarm actions group box. Select the type of alarm you want in the Upper alarm actions group box. For the purpose of this example, select Show in Alert Center for the action taken when an upper threshold value is reached.

    If the upper threshold value of zero is reached, an icon will appear in the Alert Center to represent the database object (in this case the SAMPLE database) that is in a state of alert.

  12. Click on OK to save your settings and close the notebook.

  13. From the Control Center, click with mouse button 2 on the SAMPLE database and select Snapshot monitoring -> Start Monitoring from the pop-up menu to begin monitoring. The database will be monitored continuously until you select Snapshot monitoring -> Stop Monitoring.

    Performance variables will be monitored for the database at the sampling interval you selected. Some of the performance variables are:

  14. Next, you will generate some simple activity against the database. Enter the following SQL statement in the Command Center (which you access from the toolbar): 
    select * from employee order by lastname

    At this point in the scenario, the Alert Center may open automatically. Just ignore it for now and we will work with it later.

  15. To see the standard information that is collected for a snapshot taken of the database, from the Control Center, click with mouse button 2 on the SAMPLE database and select Snapshot monitoring -> Show monitor details from the pop-up menu. The Performance Details window opens and you can see all the performance information being captured for the SAMPLE database.

    1. Look for your Total Connections performance variable by scrolling down the rows. The Total Connections performance variable will have a red icon in front of it indicating that you have reached an alarm threshold and have generated an alert. The alert indicator icons are red for an alarm, yellow for a warning, and green for a value within its thresholds.

    2. Look at the column called Value. This column shows the total connections to the SAMPLE database. Look at the column called Upper alarm. This column shows that the upper alarm threshold was set to zero. The Value column is equal to or greater than the Upper alarm and therefore an alert was generated.

    As you move through the Snapshot Monitor scenario, you can use the Help menu (or F1) to open the online help and see the other tasks you can do from the window.

  16. You can see the Total Connections performance variable plotted on a graph. From the Performance Details view, select the Total Connections performance variable (and any other ones you might be interested in) and open the performance graph by selecting Performance variable -> Show performance graph.

  17. You can also see a summary of all the databases you are currently monitoring. To open a Monitored Database Summary view:

    1. From the Control Center, click with mouse button 2 on the Databases folder object and select Show monitor summary from the pop-up menu. The Monitored Databases Summary window opens. It shows the SAMPLE database being monitored. The icon beside the database name is red. This indicates that an alert has been generated for this database.

    2. Scroll across the columns of performance variables until you find Total Connections. The number for Total Connections corresponds to the number of connections you made to the SAMPLE database. The value is greater than zero and therefore an alert was issued.

    3. Later in this example, after you have looked at your performance views and the Alert Center, remember to stop your Snapshot Monitor session.

      To stop your monitoring session, from the Control Center, click with mouse button 2 on the SAMPLE database. Select Snapshot monitoring -> Stop monitoring from the pop-up menu to stop the monitoring of the SAMPLE database.

The Alert Center is a simplified view of the Snapshot Monitor that shows you the results of monitoring. It gives you a dynamic view of the database objects in trouble.

Any database you select to be monitored, or any object that is being monitored, will appear as an icon in a state of alert in the Alert Center if they reach any of their threshold values. They appear only for the period of time during which the threshold is exceeded.

If you want to keep a close watch on the objects being monitored, keep the Alert Center open. You can also modify the Control Center settings so the Alert Center opens automatically if a new warning or alarm is added to it. From the Alerts Center you can also temporarily suspend the alerts while monitoring continues.

What Do I Do When an Object Appears in the Alert Center?

The Alert Center will automatically open by default to display any monitored objects that are in a state of alarm or warning (that is, their thresholds have been exceeded). (You can change this default from the Tools Settings window.)

If you see an object in the Alert Center:

  1. Double-click on its icon. The Monitoring Details View window opens, showing all the performance variables being monitored.

  2. Check the color of the icon and analyze the data.

    The color of the icon reflects the condition of the most severe alert within a group of performance variables. A red icon indicates an alarm. A yellow icon indicates a warning.

    The data returned for the performance variable is displayed. See the online help available from the Help menu of any Snapshot Monitor window for instructions on how to analyze the data.

Analyzing an Event for a Period of Time

The Event Analyzer is also a DB2 performance tool. You use this tool when you want diagnostic information for an event that has taken place. You use the Event Analyzer in conjunction with an event monitor. For example, you can use an event monitor to trace database activity such as connections, transactions, statements, and deadlocks, while a database is active. An event monitor can also record cumulative performance data that is logged when an application disconnects from the database. After the event monitor has created the event monitor file, you look at your performance information using the Event Analyzer.

The Event Monitor tools let you perform the following:

The Event Analyzer lets you view the data generated by an event monitor for the following event types:

You can create an event monitor for the following event types; however, to view data generated for them, use the db2evmon executable (described in the Command Reference and the System Monitor Guide and Reference):

To analyze event data using an event monitor and the Event Analyzer, follow the steps below. They represent only one example of how to create an event monitor for connection and statement events.

  1. From the Control Center, click mouse button 2 on the SAMPLE database and select Monitor events from the pop-up menu. The Event Monitors window opens.

  2. Click on the Create icon in the toolbar. The Create Event Monitor window opens.

  3. Enter a name for the event monitor in the Name field, and select Connections and Statements. By default, deadlocks are monitored and monitoring starts automatically.

  4. Click on Ok. The Event Monitors window is refreshed, and now lists the monitor that you have just created.

    The Directory column lists the directory where the event monitor files will be written. Note in this simple example the default directory is being used for the files.

  5. Connect to the SAMPLE database:
    1. From the Control Center toolbar, click on the Command Center icon. The Command Center opens.
    2. Enter connect to sample and click on the large push button at the top of the window. You connect to the SAMPLE database.

  6. Generate activity against the SAMPLE database by issuing a simple query from the Command Center such as: 
    select * from employee

    Information will be gathered for connections and SQL statements:

  7. Next, turn off the event monitoring by clicking mouse button 2 on the event monitor you want to use and selecting Stop Event Monitoring from the pop-up menu.

    This forces the event monitor to write the trace file. If not turned off, a write would only occur when the buffer is full or all connections end.

  8. In this simple scenario, you can view the resultant event data by clicking with mouse button 2 on the event monitor you created, and selecting View Event Monitor Files from the pop-up menu. The Monitored Periods View window opens.

    However, in some cases you must use the Event Analyzer to view event monitor data, for example if:

    To access the Event Analyzer:

    1. From the desktop, click on the Event Analyzer icon. The Event Analyzer window opens. (You can also invoke the Event Analyzer from its icon in the Control Center's tool bar.)

    2. Enter the default directory for the event monitor in the Path field. Click on Ok. The Monitored Periods View window opens.

  9. Click mouse button 2 on a monitored period, and select Open as -> Connections from the pop-up menu. The Connections View window opens. This shows the list of connections that were made during the event monitoring session. (There may be more than one connection listed. The connection you are interested in may not be the first one in the list.)

  10. Click mouse button 2 on a connection, and select Open as -> Statements from the pop-up menu. The SQL Statements View window opens. All statements for the selected connection are displayed. Columns of information are provided for each statement, including:

The online help for the event monitor and the Event Analyzer provide detailed instructions for creating event monitors and viewing the resultant event data.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]