Can I disable QUERY OPTIMIZATION for MQT in DB2 LUW without recreating it?

Question

UPDATE from 2021

This question is no longer actual for me.

It was a short period when I worked with DB2 and I don't know how it's in recent versions.

The problem was: I could not test effect of MQT without rebuilding it.

Which was not practical when you deal with multi-Gb data.

I did not found solution earlier, I don't know why question was minused.

SO recommends to not delete questions with answers and who knows: maybe somebody finally answers that.

I have a MQT in DB2 10.5 LUW:

CREATE TABLE MyMQT AS(
SELECT * FROM MyTable
    WHERE
        ServerName = 'COL'
        AND LASTOCCURRENCE > TIMESTAMP '2015-12-21 00:00:00' 
)
DATA INITIALLY DEFERRED REFRESH immediate
ENABLE QUERY OPTIMIZATION
MAINTAINED BY SYSTEM;

I want to DISABLE QUERY OPTIMIZATION without DROP/CREATE.

I found "Altering materialized query table properties" https://www-01.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.admin/src/tpc/db2z_changemqtableattribs.html but this is for z/OS.

If I try:

ALTER TABLE MyMQT DISABLE QUERY OPTIMIZATION;

I get:

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "TABLE" was found following "ALTER ".  Expected
tokens may include:  "VIEW".  SQLSTATE=42601

Documentation for LUW explains how to change MQT to regular table and otherwise.

Can I alter MQT options in DB2 LUW without recreating it?

Edit

It's quite strange, but looks like this is impossible to achieve in DB2 LUW.

As data_henrik mentioned, it's possible to disable/enable optimization for all MQTs.

I accept his answer although it's not quite what I was looking for.

I want to quickly test queries with and without QUERY REWRITE (oh, sorry, it's an oracle term?). I don't want to wait several minutes with drop-create MQT. — Alex Yu, Dec 22 '15 at 01:45

score 0 · Answer 1 · answered Dec 22 '15 at 15:41

0

No personal experience with it, but you could:

SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION = NONE

This would tell DB2 to not consider any MQT. Later on you would enable query optimization by setting that variable to "system" (the default) or something else. That statement is documented here.

answered Dec 22 '15 at 15:41

data_henrik

16,724
2
28
49

Thank you. It's not quite what I'm looking for. – Alex Yu Dec 23 '15 at 06:31
What are you looking for? That's how you could disable/enable usage of MQTs. – data_henrik Dec 23 '15 at 08:01
Yes. But it disables **all** MQTs. It makes it difficult to use in testing environment and impossible in production. – Alex Yu Dec 24 '15 at 15:51

score -2 · Answer 2 · edited Aug 18 '20 at 06:45

-2

Try this:

refreshable-table-options

|--●--DATA INITIALLY DEFERRED--●--REFRESH--+-DEFERRED--+--●----->
                                           '-IMMEDIATE-'      

   .-ENABLE QUERY OPTIMIZATION--.      
>--+----------------------------+--●---------------------------->
   '-DISABLE QUERY OPTIMIZATION-'

edited Aug 18 '20 at 06:45

Adriaan

17,741
7
42
75

answered Aug 18 '20 at 06:44

Stefan Mihokovic

1

3

Welcome to Stack Overflow! Please read [ask] and remember that you are not only answering to the OP, but to any future readers of the question, especially when answering a 5 year old question. Thus, why should I, or anyone else for that matter, "try this"? Instead of adding such a non-sentence to a post, please [edit] it to include an explanation as to **why** this works. – Adriaan Aug 18 '20 at 06:46

Can I disable QUERY OPTIMIZATION for MQT in DB2 LUW without recreating it?

UPDATE from 2021

Edit

2 Answers2