Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    85

    Unanswered: MQTs, REFRESH TABLE and Logging

    It seems that I was under a misguided impression that ALTER TABLE xxx ACTIVATE NOT LOGGED INITIALLY would not log activity of a REFRESH TABLE xxx (which is an MQT).
    Wrong-- I got a transaction log is full.

    So, the question is:
    If the refresh will cause millions of rows to be loaded/inserted, how can one refresh the MQT without (significant) logging?

    I'm unsure whether the LOAD FROM xx OF CURSOR is allowed for an MQT. If it is, then I guess I could define a cursor as the SELECT which defines the MQT and then use the LOAD.

    We're at V8.1 FP5.
    Last edited by rubystep; 06-16-04 at 10:03.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I don't know if refresh can avoid being logged, but exactly how did you submit the SQL to not log initially and then refresh? If you put these statements in a script, then you must turn off auto-commit using the +c option. Otherwise a commit will happen right after the alter table (and every SQL statement), and logging will be turned back on before the refresh.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Sep 2003
    Posts
    85
    OK. Here's the solution which Steve Mazer suggested with a slight addition:
    To avoid logging during a "load", the REFRESH TABLE will NOT accomplish this as the ALTER TABLE ACTIVATE NOT LOGGED INITIALLY doesn't apply to REFRESH TABLE.
    So, I issued an ALTER TABLE SET MATERIALIZED QUERY AS DEFINITION ONLY
    (and I now see on the UDB Information Center that there's a DROP MATERIALIZED QUERY). This will place the table as a "regular" table.
    Then I declared a cursor as the SELECT definition used for the MQT and then issued a LOAD FROM xx OF CURSOR (and one must ensure that in the SELECT in the cursor that there's a 1:1 column name mapping matching the MQT's table columns).
    Then I had to issue an ALTER TABLE SET MATERIALIZED QUERY AS the full select DATA INITIALLY DEFERRED etc. (and I now see in the UDB Information Center that one could issue an ALTER TABLE ... ADD QUERY full select DATA INITIALLY DEFERRED etc.) and then a SET INTEGRITY FOR tablename MATERIALIZED QUERY IMMEDIATE UNCHECKED which will then take the MQT out of Check Pending and allow usage of the table.

Posting Permissions

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