Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2008

    Unanswered: Any option to make the stagging table refresh to be deferred?


    i am using MQTs to improve the performance of the summary level queries.

    for refreshing the MQT, i am using stagging table concept. i give the following sql to create the stagging table -

    create table MAT_STG_TAB for MAT_TAB propagate immediate

    &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& full code
    create table MAT_TAB as (select COL1,COL2, COL3, count(*) as count
    from BAS_TAB group by COL1,COL2, COL3)
    data initially deferred refresh deferred

    create table MAT_STG_TAB for MAT_TAB propagate immediate
    set integrity for MAT_TAB materialized query immediate unchecked
    set integrity for MAT_STG_TAB staging immediate unchecked


    when i insert/update the base table, rows automotically get into the stagging table. i dont want the stagging table to be populated automatically. is there any way to refresh it deferred?


    DB2 Version : 8.1

  2. #2
    Join Date
    Apr 2008
    Use REFRESH DEFERRED keywords when CREATing the MQT.

  3. #3
    Join Date
    Mar 2008
    Pls note that - i have asked if there is any option available for refresh deferring "Stagging table" , and not MQT .

  4. #4
    Join Date
    Nov 2005
    Quote Originally Posted by aveerabadran
    create table MAT_STG_TAB for MAT_TAB propagate immediate
    By definition your staging tables will be updated right away. Here is a paragraph from the manual.

    PROPAGATE IMMEDIATE The changes made to the underlying tables as part of a delete, insert, or update operation are cascaded to the staging table in the same delete, insert, or update operation. If the staging table is not marked inconsistent, its content, at any point-in-time, is the delta changes to the underlying table since the last refresh materialized query table.
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os

Posting Permissions

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