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