Using IBM Performance Analyst to Analyze DB2 Snapshots
RickRhea 060000AVGB Comment (1) Visits (3563)
SQL query tuning and creating indexes against a database can help to obtain maximum performance from IBM Tivoli service management products. When using DB2 as the database platform for IBM Tivoli service management products, DB2 snapshots are one of the primary mechanisms you can use for ensuring that queries and indexes are tuned appropriately.
DB2 snapshots provide general statistics and information about all applications, tables, table spaces, buffer pools, and locks for a specified database. Since snapshots only capture a point in time, you must make sure that the snapshot is taken while transactions are occurring that may be causing performance issues.
The basic set of commands needed to take a DB2 snapshot is as follows:Note that there is overhead with the monitor switches, so they should always be enabled prior to taking the snapshot, and then disabled after the snapshot data is gathered.
The output from the above commands is stored in text readable format. However, parsing the file and finding the information can be tedious at best.
To assist clients with analyzing performance data, IBM Tivoli service management products recently released the IBM Performance Analyst tool which can be downloaded at: http
Once you have installed the tool and taken a DB2 snapshot, it is quite easy to open the snapshot in Performance Analyst and look for potential issues.
First, launch the Performance Analyst tool:
You can now click through the various tabs to analyze the data collected in the snapshot. You can also filter the data and show only the data flagged as having issues by clicking the Only Alerts checkbox and the bottom of the window:
Context-help is available for all snapshot metrics. Simply double click on any cell and a help window appears showing the corresponding DB2 information center entry for that cell. For example, double-clicking on hash_join_overflows in the sample above would open a window showing the following information:
By default, the tool shows the top 25 statements ordered by total CPU utilization; however, you can choose to show more rows, filter the data, show only those rows with alerts, and order by any column by clicking on the column header.
Also note that identical statements have been grouped with the top-level “root” row showing the query in a parameterized format. To see an example of the statement with actual values, just click the twistie next to the statement in the num_executions column.
In the example below, the top consuming SQL statement is:
select * from incident where status not in (select value from synonymdomain where maxvalue in ('RE
This statement was identified as a StartCenter portlet query that was written inefficiently. By re-writing the query to eliminate the use of NOT IN, the client was able to significantly improve the performance of this query, which in turn had a positive impact on performance for the entire system.