Hello Ian, hello Jeff,
first of all - thank you for your quick reply. So I will go a little
more into detail here:
Probably the best course of action is to identify the reason why the
MQT can't be REFRESHed IMMEDIATEly. There are many reasons why it
cannot be, but thankfully DB2 will report the specific reason when it
throws the error. If you can reply with the cause, we can possibly
find a workaround.
The reason why i cannot have the MQT refreshed immediately is, as far
as I understand, because of the number of joins contained in the
underlying query (I hope the statement will be readable in your
newsclients...):
CREATE TABLE V_Doc_TRE (class, sourceid, tragetid) AS(
SELECT 'DOCUMENT' AS class, DOCID AS sourceid, DOCID as targetid FROM
DOCUMENT
UNION
SELECT 'TERM' AS class,term_1_7.TERMID AS targetid ,
document_1_0.DOCID AS sourceid FROM DOCUMENT document_1_0, TRE
tre_1_1, TERM term_1_2, TERM term_1_3, TERM term_1_4, TERM term_1_5,
TERM term_1_6, TERM term_1_7 WHERE document_1_0.DOCID = tre_1_1.DOCID
AND tre_1_1.TERMID = term_1_2.TERMID AND term_1_2.PARENT =
term_1_3.TERMID AND term_1_3.PARENT = term_1_4.TERMID AND
term_1_4.PARENT = term_1_5.TERMID AND term_1_5.PARENT =
term_1_6.TERMID AND term_1_6.PARENT = term_1_7.TERMID
UNION
SELECT 'TERM' AS class,term_2_6.TERMID AS targetid ,
document_2_0.DOCID AS sourceid FROM DOCUMENT document_2_0, TRE
tre_2_1, TERM term_2_2, TERM term_2_3, TERM term_2_4, TERM term_2_5,
TERM term_2_6 WHERE document_2_0.DOCID = tre_2_1.DOCID AND
tre_2_1.TERMID = term_2_2.TERMID AND term_2_2.PARENT = term_2_3.TERMID
AND term_2_3.PARENT = term_2_4.TERMID AND term_2_4.PARENT =
term_2_5.TERMID AND term_2_5.PARENT = term_2_6.TERMID
UNION
SELECT 'TERM' AS class,term_3_5.TERMID AS targetid ,
document_3_0.DOCID AS sourceid FROM DOCUMENT document_3_0, TRE
tre_3_1, TERM term_3_2, TERM term_3_3, TERM term_3_4, TERM term_3_5
WHERE document_3_0.DOCID = tre_3_1.DOCID AND tre_3_1.TERMID =
term_3_2.TERMID AND term_3_2.PARENT = term_3_3.TERMID AND
term_3_3.PARENT = term_3_4.TERMID AND term_3_4.PARENT =
term_3_5.TERMID
UNION
SELECT 'TERM' AS class,term_4_4.TERMID AS targetid ,
document_4_0.DOCID AS sourceid FROM DOCUMENT document_4_0, TRE
tre_4_1, TERM term_4_2, TERM term_4_3, TERM term_4_4 WHERE
document_4_0.DOCID = tre_4_1.DOCID AND tre_4_1.TERMID =
term_4_2.TERMID AND term_4_2.PARENT = term_4_3.TERMID AND
term_4_3.PARENT = term_4_4.TERMID
UNION
SELECT 'TERM' AS class,term_5_3.TERMID AS targetid ,
document_5_0.DOCID AS sourceid FROM DOCUMENT document_5_0, TRE
tre_5_1, TERM term_5_2, TERM term_5_3 WHERE document_5_0.DOCID =
tre_5_1.DOCID AND tre_5_1.TERMID = term_5_2.TERMID AND term_5_2.PARENT
= term_5_3.TERMID
UNION
SELECT 'TERM' AS class,term_6_2.TERMID AS targetid ,
document_6_0.DOCID AS sourceid FROM DOCUMENT document_6_0, TRE
tre_6_1, TERM term_6_2 WHERE document_6_0.DOCID = tre_6_1.DOCID AND
tre_6_1.TERMID = term_6_2.TERMID
) DATA INITIALLY DEFERRED REFRESH IMMEDIATE
If it's not possible to create the refresh immediate MQT, and you're
willing to call 'REFRESH MQT' every time there is a change to the base
table, I'd be curious to see why? During the refresh the MQT will be
locked (i.e. unavailable for your app). If the refresh is quick this
may not be a problem, but if it is quick, I wonder if you really need
the MQT in the first place?
What I am trying to achieve with this (it is part of an academic
prototype) is that I want to precompute some "paths" in an object-
structure which will later be used to issue joins against this MQT so
that I do not have to do this path computation in every query. This is
also the reason why I do not mind about the performance of the
refresh: the object structure from which the paths will be computed
will change very infrequently, compared to the number of joins against
the MQT. So it is important to keep the MQT in-sync whenever I need
it, but I cannot predict when the base-tables will change so I want
the DB to keep it in sync instead of regularily issuing "REFRESH"-
commands from the "outside". Since my approach should be independent
of the application, there is also no way to change the application
(which might know when base-tables are updated) and make it update the
MQT.
Also: What's the error you get when trying to call 'REFRESH TABLE'
from your stored proc? This does work fine.
My first try was to install the following trigger (yes, '@' is
configured as delimiter in CLI :)):
CREATE TRIGGER updateView AFTER INSERT ON TERM
FOR EACH STATEMENT
MODE DB2SQL
BEGIN ATOMIC
REFRESH TABLE V_Doc_TRE;
END@
which gives me a
sqlcode : -104
sqlstate : 42601
Then I tried to create the stored procedure and call that procedure
from a similar trigger:
CREATE PROCEDURE refreshView ()
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
REFRESH TABLE V_Doc_TRE;
END@
which again leads to
sqlcode : -104
sqlstate : 42601
I always thought there was a semantic problem with this procedure, but
if it works for you, Ian, maybe there is just some syntactic mistake?
Thanks again,
Michael
