Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2012
    Posts
    10

    Unanswered: Issue with Refresh MQT

    I was trying to refresh MQT, but failed to refresh as I am getting Transaction log full error.

    Could anyone please help on the same, how to refresh without increasing log file size or primary / secondary logs.

    Below script i tried

    CREATE TABLE STAFF.STAFF_DETAILS_MQT
    (<columns>)
    AS (SELECT ...
    )data initially deferred refresh deferred ;

    alter table STAFF.STAFF_DETAILS_MQT activate not logged initially

    SET INTEGRITY FOR STAFF.STAFF_DETAILS_MQT MATERIALIZED QUERY IMMEDIATE UNCHECKED;

    REFRESH TABLE STAFF.STAFF_DETAILS_MQT ;

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Two possible solutions:

    What is the size of the log space?

    Do you have autocommit turned on while running the script (it defaults to on)?

    Andy

  3. #3
    Join Date
    Oct 2012
    Posts
    10
    Hi,

    Primary log is 30
    Log second is 50

    log file size is 4k

    My AutoCommit is ON

  4. #4
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by Sacingle View Post
    I was trying to refresh MQT, but failed to refresh as I am getting Transaction log full error.

    Could anyone please help on the same, how to refresh without increasing log file size or primary / secondary logs.

    Below script i tried

    CREATE TABLE STAFF.STAFF_DETAILS_MQT
    (<columns>)
    AS (SELECT ...
    )data initially deferred refresh deferred ;

    alter table STAFF.STAFF_DETAILS_MQT activate not logged initially

    SET INTEGRITY FOR STAFF.STAFF_DETAILS_MQT MATERIALIZED QUERY IMMEDIATE UNCHECKED;

    REFRESH TABLE STAFF.STAFF_DETAILS_MQT ;

    Does your MQT meet the requirement for using a staging table?
    --
    Lennart

  5. #5
    Join Date
    Oct 2012
    Posts
    10
    It meets the requirement but to populate the Staging Table it will take the same logs to populate the data.

  6. #6
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by Sacingle View Post
    It meets the requirement but to populate the Staging Table it will take the same logs to populate the data.
    It is much easier to do an incremental refresh via a staging table, than doing a full refresh. A full refresh rebuilds the whole table, contrary to an incremental refresh.

    A word of warning though, you might en up with rows in the mqt where your count becomes 0. In a full refresh these rows are removed. A trick is to hide the mqt behind a view with a predicate filtering out those rows.
    --
    Lennart

  7. #7
    Join Date
    Oct 2012
    Posts
    10
    Hi,

    I agree your point that i can do the incremental refresh,

    But the main problem is loading initial data,

    Rest is good, we can handle using incremental refresh.

    Thanks,
    Sachin

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by Sacingle View Post
    I was trying to refresh MQT, but failed to refresh as I am getting Transaction log full error.

    Could anyone please help on the same, how to refresh without increasing log file size or primary / secondary logs.

    Below script i tried

    CREATE TABLE STAFF.STAFF_DETAILS_MQT
    (<columns>)
    AS (SELECT ...
    )data initially deferred refresh deferred ;

    alter table STAFF.STAFF_DETAILS_MQT activate not logged initially

    SET INTEGRITY FOR STAFF.STAFF_DETAILS_MQT MATERIALIZED QUERY IMMEDIATE UNCHECKED;

    REFRESH TABLE STAFF.STAFF_DETAILS_MQT ;

    You did not active NLI for your UOW, see:
    http://www-01.ibm.com/support/docvie...=utf-8&lang=en
    Last edited by db2girl; 10-26-12 at 16:10.

Posting Permissions

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