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
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:
Buffer pool considerations
DIAGPATH directory path placement
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?
File system or raw logical volume.
Chapter 3. Planning and design 105
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.
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
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:
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.
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.