DB2 Cube Views: A Primer

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

SORTHEAP size.

The following recommendation apply only to non-clustered (or single node DB2)

configurations:

򐂰 SORTHEAP:

– 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.

򐂰 STATEMENT HEAP:

– 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

MQT.

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,

SG24-6917.

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).

Notes:

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.