MQT with MQT

[sivuj18]

Hi,

I am using DB2 UDB 9.1, today i tried to create an MQT using another MQT.

For example : create table mqt_test as (select col1,col2 from mqt1) data initially deferred refresh deferred;

In the above statement mqt1 is one MQT created in my db, now i am trying to create another mqt i.e, mqt_test on top of it.

Is it possible to create like this because Oracle Mat views supports this, but i dont know about DB2. Can any one suggest me on this.


Thanks,
Siva

[cburnett]

Using the sample EMPMDC, I just successfully created one along the lines you suggest so looks like this is fine in DB2 also.

create table testmdc as (select empno, dept from colin.empmdc) data initially deferred refresh deferred

[sivuj18]

Thanks BURNETT,

"colin.empm dc" is the table or MQT in your database????

[cburnett]

Oops. Freudian slip - I read this as MDC (Multi-dimensional Cluster) tables. For MQTs, you cannot create an MQT based on another MQT. The following sample code:

create table dept_sum(deptno,
                      employees,
                      total_salary) as
       (select workdept, count(*), sum(salary)
          from colin.employee
         group by workdept)
       data initially deferred refresh deferred;

create table dept_avgsal(deptno, avg_salary) as
       (select deptno,
               total_salary/employees
          from dept_sum)
       data initially deferred refresh deferred;

yields error message:

SQL20058N The fullselect specified for the materialized query table
"COLIN.DEPT_AVG SAL" is not valid. Reason code = "2". SQLSTATE=428EC

2 The fullselect must not reference any of the following object
types:
* materialized query table
* staging table
* declared global temporary table
* typed table
* system catalog table
* view that violates any of the materialized query table
restrictions
* A protected table
* A nickname that was created using the DISALLOW CACHING
clause in the CREATE NICKNAME or ALTER NICKNAME statement
* A view that depends, either directly or indirectly, on a
protected table.


This is the behaviour for V9.5 and V9.7.