Wednesday, December 30, 2009

User-Maintained MQTs



[ Team LiB ]





User-Maintained MQTs


New in v8, user-maintained MQTs enable you to load precomputed data into a user-defined summary table.


NOTE



User-maintained summary tables make it easier for Oracle users to migrate to DB2.



The primary difference between a system-maintained summary table and a user-maintained MQT is that with a user-maintained MQT, the creation and loading of the table is under user control.


Since the data is generated by the user, it is not refreshed by DB2. It is the user's responsibility to periodically update the table based on your particular business rules. Use the MAINTAINED BY USER option on the CREATE SUMMARY TABLE statement to create a user-maintained summary table. The following is an example of how to load a user-maintained summary table:



INSERT INTO UMST_INVOICE
  SELECT * FROM
    (SELECT VENDOR, COUNT(*)  SUM (INV_AMT)
     FROM INVOICE
     GROUP BY VENDOR  )
AS V;

To prevent other summary tables from being used while loading the table, you can issue the following command:



SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION = NONE;

Set this register back to ALL when you have completed loading the user-maintained summary table. Also, don't forget to set the CURRENT REFRESH AGE special register to ANY so that the optimizer will consider the summary table for optimization.


NOTE



Insert, Update, and Delete statements can be used to modify and delete data in a user-maintained MQT.






    [ Team LiB ]



    Posted by at

    No comments:

    Post a Comment

    Subscribe to: Post Comments (Atom)