It is a quite risky affair to re-publish tips originally written in danish almost three years ago. But in this case I will do it anyway, though the tip might not work anymore, because I haven't checked it and because the DB2-optimizer might have changed behaviour.

I have an ongoing battle with the DB2-optimizer to make it choose the best access path, which it doesn't always do despite what IBM tells us. In this case I will present two ways of using an IN-list that might lead to different access paths. This is done by using both host-variables and constants in the same IN-list in different order.

Let me illustrate the problem:

SELECT * FROM MYTABLE
WHERE FIRSTINDEXCOL IN (:hostvariable, 'SOME-VALUE')

The column FIRSTINDEXCOL is the first column in an index and has an uneven distribution of values registered in SYSIBM.SYSCOLDIST because a RUNSTATS has been run against MYTABLE. Depending on how uneven the distribution is the optimizer might choose another access path if you write IN ('SOME-VALUE', :host-variable).

Now it is up to you to decide which access path you like the most. If your SQL statement right now says FIRSTINDEXCOL = :hostvariable, then you can try IN (<CONSTANT>, :hostvariable) where you are sure, that <CONSTANT> is not a value represented in FIRSTINDEXCOL.

Another possibility is to consider using the BIND parameter REOPT(VARS). In this case DB2 will decide the access path at run time based on the actual value of your host variable. The most important thing is that you try and see how the different access paths works for your application. Most of the time the decisions made by the optimizer are quite good and not as bad as we try to make them.