16.3. The DB2 Configuration AdvisorThe 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 LineYou 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 CenterThe 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. Figure 16.2. The Configuration Advisor Introduction pageThe 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 pageOn 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 pageUse 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 pageNOTE 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 pageIndicate 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 pageIndicate 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 pageSpecify 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 pageThe 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 |