MQTs under z/OS - how to get IMMEDIATE REFRESH functionality

[BD]

I'm doing some cross-platform development, under LUW 8.2 (Windows) for
z/OS 8.

I have some fairly complex queries which address some rather large
tables.

One query takes approximately 30 seconds to complete, once the result
set has been loaded into buffer caches. The initial run of the query
is about 2 minutes.

I created an MQT, and that sped up the performance of my query
considerably.

Although I was able to structure the fullselect to 'obey the laws' for
'REFRESH IMMEDIATE' MQTs under LUW, I appear to be out of luck under z/
OS, simply because, according to the dox I've read, z/OS does not
offer REFRESH IMMEDIATE as an option. It's important that the MQT is
kept well up-to-date with its source tables.

I thought of putting a trigger on the source tables to initiate a
REFRESH TABLE command, but the list of 'allowed' statements which a
trigger can, well, trigger - does not appear to include 'REFRESH
TABLE'.

Best I can find, by scouring the group and other forums, is a solution
that involves creating a trigger that calls a stored procedure, which
initiates a REFRESH TABLE.

Has anyone else found a slightly more intuitive solution for this
problem? Am I incorrect in my conclusion that IMMEDIATE REFRESH is not
supported under z/OS UDB?

[BD]

Re: MQTs under z/OS - how to get IMMEDIATE REFRESH functionality

Has anyone else found a slightly more intuitive solution for this
problem? Am I incorrect in my conclusion thatIMMEDIATERE FRESH is not
supported underz/OSUDB?

Anyone? Anyone?? ...Beuller?

[Serge Rielau]

Re: MQTs under z/OS - how to get IMMEDIATE REFRESH functionality

BD wrote:

>Has anyone else found a slightly more intuitive solution for this
>problem? Am I incorrect in my conclusion thatIMMEDIATERE FRESH is not
>supported underz/OSUDB?

>
Anyone? Anyone?? ...Beuller?

To the best of my knowledge REFRESH IMMEDIATE is not supported in DB2
for zOS.


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[BD]

Re: MQTs under z/OS - how to get IMMEDIATE REFRESH functionality

To the best of my knowledge REFRESH IMMEDIATE is not supported in DB2
for zOS.

This is my understanding as well - what I'm trying to establish is a
mechanism of maintaining an MQT with some assurance that updates to it
will remain closely tied with updates to its source tables. I'm okay
with relying on triggers and stored procs, but have not (for example)
had success in coding a stored proc which calls a 'refresh table'
statement, even though it's mentioned in the z/OS SQL guide as a
supported statement for a stored proc.

I recognize that I cannot initiate a REFRESH TABLE from within a
trigger directly - I've read in the SQL dox about the dozen-or-so
valid statements for triggers.

I believe I should be able to code a stored proc to do it, but the
following proc does not compile:

CREATE PROCEDURE MYSCHEMA.MQTREF RESH ()
LANGUAGE SQL
BEGIN
REFRESH TABLE MQT1;
END

It returns an 'unexpected token "REFRESH TABLE"' message, expecting a
token of "SPACE"

If I schema-qualify the MQT name, returns an "unexpected token
MQT1...followin g MYSCHEMA." message.

Please don't misunderstand - I'm not looking for help coding a stored
proc (I recognize I'm not particularly experienced with them, either).
I'm simply trying to understand how, under z/OS, do DBAs who want the
IMMEDIATE REFRESH functionality of an MQT, get that kind of
functionality? If I want an MQT with some assurance that updates to it
will occur as closely as possible in time to the updates on the source
tables, what's the best strategy, in the absence of an IMMEDIATE
REFRESH MQT?

Do people build tables manually, and rely on manually-coded triggers,
somehow 'emulating' the immediate functionality?

Thanks kindly for the response...

BD