I have a queries which retrives data from a table containg 130 collumns and 80,000 rows into 14 tables. It takes about 3 to 4 hrs. Can any one suggest about how to reduce this time. Is there any property that can be set so that this happens in less time. In Oracle i heard that the some parameter called "undosegment" . Is there such property in DB2. Please help me.

Hello

Tell me these things first,

wht type of data?
Is there any time varience in different time?
Do you have enough space in bufferpool and wht is your os?

1 .The data is mainly of numeric type except 5 or collumns. consists of smallInt, Num, Float,Decimal.

2. what do you mean by time varience. sorry I am new to programming.

3. My Os is Win Xp. How much min should be there in buffer pool.

Hey Satish,

I guess you are from India and if you don't mind you can give call on 09890637850 after 3.5 hour.

I would suggest that you take the query and run it through the Design Advisor. It will give you some suggestions on Indexes, MQTs etc., which you can then use to tune your system.

Also, have you collected recent statistics?

Hi Stolze,
For my table there are no Indexes and cant apply also as it is data produced from an experiment and my queries are written in stored procedure which simple
"Insert into sample1 select 1,2,3 from source"
"Insert into sample2 select 1,4,5 from source" similarly 14 queries.
2 . What statistics should i check.

My thought is to find for a property such as to stop log entery or some thing like that which can be made off to reduce the time of loading.
Any other suggestions would be highly helpful.

What exactly do you try to do? Load data into some tables (as you say now) or retrieve it from there and do some sort of processing (as you said in the initial post)?

I don't get the part "data produced from an experiment." DB2 doesn't care where the data comes from - as long as it is the data you want to process.

For data retrieval: You can extract the single INSERT/UPDATE/DELETE/SELECT statements from the stored procedures and provide those as workload to the Design Advisor. Then you will get suggestions.

As for statistics, have a look at your code and which tables are accessed there. Then collect statistics on those tables (hint: RUNSTATS command).

For data loading: You could turn logging of with the ALTER TABLE ... ACTIVATE NOT LOGGED INITIALLY statement. (Note that this has certain implications on the recoverability of the database.) Also, using the LOAD statement would be your best approach to quickly load tables. Maybe you should prepare LOAD files and dump the content into the database using the LOAD command (available through the ADMIN_CMD procedure if I'm not mistaken).

Hi stolze,
Thank u very much. The property activate not logged intially is helping me. I have made a test. I hope it works with my original query also.

Thanks
Satish