1

Have a slow query. Need an MQT so the optimizer will rewrite the query and use my MQT instead.

Problem: a manual full refresh is much much faster than using refresh immediate type or a staging table: because of the structure of our db updating a single row takes 6 minutes! Just refreshing the MQT takes 30s.

So I chose the refresh deferred type, but db2 has no such thing as automatic MQT invalidation, when underlying tables change! I tried updating the table, but my MQT remains accessible.

What to do? Create a trigger on each table, which calls set integrity off?

current DDL:

create table xfacetattr as (
  SELECT
    distinct
  -- filter columns:
    AVD.language_id,
    A.storeent_id A_storeent_id,
    ADSC.mastercatalog_id
    ,
  -- return columns:
    ADSC.SRCHFIELDNAME,
    AVD.value,
    AV.storeent_id,
    AVD.sequence,
    AVD.image1,
    AVD.image2
  FROM
    attrdictsrchconf ADSC,
    attr A,
    attrval AV,
    attrvaldesc AVD
  WHERE
    ADSC.ATTR_ID           IS NOT NULL
  AND ADSC.attr_id          = A.attr_id
  AND A.facetable           = 1
  AND AV.attr_id            = A.attr_id
  AND AV.attrval_id         = AVD.attrval_id
  --and 1=2
) data initially deferred refresh deferred
;


create trigger xfacetattr_t1 after update of attr_id,facetable, storeent_id on attr FOR EACH STATEMENT call XEXECUTE('set integrity for xfacetattr off');
create trigger xfacetattr_t2 after insert on attr FOR EACH STATEMENT call XEXECUTE('set integrity for xfacetattr off');
create trigger xfacetattr_t3 after delete on attr FOR EACH STATEMENT call XEXECUTE('set integrity for xfacetattr off');

create trigger xfacetattr_t4 after update of mastercatalog_id,SRCHFIELDNAME,ATTR_ID on attrdictsrchconf FOR EACH STATEMENT call XEXECUTE('set integrity for xfacetattr off');
create trigger xfacetattr_t5 after insert on attrdictsrchconf FOR EACH STATEMENT call XEXECUTE('set integrity for xfacetattr off');
create trigger xfacetattr_t6 after delete on attrdictsrchconf FOR EACH STATEMENT call XEXECUTE('set integrity for xfacetattr off');

create trigger xfacetattr_t7 after update of storeent_id,attr_id,attrval_id on attrval FOR EACH STATEMENT call XEXECUTE('set integrity for xfacetattr off');
create trigger xfacetattr_t8 after insert on attrval FOR EACH STATEMENT call XEXECUTE('set integrity for xfacetattr off');
create trigger xfacetattr_t9 after delete on attrval FOR EACH STATEMENT call XEXECUTE('set integrity for xfacetattr off');

create trigger xfacetattr_t10 after update of language_id,value,sequence,image1,image2,attrval_id on attrvaldesc FOR EACH STATEMENT call XEXECUTE('set integrity for xfacetattr off');
create trigger xfacetattr_t11 after insert on attrvaldesc FOR EACH STATEMENT call XEXECUTE('set integrity for xfacetattr off');
create trigger xfacetattr_t12 after delete on attrvaldesc FOR EACH STATEMENT call XEXECUTE('set integrity for xfacetattr off');
  • I'm not sure how issuing set integrity off on the MQT solves your problem. – mustaccio Jun 26 '15 at 15:00
  • @mustaccio doing this ensures query result always consistent, just slower sometimes, because MQT not used by optimizer. I can poll SYSCAT.TABLES.ACCESS_MODE and issue set integrity for xfacetattr immediate checked when it's not 'F' – basin Jun 26 '15 at 15:22
  • "Automatic MQT invalidation" is implemented by setting the value of CURRENT REFRESH AGE in the session, which will prevent the optimizer from using the MQT beyond the refresh age threshold. – mustaccio Jun 26 '15 at 16:39
  • @mustaccio REFRESH AGE register name is irrelevant to what it really does – basin Jun 26 '15 at 20:26
  • It has only 2 positions: off (0) and on (99999999999999). When switched on, query rewrite is allowed. – basin Jun 26 '15 at 20:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.