Hi.
I am working with db2v7.2 fixpack 6 aix 4.3
my query is like

SELECT DISTINCT min(bstrm.account_coid)
,sp.defining_prod_coid as Product_COID
,char(bstrm.TrmDate_Start, ISO) as PP_Date
,char(bstrm.TrmDate_End, ISO) as MM_Date
,char(bsidx.Part_Adj) as Paa_Adj
,char(bsidx.Part_Level) as Paa_Lvl
FROM bsidx
INNER JOIN bstrm
ON (bsidx.account_coid = bstrm.account_coid
AND bsidx.product_seq = bstrm.product_seq
AND char(bstrm.trmdate_start, ISO) <= '2004-02-14')
group by ....
I did runstats and bind .
I have indexes on both table bsidx and bstrm but optimizer does not use those indexes
Thank you for your help

What indexes? Please give index DDL.

Are the tables disorganized? Did you run runstats with full stats after reorg? If not, please do so and retest.

Hi Marcus
we have unique index for both tables like

CREATE UNIQUE INDEX ON bsidx (account_coid,product_seq,feature_id),
1- I reorg entire database this weekend and runstats like
runstats on table $schema.$tab with distribution and indexes all shrlevel change.
2- even I treid and added
AND bsidx.feature_id = bstrm.feature_id in the join cluse, still problem

Thank you for time Marcus.

Hard to say for sure, but it looks to me like this is the issue:

AND char(bstrm.trmdate_start, ISO) <= '2004-02-14')

If this would select a significant number of rows on the table, then all pages need to be accessed, and a table space scan (hopefully with prefetch) is the best way to access the data. DB2 probably reads and sorts both tables and then joins them.

But I cannot be certain without seeing the explain output.

BTW, isn’t ISO your default date format?

Hi Marcus,
Even after I changed to AND bstrm.trmdate_start <='2004-02-14' it does not work, but you are right because:
bsidx has 300000 records and bstrm has 3000000 records and all bsidx.account_coid (the number is 300000) are selected in
bsidx.account_coid = bstrm.account_coid so optimizer use TBSCAN for bsidx table , I think we should change the application design, anyway thank you again for spending your valuable time.

Yes, without the date column in the where clause you were selecting all rows of the table, so a tablespace scan is usually more efficient for reading all the rows. Even with the date column, using the date value supplied, a large percentage of the table will need to be read.

DB2 will use an index if it can avoid reading at least some 4K pages in the tablespace (which means that none of the rows on that page will be read). This is because all physical reads are done in 4K page (or larger page if defined) increments.

If DB2 has to read at least one row on every 4K page, then it will do a tablespace scan. Even in some cases where not every 4K page needs to be read by DB2, it will do a tablespace scan anyway because sequential prefetch can be enabled, which improves efficiency over the same number of random reads from disk.

One more thing. If you created a new index with bstrm.trmdate_start as the only column or the first column of the index, and the index was the clustering index, then that index might be used to limit the rows selected, and the other indexes might be used for the join.

But if you created such an index, you would need to ensure that the table is easy to keep in clustered sequence as the result of new rows inserted into the table.