IBM Support

Using IBM Performance Analyst to Analyze DB2 Snapshots

Technical Blog Post


Abstract

Using IBM Performance Analyst to Analyze DB2 Snapshots

Body

The database is central to the functionality of IBM Tivoli service management products. It stores all data that is collected and calculated by the applications. This database also stores metadata for configuring and maintaining the environment. Reporting also generates resource-intensive management reports. Because all functionality is based on database performance, the database can be a key focus for performance tuning.

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:
  • db2 update monitor switches using bufferpool on lock on sort on statement on table on uow on
  • db2 -v reset monitor all
  • Allow the system to process for a period of time (e.g. 15 minutes)
  • db2 -v get snapshot for dbm > snapshot.txt
  • db2 -v get snapshot for all on database-alias >> snapshot.txt
  • db2 update monitor switches using bufferpool off lock off sort off statement off table off uow off
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://ibm.biz/BdxDbg. One of the features of the tool is DB2 snapshot file analysis.

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:
image

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Then, click File --> Open and select the DB2::Snapshot plug-in and click OK:
image
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 Navigate to the snapshot file, select it, and click Open:
image

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Performance Analyst will process the snapshot file and present you with a window similar to the following:
image

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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:
image
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Cells highlighted in pink are considered to be warnings, while cells highlighted in red are considered to be critical alerts. Alerts are generated based on rules written for each plug-in. The tool comes with a set of rules based on IBM Tivoli service management products best practices; however, these rules can be modified or new rules added if desired.

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:
image
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Now, let’s take a look at the Statement tab for the snapshot file:
image
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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 ('RESOLVED','CLOSED') and domainid in 'INCIDENTSTATUS')  and (internalpriority=1 or internalpriority=2) for read only with UR

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.

Hopefully, this example has illustrated the power of IBM Performance Analyst in assisting with DB2 snapshot analysis. And, remember, this is just one of the features of the tool for assisting with analyzing performance data.

For more information on IBM Performance Analyst, see the announcement blog entry written by Brian Tsai.

 

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSLKT6","label":"IBM Maximo Asset Management"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]

UID

ibm11134423

Page Feedback