Lock the MQTs during REFRESH

[sree]

Hi

In my application , I have created MQTS using the option ->
"DATA INITIALLY DEFERRED REFRESH DEFERRED"
Everyday ,I execute the command "REFRESH table <MQT_name>" to refresh the MQTs.

My question are :-
1. How do we restrict a user to perform SELECT operation on an MQT during the REFRESH ?
2. are the MQTs locked during the time of REFRESH?

3. what happens if the data in any of the source table[table used in MQT definition] changes during refresh time?

4. will the REFRESH process wait in order to accept the new change to source table as well?

5.can any one provide better ways to improve performance of MQTs?
Its DB2/NT64 8.2.5 running on windows 2003 server

Thanks
Sree

[Ian]

Post by sree
Hi
In my application , I have created MQTS using the option ->
"DATA INITIALLY DEFERRED REFRESH DEFERRED"
Everyday ,I execute the command "REFRESH table <MQT_name>" to
refresh the MQTs.
My question are :-
1. How do we restrict a user to perform SELECT operation on an MQT during the REFRESH ?
2. are the MQTs locked during the time of REFRESH?

Yes, during refresh the MQTs are locked so they can't be accessed.

Post by sree
3. what happens if the data in any of the source table[table used in MQT definition] changes during refresh time?

This is handled via locks -- the same way any other query is handled.

Post by sree
4. will the REFRESH process wait in order to accept the new change to source table as well?

That depends on the ordering of the statements.

Post by sree
5.can any one provide better ways to improve performance of MQTs?
Its DB2/NT64 8.2.5 running on windows 2003 server

Improve what?

Refresh performance? Use a staging table so you can do incremental
refreshes.

Query performance against the MQT? Same as any other tuning: Proper
table design, indexing, etc.


Good luck,


Ian Bjorhovde