DB2 7.2 (7.1.0.68) on AIX 5.2.

I have a query that does a table scan on one database, and an index
scan on another.

The statistics on both databases are up-to-date. Indexes are
identical. Only rowcounts for one of the tables vary (it's using the
index where the table has 1,000 rows, but not where it has 10,000
rows).

Can anyone shed any light on why the optimiser's not picking up the
index consistently?

Here's the offending SQL if helpful:

SELECT MSG.MSG_Q_ID
FROM GFX.VU_INTRL_MSG_Q MSG,
GFX.VU_INTRL_MSG M
WHERE (
( MSG.MEMB_ID = 500000 AND
MSG.REGN_ID IS NULL ) OR
MSG.REGN_ID IN ( SELECT USR.REGN_ID
FROM GFX.VR_USR_ROUTE USR
WHERE USR.REGN_ID = MSG.REGN_ID
AND USR.MEMB_ID = 500000
AND USR.USR_ID =
18006554984185 ) )
AND M.MSG_ID = MSG.MSG_ID
AND ( 'B' IS NULL OR
LOCATE( COALESCE( MSG.MSG_TYPE, M.MSG_TYPE ),
'B' ) > 0 )
AND ( 'DAN' IS NULL OR
LOCATE( M.MSG_CATGY, 'DAN' ) > 0 )
AND ( ( 'A' = 'A' )
OR ( 'A' = 'U'
AND MSG.OWN_USR_ID IS NULL )
OR ( 'A' = 'Y'
AND MSG.OWN_USR_ID IN (SELECT USR_ID
FROM GFX.VU_USR
WHERE
rtrim(UPPR_FULL_NAME) like (rtrim (translate (UCASE ('DAN'), '%',
'*'))) ) )
OR ( 'A' = 'N'
AND MSG.OWN_USR_ID IN (SELECT USR_ID
FROM GFX.VU_USR
WHERE
UPPR_FULL_NAME = ( UCASE('DAN') ) ) )
OR ( 'A' = 'M'
AND MSG.OWN_USR_ID = 18006554984185 )
)
ORDER BY MSG.CRTD_TSTMP DESC
;

Thank you
Bruce

are the cluster factors identical?

No, but those indexes with lower cluster ratios are being scanned?!?

INDNAME CLUSTERFACTOR
------------------ ------------------------
XAK1INTRL_MSG_Q +1.00000000000000E+000*
XIE1INTRL_MSG_Q +9.99159663865546E-001*
XIF11168AINTRLMSGQ +1.00000000000000E+000
XIF11168BINTRLMSGQ +7.77310924369748E-001
XIF1196INTRL_MSG_Q +8.84033613445378E-001
XIF1265INTRL_MSG_Q +7.77310924369748E-001
XPKINTRL_MSG_Q +1.00000000000000E+000

INDNAME CLUSTERFACTOR
------------------ ------------------------
XAK1INTRL_MSG_Q +9.80784161490683E-001
XIE1INTRL_MSG_Q +9.99320652173913E-001
XIF11168AINTRLMSGQ +9.80784161490683E-001
XIF11168BINTRLMSGQ +6.24805900621118E-001
XIF1196INTRL_MSG_Q +7.69701086956522E-001
XIF1265INTRL_MSG_Q +6.34025621118012E-001
XPKINTRL_MSG_Q +9.80784161490683E-001

*Those indexes being scanned.

well, the cluster factors are almost identical.
what about the percentage of rows that satisfy the criteria?

For instance, if the query uses an index and returns 50 rows out of 1K
(0.5%), using an index is justified.
If the same query scans the 10K table and returns 8500 rows out of 10K
(85%), that optimizer's choice to scan the table also makes perfect
sense

The query I'm using returns 0 record(s) selected on both databases.
I'm thinking maybe this has something to do with IO / tablespace
container configuration, just not sure how to confirm this. Any tips
much appreciated.
Thanks

you might also want to use OPTIMIZE FOR FIRST and / or SELECTIVITY
clause
that might influence the optimizer to choose the index.
Then you could compare real execution costs and see if choosing the
index what the right thing to do