They've just executed my request to run "db2advis" using the whole package-cache as input. Scanning through the index-advices I notice some weird things:

- include columns are nice, but including each & every column? I am pretty sure that some columns are never part of a "where" or "join on" clause and that is where the include kicks-in, right?

- there are more that a hundred indexes recommended in random(?) order. How is that list sorted? On impact? Does that mean that implementing the 1st advise has the most impact on overall performance?

In the past it happened to me once that the production instance went down while I ran db2advis. Since that time I am very reluctant to use this tool. Do you share that feeling or do you run it on production machines during peak-times?

Because of the above we only run db2advis as part of a scheduled system-down. So we:
1. stop the application servers
2. run db2advis on package-cache
3. stop de database

But I doubts about the package-cache. On a daily basis we execute runstats followed by a "flush package-cache" and a db2rbind. Good practice I assume..... but that "flush package cache" ever night does that also delete the input for db2advis? If so I'm only evaluating a few hours instead of a few months of processing including the month-end batches.

Please share your thoughts.

Its a great tool, but you have to look at the recommendations and make decisions based on your application and its usage. I would never, just blindly implement the suggested changes. Also, even if you decide to implement one/some/all, you need to thoroughly test those changes to ensure it does not cause more performance woes. As far as the order of recommendations, I'll leave that to someone else.
Daily runstats may be a bit of over kill. Most places I have seen will run them weekly or monthly or on Z/OS maybe only as part of a load or reorg, and then rely on RTS the rest of the time.
Also, if you are going to runstats, you have to be willing to reorg as well. Say some special task was run last night that performed millions of updates and deletes against some table(s) and now the table is in real need of a reorg, you have now collected stats and the optimizer decides its not going to use the index it used yesterday and opts for some very poor access path, when it still could have used the poorly organized index for much faster access.
Dave Nance

We reorg (offline) once a week (all the table except those which take longer than 1 minute). Runstats every night. When we skip runstats for whatever reason we get signals about degrading performance, so we are not going to change that.

Nobody knows about the possible relation between "flush package cache" and db2advis using the cache? Too bad

I have an answer for the include columns question: Include columns mean that the column values are copied into the index but those values are not used as sort criteria. They can and will be used if a query just touches the indexed columns and the include columns but nothing else. In such a situation, DB2 may not have to fetch the rows from the data pages and can work with index-only access. That's where the performance improvement will come from. However, the drawback is clearly that index entries are wider and, thus, less index entries fit on a page, which results in a less-compact index compared to one without any include columns. So you have to make the judgment yourself whether the benefits outweight the costs.

As already mentioned by Knut, db2advis tends to favor "index only access" to avoid reading the table page, so that is one reason why they recommend a lot of wide indexes.

Personally, I find that db2advis does not produce optimum solutions for varied workloads. The biggest danger to watch out for is when db2advis recommends creating a lot of very similar indexes, when one compromise index would be much better in terms of overall performance and storage costs (db2advis does not like to compromise). db2advis doesn't automatically take into account things like the cost of insert, update, or delete statements to maintain the data, nor does it consider other select statements unless they are included in the same workload.

db2advis is sometimes a good tool used to suggest an index for a very complex (and poorly written) SQL statement, where it is not so obvious what index should be created to optimize it, but I don't take the recommendations as gospel. It is also useful for novices who can't intuitively understand what indexes would be required to optimize even a relative simple SQL.