I often manipulate with DB2 SQL statements to acheive a better access path than the one presented to me by DB2 in the first place. As described in week 42 you cannot see every detail of an access path in the PLAN_TABLE. In the manual "Application programming and SQL guide" it is explained that predicates on the same stage are evaluated in the order they appear in the SQL statement. What I present in the rest of this tip may be subject to change from one version of DB2 to another, so please check against your own DB2 to verify if the tip is still valid.

Consequently when you have more than one predicate in your SQL statement against the same table, the first predicate should be the predicate that is most often false because it filters out most rows. This has some funny consequences like when you use a WHERE A > 5 AND A < 10. Such two simple predicates can give different access paths depending on runstats information for column A. If LOW2KEY is 0 and HIGH2KEY is 100, DB2 will calcualte that 94% of all the rows are greater than 5 and therefore DB2 will not select an index where A participates. The fact that A also should be less than 10 is not evaulated, because column A has already been evaluated.

IF (LOW2KEY, HIGH2KEY) = (-90, 10) then DB2 will calculate that 4% of the rows are greater than 5 and consider using an index. And what should you do about this situation. First of all you should consider using BETWEEN where both values are evaulated. If this is not possible for some reason you should put the predicates in the order dictated by the values of LOW2KEY and HIGH2KEY to give the best result. In the fist example it will be better to write WHERE A < 10 AND A > 5 even though it is not very logical.

I have also found out that DB2 has trouble using values outside the range from LOW2KEY and HIGH2KEY. Let's assume (LOW2KEY, HIGH2KEY) = (1000, 10000) and the predicate A > 11000 (or A < 0). Even though column A is a participant of an index, DB2 will not consider using an index for A in this case. According to my calculations DB2 must assume that 0% of the rows will match the predicate. If you have such a problem then try using A > 9900 AND A > 11000, now DB2 indeed will consider using an index for A.

I will consider the last problem as an error in the optimizer, so it will probably work OK on your installation. I have described the examples using integers, but my encounters with the problems has all been with timestamp columns. Maybe DB2 works all right with integers and other data types. If you have a problem with the access path that DB2 chooses you should try and rearrange your predicates. I have even heard that in some very rare cases the order in which the tables appear in the from clause can influence access path selection.