Section 16.3. The DB2 Configuration Advisor


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


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


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


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


NOTE

Use the Snapshot Monitor with the get snapshot command (discussed in 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


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


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


Use the Snapshot Monitor to get an accurate measurement of the number of remote and local applications that connect to the database.


Specify the isolation level that the applications will use to access the database on the Isolation page (see Figure 16.9). If you use multiple isolation levels, specify the one that is used most frequently in the applications, or the one used by the most important application. Refer to Chapter 11, Understanding Concurrency and Locking, for more information about isolation levels.

Figure 16.9. The Configuration Advisor Isolation page


Specify whether a tools catalog database should be created to store information about scheduled tasks on the Schedule page (see Figure 16.10). The Task Center is required for the DB2 scheduling function to be enabled, and it requires the tools catalog (see section 4.4.4, The Task Center).

Figure 16.10. The Configuration Advisor Schedule page


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




Understanding DB2(R. Learning Visually with Examples)
Understanding DB2: Learning Visually with Examples (2nd Edition)
ISBN: 0131580183
EAN: 2147483647
Year: 2004
Pages: 313

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net