Three years ago I wrote a tip about joining columns with different definitions. This tip has long ago been outdated by new functionality in the DB2 optimizer. The CHAR function in DB2 has been able to solve the problem presented in the old tip since version 7 of DB2. The problem is now solved with a single SQL statement:

SELECT C.CUSTOMERNO, C.ZIPCODE, I.ORDERNO, I.ORDERTYPE
FROM CUSTOMER C, INTERNETORDER I
WHERE CHAR(C.CUSTOMERNO) = I.CUSTOMERNO
  AND C.INTERNETCUSTOMER = 'Y'

If I assume that there exists an index where CUSTOMERNO in table INTERNETORDER is the first column the above SQL statement is able to use this index. This is a good example of what happens with your knowledge about how DB2 is able to execute SQL statements. It grows old while time passes because new releases improves the DB2 optimiser.

With DB2 version 8 DB2 has finally reached the point where many of us expected DB2 to be for at least 10 years ago regarding the usage of indexes. In each version of DB2 new improvements of the usage of indices has arrived. These improvements are documented with a list of predicates which are able to use indices. Here is a link to the list for Version 7 and here is the list for Version 8.

Now you are able to see for yourself that significant differences has been introduced. The list has become larger because version 8 is able to do more. There is an important detail which is "missing" in version 8. If you read the notes very carefully you should focus on note 3 in version 7 regarding differences in columns definitions. This note is not present in version 8, simply because in version 8 DB2 is able to use indices between different numeric data types and between different non-numeric data types. There are (very) few limitations which are decribed in the notes.

Now back to the example: the CHAR function is able to convert a numeric data type into a non-numeric data type which makes it able to use an index. A funny detail is that the DECIMAL function is able to convert a non-numeric data type to a numeric, but is not able to use an index. This information is not stated anywhere. DB2 still does not allow comparison of numeric and non-numeric values and this will continue for many DB2 versions to come.