Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483

    Unanswered: Try to simulate MQT with REFRESH IMMEDIATE and OUTER JOIN

    I tried to simulate MQT with REFRESH IMMEDIATE and OUTER JOIN.

    It failed to create MQT with REFRESH IMMEDIATE as Example 1).
    Because the select includes OUTER JOIN.
    So, I tried Example 2) which made same result as Example 1),
    if a row with deptno = 'A00' in (inner)table department was exists.

    But, MQT with REFRESH IMMEDIATE as Example 2) also failed with message...
    SQL20058N The fullselect specified for the materialized query table
    "DB2ADMIN.EMP_MANAGE_DEPT" is not valid. Reason code = "7". SQLSTATE=428EC

    After many trial and error,
    I think that I succeeded in the attempt on the assumption that a row with deptno = 'A00' in (inner)table department was guaranteed.

    I will post the attempt in the next post in this thread.

    Example 1)
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT e.empno
         , e.firstnme || RTRIM(' ' || e.midinit)|| ' ' || e.lastname AS fullname
         , e.workdept
         , d.deptno
         , d.admrdept
         , d.deptname
      FROM employee   e
      LEFT OUTER JOIN
           department d
       ON  d.mgrno  = e.empno
    ;
    ------------------------------------------------------------------------------
    
    EMPNO  FULLNAME                       WORKDEPT DEPTNO ADMRDEPT DEPTNAME                            
    ------ ------------------------------ -------- ------ -------- ------------------------------------
    000010 CHRISTINE I HAAS               A00      A00    A00      SPIFFY COMPUTER SERVICE DIV.        
    000020 MICHAEL L THOMPSON             B01      B01    A00      PLANNING                            
    000030 SALLY A KWAN                   C01      C01    A00      INFORMATION CENTER                  
    000050 JOHN B GEYER                   E01      E01    A00      SUPPORT SERVICES                    
    000060 IRVING F STERN                 D11      D11    D01      MANUFACTURING SYSTEMS               
    000070 EVA D PULASKI                  D21      D21    D01      ADMINISTRATION SYSTEMS              
    000090 EILEEN W HENDERSON             E11      E11    E01      OPERATIONS                          
    000100 THEODORE Q SPENSER             E21      E21    E01      SOFTWARE SUPPORT                    
    000110 VINCENZO G LUCCHESSI           A00      -      -        -                                   
    000120 SEAN O'CONNELL                 A00      -      -        -                                   
    .....
    .....
    
    000330 WING LEE                       E21      -      -        -                                   
    000340 JASON R GOUNOT                 E21      -      -        -                                   
    
      32 record(s) selected.
    Example 2)
    Code:
    SELECT e.empno
         , e.firstnme || RTRIM(' ' || e.midinit)|| ' ' || e.lastname AS fullname
         , e.workdept
         , CASE d.mgrno
           WHEN e.empno THEN
                d.deptno
           END AS deptno
         , CASE d.mgrno
           WHEN e.empno THEN
                d.admrdept
           END AS admrdept
         , CASE d.mgrno
           WHEN e.empno THEN
                d.deptname
           END AS deptname
     FROM  employee   e
         , department d
     WHERE d.mgrno  = e.empno
       OR  d.deptno = 'A00'
       AND NOT EXISTS
           (SELECT 0
             FROM  department de
             WHERE de.mgrno  = e.empno
          )
    ;
    Last edited by tonkuma; 01-25-11 at 16:28.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The steps to make the MQT(and a view).

    Step 1) Create MQT emp_manage_dept of which results include encoded deptno, admrdept and deptname.
    Step 2) Issue Set integrity.
    Step 3) Create function nbr_to_char which returns original character string from encoded number.
    Step 4) Create view emp_manage_dept which makes same result of Example 1) in original post
    by decoding deptno, admrdept and deptname using expressions and function nbr_to_char.

    Example 3) Query the view.

    Step 1) Create MQT.
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE emp_manage_dept
    AS (
    SELECT e.empno
         , e.firstnme || RTRIM(' ' || e.midinit)|| ' ' || e.lastname AS fullname
         , e.workdept
         , COUNT(*) AS count_row
         , SUM  (
                 CASE d.mgrno
                 WHEN e.empno THEN
                      SMALLINT( TRANSLATE(d.deptno , '123456789' , 'ABCDEFGHI') )
                 END
                ) AS deptno_sum
         , COUNT(
                 CASE d.mgrno
                 WHEN e.empno THEN
                      SMALLINT( TRANSLATE(d.deptno , '123456789' , 'ABCDEFGHI') )
                 END
                ) AS deptno_cnt
         , SUM  (
                 CASE d.mgrno
                 WHEN e.empno THEN
                      SMALLINT( TRANSLATE(d.admrdept , '123456789' , 'ABCDEFGHI') )
                 END
                ) AS admrdept_sum
         , COUNT(
                 CASE d.mgrno
                 WHEN e.empno THEN
                      SMALLINT( TRANSLATE(d.admrdept , '123456789' , 'ABCDEFGHI') )
                 END
                ) AS admrdept_cnt
         , SUM  (
                 CASE d.mgrno
                 WHEN e.empno THEN
                      CAST( TRANSLATE( 'apbqcrdsetfugvhwixjykzl0m1n2o3'
                                     , TRANSLATE( SUBSTR(d.deptname , 1 , 15)
                                                , '000000000111111111122222222223333333333444'
                                                , '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ''., &@'
                                                )
                                       ||
                                       TRANSLATE( SUBSTR(d.deptname , 1 , 15)
                                                , '123456789012345678901234567890123456789012'
                                                , '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ''., &@'
                                                )
                                     , 'abcdefghijklmnopqrstuvwxyz0123'
                                     )
                            AS DEC(30 , 0)
                          )
                 END
                ) AS deptname_1_15_sum
         , COUNT(
                 CASE d.mgrno
                 WHEN e.empno THEN
                      CAST( TRANSLATE( 'apbqcrdsetfugvhwixjykzl0m1n2o3'
                                     , TRANSLATE( SUBSTR(d.deptname , 1 , 15)
                                                , '000000000111111111122222222223333333333444'
                                                , '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ''., &@'
                                                )
                                       ||
                                       TRANSLATE( SUBSTR(d.deptname , 1 , 15)
                                                , '123456789012345678901234567890123456789012'
                                                , '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ''., &@'
                                                )
                                     , 'abcdefghijklmnopqrstuvwxyz0123'
                                     )
                            AS DEC(30 , 0)
                          )
                 END
                ) AS deptname_1_15_cnt
         , SUM  (
                 CASE d.mgrno
                 WHEN e.empno THEN
                      CAST( TRANSLATE( 'apbqcrdsetfugvhwixjykzl0m1n2o3'
                                     , TRANSLATE( SUBSTR(d.deptname , 16 , 15)
                                                , '000000000111111111122222222223333333333444'
                                                , '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ''., &@'
                                                )
                                       ||
                                       TRANSLATE( SUBSTR(d.deptname , 16 , 15)
                                                , '123456789012345678901234567890123456789012'
                                                , '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ''., &@'
                                                )
                                     , 'abcdefghijklmnopqrstuvwxyz0123'
                                     )
                            AS DEC(30 , 0)
                          )
                 END
                ) AS deptname_16_30_sum
         , COUNT(
                 CASE d.mgrno
                 WHEN e.empno THEN
                      CAST( TRANSLATE( 'apbqcrdsetfugvhwixjykzl0m1n2o3'
                                     , TRANSLATE( SUBSTR(d.deptname , 16 , 15)
                                                , '000000000111111111122222222223333333333444'
                                                , '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ''., &@'
                                                )
                                       ||
                                       TRANSLATE( SUBSTR(d.deptname , 16 , 15)
                                                , '123456789012345678901234567890123456789012'
                                                , '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ''., &@'
                                                )
                                     , 'abcdefghijklmnopqrstuvwxyz0123'
                                     )
                            AS DEC(30 , 0)
                          )
                 END
                ) AS deptname_16_30_cnt
     FROM  employee   e
         , department d
     WHERE d.mgrno  = e.empno
       OR  d.deptno = 'A00'
     GROUP BY
           e.empno
         , e.firstnme || RTRIM(' ' || e.midinit)|| ' ' || e.lastname
         , e.workdept
    )
    DATA INITIALLY DEFERRED REFRESH IMMEDIATE
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Step 2) Issue SET INTEGRITY.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SET INTEGRITY FOR emp_manage_dept IMMEDIATE CHECKED;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Step 3) Create a function.
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE OR REPLACE
     FUNCTION nbr_to_char( in_nbr DEC(30 , 0) )
      RETURNS VARCHAR(15)
      LANGUAGE SQL
      READS SQL DATA
      DETERMINISTIC
      NO EXTERNAL ACTION
    RETURN
    SELECT XMLCAST(
              XMLGROUP(
                 SUBSTR( '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ''., &@'
                       , SUBSTR( DIGITS(in_nbr) , k , 2 )
                       , 1 ) AS d
                 ORDER BY k )
              AS VARCHAR(15)
           ) AS result_str
      FROM (VALUES 1,3,5,7,9,11,13,15,17,19,21,23,25,27,29) k(k)
     WHERE 29 - LOG10(in_nbr) <= k
     GROUP BY in_nbr
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Step 4) Create a view
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE OR REPLACE
      VIEW emp_manage_dept_view AS
    SELECT empno
         , fullname
         , workdept
         , SUBSTR('ABCDEFGHI' , deptno_sum   / 100 , 1)
           ||
           SUBSTR( DIGITS(deptno_sum)   , 9 , 2 ) AS deptno
         , SUBSTR('ABCDEFGHI' , admrdept_sum / 100 , 1)
           ||
           SUBSTR( DIGITS(admrdept_sum) , 9 , 2 ) AS admrdept
         , nbr_to_char(deptname_1_15_sum)
           ||
           nbr_to_char(deptname_16_30_sum)        AS deptname
      FROM emp_manage_dept
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.

    Example 3) Qery the view.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT *
     FROM  emp_manage_dept_view
    ;
    ------------------------------------------------------------------------------
    
    EMPNO  FULLNAME                       WORKDEPT DEPTNO ADMRDEPT DEPTNAME                      
    ------ ------------------------------ -------- ------ -------- ------------------------------
    000010 CHRISTINE I HAAS               A00      A00    A00      SPIFFY COMPUTER SERVICE DIV.  
    000020 MICHAEL L THOMPSON             B01      B01    A00      PLANNING                      
    000030 SALLY A KWAN                   C01      C01    A00      INFORMATION CENTER            
    000050 JOHN B GEYER                   E01      E01    A00      SUPPORT SERVICES              
    000060 IRVING F STERN                 D11      D11    D01      MANUFACTURING SYSTEMS         
    000070 EVA D PULASKI                  D21      D21    D01      ADMINISTRATION SYSTEMS        
    000090 EILEEN W HENDERSON             E11      E11    E01      OPERATIONS                    
    000100 THEODORE Q SPENSER             E21      E21    E01      SOFTWARE SUPPORT              
    000110 VINCENZO G LUCCHESSI           A00      -      -        -                             
    000120 SEAN O'CONNELL                 A00      -      -        -                             
    .....
    .....
                      
    000330 WING LEE                       E21      -      -        -                             
    000340 JASON R GOUNOT                 E21      -      -        - 
    
      32 record(s) selected.
    Last edited by tonkuma; 01-25-11 at 16:38.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example 4) Update a base table.
    Example 5) MQT was updated immediately.


    Example 4) Update table department.
    Code:
    ------------------------------ Commands Entered ------------------------------
    UPDATE department
       SET (mgrno , deptname)
         = (CASE
            WHEN deptno = 'E11' THEN
                 '000290'
            ELSE mgrno
            END
           ,CASE
            WHEN deptno = 'B01' THEN
                 'PLAN AND REVIEW'
            ELSE deptname
            END
           )
     WHERE deptno IN ('E11' , 'B01')
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Example 5) MQT was updated immediately.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT *
     FROM  emp_manage_dept_view
    ;
    ------------------------------------------------------------------------------
    
    EMPNO  FULLNAME                       WORKDEPT DEPTNO ADMRDEPT DEPTNAME                      
    ------ ------------------------------ -------- ------ -------- ------------------------------
    000010 CHRISTINE I HAAS               A00      A00    A00      SPIFFY COMPUTER SERVICE DIV.  
    000020 MICHAEL L THOMPSON             B01      B01    A00      PLAN AND REVIEW               
    000030 SALLY A KWAN                   C01      C01    A00      INFORMATION CENTER            
    000050 JOHN B GEYER                   E01      E01    A00      SUPPORT SERVICES              
    000060 IRVING F STERN                 D11      D11    D01      MANUFACTURING SYSTEMS         
    000070 EVA D PULASKI                  D21      D21    D01      ADMINISTRATION SYSTEMS        
    000090 EILEEN W HENDERSON             E11      -      -        -                             
    000100 THEODORE Q SPENSER             E21      E21    E01      SOFTWARE SUPPORT              
    000110 VINCENZO G LUCCHESSI           A00      -      -        -                             
    000120 SEAN O'CONNELL                 A00      -      -        -                             
    000130 DELORES M QUINTANA             C01      -      -        -                             
    000140 HEATHER A NICHOLLS             C01      -      -        -                             
    000150 BRUCE ADAMSON                  D11      -      -        -                             
    000160 ELIZABETH R PIANKA             D11      -      -        -                             
    000170 MASATOSHI J YOSHIMURA          D11      -      -        -                             
    000180 MARILYN S SCOUTTEN             D11      -      -        -                             
    000190 JAMES H WALKER                 D11      -      -        -                             
    000200 DAVID BROWN                    D11      -      -        -                             
    000210 WILLIAM T JONES                D11      -      -        -                             
    000220 JENNIFER K LUTZ                D11      -      -        -                             
    000230 JAMES J JEFFERSON              D21      -      -        -                             
    000240 SALVATORE M MARINO             D21      -      -        -                             
    000250 DANIEL S SMITH                 D21      -      -        -                             
    000260 SYBIL P JOHNSON                D21      -      -        -                             
    000270 MARIA L PEREZ                  D21      -      -        -                             
    000280 ETHEL R SCHNEIDER              E11      -      -        -                             
    000290 JOHN R PARKER                  E11      E11    E01      OPERATIONS                    
    000300 PHILIP X SMITH                 E11      -      -        -                             
    000310 MAUDE F SETRIGHT               E11      -      -        -                             
    000320 RAMLAL V MEHTA                 E21      -      -        -                             
    000330 WING LEE                       E21      -      -        -                             
    000340 JASON R GOUNOT                 E21      -      -        -                             
    
      32 record(s) selected.

  4. #4
    Join Date
    Oct 2011
    Posts
    8
    Hallo tonkuma,

    thanks for your post.

    One question: What's the reason why the MQT in Step 1 was created successfully? I just want to join two tables like (simplified):


    create table myMQT as (select
    a.column01,
    b.column02
    from table01 a, table02 b where a.column01= b.column02
    )
    data initially deferred refresh immediate;

    It will not work with 'refresh immediate': The fullselect specified for the materialized query table xxx is not valid


    Can you help me?

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I guessed the reson might be violated this rule...
    When REFRESH IMMEDIATE is specified:
    ...
    ...

    •The materialized query table must not contain duplicate rows, and the following restrictions specific to this uniqueness requirement apply, depending upon whether or not a GROUP BY clause is specified.
    ...
    ...

    ◦When a GROUP BY clause is not specified, the following uniqueness-related restrictions apply:
    ■The materialized query table's uniqueness requirement is achieved by deriving a unique key for the materialized view from one of the unique key constraints defined in each of the underlying tables. Therefore, the underlying tables must have at least one unique key constraint defined on them, and the columns of these keys must appear in the select list of the materialized query table definition.
    CREATE TABLE - IBM DB2 9.7 for Linux, UNIX, and Windows
    Last edited by tonkuma; 10-07-11 at 20:38. Reason: Add "When REFRESH IMMEDIATE is specified:" to quoted text.

  6. #6
    Join Date
    Oct 2011
    Posts
    8
    A primary key is an unique key. In my example, column01 is primary key in table01 and column02 is primary key in table02 (foreign key constraint between these tables).

    So I think the condition you wrote is fullfilled. But it does not work

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Meharas,

    What are your DB2 version/release and platform OS?
    What are your exact DDLs of tables?

    Here is my trial on DB2 9.7 for Windows.
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE table01
    ( column01 INTEGER NOT NULL PRIMARY KEY
    , column11 VARCHAR(20)
    )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE table02
    ( column02 INTEGER NOT NULL PRIMARY KEY
    , column21 VARCHAR(20)
    )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    create table myMQT as (select 
    a.column01,
    b.column02
    from table01 a, table02 b where a.column01= b.column02
    )
    data initially deferred refresh immediate
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.

  8. #8
    Join Date
    Oct 2011
    Posts
    8
    Tonkuma,

    detailled system information i will send you on monday. I will try your examble.

    Here is the (shortened) definition of the tables out of my mind:

    CREATE TABLE TABLE_A
    COLUMN_A1 INTEGER NOT NULL,
    COLUMN_A2 VARCHAR(64) NOT NULL,
    CONSTRAINT PK_TABLE_A PRIMARY KEY (COLUMN_A1);

    CREATE TABLE TABLE_B
    COLUMN_B1 INTEGER NOT NULL,
    COLUMN_A1 INTEGER NOT NULL,
    COLUMN_B2 VARCHAR(64),
    ...
    CONSTRAINT PK_TABLE_B PRIMARY KEY (COLUMN_B1)
    CONSTRAINT FK_TABLE_B FOREIGN KEY (COLUMN_A1)
    REFERENCES TABLE_A (COLUMN_A1);

    Thanks for your help so far, have a nice weekend

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here are my results.
    Both of primary keys(column_a1 and column_b1) should be included in the select-list of the MQT.

    What are your create statements of MQT and the resulting error messages?

    Base tables:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE TABLE_A
    ( COLUMN_A1 INTEGER NOT NULL,
      COLUMN_A2 VARCHAR(64) NOT NULL,
      CONSTRAINT PK_TABLE_A PRIMARY KEY (COLUMN_A1)
    )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE TABLE_B
    ( COLUMN_B1 INTEGER NOT NULL,
      COLUMN_A1 INTEGER NOT NULL,
      COLUMN_B2 VARCHAR(64),
      CONSTRAINT PK_TABLE_B PRIMARY KEY (COLUMN_B1)
    , CONSTRAINT FK_TABLE_B FOREIGN KEY (COLUMN_A1) 
      REFERENCES TABLE_A (COLUMN_A1)
    )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.

    MQT: Successful
    Code:
    ------------------------------ Commands Entered ------------------------------
    create table myMQT_x as (select 
    a.column_A1,
    b.column_B1
    from table_A a, table_B b where a.column_A1 = b.column_A1
    )
    data initially deferred refresh immediate
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    create table myMQT_y as (select 
    a.column_A1,
    b.column_B1
    from table_A a, table_B b where a.column_A1 = b.column_B1
    )
    data initially deferred refresh immediate
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.

    MQT: Error
    Code:
    ------------------------------ Commands Entered ------------------------------
    create table myMQT_z1 as (select 
    a.column_A1,
    b.column_A1 AS b_col_a1
    from table_A a, table_B b where a.column_A1 = b.column_A1
    )
    data initially deferred refresh immediate
    ;
    ------------------------------------------------------------------------------
    DB21034E  The command was processed as an SQL statement because it was not a 
    valid Command Line Processor command.  During SQL processing it returned:
    SQL20058N  The fullselect specified for the materialized query table 
    "DB2ADMIN.MYMQT_Z1" is not valid. Reason code = "7".  SQLSTATE=428EC
    
    ------------------------------ Commands Entered ------------------------------
    create table myMQT_z2 as (select 
    a.column_A1,
    b.column_B2
    from table_A a, table_B b where a.column_A1 = b.column_A1
    )
    data initially deferred refresh immediate
    ;
    ------------------------------------------------------------------------------
    DB21034E  The command was processed as an SQL statement because it was not a 
    valid Command Line Processor command.  During SQL processing it returned:
    SQL20058N  The fullselect specified for the materialized query table 
    "DB2ADMIN.MYMQT_Z2" is not valid. Reason code = "7".  SQLSTATE=428EC
    Last edited by tonkuma; 10-08-11 at 22:06.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    SQL20058N ... Reason code 7

    Code:
    ...
    
    The statement cannot be processed because it violates a restriction as
    indicated by the following reason code:
    
    ...
    
    
    7        
    
             When REFRESH IMMEDIATE is specified:
    
              
             *  the materialized query table must not contain duplicate rows
             *  when a GROUP BY clause is specified, all GROUP BY items must
                be included in the select list
             *  when a GROUP BY clause is specified which contains GROUPING
                SETS, CUBE, or ROLLUP, then no grouping sets can be
                repeated, and if C is a nullable GROUP BY item that appears
                within GROUPING SETS, CUBE, or ROLLUP, then GROUPING(C) must
                appear in the select list
             *  when no GROUP BY clause is present, then the underlying
                tables must each have at least one unique key defined, and
                all columns of these keys must appear in the select list of
                the materialized query table definition
    
    ...

  11. #11
    Join Date
    Oct 2011
    Posts
    8
    I'm quite sure my definition contains both primary key but I'll know tomorrow.

    Thank you anyway, have a great sunday

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Meharas,

    First of all,
    please let us know the error message(code and full text) you got
    and (if there was) a reason code.

    My last three or four posts were based on the assumption that the error message you got was "SQL20058N ... Reason code 7".
    If the assumption was wrong, all these my posts would be in vain.

  13. #13
    Join Date
    Oct 2011
    Posts
    8
    tonkuma,

    i was able to create a mqt with inner join based on your help. An primary key column was missing in the mqt selection list. But it does not work with outer join:

    Code:
    CREATE TABLE TABLE_A
    ( COLUMN_A1 INTEGER NOT NULL,
      COLUMN_A2 VARCHAR(64) NOT NULL,
      CONSTRAINT PK_TABLE_A PRIMARY KEY (COLUMN_A1)
    );
    
     0 record(s) affected 
    
     [Executed: 10.10.11 08:05:20 GMT ] [Execution: 13203/ms] 
    
    CREATE TABLE MQT_TABLE AS (SELECT
        A.COLUMN_A1,
        A.COLUMN_A2,
        B.COLUMN_B1
      FROM TABLE_A A LEFT JOIN TABLE_B B ON (A.COLUMN_A1=B.COLUMN_A1))
      DATA INITIALLY DEFERRED REFRESH IMMEDIATE;
    
     0 record(s) affected 
    
     [Executed: 10.10.11 08:06:46 GMT ] [Execution: 43313/ms] 
    
    CREATE TABLE MQT_TABLE AS (SELECT
        A.COLUMN_B1,
        B.COLUMN_A1,
        B.COLUMN_A2
      FROM TABLE_B A LEFT JOIN TABLE_A B ON (A.COLUMN_A1=B.COLUMN_A1))
      DATA INITIALLY DEFERRED REFRESH IMMEDIATE;
    
    DB2 SQL error: SQLCODE: -20058, SQLSTATE: 428EC, SQLERRMC: M001.MQT_TABLE;10
     Message: The fullselect specified for the materialized query table "M001.MQT_TABLE" is not valid. Reason code: "10".
    Any ideas? Both primary key columns are included in the mqt selection list.

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    SQLCODE: -20058, SQLSTATE: 428EC,
    Reason code: "10"

    10

    When REFRESH IMMEDIATE is specified and the FROM clause references more than one table, only an inner join, without using the explicit INNER JOIN syntax, is supported.
    There are more restrictions for "REFRESH IMMEDIATE".


    So, I tried to simulate OUTER JOIN and REFRESH IMMEDIATE by rather complex/lengthy Steps on this thread...
    Step 1) Create MQT emp_manage_dept of which results include encoded deptno, admrdept and deptname.
    Step 2) Issue Set integrity.
    Step 3) Create function nbr_to_char which returns original character string from encoded number.
    Step 4) Create view emp_manage_dept which makes same result of Example 1) in original post
    by decoding deptno, admrdept and deptname using expressions and function nbr_to_char.
    Some reasons of Steps 1) to 4) are...
    a) SQLCODE: -20058, Reason code: 10
    b) Use of subqueries are very restricted.
    c) Only COUNT, COUNT_BIG, and SUM(No MAX, MIN, AVG) aggregate functions are allowed.
    d) No OLAP specifications are allowed.
    so on...

    I don't want to show the places of documentations, because it's too troublesome for me now.
    Most restrictions are documented in Description of CREATE TABLE statement.
    CREATE TABLE - IBM DB2 9.7 for Linux, UNIX, and Windows

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •