db2建物化表讨论

由于应用有一个3表联查视图，造成查询3表行锁过多，引起性能下降。这两天考虑建物化表替换视图查询。
也是遇到了几个问题，现在晒晒已做参考。

一、先建一张简单的物化表

1. CREATETABLETEST1AS(
2. SELECTA.REF_IDASIDFROMPHOTO_REFASA
3. )
4. DATAINITIALLYDEFERREDREFRESHIMMEDIATE；

CREATE TABLE TEST1 AS (
      SELECT A.REF_ID AS ID FROM PHOTO_REF AS A
    )
    DATA INITIALLY DEFERRED REFRESH  IMMEDIATE；

1. setintegrityfortest1immediatecheckednotincremental

 set integrity for test1  immediate checked not incremental

1、首先 REFRESH IMMEDIATE指定由系统维护的物化表，不允许对该物化表 insert、update 或 delete 操作。其中数据都是对底层表的更改（即 insert、update 或 delete 操作）来更新（系统触发）。
2、 DATA INITIALLY DEFERRED
在执行 CREATE TABLE 语句的时候，并不将数据插入到表中。就处于检查暂挂（check pending）状态。
3、 IMMEDIATE CHECKED 子句规定，根据用于定义该 MQT 的查询对数据进行检查，并刷新数据。NOT INCREMENTAL 子句规定对整个表进行完整性检查。

二、建一张3表联查的物化表
我的这个视图有一个基表ISSUE,它与另两表联查并UNION ALL成视图。我照着这个语法建物化表却失败了。

1. CREATETABLETEST2AS(
2. SELECT
3. A.REF_IDASID,
4. A.PHOTO_NAMEASPHOTO_NAME,
5. C.ISSUE_IDASISSUE_ID
6. FROM
7. PHOTO_REFASA,ISSUEASC
8. UNIONALL
9. SELECT
10. B.PROCESS_IDASID,
11. B.PHOTO_NAMEASPHOTO_NAME,
12. C.ISSUE_IDASISSUE_ID
13. FROM
14. PHOTO_PROCESSASB,ISSUEASC
15. )
16. DATAINITIALLYDEFERREDREFRESHDEFERRED
17. ENABLEQUERYOPTIMIZATION
18. MAINTAINEDBYUSER；

CREATE TABLE TEST2 AS(
     SELECT
        A.REF_ID AS ID,
        A.PHOTO_NAME AS PHOTO_NAME,
        C.ISSUE_ID AS ISSUE_ID
     FROM 
        PHOTO_REF AS A,ISSUE AS C
   UNION ALL
     SELECT
        B.PROCESS_ID AS ID,
        B.PHOTO_NAME AS PHOTO_NAME,
        C.ISSUE_ID AS ISSUE_ID
     FROM
        PHOTO_PROCESS AS B,ISSUE AS C
    )
    DATA INITIALLY DEFERRED REFRESH DEFERRED 
    ENABLE QUERY OPTIMIZATION
    MAINTAINED BY USER；

1. setintegrityfortest2materializedqueryimmediateunchecked；

set integrity for test2 materialized query immediate unchecked；

1、 REFRESH DEFERRED
因为是union表，系统无法确定唯一的基表，保证数据完整性。系统维护该物化表便不可能了。所以采用用户维护。
2、 MAINTAINED BY USER
而就因为我的物化表不是单表为基表，所以也不能MAINTAINED BY SYSTEM，用REFRESH TABLE 来更改值。所以采取 MAINTAINED BY USER，这样我将有完全insert、update 或 delete 操作权限。
3、 MATERIALIZED QUERY IMMEDIATE UNCHECKED
该表将启用完整性检查，但是不必检查它是否违反了完整性约束，便可以使之脱离检查暂挂状态。

注意：我的两个联表中有LONG VARCHAR 字段，我建物化表时就报错。在网上查资料后知道，物化表不能建大字段。

三、建立触发器维护物化表
对于这个3表联查的物化表，我建了两组触发器处理基表和2个关联表insert、update、delete操作。

1. CREATETRIGGERTEST2_REF_I_TRIG
2. AFTERINSERTONDB2INST1.ISSUEREFERENCINGNEWASNFOREACHROWMODEDB2SQL
3. BEGINATOMIC
4. DECLAREREF_ID_SVARCHAR(30);
5. DECLAREPHOTO_NAME_SVARCHAR(90);
6. SETREF_ID_S=
7. (SELECTI.REF_IDFROMISSUEASIWHEREI.ISSUE_ID=N.ISSUE_ID);
8. SETPHOTO_NAME_S=
9. (SELECTI.PHOTO_NAMEFROMPHOTO_REFASIWHEREI.REF_ID=REF_ID_S);
10. INSERTINTODB2INST1.TEST4(ID,PHOTO_NAME,ISSUE_ID)VALUES(REF_ID_S,PHOTO_NAME_S,N.ISSUE_ID);
11. END

CREATE TRIGGER TEST2_REF_I_TRIG 
       AFTER INSERT ON DB2INST1.ISSUE REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL 
       BEGIN ATOMIC 
           DECLARE REF_ID_S VARCHAR(30);  
           DECLARE PHOTO_NAME_S VARCHAR(90);
           SET REF_ID_S = 
                  (SELECT I.REF_ID FROM ISSUE AS I WHERE I.ISSUE_ID = N.ISSUE_ID); 
           SET PHOTO_NAME_S = 
                  (SELECT I.PHOTO_NAME FROM PHOTO_REF AS I WHERE I.REF_ID = REF_ID_S);
           INSERT INTO DB2INST1.TEST4(ID,PHOTO_NAME,ISSUE_ID) VALUES (REF_ID_S,PHOTO_NAME_S,N.ISSUE_ID);
         END

四、总结
由于涉及到公司代码保密，大部分数据匿名处理。数据库对象及存储非常复杂，吾将上下而求索。