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

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

    Hi,

    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?

    Thanks,
    Arunvijay

    DB2 Version : 8.1

  2. #2
    Join Date
    Apr 2008
    Posts
    51
    Use REFRESH DEFERRED keywords when CREATing the MQT.
    db2topgun.com

  3. #3
    Join Date
    Mar 2008
    Posts
    11
    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
    Location
    IL
    Posts
    557
    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 6.1.0.0

Posting Permissions

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