REVIEWER: John Aragi, database manager for the Hanover Insurance Group.

BACKGROUND: The Hanover Insurance Group was founded in 1852; its main offices are located in Worcester, Massachusetts. The Hanover ranked among the top 35 property and casualty insurers in the U.S.


PROBLEM SOLVED: Hanover brought BMC APPTUNE for DB2 in for a product trial about eight years ago. The company was developing an application that used mostly dynamic SQL. We really needed a product that would tell us what was going on inside DB2, how long each call to the database took and then collect performance statistics without consuming too many resources itself. Most importantly, we wanted a product that stored the information historically, so that we don't have to plan out tests that re-create problems and set traces on. We do plan performance tests and actively monitor them with BMC APPTUNE, but many times a problem will occur that you can't re-create. Going back through the history and having the ability to see what happened is a key functionality for us. Having history is very helpful with dynamic SQL because the statement can potentially acquire a different access path when it runs next. It's better to see what happened if you can, rather than attempt to re-create it. When a developer asks for help with a databases-tuning problem, we don't ask him or her to run the SQL again. We ask, "When did it run last?" BMC APPTUNE gave us quick access to performance data that is always available because it collects in the background. We currently use two-hour time intervals, which are customizable.

PRODUCT FUNCTIONALITY: BMC APPTUNE provides several different views of summary data. From these views, you can drill down to individual statements. A DBA can view all activity at the subsystem level and drill down to a user, plan or package and find the most resource-intensive statements. Many times you do find one statement in a program that has a tablespace scan or some other problem - the one bad apple. We've resolved numerous performance problems over the years. BMC APPTUNE helps us keep our applications within response time thresholds. We also use the batch reporting feature with predefined application groups. BMC APPTUNE also allows you to create groups, so if you want to view several IDs as one application group and summarize the data, you can. We have several groups set up and run nightly reports for long-term historical trend analysis of that application. We can watch the growth over a period of months or years. We feed the data into one of our business service management tools, which allows us to better align our IT with our business goals.

STRENGTHS: What we like most about BMC APPTUNE is the summarization of data. We recently had a performance problem made evident by the use of a large amount of CPU resources. With BMC APPTUNE, we could see a unique SQL statement that used an almost negligible amount of CPU each time it ran, but it was running thousands of times per hour. Seeing the total resources used summarized over an hour, two hours or sometimes the whole day is quite an eye opener. It takes the burden of extensive analysis off the DBAs and frees them up to do the tuning rather than spending time searching for the problem. BMC APPTUNE also gives great recommendations on SQL tuning and quick access to plan data.

WEAKNESSES: BMC APPTUNE is not a thread analyzer. Its power is in summary of information by preset time intervals. You lose the fine detail when looking for a specific-point- in-time issue related to a thread.

SELECTION CRITERIA: The historical data capture and summary by unique dynamic statement and user are key features. It collects both dynamic and static SQL and has batch reporting capabilities. It's also very customizable.

DELIVERABLES: Deliverables include performance data, performance reports (both batch and interactive) and reports by application and user.

VENDOR SUPPORT: The vendor always responds promptly and takes ownership of problems. They follow through until resolution is complete. Our technical representatives are an excellent source of information. They train you and follow up to make sure you're satisfied. We always feel our opinion is appreciated and respected. Some of our suggestions were built into the product.

DOCUMENTATION: The product has adequate documentation in the manual and help text. Much of the product is intuitive and easy to use.

BMC Software
2101 CityWest Blvd
Houston, TX 77042
(713) 918-8800

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access