Learning Visually with Examples [Electronic resources]

Raul F. Chong, Clara Liu, Sylvia F. Qi, Dwaine R. Snow

نسخه متنی -صفحه : 312/ 204
نمايش فراداده

16.3. The DB2 Configuration Advisor

The Configuration Advisor is a tool that can be used to obtain an initial set of database configuration parameters. This tool can be run in either a graphical mode via the Control Center, or via command line.

Let's begin by covering how to use the Configuration Advisor via command line, and follow with the graphical version.

16.3.1. Invoking the Configuration Advisor from the Command Line

You can invoke the Configuration Advisor from the command line using the command

autoconfigure . Figure 16.1 shows the syntax of the

autoconfigure command.

Figure 16.1. The autoconfigure command
>>-AUTOCONFIGURE--+---------------------------------------+----->
|        .----------------------------. |
|        V                            | |
'-USING----input-keyword--param-value-+-'
>--+-----------------------+-----------------------------------><
|        .-DB ONLY----. |
'-APPLY--+-DB AND DBM-+-'
'-NONE-------'

Table 16.1 lists and describes the input keywords.

Table 16.1. Input Keywords for the autoconfigure Command

Keyword

Valid Values [default]

Explanation

mem_percent

1100 [80]

Percentage of memory to dedicate. If other applications (other than the operating system) are running on this server, set this to less than 100.

workload_type

simple, mixed, complex [mixed]

Simple workloads tend to be I/O intensive and mostly transactions, whereas complex workloads tend to be CPU-intensive and mostly queries.

num_stmts

11000000 [10]

Number of statements per unit of work.

tpm

150000 [60]

Transactions per minute.

admin_priority

performance, recovery, both [both]

Optimize for better performance (more transactions per minute) or better recovery time.

is_populated

yes, no [yes]

Whether the database is populated with data.

num_local_apps

05000 [0]

Number of connected local applications.

num_remote_apps

05000 [10]

Number of connected remote applications.

isolation

RR, RS, CS, UR [RR]

Isolation level of applications connecting to this database (Repeatable Read, Read Stability, Cursor Stability, Uncommitted Read).

bp_resizeable

yes, no [yes]

Whether the buffer pools are resizable.

The

autoconfigure command lets you apply the suggested changes to the database only (

DB ONLY ), the database and the database manager (

DB AND DBM ), or not apply the suggested changes at all (

NONE ).

You can also invoke the

autoconfigure command as an option of the

CREATE DATABASE command:

CREATE DATABASE mydb 

AUTOCONFIGURE using mem_percent 75 APPLY DB AND DBM

16.3.2. Invoking the Configuration Advisor from the Control Center

The Configuration Advisor asks you a series of questions about the database server, the nature of the workload, transactions, priority, connections, and isolation level to determine a starting set of database configuration parameter values. You can modify these parameters later to suit the production workload and for additional fine-tuning.

The following are the general steps for configuring a database for optimal performance using the Configuration Advisor.

1.

Open the DB2 Control Center.

2.

Select the database to be configured.

3.

Choose the Configuration Advisor.

4.

Complete each of the applicable wizard pages. Discussion of each page follows.

5.

The

Finish button is available once enough information has been supplied for the Configuration Advisor to configure performance parameters for the database.

6.

Click

Finish to get a list of suggested configuration parameters for the database.

As you can see in Figure 16.2, the Configuration Advisor takes you through step-by-step.

Figure 16.2. The Configuration Advisor Introduction page

[View full size image]

The Introduction page lists the database that is currently being examined (see Figure 16.2). Verify that the correct database is shown. If the correct database is not listed, you might have selected a different database by mistake. Close the Configuration Advisor by selecting

Cancel and start again.

Use the Server page to specify what percentage of the server's memory is to be used by the database manager (see Figure 16.3). For a dedicated DB2 server, choose 100 percent; if other applications are also running on the server, set the value to less than 100 percent.

Figure 16.3. The Configuration Advisor Server page

[View full size image]

On the Workload page, indicate the type of workload for which the database will be used (see Figure 16.4). Indicate if the database is used mainly for queries (for a data warehousing environment), for transactions (for an order entry application), or a mixed workload (for a combination of queries and transactions).

Figure 16.4. The Configuration Advisor Workload page

[View full size image]

Use the Transactions page to describe a typical SQL transaction for the database (see Figure 16.5). Indicate whether the average number of SQL statements per transaction is typically fewer than or more than 10. It is also important to give an indication of the transaction rate for the database.

Figure 16.5. The Configuration Advisor Transactions page

section 16.7, The Snapshot Monitor) to get an accurate measurement of the number of transactions per minute if the database is already operational.

Specify the priority for the selected database on the Priority page (see Figure 16.6). If the database is optimized for fast transaction processing, the database may take longer to recover in the event of an error. If the database is optimized for fast recovery time, transaction performance normally will be slower. If it is equally important to optimize both, choose to balance the optimization of the two.

Figure 16.6. The Configuration Advisor Priority page

[View full size image]

Indicate whether the database has been populated with data on the Populated page (see Figure 16.7). This is important because if the database has already been populated, the Configuration Advisor can use database statistics as input to its suggestions.

Figure 16.7. The Configuration Advisor Populated page

[View full size image]

Indicate the average number of local applications and the average number of remote applications that will connect to the database on the Connections page (see Figure 16.8). If these numbers are not available and you don't have a good estimate, use the default values.

Figure 16.8. The Configuration Advisor Connections page

Chapter 11, Understanding Concurrency and Locking, for more information about isolation levels.

Figure 16.9. The Configuration Advisor Isolation page

section 4.4.4, The Task Center).

Figure 16.10. The Configuration Advisor Schedule page

[View full size image]

The Results page displays the Configuration Advisor's recommended configuration parameter settings based on the information provided (see Figure 16.11). You can choose to apply the suggestions immediately, or save them to a script so you can apply the changes later.

Figure 16.11. The Configuration Advisor Results page

[View full size image]