Q and A

Asked and Answered

Q. How do you determine whether to use a materialized query table (MQT) using DB2 Cube Views or DB2 OLAP Server for a particular analytic need?

Robert Catterall responds:

Regarding materialized query tables and the DB2 OLAP Server, it's not a matter of either/or. MQTs can boost the performance of online analytical processing (OLAP) performed via tools such as the DB2 OLAP Server. OLAP typically involves the aggregation of data values in a database, and MQTs provide a means of pre-aggregating data that will subsequently be accessed via the OLAP tool.

So, where does DB2 Cube Views come in? Well, MQTs are certainly good if you're interested in high-performance OLAP. The trick, however, is to create the right MQTs to support your OLAP workload. DB2 Cube Views allows you to enter information about the data cubes you want to build (a part of the OLAP work your users want to do) from data in the DB2 database. This information is stored in tables that are extensions of the DB2 catalog. DB2 Cube Views can then use this information to give you expert advice on the MQTs that should be created to accelerate the building of the data cubes.

DB2 Cube Views doesnęt construct MQTs. You define them, and DB2 constructs them based on your definitions. DB2 Cube Views helps to ensure that you're defining the right MQTs to support your OLAP objectives. Without DB2 Cube Views, you might fail to create MQTs that would improve your OLAP processing, or you might create MQTs that really don't improve OLAP performance but consume CPU (for build and update) and disk storage resources.

As previously mentioned, DB2 Cube Views can work very well with OLAP tools such as the DB2 OLAP Server, and in fact DB2 Cube Views was designed to boost the performance of OLAP accomplished using an OLAP tool.

Thereęs some very good information about MQTs in the DB2 UDB for Linux, Unix, Windows documentation (http://www-306.ibm.com/software/data/db2/udb/support/manualsv8.html)

I'd recommend the section titled, "Creating a materialized query table" in Chapter 4 of the Administration Guide: Implementation. (I usually refer to the manuals for Version 8.2 of DB2 for LUW).

See a complete archive of reader/author Q&As .