Re: MQT Refresh Immediate

I'm going a bit Rube Goldberg here, Dave, but let me see if I understand.

1. You have a lot of data in 14 tables
2. You want a view over that data as of a point in time (the MQT)
3. You want to be able to update that MQT on demand without locking

Since an MQT is basically an extract, theoretically, if you did your MQT over a mirror of those files as opposed to the files themselves, you'd be fine.  That is, of course, supposing that the mirrors were in sync with the original tables as of a given point in time.

So what about this?  Create mirrors of the files in question. Journal the originals.  Whenever you need to refresh the MQT, perform APYJRNCHG to apply the latest changes to the mirrors.  Run the MQT over those mirrored files.  If you're worried about how long the APYJRNCHG would take, you can always do that periodically so that when you're ready to create the MQT, you have a smaller backlog to apply.

Even easier would be to simply CPYF CRTFILE(*YES) from the originals to the mirrors, but you have the issue of data currency; you might get versions of the files that are slightly out of sync.  If that's not an issue, CPYF is less complex than journaling.

ANd of course, this whole thing is predicated on having the space.  Now you need not only the MQT, but you also need copies of all the source files.  But it does address the issue of recreation on demand.



On 4/25/2018 12:16 PM, dlclark@xxxxxxxxxxxxxxxx wrote:

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx> wrote on 04/25/2018
12:55:43 PM:

I assume you looked at creating triggers on the source tables to keep
the MQT up to date?

No, we did not. When we created it we thought we would be able to
refresh it when we wanted to. But with 14 joined tables and 8.99 million
materialized rows involved, it would be a bit of a task to try and
maintain the MQT programmatically.


Sincerely,

Dave Clark