MQT table use by optimizer

[Kevin]

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.

[Shashi Mannepalli]

Check for this in the documentation

SET CURRENT REFRESH AGE= ANY

cheers..
Shashi Mannepalli

[Kevin]

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]

Yes

SET CURRENT REFRESH AGE=ANY

SELECT YEAR(pdate) AS year, SUM(ti.amount) AS amount
FROM cube.transitem AS ti, cube.trans AS t,
cube.loc AS loc, cube.pgroup AS pg,
cube.prodline AS l
WHERE ti.transid = t.id
AND ti.pgid = pg.id
AND pg.lineid = l.id
AND t.locid = loc.id
AND YEAR(pdate) IN (1995, 1996)
GROUP BY year(pdate);

An example i saw in this

http://publib.boulder.ibm.com/infocenter/db2luw/v8//index.jsp

There are some restrictions/instructions also available in the above
link.
Search for CURRENT REFRESH AGE in that link

[fbo...@fastmail.fm]

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/developerworks/db2/library/techarticle/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:

[Ian]

Kevin wrote:
> 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?
>

You can also set the DFT_REFRESH_AGE database configuration parameter
in the event that you can't force all clients to issue the SET CURRENT
REFRESH AGE register.