Results 1 to 10 of 10
  1. #1
    Join Date
    May 2012
    Posts
    155

    Unanswered: pending state -> set integrity

    Hallo everyone,

    I have a table called A and a MQT-table called MQT_A. I tried to load a file into my table A. It has worked. I know that after the loading, table A will be set to pending state(C). In order to solve this, I run set integrity. I suppose, this should put back the snychronisation between A and the MQT_A. I wonder, after this set integrity, table MQT_A ist STILL NOT YET UPDATED. How come? I have checked it, things are missing in MQT_A.

    Chronology -step by step-:
    1. Loading in table A (table A has MQT called MQT_A)
    2. Loading finished
    3. Table A set to pending check(C)
    4. Run set integrity to table A
    5. Set integrity finished (table A should be set to normal again)
    6. MQT_A is not "synchronized"

    Why?

    Thank you.

    Regards,
    Ratna

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What is your DB2 version and OS?

    How is MQT_A defined?

    Andy

  3. #3
    Join Date
    May 2012
    Posts
    155
    I have 9.7 and Unix

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Again, how is the MQT defined?

    Andy

  5. #5
    Join Date
    May 2012
    Posts
    155
    create table ${SCHEMA}.${TAB} as (
    ...
    ...
    ) DATA INITIALLY DEFERRED REFRESH IMMEDIATE ENABLE QUERY OPTIMIZATION NOT LOGGED INITIALLY COMPRESS YES

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What is the command you are using for SET INTEGRITY?

    Andy

  7. #7
    Join Date
    May 2012
    Posts
    155
    command="SET INTEGRITY FOR ${SCHEMA}.${TAB} ${MQTLIST} IMMEDIATE CHECKED"

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    That was not very helpful. I need to know the exact command that is getting executed.

    Andy

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you use LOAD, you have to REFRESH the MQT. If you use IMPORT (or INSERT, UPDATE, DELETE), The MQT can be automatically synched if the MQT is set up to do that.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    May 2012
    Posts
    155
    Hallo everyone,

    thanks for the suggestions. I decide to just cascade the check state the descendents from the particular table:

    CHECK PENDING CASCADE IMMEDIATE

    So that if I load a file in the table, the MQTs from the table will also be "locked" at the same time.

    The problem I still have:
    During the loading(table is in check state), it is still possible to load in the table. I will have to build a mechanism, which is checking the status of the table, so that a loading is only possible if the status of the table is N.

    I will look, if I have taken REFRESH after the loading.

Posting Permissions

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