Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2009
    Posts
    6

    Unanswered: Materialized query tables

    I am trying to create MQT in DB2 V10 on IBM Z/OS to improve performance on of the SQL. When I try with DATA INITIALLY DEFERRED REFRESH DEFERRED it creates and works. But When I change to DATA INITIALLY DEFERRED REFRESH IMMEDIATE while creating it gives Error at IMMEDIATE.

    SET CURRENT SQLID = 'VARVZP';
    CREATE TABLE VARVZP.VAC_APPROVAL_MQT
    (
    ESG_CLAIM_NUMBER
    ,CREATE_TIMESTAMP
    )
    AS (
    SELECT ESG_CLAIM_NUMBER,MAX(CREATE_TIMESTAMP) CREATE_TIMESTAMP
    FROM VARVZP.VAC_APPROVAL
    GROUP BY ESG_CLAIM_NUMBER
    )
    DATA INITIALLY DEFERRED REFRESH IMMEDIATE
    MAINTAINED BY SYSTEM
    DISABLE QUERY OPTIMIZATION
    CCSID EBCDIC
    NOT VOLATILE
    ;

    Can you tell me why this error. Refresh Immediate will not accept MAX function?. If so who to rewrite this MQT. Since I need this to be refreshed real time. Is there way to use this instead of trigger.

    Thank you
    Ravi

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Looking at the syntax diagram, there is no option for REFRESH IMMEDIATE in DB2 z/OS V10: DB2 10 - DB2 SQL - CREATE TABLE
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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