Disabling a MQT in db2 9.7
in [DB2]
|
Prev: Yellowfin discusses, Selection criteria for an in-memory analysis solution
Next: Create Index in Separate table space
From: kenfar on 12 May 2010 18:40 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 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.
From: MarkB on 13 May 2010 03:33
On May 13, 2:40 am, kenfar <ken...(a)gmail.com> wrote: > 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 > closest link I can seem to find is this:http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=... > > 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. |