Keeping MQT in-sync

[Michael.Guppenberger]

Hello everyone,

I am currently trying to create a materialized query table which
should be in-sync all the time. So my first attempt was to use the
"REFRESH IMMEDIATE" option of the create table statement. However, due
to the complexity of the underlying query, this is not allowed.

As performance is not an issue and I absolutely want to have the MQT
in-sync my next try was to create a trigger on the base tables of the
MQT. This trigger should have called "REFRESH TABLE" on my MQT. Again,
this did not work (the CLI did not accept my trigger definition).

The next idea was to create a stored procedure refreshing the view;
but again, this did not work although "REFRESH TABLE" is explicitly
listed as an allowed statement in stored procedures modifying sql
data.

Has anybody got an idea how to overcome these problems? I did not post
my SQL-statements on purpose because I don't want to make my question
more complicated than it is; maybe there is no solution to my problem?
If anyone has detailed ideas what the problem might be, I can post any
of my statements at any time.

Thanks in advance & regards,
Michael

[jefftyzzer]

On Mar 20, 9:15 am, Michael.Guppenb [e-mail] wrote:

Hello everyone,

I am currently trying to create a materialized query table which
should be in-sync all the time. So my first attempt was to use the
"REFRESH IMMEDIATE" option of the create table statement. However, due
to the complexity of the underlying query, this is not allowed.

As performance is not an issue and I absolutely want to have the MQT
in-sync my next try was to create a trigger on the base tables of the
MQT. This trigger should have called "REFRESH TABLE" on my MQT. Again,
this did not work (the CLI did not accept my trigger definition).

The next idea was to create a stored procedure refreshing the view;
but again, this did not work although "REFRESH TABLE" is explicitly
listed as an allowed statement in stored procedures modifying sql
data.

Has anybody got an idea how to overcome these problems? I did not post
my SQL-statements on purpose because I don't want to make my question
more complicated than it is; maybe there is no solution to my problem?
If anyone has detailed ideas what the problem might be, I can post any
of my statements at any time.

Thanks in advance & regards,
Michael

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.

--Jeff

[Ian]

jefftyzzer wrote:

On Mar 20, 9:15 am, Michael.Guppenb [e-mail] wrote:

>Hello everyone,

I am currently trying to create a materialized query table which
should be in-sync all the time. So my first attempt was to use the
"REFRESH IMMEDIATE" option of the create table statement. However, due
to the complexity of the underlying query, this is not allowed.

As performance is not an issue and I absolutely want to have the MQT
in-sync my next try was to create a trigger on the base tables of the
MQT. This trigger should have called "REFRESH TABLE" on my MQT. Again,
this did not work (the CLI did not accept my trigger definition).

The next idea was to create a stored procedure refreshing the view;
but again, this did not work although "REFRESH TABLE" is explicitly
listed as an allowed statement in stored procedures modifying sql
data.

Has anybody got an idea how to overcome these problems? I did not post
my SQL-statements on purpose because I don't want to make my question
more complicated than it is; maybe there is no solution to my problem?
If anyone has detailed ideas what the problem might be, I can post any
of my statements at any time.

Thanks in advance & regards,
Michael

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.

I would also recommend this.

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?

Also: What's the error you get when trying to call 'REFRESH TABLE'
from your stored proc? This does work fine.

[Michael.Guppenberger]

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.DO CID 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.DO CID = 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.DO CID 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.DO CID = 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.DO CID 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.DO CID = 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.DO CID 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.DO CID = 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.DO CID AS sourceid FROM DOCUMENT document_5_0, TRE
tre_5_1, TERM term_5_2, TERM term_5_3 WHERE document_5_0.DO CID =
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.DO CID AS sourceid FROM DOCUMENT document_6_0, TRE
tre_6_1, TERM term_6_2 WHERE document_6_0.DO CID = 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

[Michael.Guppenberger]

No ideas? Starts getting urgent for me ...

Regards,
Michael

[Lennart]

Mi************* *****@gmail.com wrote:

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...) :

I dont think that is the problem. I think you will need an aggregate (
and I think it will have to be count(*) ), if you want refresh
immediate. Atleast that was the case with V8. Try the following and see
if it works ( not saying that it is a good idea though )

CREATE TABLE V_Doc_TRE (class, sourceid, tragetid) AS(

select class,sourceid, targetid, count(*) from (

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.DO CID 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.DO CID = 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.DO CID 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.DO CID = 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.DO CID 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.DO CID = 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.DO CID 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.DO CID = 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.DO CID AS sourceid FROM DOCUMENT document_5_0, TRE
tre_5_1, TERM term_5_2, TERM term_5_3 WHERE document_5_0.DO CID =
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.DO CID AS sourceid FROM DOCUMENT document_6_0, TRE
tre_6_1, TERM term_6_2 WHERE document_6_0.DO CID = tre_6_1.DOCID AND
tre_6_1.TERMID = term_6_2.TERMID

) X (class,sourceid ,targetid)

) DATA INITIALLY DEFERRED REFRESH IMMEDIATE

/Lennart

[Knut Stolze]

Lennart wrote:

Mi************* *****@gmail.com wrote:

>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... ):

I dont think that is the problem. I think you will need an aggregate (
and I think it will have to be count(*) ), if you want refresh
immediate. Atleast that was the case with V8.

You could use a REPLICATED MQT without aggregates.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Michael Guppenberger]

Unfortunately, none of your proposals (adding aggregates, using
replicated MQT) did work for me. Instead of focusing on how to get the
MQT "refreshed immediate", I would rather like to find out why my
stored procedure refreshing the table cannot be issued.. Any ideas
about that?

This

CREATE PROCEDURE refreshView ()
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
REFRESH TABLE V_Doc_TRE;
END@

does not work, as I mentioned above, and I do not know why ...

Regards,
Michael

[Michael Guppenberger]

I think I found a solution. Instead of writing a SQL-Stored Procedure,
I embedded the "REFRESH TABLE"-statement into an external procedure
written in Java which can then be called from the respective trigger.
I still have to test it, but I think it should work.

Thanks to all who answered.

Regards,
Michael