I am running db2 v8.1.6.664 on win2k.

I have a generic question.
We have a Data warehouse db and queries are taking some time. I did a explain plan, reorgchk/reorg and runstats, bind etc but the issue seem to be with tablescan where most of the time gets spent. This in turn impact resource utilization eg: CPU, disk sorts etc

btw, summary table/materialized view is not an option since most of the queries are generated at runtime through external application...

Are there any specific ways in which one can avoid full tablescans?

Any thoughts/ideas appreciated.

TIA.

Yes. Indexes. For example, if most of your queries involve a date range, then it is a very good idea to put an index on the date column. Version 8.1 as the "Design Advisor" to help in this area.

HTH

Andy

thanks! for your response.

I have tried design advisor and that does not seem to suggest index creation which would reduce time nor avoid ts...

I am doing a sum(.....) in my query therefore assuming that this will go through each and every record in the table. TS relates in high % of sorts (inspite of increasing SORTHEAP and SHEAPTHRES parameters....)

I am still not sure how to go about this?

If you are on a Datawarehouse, many of your sorts tend to overflow ... You should consider tuning I/O, like placement of tempspace containers, bufferpools etc.

MQTs may still be an option if your queries(though user generated on the fly) use a similar predicates, summarization etc.

HTH

Sathyaram

There are two things that greatly affect whether a tablescan will be used instead of an index scan. The WHERE clause and the ORDER BY clause.
Examine these, mostly the WHERE clause. Some predicates in the WHERE clause can cause a tablescan even if some of them refer to indexed columns. Best rule of thumb here is to keep the predicates simple and not to use functions here.

Andy

If DB2 needs to read all the pages in the tablespace, then it will do a tablespace scan because that is the fastest way to do it. If you post the DDL and SQL, then maybe someone can verify that a tablespace is needed.

Even if you have a tablespace scan, there are ways to speed it up, such using the correct extent size, prefetch size, proper number and placement of containers, and proper settings to invoke intra-partition parallelism. How well these things will work depends somewhat on your disk subsystem and how many seperate disks (or arrays) you are able to use for the tablespace.

Thanks for all the suggestions..

Let me work on these input to see if we can avoid TS...

Regards.