Learning Visually with Examples [Electronic resources] نسخه متنی

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.

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.

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

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.

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

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.