With the help of db2advis I found out that on one query we should have 2 indexes:


I would like to create those indexes and would like to show to the application team that after making the indexes the query will take less time.
I would like to get the execution time before creating indexes and after creating indexes.

How should I do it?

Which DB2 version and OS?

Andy

Use the db2batch utility. It allows you to run a sample workload, possibly with varying parameters, and reports execution time summary. You will find more details in the manual.

explain shows a number of timerons and if indexes were used. you can compare the timeron values before and after to prove that there is a performance improvement and indexes were used.

Unfortunately, this is a common misconception. Cost estimate does not prove anything; it indicates that there might be a possible performance improvement, from the optimizer point of view. It may or may not reflect the actual change in query performance.

Only by running the actual workload on the actual system you can prove to yourself and to others that query performance has improved.

and if you don't current statistics, you may not get any benefits from additional indexes because DB2 doesn't know how selective they may be

I am using DB2 9.1 FP4 on Solaris.

How many indexes do you already have? Do you need all of them? Are your stats upto date? How big is the table and is it partitioned or not? Just because you were told to build indexes , does not mean you have to.

We have one indexes already existing which db2advis shows that we should drop it as it is unused. Table has approximately 1250000 rows. The table is not partitioned. Statistics are updated every week.

set up db2pd to capture information on index usage to verify that that index is not used for sure before you drop it.

Is this SQL a one time deal or it is going to be run on the regular basis? If later, then you might want to create it.

How many tables are joined in your SQL? Are they joined correctly?