Basic performance adjustment 2
Basic Performance TUNING PART 2
Roger Sanders
Laughing paste
Paradise bird free space original work
Paradise Bird Free Space © 2002-2005 Copyright
Reprint, please keep the integrity of the document
Access more you can view http://hbird.myrice.com/myself.html
Mailto: jackey.wu@163.com
Basic performance adjustment 2
Roger Sanders
Laughing paste
Original source: "DB2 Magazine" Quarter 4, 2003 · Vol. 8, Issue 4
English original (due to unauthorized article translation, please retain the original link when reprint)
How to modify the availability of a database parameters that change your instance and the best performance effect.
If you have read the first part of the DB2 Magazine Quarter 3, 2003 Distribued DBA column, you should know that the database performance issue will appear in the following factors:
System (environment) configuration
Instance configuration
Database configuration
Database Design
Application design
In the previous period, I talked about some general adjustment suggestions and describes how to use registered variables to adjust DB2 UDB8.1 for Linux, UNIX, and Windows versions of the system level. (Please pay attention to correcting the end of this article)
In this period, I will explain the instance and the configuration parameter variables available for the DB2 UDB database, and the configuration of the new version of the new version of the V8.1.
DB2 UDB configuration parameters
DB2 UDB uses an extended configuration parameter array to control how system resources and database resources are allocated and used.
Keep in mind that these default values for most of these configuration parameters generated together with very simple systems. (The goal of DB2 UDB is to run, should not be subject to this constraint, in fact it can be improved on any platform, not only a specific platform is improved). Therefore, you usually improve the entire system and database performance - with a lot of effort-varying one or more configuration parameters. In fact, you should regularly modify the DB2 UDB configuration parameter value if your database environment contains one or more:
Big database
Usually working on a large number of concurrently connected databases
One or more special applications with high performance requirements
Special hardware structure
Unique query or transaction loading
Unique query or trading type.
Configuration instance
Whenever an instance is created, a corresponding DB2 database management profile will also create and initialize. Each DB2 database management profile includes 68 different parameters. Some of these parameters will have a significant impact on performance; others may have little effect. Table 1 is some database management instance configuration parameters that may lead to important performance improvement
Table 1: Key DB2 Database Management Configuration Parameters.
View or modify parameters
So how do you determine the current value of the DB2 database management configuration parameter? In DB2 UDB V8.1, there are two ways to view the contents of the DB2 database management profile:
Using the DBM Configuration dialog, it is easy to get from the control center or configuration assistant; or execute the DB2 command line processor (CLP) command GET DATABASE Manager Configuration. The syntax of this command:
Get Database Manager | DB Manager | DBM Configuration | Config | CFG
Similarly, one or more DB2 database manager configuration parameters can also use the DBM Configuration dialog, or execute the CLP command Update Database Manager Configuration to change; the syntax is as follows:
Update Database Manager | DB Manager | DBM Configuration | Config | CFG Using Parameter Value, ...
Parameter clarifies that one or more DB2 database manager configuration parameters (according to keyword) will be modified.
Value clarifies the new value that will be assigned to the specified DB2 database manager configuration parameter is specified.
Note: The parameters (<>) in the corner bracket are optional; variables or options in parentheses or options must be provided; commas and omitted numbers followed behind him (...) indicate the front The parameters can be repeated multiple times.
If the immediate option is specified, if possible, all changes will take effect immediately. (Not all changes can be performed immediately - see DB2 UDB Administration section 13 - Performance Documentation for more information.) If the deferred option is selected, the change does not begin effective until the corresponding instance is stopped and restarted. In the case where not specified, immediate is the default.
Therefore, if you want to turn the value of the DB2 Database Manager Configure the Treasure Intra_PARALLEL, you only need to use this statement to update the database manager configuration: Update DBM CFG Using Intra_PARALLEL YES.
DB2 database configuration
Like a DB2 Database Manager profile is created and initialized while the instance is created; a database configuration file is also created and initialized while each new database is created. Each database configuration file includes 82 different parameters. Some of them have a major role in performance, while others have little effect. Table 2 shows a database configuration parameter that is likely to cause significant performance improvement.
Table 2: Key database configuration parameters.
See or change these parameters
There are two ways to view the contents of the database profile: Use the Database Configuration dialog (more easily accessible from the control center), or by executing the CLP command GET DATABASE CONFIGURATION. The syntax is as follows:
Get Database | DB Configuration | Config | CFG for DatabaseAlias
DatabaseAlias refers to a DB2 UDB database (depending on the alias) to display the configuration parameter value.
The specific value of one or more DB2 database configuration parameters can also be used to use the Database Configuration dialog, or execute the CLP command Update Database Configuration; the syntax is as follows:
Update Database | DB Configuration | Config | CFG for DatabaseAlias Using Parameter Value, ...
Parameter clarifies that one or more DB2 database configuration parameters (according to keyword) will be modified.
Value clarifies the new value that will be assigned to the specified DB2 database configuration parameter.
In this case, if the immediate option is specified, if possible, all changes will take effect immediately. If the deferred option is selected, the change does not begin to effectively until all existing connections to the database are terminated and the new database connection is established. The Immediate option is the default.
For example, if you want to change the value of the Samplect database configuration parameter of the Sample database, you can perform the Update Database Configuration statement as follows:
Update DB CFG for Sample Using SEQDetect NO Configuration Wizard
Given the selection range of the configuration parameters is relatively wide, it is difficult to start and what to do. Fortunately, DB2 UDBV8.1 encapsulates a tool to help you start your configuration - Configuration Advisor. You can access configuration consultants from the Control Center, which is designed to get specific information about your database environment and recommend some configuration parameters based on the obtained information. Because the configuration of the configuration consultant almost always leads to performance improvement, I recommend using this tool before trying other configuration adjustments.
As so much, what should I do if you don't have the installation control center? You can use the DB2AUTOCONFIG () API function to develop a special program to complete the same function, or use the autoconfigure clp command, this command has introduced in DB2 UDB V.8.1, which is as follows:
AutoConfigure
Keyword refers to one or more keywords shown in Table 3.
Value indicates the value associated with the keyword.
Table 3 describes some keywords that may be used by this command.
Table 3: AutoConfigure command keywords and values.
If the value of any keyword displayed in Table 3 is not specified, the default value of these keywords will be used when the autoconfigure command is executed. Also, if the Apply option is not specified, all recommended configuration changes cause the AutoConfig command to be applied to the database configuration, not the DB2 database manager configuration.
Note: DB2 UDBV8.1 version of the Create Database command provides an AutoConfigure option that he uses the same keywords and syntaxes as the AutoConfigure command. Therefore, when a new database is created, the configuration changes may be automatically executed.
Next
Now you know some ways to improve performance by modifying instances and database configuration parameter values. At the same time, you also learned how to make some attempts to adjust the instance and database configuration with the help of the configuration consultant or the AutoConfig command. So, this is what you need to know all in order to get the best DB2 performance? Don't, in the later part, I will tell you about the performance of the database design in performance.
correct
The first part of this article is also an error message about the DB2 UDB V.8.1 registered variable:
In V.8.1, the default value of db2_haash_join is YES (NO is the default value of the 7.2 version).
DB2_striped_containers are no longer used in 8.1. The DB2 documentation: "In earlier versions of DB2, the DB2_STRIPED_CONTAINERS registry variable was used to create table spaces with an extent-sized tag However, because this is now the default behavior, this registry variable no longer has any affect.."
About author
