Indexing for DB2

Indexing is a way of making searches on a database faster and it's usually done by creating an index file focusing on a particular field.

In here, I'm going to be giving an example of how I managed to improve the performance of running an Impromptu report by tweaking a DB2 database file so that it has an appropriate index.

The first thing I did was to grab a copy of the SQL that the Impromptu report was using. I pasted this SQL into the IBM DB2 Connect software that allows me to run SQL code. Sometimes you have to edit the code to strip some unnecessary calculations or fields. After running the code, I click on Access Plan. This provides a graphical view of the tables and joins that have taken place. Where possible it would show the existing index tables. By click on a DB2 table, it would provide information on which index it ran against.

The important thing to note is that I mananged to locate a critical join where there were no indexes. Using the DB2 Connect software, I was able to drop in an index where I thought it was needed by going to the appropriate window, doing a right-click and add an index file on a certain key field.

With some tweaking I did manage to improve the Impromptu report so that it ran quicker.

Although the example used here was an Impromptu report, the important lesson is to consider index files when running any type of code in any language to improve performance. If things are running slow, keep indexing in mind.