MQT with MQT

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • sivuj18
    New Member
    • Oct 2009
    • 4

    MQT with MQT

    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
    New Member
    • Aug 2007
    • 57

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

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

    Comment

    • sivuj18
      New Member
      • Oct 2009
      • 4

      #3
      Thanks BURNETT,

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

      Comment

      • cburnett
        New Member
        • Aug 2007
        • 57

        #4
        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:

        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.

        Comment

        Working...