All DB2 users has been told to use COLX IN (2,10) instead of COLX = 2 OR COLX = 10. When DB2 version 5 was introduced, DB2 became extremely good at using indexes in combination with IN lists.

Assume INDEX1 on table TABLEX consisting of column COLA followed by COLB. The following SQL statement will most likely result in a MATCHCOLS of 2:

SELECT COLA, COLB, COLC
FROM TABLEX
WHERE COLA IN (15, 20, 50, 99)
  AND COLB = '2000-01-01'

This is exceptional, isn't it. Now assume TABLEY having a column COLA defined exactly the same way as COLA in table TABLEX (NOT NULL and NOT NULL WITH DEFAULT is exactly the same thing, while NULL and NOT NULL are different). Now you can try to guess the MATCHCOLS value for the outer SELECT in the following SQL statement:

SELECT COLA, COLB, COLC
FROM TABLEX
WHERE COLA IN (SELECT COLA FROM TABLEY)
  AND COLB = '2000-01-01'

The result is also MATCHCOLS = 2. The DB2 lab deserves at least one exclamation mark for this achievement. At one of the installations I have been working with we managed to reduce the elapsed time for a job on the critical path from one hour to five minutes using the technique in the latter SQL statement. All other DB2 tricks I know of were tried and failed. This new trick only works, when you do not need to join TABLEX and TABLEY as you would have to in order to SELECT columns from TABLEY.