DB2 Integrated Cluster Environment Deployment Guide

104 DB2 Integrated Cluster Environment Deployment Guide

3.3.8 Size for MDC utilization

You will get significantly improved performance and maintenance advantage

when using MDC, if you choose the right set of dimensions for clustering a table

and the properly extent sizes. On the other hand, if incorrect choices are made,

utilization of space might be poor and performance could degrade. To design

good MDC tables you must first identify potential dimension candidates, and then

evaluate the storage space needed for an MDC table using some variation of

those dimensions.

3.3.9 Size for MQT utilization

A materialized query table (MQT) is a table whose definition is based on the

result of a query. The data of MQT is in the form of precomputed results that are

taken from one or more tables on which the MQT definition is based. It can be

used for applications that have a pattern of analysis using more or less similar

queries with minor variations in a query’s predicates, and are often issued

repetitively against large volumes of data.

Based on this, the size for a MQT is estimated using the same calculation as for

a base table. For more details about table sizing see 3.3.7, “Size the tables” on

page 103. But, if your MQT is also defined as MDC, you also have to consider

the MDC sizes.

3.3.10 Configure DB2 UDB

In this section we provide some important DB2 UDB configurations and

recommendations for your database.

The topics included are:

 Database logs

 Buffer pool considerations

 DIAGPATH directory path placement

Database logs

In this section we describe management and performance considerations

relating to database logging. If set up inappropriately for your workload, database

logging could become a significant overhead.

The topics covered are:

 On which disks are the logs to be placed?

 Circular or archival log?

 Logging performance.

 File system or raw logical volume.

Chapter 3. Planning and design 105

 Mirroring.

 Number of log files.

 Size of logs.

On which disks are the logs to be placed

Due to the importance of the log files, it is recommended that the log files should

always reside on their own physical disk(s), separate from the rest of the

database objects. The disks ideally should be dedicated to DB2 logging to avoid

the possibility of any other processes accessing or writing to these disks.

Availability

Irrespective of the type of logging you choose, whether it be circular logging or

archival logging, the availability of the physical disks is crucial to the database.

For this reason, it is strongly recommended that you protect the log against

single disk failures:

 By using RAID 1 (mirroring the log devices) recommended for SSA disks.

 By storing log files on a RAID 5 array recommended for ESS disks. Since a

RAID 5 array has a large cache, the write penalty effects can be significantly

decreased.

For details about RAID levels, see 3.2.1, “Summary of the most popular RAID

levels” on page 65.

Circular or archival log

DB2 UDB can operate in one of two modes relating to transaction logging:

 Circular logging

This means that writes to the database are logged to enable rollback of

outstanding units of work. However, after a commit, log records relating to

that unit of work are not retained, and the log files will be reused in a circular

manner. This means that it is not possible to replay transactions to recover

data when operating in this mode. Online backups, table space backups, and

roll forward are not allowed when using circular logging.

 Archival logging

In this mode writes are logged in the same way, but log records are retained

after a commit. This means that following a restore of the database, it is

possible to replay transactions or roll forward through log records, up to a

point in time or the end of the log records.

Tip: It is highly recommended to place the database logs on separate disks

from the data.