Who'd have thought?
It was back in 1997 that I first presented "Tune DB2 While You Golf" at an IBM DB2 Technical Conference. The idea back then was to help people automate the analysis and tuning of their DB2 databases.
Now it's 2005, and IBM has made the vision of autonomic tuning a reality, and they continue to enhance DB2's autonomic tuning and management capabilities.
The Index Advisor was born in DB2 UDB V6, and in V8.2 IBM gave us the gift of the Design Advisor. Have you tried out this remarkable DB2 technology yet? It is by far my favorite autonomic computing feature of DB2.
I was speaking with someone at the IBM Toronto Lab recently and I shared with him a number of Index/Design Advisor success stories from past consulting engagements. He, as a key contributor towards the Design Advisor technology, was quite pleased to learn of the dramatic and substantial ways that this technology helps IBM DB2 customers.
Without naming names (we are, after all, a privacy obsessed world), I thought it might be helpful for DB2 UDB LUW users in general to be aware of some of these success stories.
Getting Business Done in a Fraction of the Time
There's a company in Maryland running SAP R3 on DB2/AIX. There was a warehouse transaction that was taking over 3 minutes to complete. An SAP America consultant had been on site working this problem for over 3 months when I showed up. I performed an SQL Workload Analysis as described by DB2 Magazine article Measure, Improve, Repeat and found an SQL statement using some 90%+ of CPU time with average response time of about 90 seconds, and this statement was run twice for each transaction. We passed this statement to the IBM Advisor (db2advis), and it provided us with an index solution that cut transaction response time from 3 minutes down to about 10 seconds. By combining SQL Equalization and Cost Aggregation workload analysis with db2advis, we solved a difficult performance problem in about two hours that another "expert" had already been working on for 3 months.
Cutting Machine CPU Utilization by 80%
There's a company in Poughkeepsie NY running DB2/AIX. Their machine was running 100% CPU busy all day and transaction response times were unacceptably slow. After performing SQL Equalization and Cost Aggregation as described by US Patent 6,772,411, a very costly SQL statement was discovered that had a very inefficient access strategy. This statement was passed into db2advis, and the Advisor provided an index solution that cut CPU utilization on the machine by more than 80%. Not surprisingly, transaction response times started to fly.
Please be aware that the Design Advisor aggressively favors Index Only Access and makes liberal use of suggesting multi-column composite indexes. The DBA should make sure that suggested indexes do not cause redundant indexes to exist, that the suggested indexes have sufficiently high cardinality relative to the base table cardinality (at least 80% as a rule of thumb), and that the suggested indexes are not subject to skewed values.
Design Advisor Help
At the command prompt, you can easily get help with the Advisor tool by using the command:
$ db2advis -h
I hope this blog post is helpful for some of the readers. Do you have a db2advis success story? Please post a brief comment and tell us about it.