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.
You can invoke the Configuration Advisor from the command line using the command autoconfigure. Figure 16.1 shows the syntax of 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
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. |
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.
NOTEUse 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.
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.
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.
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.
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).
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.