Shashi,
if you created a "refresh immediate" MQT it will be used in any case
when the query benefits from the MQT.
if you created the MQT with "refresh deferred" it depends whether the
MQT is maintained by system or maintained by user.
maintained by system (default):
use set current refresh age = any
there is another register:
set current maintained table types for optimization (default = SYSTEM)
maintained by user:
set current maintained table types for optimization [USER|ALL]
You can set that register or set the DB CONFIG PARAM:
DB2 UPDATE DATABASE CONFUGURATION FOR [DBNAME] USING DFT_MTTB_TYPES
[USER|SYSTEM|ALL]
Not sure if this still applies to Version 8.2 but if you already run a
SQL statement prior to the creation of the MQT the SQL might still
reside in the dynamic statement cache and if you created the MQT it
might not be considered by the optimizer because of the existing plan
in the cache. So to be sure from command line issue a:
FLUSH PACKAGE CACHE--DYNAMIC
in order to have the optimizer creating a fresh access path
This articel covers a lot of the MQT world:
http://www-128.ibm.com/developerwork...le/dm-0605lin/
Last but not least: In Version UDB 7 the visual explain was not able to
show the usage of an MQT allthough it was used. Step 10 of the
developer works articel covers the access paths of MQT usage.
I hope that helps
Cheers
Florian
-------------------------
Speedgain for DB2 LUW - Performance Monitoring for DB2 UDB LUW
Kevin schrieb:
Shashi,
I am using explain to verify that the mqt table is being used. Should
set current refresh age be included prior to executing my sql
statement?
Thanks.
Shashi Mannepalli wrote:
Check for this in the documentation
SET CURRENT REFRESH AGE= ANY
cheers..
Shashi Mannepalli
Kevin wrote:
Using a base table, a MQT table was created. With optimization - when
querying the base table with calcuation that are already completed in
the MQT - I would assume the optimizer would use the MQT table instead
of the base table.
>
What causes the optimizer to use the MQT tables rather than the tables
as defined by the query? Is there a way to "encourage" use of the MQT
tables?
>
Thanks.