DB2: Finding and adding indices

DB2 has a very powerful tool (db2advice) to determine necessary indices for your DB2 database. Having a good index can have a very big boost in performance in any application. If working with high throughput systems it is crucial to have the right index. Note that my databse user is db2inst1 and database name MMS.

STEP 1: Initialize db2advis as db2inst1 database user


db2 connect to MMS
db2 -tf sqllib/misc/EXPLAIN.DDL


STEP 2: Detect the costly queries


db2 connect to MMS
db2top


In the db2top interface you have to do the following steps:

D to show queries

z to sort after column; sort after average execution time

f to freeze

L to show query: enter the id of the query from the top of the frozen window

w to write; this will save the query to a file to disk(query.sql)

STEP 3: Obtain the suggested index

First edit the file containing the query saved at STEP 2. Replace the table name with fully qualified table name.
Obtain the db2advice index suggestion by running the following:


db2advis -d MMS -i query.sql -t 5


STEP 4: Apply the suggested index

To apply the index simply execute the query generated by db2advice.

STEP 5: Update statistics

The database statistics have to be updated after a new index is added to one of the tables. Without the update the index will not have any notable effect on the performance of your problem query. In the bellow example I use db2inst1.MYTABLE as the table to which the index was added. Replace with your table name.


db2 "runstats on table db2inst1.MYTABLE on all columns and indexes all ALLOW WRITE ACCESS"