Use of MQTs

[Suresh]

At one of the clinets I am working with, we are having a debate about
the use of MQTs or go with traditional way of creating aggregate
tables and populating them. I would go with the MQTs instead of the
traditional way.

Does anyone has any input as to which way is better from an apporach
point of view which would result that is better for an organization.

Any feedback is appreciated.

Thanks,
Suresh

[AK]

is it DSS or OLTP?
do you plan to use REFRESH IMMEDIATE or REFRESH DEFERRED?

[Suresh]

This is a DSS. And as an alternative to an MQT, they are planning to
go with an explicit aggregate table and maintain it manually. We need
to refresh the data once a day. So, REFRESH DEFERRED...
ak************@yahoo.com (AK) wrote in message news:<46**************************@posting.google. com>...

is it DSS or OLTP?
do you plan to use REFRESH IMMEDIATE or REFRESH DEFERRED?

[Buck Nuggets]

my*****@gmail.com (Suresh) wrote in message news:<ef**************************@posting.google. com>...

This is a DSS. And as an alternative to an MQT, they are planning to
go with an explicit aggregate table and maintain it manually. We need
to refresh the data once a day. So, REFRESH DEFERRED...

I've got a warehouse with quite a few explicitely created summary
tables. I think that there's probably a value in both MQTs and
manually-managed summary tables. In my case I had a lot of SQL that
MQT's didn't support:
- declared temp tables
- order by (in order to get top X values)
- processes external to database (involving extraction, data mining
function, and reloading)
- UDFs
- I think that there were a few other restrictions as well at the
time that have been addressed in various fixpacks since 8.1.0.

Additionally, most of the access to these summary tables was by a
custom-developed application. This simple application was able to be
coded directly against the summary tables. While that involved a bit
of education of the programmers - it didn't involve *any* work in
trying to figure out why DB2 was or wasn't rewriting a query properly.
Plus - some queries would never be eligible for rewrite anyway.

In my case manual summary tables have worked fine. However, I am
planning to implement some MQTs for an adhoc environment that I've
got. In this environment it isn't practical to educate the users or
customize the application to support a dozen summary tables - so DB2's
rewriting of queries is really the best choice.

buck

[Suresh]

Thanks for your response.
I understand in cases where MQTs can not be created, an explicit
summary table can be used.
But, the argument is that DB2 optimizer does something goofy in the
way it selects the MQT when querying the base tables. And it is a lot
of maintenance for a DBA. But it is my understanding that you can
create an MQT and disable the optimizer not to select MQT when
querying the base tables as a start.

But, instead of maintaining a explict summary table and make sure it
is loaded properly, why not use an MQT and load it deferred and use
the MQT directly when querying.

-Suresh

bu*********@yahoo.com (Buck Nuggets) wrote in message news:<66**************************@posting.google. com>...

my*****@gmail.com (Suresh) wrote in message news:<ef**************************@posting.google. com>...

This is a DSS. And as an alternative to an MQT, they are planning to
go with an explicit aggregate table and maintain it manually. We need
to refresh the data once a day. So, REFRESH DEFERRED...

I've got a warehouse with quite a few explicitely created summary
tables. I think that there's probably a value in both MQTs and
manually-managed summary tables. In my case I had a lot of SQL that
MQT's didn't support:
- declared temp tables
- order by (in order to get top X values)
- processes external to database (involving extraction, data mining
function, and reloading)
- UDFs
- I think that there were a few other restrictions as well at the
time that have been addressed in various fixpacks since 8.1.0.

Additionally, most of the access to these summary tables was by a
custom-developed application. This simple application was able to be
coded directly against the summary tables. While that involved a bit
of education of the programmers - it didn't involve *any* work in
trying to figure out why DB2 was or wasn't rewriting a query properly.
Plus - some queries would never be eligible for rewrite anyway.

In my case manual summary tables have worked fine. However, I am
planning to implement some MQTs for an adhoc environment that I've
got. In this environment it isn't practical to educate the users or
customize the application to support a dozen summary tables - so DB2's
rewriting of queries is really the best choice.

buck

[Buck Nuggets]

my*****@gmail.com (Suresh) wrote in message news:<ef*************************@posting.google.c om>...

But, instead of maintaining a explict summary table and make sure it
is loaded properly, why not use an MQT and load it deferred and use
the MQT directly when querying.

Well, extra and unnecessary dependencies I suppose. My explicit
summary tables are very easy to manage and publish from. And - have
no dependencies on underlaying tables - which are tweaked about every
few months - either due to large data quality conversions or to major
feature upgrades.

buck