By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,199 Members | 1,458 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,199 IT Pros & Developers. It's quick & easy.

White paper or source for Materialized tables?

P: n/a
Is there a good source for the performance details of Materialized
Query Tables? I have to worry about how often to refresh them in
UDB. Yes, I know that is very vague.
Feb 21 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Ian
--CELKO-- wrote:
Is there a good source for the performance details of Materialized
Query Tables? I have to worry about how often to refresh them in
UDB. Yes, I know that is very vague.
Joe,

As you'd expect, it depends completely on the specific MQT. A couple
of things to consider:

1) When you do a full refresh of an MQT, DB2 effectively does a
DELETE FROM MQT and then INSERT INTO MQT. These are logged
operations, so if you MQT is large this can take a while.

You can help this by turning off logging during the refresh.
Or, you can even (since V9.1) truncate the MQT with "load from
/dev/null replace into MQT" to nearly eliminate the delete
phase of the full refresh. Obviously, both of these options
have recoverability consequences, so you have to be careful.

2) Incremental refresh (via a staging table) is really great, if
you can use it.

3) During an MQT refresh, there is an exclusive lock on the table
(not sure if it's a Z or X lock), so there are availability
considerations if you're refreshing the tables while users are
querying the database.
Also: Refresh frequency is mostly a function of the users' tolerance
for stale data. Of course, all users will tell you that they can't
tolerate any stale data. ;-)

Thanks -
Ian Bjorhovde
Feb 21 '08 #2

P: n/a
>Of course, all users will tell you that they can't tolerate any stale data. <<

LOL! I tell people that Dr. Land did not invent the Polaroid camera.
He invented the "ten second eternity" -- we used to wait for week to
get black and white photographs developed at a drug store, but after
the Polaroid camera, we go crazy waiting ten seconds for a color
picture.
Feb 21 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.