DB2 Integrated Cluster Environment Deployment Guide

170 DB2 Integrated Cluster Environment Deployment Guide

series of questions. You can also invoke Configuration Advisor through the

command-line processor, too. We describe these two methods separately. In

addition, we introduce some commonly used DB2 registry and environment

variables related to performance.

4.4.1 Tuning your DB2 through Configuration Advisor GUI tool

Once your database is created, we recommend that you use Configuration

Advisor to check the database and instance parameter settings. The default

settings frequently are not enough. In our lab environment, we created a

database TPCH. We do initial configuration for the TPCH database and instance

using Configuration Advisor.

1. Start your control center. From the menu, click Tools

→ Wizards. The

Wizards window appears (Figure 4-13); then select Configuration Advisor,

and click OK.

Figure 4-12 Wizards window

2. The Database Selection window appears (Figure 4-14 on page 171). Select

the system, instance and database in the list box, then click OK.

Figure 4-13 Select database

Chapter 4. Implementation 171

3. The Configuration Advisor window appears (Figure 4-15 on page 172). The

Configuration Advisor needs some information from you. In the next few

steps, the tool will ask for your input. If you use the GUI equivalent command

AUTOCONFIGURE, these input are specified as the command options. We

list these options in Table 4-12 on page 181 when we discuss the

AUTOCONFIGURE command.

Figure 4-14 Configuration Advisor window

4. Specify the percent of memory used for this database. This is equal to the

num_percent parameter in the AUTOCONFIGURE command. When you

specify percent of memory, consider the following:

a. On systems with multiple logical partitions, the mem_percent parameter

refers to the percentage of memory that is to be used by all logical

partitions. For example, if DB2 uses 80 percent of the memory on the

system, specify 80 percent regardless of the number of logical partitions.

b. The database configuration recommendations made will be adjusted for

one logical partition. This command makes configuration

recommendations for the currently connected database, assuming that the

database is the only active database on the system. If more than one

database is active on the system, adjust the mem_percent parameter to

172 DB2 Integrated Cluster Environment Deployment Guide

reflect the current database's share of memory. For example, if DB2 uses

80 percent of the system's memory and there are two active databases on

the system that should share the resources equally, specify 40 percent (80

percent divided by two databases) for the parameter mem_percent.

In our lab environment, we have a dedicated database server with only one

TPCH database, so we use 80 percent memory for this database.

Figure 4-15 Specify percent of memory used for TPCH database

5. Specify the workload type for your database (Figure 4-16 on page 173).

There are three options:

– Queries (data warehousing)

–Mixed

– Transactions (order entry)

If your system is a typical Online Analytical Processing (OLAP) system, most

of the operations are complex queries; you select Queries (data

warehousing). If your system is a typical Online Transactional Processing

(OLTP) system, you select Transactions (order entry); If your system is

neither a pure OLTP nor a pure OLAP system, it has some functions of both

OLAP and OLTP, then you choose Mixed. In our example, it is a OLAP

system, so we choose Queries (data warehousing).

Chapter 4. Implementation 173

Figure 4-16 Specify workload type for your database

6. Specify a database administration priority (Figure 4-17 on page 174). There

are three options:

– Faster transaction performance (slower recovery)

–Both

– Faster database recovery (slower transaction)

Select the proper administration priority according to your requirements. This

question mainly affects the database configuration parameters such as

LOGBUFSZ and MINCOMMIT. How to select it depends on your system’s

requirements. We select Both in our example.

174 DB2 Integrated Cluster Environment Deployment Guide

Figure 4-17 Specify a database administration priority

7. Specify whether your database is populated with data (Figure 4-18 on

page 175). You should select Yes or No according to whether your database

is populated with data or not. In our example, we have loaded data into

database TPCH, so we select Yes.