DB2 INDEX USAGE

1/ Some definitions may help
BTree index is an efficient way to store a tree. This means you can browse it from lowest to highest and back.
Other kind of index generally does not allow to browse but are faster to retrieve one single value. Hash index are good examples.
A Clustered index is an index which is although used to sort the data pages.
Data pages should be kept very dense to avoid fragmentation.
Indexes based on non selective columns are never used because query optimizer detects it will retrieve more than 12-15% of the overall data pages and start with a full scan (which is faster in this case because it is faster to read straight forward a 1Go file than reading 15% of it by small chuncks).

2/ What and when to use indexes
a) Depend on your query
Looking at a specific query, you will find that joining, criteria, grouping or sorting columns are good candidates.
==> The problem is you must "guess" how many rows are to be retrieved at every step of your query to create the best indexes...
Some rules are:
-Criteria columns are to be considered only if they are restrictive enough (<15% of the overall data rows)
-You may look at joining columns as criteria column for Inner join
-Create an index using only the most restrictive columns
-Create a cluster index on the columns you use most of the time to sort your data
-Your Grouping columns should although be ordered from the most restrictive to the least one
-A "criteria" index may be sufixed with Grouping columns and prefixed by Sorting columns
==> Always keep in mind an index is unusefull if not restrictive enough

b) Depend on how many users and updates
To avoid contention, you may use a different clustered index if too many users modified rows on the same data pages than the sorting one.
To allow for faster modifications, you may keep only the necessary indexes.

Hope this helps.