DB2 Configuration Wizard configuration parameters are simple!

The configuration parameters in the DB2 Configuration Wizard are described in this article and examples of using the autoconfigure command for configuration are provided. DB2 version 8 provides a tool for automatic parameter configuration-Configuration Wizard. You can start the graphical interface configuration wizard in the control center or use the autoconfigure command to configure your database.

The Configuration Wizard allows you to configure the Database Manager, database configuration parameters, and buffer pool size. Note: You must connect to the database before using the database DB2 Configuration Wizard. In the case of a partitioned database, the Database Configuration Wizard only configures one partition for your connection. In a partitioned database environment, you can use the db2_all command to configure parameters for All Database partitions.

If you use the graphical interface's DB2 Configuration Wizard, you need to answer a series of questions, which are equivalent to parameters in the AUTOCONFIGURE command (as shown in Table 1 ). First, let's take a look at the AUTOCONFIGURE command format:

 
 

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

Table 1 shows the option keywords and value ranges.

Table 1 keywords and value range

Keywords

Value range default value explanation

Mem_percent 1-100 80 sets the memory usage of DB2. If other applications are used, select a value smaller than 100.

Workload_type simple, mixed,

Complex mixed specifies the workload type, simple) type mainly refers to IO access is concentrated, for most OLTP systems, select simple type. The Complex type mainly refers to Complex query systems that occupy a large amount of CPU, such as OLAP systems. If your system is between the two, select the mixed type.

Num_cmdts

1-000 Number of SQL statements contained in each transaction in 10 Systems

Tpm 1-200 000 60 transactions per minute.

Admin_priority

Performance, recovery, both select your management policy, which can be better performance or faster database recovery time.

Is_populated

Yes, no yes, data already exists in the Database

Num_local_apps

0-5 000 0 local connections

Num_remote_apps 0-5 000 10

Remote connection count

Isolation RR, RS, CS, UR RR

Application Isolation level

Bp_resizeable

Yes, no yes, whether the buffer pool size can be adjusted

APPLY DB ONLY

Under the configuration parameters of the current database manager, the recommended database and buffer pool parameters are displayed and applied to the database and buffer pool.

APPLY DB AND DBM

Display and apply the recommended Database Manager parameters, database parameters, and buffer pool parameters.

APPLY NONE

Only the recommended values are displayed, but the configuration parameters are not changed.

Use this command to configure the database. The key is to select the appropriate parameters (see Table 1 to select your Parameter options ). The following example shows how to use the AUTOCONFIGURE command to configure the database. Assume that in a partitioned database environment, there is only one database instance db2inst1 and the Data Warehouse TESTDW, and data has been loaded. Use the following command to adjust the parameters of the database:

 
 

  
  
db2_all “db2 connect to testdw; db2 autoconfigure using mem_percent 80 workload_type complex num_stmts 10   
  
  
tpm 20 admin_priority both num_local_apps 5 num_remote_apps 50 isolation cs apply db and dbm; db2 connect reset;”  
 
 

You can also create a script autoconfigure. db2, which is stored in the shared directory of the DB2 instance. Assume It is/home/db2inst1. The content is as follows:

 
 

  
  
connect to testdw;  
  
  
autoconfigure using mem_percent 80 workload_type complex num_stmts 10 tpm 20 admin_priority 
both num_local_apps 5 num_remote_apps 50 isolation cs apply db and dbm;  
  
  
connect reset;  
 
 

Then run the db2_all command to execute this command on all database partitions. Assume that the directory of the script is/home/db2inst1/autoconfigure. db2:

 
 

  
  
db2_all “\”db2 -tvf /home/db2inst1/autoconfigure.db2 >>autoconfigure##.log” 
 
 

After the command is executed, you can use autoconfigure *. log to view the execution of the command on each database partition. Then restart the DB2 instance to make these parameters take effect. Run some benchmark test query statements to test the configuration results of the DB2 Configuration Wizard.

You can also use the DB2 Configuration Wizard graphical interface to configure your database. You can select your database in the control center, right-click it, and select "Configuration Wizard" to start the Configuration Wizard. If you need to configure a partitioned database using the graphic interface, remember to save the result as a task and export the configuration result to a script file, such as/home/db2inst1/autoconfigure. db2. In this way, you can use the db2_all command to execute this script to configure all database partitions:

 
 

  
  
db2_all “\”db2 -tvf /home/db2inst1/autoconfigure.db2 >>autoconfigure##.log” 
 
 

If you are familiar with DB2, you can modify the configuration file generated by DB2 automatic configuration. In short, the DB2 Configuration Wizard is a very easy-to-use tool that can greatly reduce the workload of DBAs.