216 DB2 Cube Views: A Primer
4.11 Configuration considerations
When using MQTs, two of the main questions will be:
򐂰 How to estimate the memory required for MQTs
򐂰 How to estimate the storage required for MQTs
4.11.1 Estimating memory required for MQTs
In general, MQTs can help to reduce the amount of memory required in a query
environment because they can help reduce the need for sorting. By avoiding sort
data, you are avoiding use of a large amount of memory for sort purposes.
However, there is a certain time where large amounts of memory can be
required. It happens during the refresh of the MQT. Depending on the size of the
tables involved on the MQT, the large amount of memory is required for
The following recommendation apply only to non-clustered (or single node DB2)
SORTHEAP is usually very important for MQT REFRESH.
The size of the sortheap allocation depends on the complexity of the MQT.
Look at the Explain plan of the full select used by the MQT to estimate
sortheap requirements and size accordingly....
If you have the memory to consume (32 bit versus 64 bit), this is a good
candidate for over allocation.
You need to ensure that the dbm cfg sort heap threshold parameter is
sized appropriately to support the sortheap specified.
Note that the sortheap allocation will usually be significantly less in the
runtime environment.
An MQT refresh may require a lot of statement heap. Otherwise, you may
get an error like “statement too complex”.
4.11.2 Estimating space required for MQTs
DB2 Optimization Advisor provides you with a good estimate on the size for the
MQT based on sampling of the data as already discussed in “Specify disk space
and time limitations” on page 161. If you need to change the DDL (we don’t
recommend it) for the MQT, you need to recalculate the space required for your
Chapter 4. Using the cube model for summary tables optimization 217
An MQT is a table, which contains pre-computed results whose definition is
based on the result of a query execute against the underlying tables. Based on
this, the size for an MQT is estimated using the same calculation as for a base
table. For information on how to estimate the size of the MQT, please refer to
Chapter 2 in the redbook,
Up and Running with DB2 UDB ESE: Partitioning for
Performance in an e-Business Intelligence World,
Besides the space required for the MQTs, you also need additional temporary
space for joins and aggregations.
The temporary tablespace is an important consideration during full REFRESH of
MQTs. If the MQT has a lot of measures, rollups, and/or group by elements,
more tempspace is generally required.
If the MQT's size estimate provided by DB2 Cube Views is very large, it is
probably an indication that you may need more tempspace.
The following formula helps you to estimate the TEMP space required for refresh
on an MQT:
TEMPSPACE Required = (# of pages required) * (pagesize) where
# of pages required) = (Total # of rows in the MQT) * (10+10) / 2560)
(10 + 10) = Is the size stored in the TEMP per MQT row. It is defined
twice in situation where it needs to the DELETE of old data and
INSERT of new data in the MQT.
2560 is a number based on the fact that we store 256 rows per page
(assuming 256 slots in a page of 10 bytes each).
1. The Delete is referring to deleting the old data in the MQT. If it was an initial
population, you do not need to account for this.
2. Note that the result of the formula gives the number of pages and the
number 2560 is independent of the page size. Depending on the page
size, we need to compute disk space accordingly. An 8K-page size would
require double amount of disk compared to a 4K-page size.
3. Particular care needs to be taken at the catalog node because the refresh
process of MQTs also uses TEMP space on this node. If you have a
separate the catalog node from the data nodes with very little TEMP
tablespace, you can have problems to perform full REFRESH on MQTs.
Make sure you add additional TEMP space on this node to avoid any
problems during the refresh process.

Get DB2 Cube Views: A Primer now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.