Administration Getting Started

Setting the Database Configuration Parameters

An important way to improve performance is to adjust the configuration parameters. The parameters fall into two general categories:

• Database manager parameters (at the instance level) -- Most of these parameters affect the amount of system resources that will be allocated to a single instance.

• Database parameters -- Most of these parameters specify the amount of resources allocated to a specific database.

You may need to modify the default values of these parameters if your environment has any of the following:

• A large amount of memory (greater than 64 MB)
• Large databases
• Large numbers of connections
• High performance requirements for a specific application
• Different machine configuration/use
• Unique query/transaction loads or types

Using the Performance Configuration SmartGuide

This SmartGuide helps you tune performance and balance memory requirements for a single database per instance by suggesting which configuration parameters to modify and providing suggested values for them.

To use the SmartGuide:

1. From the Control Center, click with mouse button 2 on the database for which you want to configure performance.

2. Select Configure Performance from the pop-up menu. The Performance Configuration SmartGuide opens.

3. Follow the steps in the SmartGuide.

   If you click on Apply these recommendations immediately on the Results page (last page) of the SmartGuide, then when you click on Done after you have finished selecting the appropriate options, the recommended configuration parameters for your system will be applied to the database immediately. However, updates to parameters take effect under the following conditions:

   • For database configuration parameters, all applications must first disconnect from the database. The changes will take effect only at the first new connect to the database. If the activate database command was used to activate the database, the deactivate database command must be issued before the changes will take effect.

   • For database manager configuration parameters, you must stop and then start the instance.

   The next time you open this SmartGuide, you are given the option of backing out of the options you selected the previous time the SmartGuide was open.

Figure 24. Performance Configuration SmartGuide

We recommend that each instance of the database manager have only one production database. It is difficult to tune a system with more than one production database per instance running concurrently.

The recommended values are designed to improve the performance of, though not necessarily optimize, your database system. These values should be thought of as a starting point on which you can make further adjustments to obtain optimized performance. In most cases they will provide better performance than the default values because they are based on information about your workload and your server.

The parameters affected are directed at Data Manipulation Statements (DML). This SmartGuide is not intended to improve the performance of utilities.

In order to use the SmartGuide effectively, you need to know the following about the database and your system:

• The volume of workload on your system.

• The portion of the server's memory (RAM) that can be used by this database (not including the operating system).

• Whether there is anything else running on your system. For example, the system may also be a server supporting CICS transactions.

• Whether it is more important for you to optimize transaction performance (more transactions per minute) or the time required to recover the database after a failure.

• What a typical database transaction is. Choose the number of SQL statements in a single unit of work (between commits) that best reflects the database. Also, estimate the number of transactions per minute running in your database. If you are not sure which value to use, accept the default provided by the SmartGuide.

• The number of users connecting to the database.

• The type of workload on the system. For example:

  • There are many queries of the data (data warehousing)

  • The data is updated (order entry)

  • Isolation level (row locking):

    The isolation level determines the number of locked rows and the locks' duration when a user either reads or changes data. DB2 uses locking to maintain data integrity during concurrent processing. Locking guarantees that a transaction maintains control over a database row until it has finished, and prevents another application from changing a row before the ongoing change is complete. See the Administration Guide for more information.

  • Whether there is any production data in the database. If there is, its volume is included in the recommendations.

We recommend that you rerun this SmartGuide again if the size of the database significantly increases (for example, more than 20%) or if the machine characteristics change (for example, more memory is added).

[ DB2 List of Books | Search the DB2 Books ]