Observation: Subquery Uses IN
You coded a correlated subquery or a subquery using the IN predicate. The DB2 optimizer in Db2 Version 5 will not consider available indexes in either type of subquery. Starting in Db2 Version 6, the optimizer will consider available indexes if the subquery is not correlated.
The following is an example of a subquery using IN:
SELECT empno, lastname
FROM T1
WHERE workdept IN
(SELECT deptno
FROM T2
WHERE deptname = 'X');
Consider recoding the subquery as a JOIN to allow the Db2 optimizer to consider an available index.
SELECT empno, lastname
FROM T1, T2
WHERE workdept = deptno
AND deptname = 'X';