We Help

Thank you for reading our ad in IBM Database Magazine.

Here are some quick tips!

For DB2 Performance

Having the right indexes in place is imperative to DB2 Performance. Having the wrong indexes defined can be very injurious to performance and efficiency.

For Your Marriage

His Tips

Listen attentively to her and don't try to solve her problems unless she asks.

Her Tips

Men are problem solvers with shorter attention spans. Give him a bone and keep it brief.

For Your Automobile

Efficiency Tips

Properly inflate your tires and save 3% per year on fuel costs
Get the junk out of your trunk and save another 1-2% per year
Perform tuning and maintenance on regular schedules and save another 3% per year

DB2 LUW Performance Tools and Solutions

Optimize IT Costs and Existing Assets

Improve Business Peformance, Response Times, Query Throughput

Index Design Tips

Index Cardinality should be a high percentage of Table Cardinality. Low cardinality indexes provide very little value, if any, for retrieving data, and they are extremely expensive to maintain on INSERT, UPDATE, and DELETE statements. Drop indexes having cardinality < 5% of the table's cardinality. SEE QUERY BELOW.
Use composite (multi-column) indexes in place of single column indexes. Composite indexes generally guide DB2 to the answer sets with greater precision, more quickly, at lower costs.
Drop redundant indexes. If INDEX1 is defined on columns C1 and C2, and INDEX2 is defined on columns C1, C2, and C3, then INDEX1 should be dropped immediately without hestitation.
Beware of indexes with skewed distributions. These indexes might be helpful for data values having low cardinality (such as an index on US State attempting to find Wyoming residents), but the helpfulness of these indexes for data values that occur frequently (such as an index on US State attempting to find California residents) is marginal at best. Worse, skewed distribution indexes can be incredibly expensive to maintain on INSERT, UPDATE, and DELETE because long rid lists must be maintained in sorted sequence for each distinct value in the index.
Contrary to DB2 on the mainframe, small tables should have indexes defined. We have seen enormous CPU savings and response time improvements by indexing tables with as few as 1, 32, and even a few thousand rows. Believe it or not, small tables that could fit on a thumb drive often cause a database the most pain.

Index Performance Magic

On your DB2 9.1 and higher databases, run the query shown below to find indexes that should probably be dropped. This query first finds the Top 10 most highly written to tables in your database, and then it will examine the DB2 catalog to find indexes with extremely low cardinalities that are defined on these Top 10 tables. If you drop these indexes, you should enjoy reduced locking problems and greatly reduced CPU consumption. With CPU capacity restored back into the server, your application should run faster overall. And, congratulations to you if this query returns zero rows!

Image of Query to Find Low Cardinality Indexes defined upon highly written to tables.

Let's make it easy for you. Right Click, Download, and SAVE AS... DropIndexMagic.sql

SQL Snapshot Toolbox

If you would like to obtain additional helpful SQL Snapshots, we have a SQL Snapshot Toolbox available that includes several SQL commands for performance analysis plus a PowerPoint presentation that contains more helpful tips and tuning information. This Toolbox is priced right (almost free) for extremely tight budgets, just $29.95. 50% of the revenue we collect from this limited time offer is being donated to charity. As for the other 50%, well, we're using that to buy snacks for our company break rooms. >>> Get the SQL Snapshot Toolbox <<<

Optimize Performance :: GET STARTED!!!

Learn more about DBI's Brother-Panther® for SQL/XML Statement Performance Analysis, Automated Tuning, Database Performance Analysis, and Performance Trending, Brother-Thoroughbred® for Response Time analysis and SLA attainment tracking, Brother-Hawk™ for robust lights out health and efficiency montitoring, and Brother-Eagle® for real-time database activity monitoring.
DOWNLOAD a FREE TRIAL and discover the value of working together with DBI
Contact DBI and we can have a chat about how we can best help you.