In DB2 it has become possible to create so-called index on expressions, which is an index where you instead of defining one or more index columns you define it using an expression. It is even possible to combine expressions and columns as you are familiar with from ordinary indexes having more columns.

And what do you use an index on expression for? I will show you an example. You want to join two tables. In TableA column KeyCol is defined as CHAR(10). In tableB columns KeyCol1 and KeyCol2 are both defined as CHAR(5). By some strange coincidence KeyCol1 contains the same value as the first five characters in KeyCol and KeyCol2 contains the last five characters in KeyCol. A join may look like:

SELECT *
FROM TableA A, TableB B
WHERE A.KeyCol = B.KeyCol1 !! B.KeyCol2

The problem is that DB2 will not use an index to carry out this join. A solution is to do it like this:

SELECT *
FROM TableA A, TableB B
WHERE substr(A.KeyCol, 1, 5) = B.KeyCol1 
  AND substr(A.KeyCol, 6, 5) = B.KeyCol2

If an index exists on (B.KeyCol1, B.KeyCol2) then DB2 is able to start in TableA and join with TableB, but if TableA contains an awful lot of rows and TableB contains very few rows then this join will take a lot of time. In this case it is much better to start the join in tableB. And you can make DB2 do that by defining the following index:

CREATE INDEX IxOnExpr ON TableB
(KeyCol1 !! KeyCol2)

By using the first SELECT statement DB2 will exploit the new index and start in TableB and join with TableA. If DB2 determines TableA to be the smallest table DB2 will now be able to start in TableA without you having to rewrite the first SELECT statement above.

I have already used index on expression in other situations than the above and it works awesome. I hope you will be able to take advantage of index on expression where it otherwise will be impossible to make DB2 choose a good access path.