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