Hi All,

Can any one provide me with some suggestion how to force db2 to select an index
i have a following sql for which there is a full table scan even though we have index matching the where clause

SELECT T9222.CNNO,
T9222.TRANNO,
T1015.CTRIBADT
FROM DB2LPP.TLP9222 T9222,DB2LPP.TLP9015 T1015
WHERE T9222.TRTYP = 'AL'
AND T9222.TRSTATCD IN ('SM','SI','WB','FL')
AND T9222.TRANNO = T1015.TRANNO

Please provide DB2 version, OS version, and DDL for the tables and indexes in question. Also insure that you have don runstats on table and indexes.

Andy

Hi,
provide index definition and access plan.
Grofaty

we hav an index for this table,with follwing columns

TRTYP
TRSTATCD
TRANNO
CLNTID
but this sql is not picking up this index,insted it gos for a table scqane:

access path:
Line 01 Data accessed from the table
SQL operation: SELECT statement
In......Table: DB2TEST.TLP9222
Access Method: Table scan using sequential prefetch.
Lock: Intent share

A possible reason could be that, according to the statistics, the combined TRTYP = 'AL' & TRSTATCD IN ('SM','SI','WB','FL') occur too often to justify index use.
Or maybe the index is non-clustering and the RID pool is too small.

Hi,
have you tried creating an index this way:
create index db2test.test on DB2TEST.TLP9222 (TRANNO, trtyp, trstatcd)

Is there any index on table DB2LPP.TLP9015?

Try also running distributed statistic: runstats on table xxx with distribution and detailed indexes all.

Please also provide all access plan data.

Hope this helps,
Grofaty