DB2: Finding and adding indices

By | December 7, 2015

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

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"