I want to know when an index is not used.
I have a composite index on columns abc.

I run several queries that attempt to scan the following columns:

a,b,c (of course the index is used)
a,b (still uses index)
a,c (I don't think the index will be used)
b,c (I don't think the index will be used)

There is no guarantee that DB2 will use any particular index, but if there is only one index on a table and it is on columns (a, b, c) then the following predicates will use the index as follows:

a,b,c - matching index scan on 3 columns using b-tree

a,b - matching index scan on 2 columns using b-tree

a,c - matching index scan on 1 columns using b-tree

b,c - non-matching index scan (reads entire index sequentially)

Note that on (a,c) that it depends on what the cardinality (number of unique values) of what the first column is. If there are only a few unique values of column a, then DB2 might choose not to use the index because it would not likely reduce the number of (usually 4K) data pages in the table that have to be accessed.

DB2 keeps statistics in the catalog (updated with runstats) on the cardinality of the columns and of the index taken as a whole (fullkeycard), and the first column of a multi-column index (firstkeycard).

Your guesses are correct.

Use dynexpln utility to generate expain plans and see for yourself. It's described in detail in "Administration guide: Performance".

Your guesses are not necessarily correct, because they depend on the cardinality of the columns, the distribution of the data (number of occurrences of each value), and the clustering of the table.

That's right. I'm sounding a bit peremptory on Fridays