In DB2, what are the alternative ways of evaluating an index's (or several indexes) benefit?

Question

I'm trying to evaluate different approaches to analyzing the benefit of changing the set of indexes that are available. If I pick some representative queries from my workload, I can run EXPLAIN and check if the plan of each changes when I add/drop an index. However, this may be too tedious if I have, say 100 queries. I was wondering what available alternatives (tools, scripts, etc.) exist and what are their pros/cons.

Answer 1

The best way to do this for a DB2 database running on Linux, UNIX or Windows is use the DB2 Design Advisor.

This tool (included with DB2) allows you to specify a workload (which could include hundreds of queries), and it will analyze the workload and the existing database to recommend indexes and/or other physical design changes to optimize the workload.