Upgrading a database for a J2EE software product from v8.1 to v9.1. Cannot modify the query in vendor code. The optimization level is 5. The tables have been altered to volatile. The rootid are primary keys. I want the query issued from Java to use us_usertab(rootid) index, and the usertab(us_user) index. However it seems that due to the IN clause, the query is executed using the usertab(rootid) index instead. I did the runstats with distribution and all detailed indexes and all key columns. I even went as far as updating the cardinality to be very high, reorging the table around the indexed columns that I want to use, and still no effect. One thing I have not tried is to temporarily increase optimization level to 9. Certainly the application team does not like the idea of changing the optimization level.

Anything did I miss? I suspect some configuration parameter problems because this IN query should be simple enough for DB2 optimizer to analyze.

SELECT us_1.rootId FROM UserTab Use2 , us_UserTab us_1 WHERE (Use2.rootId LIKE '%.9m' OR Use2.rootId LIKE '%.2h' ) AND (Use2.us
_User = us_1.rootId AND us_1.rootId IN ('7x1n7yt.b7', 'tbsr.b7')) AND (Use2.us_Active = 1) AND (Use2.us_PurgeState = 0) AND (Use
2.us_PartitionNumber = 2) AND (us_1.cus_Active = 1) AND (us_1.cus_PurgeState = 0) AND (us_1.cus_PartitionNumber = 0)

Il 08/10/2010 04:05, harry ha scritto:
Did you run an Explain on your query ?
IMHO the part (Use2.rootId LIKE '%.9m' OR Use2.rootId LIKE '%.2h' )
should be checked...
OR and LIKE toghether are no good for DB2 from an index point of view.

Bye
Fabio

On Oct 7, 9:05*pm, harry <u... (AT) compgroups (DOT) net/> wrote:
do you want to try some composite key on these two tables?