Disabling a MQT in db2 9.7
kenfar
maintained MQT in db2 9.7.
The online documentation for db2 9.7 has been enhanced to death. The
closest link I can seem to find is this:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.admin.dbobj.doc/doc/t0005319.html
Which I would think would mention disabling optimization. But it
doesn't.
Documentation for db2 v2r2 says this:
"Recommendation: When creating a user-maintained materialized query
table, initially disable query optimization. Otherwise, DB2 might
automatically rewrite queries to use the empty materialized query
table. After you populate the user-maintained materialized query
table, you can alter the table to enable query optimization."
According to the vast online documentation of the alter table
statement, this doesn't seem possible:
"If table-name identifies a materialized query table, alterations are
limited to adding or dropping the materialized query table, activating
not logged initially, adding or dropping RESTRICT ON DROP, and
changing pctfree, locksize, append, or volatile."
So, how do I disable & enable it later? (please, please don't tell me
to drop & recreate).
Lastly, I used to really like the infocenter. But it's killing me
now. Any suggestions for commercial documentation, I'll happily pay
at this point.
MarkB
> I'm running into an odd problem - I'd like to disable a user-
> maintained MQT in db2 9.7.
>
> The online documentation for db2 9.7 has been enhanced to death. The
>
> Which I would think would mention disabling optimization. But it
> doesn't.
>
> Documentation for db2 v2r2 says this:
> "Recommendation: When creating a user-maintained materialized query
> table, initially disable query optimization. Otherwise, DB2 might
> automatically rewrite queries to use the empty materialized query
> table. After you populate the user-maintained materialized query
> table, you can alter the table to enable query optimization."
>
> According to the vast online documentation of the alter table
> statement, this doesn't seem possible:
> "If table-name identifies a materialized query table, alterations are
> limited to adding or dropping the materialized query table, activating
> not logged initially, adding or dropping RESTRICT ON DROP, and
> changing pctfree, locksize, append, or volatile."
>
> So, how do I disable & enable it later? (please, please don't tell me
> to drop & recreate).
>
> Lastly, I used to really like the infocenter. But it's killing me
> now. Any suggestions for commercial documentation, I'll happily pay
> at this point.
Hi,
You don't have to drop your mqt but you do have to drop mqt definition
for your table:
-- disabling --
alter table your_mqt drop materialized query;
-- enabling --
alter table your_mqt add (select c1, ..., cN from ...) data initially
deferred refresh deferred maintained by user;
set integrity for your_mqt materialized query immediate unchecked;
Hope this help,
Mark B.
kenfar
> You don't have to drop your mqt but you do have to drop mqt definition
> for your table:
Thanks for the tip. I was hoping that my ETL process could easily
turn it off whenever it was out of sync with the base table, and then
easily turn it back on when back in sync. But I'd rather not have the
query definition repeated within the ETL processes or have to perform
a potentially slow set integrity afterward.
One example of this scenario is when there are a dozen different
summary tables for a single fact table, and I've got a million new
rows across that dozen to insert to them reflect the newest hour or
day. Users are mostly hitting summary tables with their ROLAP
queries and while navigating around my portal could end up hitting a
half-dozen summary tables. While loading the summaries if they all
continue to be used for AQR then charts, graphs and tables will
sometimes show the old data and sometimes show the new. My
preference is to show off AQR for most of the summaries so that we
take a performance hit but the data quality is preserved.
Any suggestions for fast set integrity? I'd like to avoid going the
route of table partitioning with most of these MQTs, so if I'm not
mistaken that eliminates the option of incremental set integrity...
Thanks again.
Ken