Apr 6 ’11

Technical Insights: Save MIPS With Effective DB2 for z/OS Indexes

by Editor in z/Journal

The primary cause of inadequate DB2 application performance is inefficient SQL. Poorly performing SQL degrades response times and typically consumes unnecessary resources. One way to improve SQL performance is to add effective indexes.

A DB2 index is a list of the locations of rows sorted by the contents of one or more columns. Indexes improve query performance, but they come with a price. Indexes use disk space and increase the cost of insert, delete, and some update operations. When you design indexes, balance costs with performance. You need to maximize index matching while minimizing the number of indexes. You need to understand how queries are using indexes. Improving index usage could be the most significant performance enhancement you make.

You can create multiple indexes on a base table. However, the more indexes you have, the more DB2 must work to keep the indexes current. Creating multiple indexes for a table with heavy updates can slow processing and lead to longer outages during maintenance work-like reorganizations.

Let’s look at some types of indexes:

When you have accurate catalog statistics and proper predicates, DB2 usually chooses the best index for each query. However, you may want to influence DB2 to use a particular index by:

In general, indexes improve query performance, but some indexes could actually degrade performance. How do you know which indexes you need and which indexes are causing performance problems and overhead?

A comprehensive index tool provides accurate, real-time performance information about DB2 indexes so you can evaluate the performance of your indexes and identify candidates for improvements. It collects and displays actual access counts for each unique SQL statement (table and index and predicate usage frequencies). It provides on-demand, dynamic data collection of index dependencies and catalog statistics.

An essential piece of an effective index tool is a “what if” index analysis that simulates the effects of adding, dropping, or updating statistics for an index with the use of cloned structures. Another valuable capability is the ability to analyze a DB2 workload and recommend additional indexes or indexes to be dropped based on actual execution.

Summary

By using indexes to improve SQL performance, balancing the cost of the index with the performance improvements, and looking for tools that help make the most of your DB2 indexes, you can improve DB2 application performance.