DB2 tuning parameters

 

+

Search Tips   |   Advanced Search

 

To optimize database performance refer to the DB2 UDB Administration Guide: Performance document.

 

DB2 logging

DB2 has corresponding log files for each database that provides services to administrators, including viewing database access and the number of connections. For systems with multiple hard disk drives, you can gain large performance improvements by setting the log files for each database on a different hard drive from the database files.

To set, at a DB2 command prompt, issue the command:

db2 update db cfg for [database_name] using newlogpath [fully_qualified_path].

Default value: Logs reside on the same disk as the database.

Recommended value: Use a separate high-speed drive, preferably performance enhanced through a redundant array of independent disk (RAID) configuration.

[AIX] See also: Tuning AIX systems.

 

DB2 configuration advisor

Located in the DB2 Control Center, this advisor calculates and displays recommended values for the DB2 buffer pool size, the database, and the database manager configuration parameters, with the option of applying these values.

 

Number of connections to DB2 - MaxAppls and MaxAgents

When configuring the data source settings for the databases, confirm the DB2 MaxAppls setting is greater than the maximum number of connections for the data source.

If you are planning to establish clones, set the MaxAppls value as the maximum number of connections multiplied by the number of clones. The same relationship applies to the session manager number of connections. The MaxAppls setting must be equal to or greater than the number of connections. If you are using the same database for session and data sources, set the MaxAppls value as the sum of the number of connection settings for the session manager and the data sources.

For example...

MaxAppls = (#_connections_data_source + #_connections_session_manager) * #_clones

After calculating the MaxAppls settings for the WAS database and each of the application databases, verify that the MaxAgents setting for DB2 is equal to or greater than the sum of all of the MaxAppls values. For example, MaxAgents = sum of MaxAppls for all databases.

 

DB2 buffpage

Improves database system performance. Buffpage is a database configuration parameter. A buffer pool is a memory storage area where database pages containing table rows or index entries are temporarily read and changed. Data is accessed much faster from memory than from disk.

To set: To view the current value of buffpage for database x, issue the DB2 command get db cfg for x and look for the value BUFFPAGE. To set BUFFPAGE to a value of n, issue the DB2 command update db cfg for x using BUFFPAGE n and set NPAGES to -1 as follows:

$ db2              <-- Use DB2 command mode. 
> connect to dbname    
> select * from syscat.bufferpools 

Note the name of the default, perhaps: IBMDEFAULTBP. If NPAGES is already -1, there is no need to issue following command.

 alter bufferpool IBMDEFAULTBP size -1

Re-issue the above "select" and NPAGES now equals -1. You can collect a snapshot of the database while the application is running and calculate the buffer pool hit ratio as follows:

  1. Collect the snapshot:

    1. Issue the update monitor switches using bufferpool on command.

    2. Verify bufferpool monitoring is on by issuing the get monitor switches command.

    3. Clear the monitor counters with the reset monitor all command.

  2. Run the application.

  3. Issue the get snapshot for all databases command before all applications disconnect from the database, otherwise statistics are lost.

  4. Issue the update monitor switches using bufferpool off command.

  5. Calculate the hit ratio by looking at the following database snapshot statistics:

    • Buffer pool data logical reads

    • Buffer pool data physical reads

    • Buffer pool index logical reads

    • Buffer pool index physical reads

  • Default value: 250

  • Recommended value: Continue increasing the value until the snapshot shows a satisfactory hit rate.

    The buffer pool hit ratio indicates the percentage of time that the database manager did not need to load a page from disk to service a page request. That is, the page is already in the buffer pool. The greater the buffer pool hit ratio, the lower the frequency of disk input and output. Calculate the buffer pool hit ratio as follows:

    • P = buffer pool data physical reads + buffer pool index physical reads

    • L = buffer pool data logical reads + buffer pool index logical reads

    • Hit ratio = (1-(P/L)) * 100%

     

    DB2 query optimization level

    • Sets the amount of work and resources that DB2 puts into optimizing the access plan. When a database query runs in DB2, various methods are used to calculate the most efficient access plan. The range is from 0 to 9. An optimization level of 9 causes DB2 to devote a lot of time and all of its available statistics to optimizing the access plan.

    • To set: The optimization level is set on individual databases and can be set with either the command line or with the DB2 Control Center. Static SQL statements use the optimization level that is specified on the prep and bind commands. If the optimization level is not specified, DB2 uses the default optimization as specified by the dft_queryopt setting. Dynamic SQL statements use the optimization class that is specified by the current query optimization special register, which is set using the SQL Set statement. For example, the following statement sets the optimization class to 1:

      Set current query optimization = 1
      
      If the current query optimization register is not set, dynamic statements are bound using the default query optimization class.

    • Default value: 5

    • Recommended value: Set the optimization level for the needs of the application. Use high levels only when there are very complicated queries.

     

    DB2 reorgchk

    Obtains the current statistics for data and rebinding. Use this parameter because SQL statement performance can deteriorate after many updates, deletes or inserts.

    To set, run...

    db2> reorgchk update statistics on table all

    Rebind packages using the bind command.

    If statistics are available, run...

    db2 -v "select tbname, nleaf, nlevels, stats_time from sysibm.sysindexes"

    If no statistic updates exist, nleaf and nlevels are -1, and stats_time has an empty entry (for example: "-"). If the runstats command was previously run, the real-time stamp from completion of the runstats operation also displays under stats_time. If you think the time shown for the previous runstats operation is too old, run the runstats command again.

  • Default value: None

  • Recommended value: None

     

    DB2 locktimeout

    Number of seconds that an application waits to obtain a lock. Setting helps avoid global deadlocks for applications.

    To view the current value of the lock timeout property for database mydb...

    db2 get db cfg for mydb

    ...and look for the value, LOCKTIMEOUT.

    To set LOCKTIMEOUT to a value of n, run...

    db2 update db cfg for mydb using LOCKTIMEOUT n

    ...where n is a value between 0 and 30 000 inclusive.

    Default value is -1, meaning lock timeout detection is turned off. In this situation, an application waits for a lock if one is not available at the time of the request, until either of the following events occurs:

    If your database access pattern tends toward a majority of writes, set this value so that it gives you early warning when a timeout occurs. A setting of 30 seconds suits this purpose. If your pattern tends toward a majority of reads, either accept the default lock timeout value, or set the property to a value greater than 30 seconds.

     

    DB2 maxlocks

    Percentage of the lock list that is reached when the database manager performs escalation, from row to table, for the locks held by the application. Although the escalation process does not take much time, locking entire tables versus individual rows decreases concurrency, and potentially decreases overall database performance for subsequent attempts to access the affected tables.

    To view the current value...

    db2 get db cfg for mydb
    Look for the MAXLOCKS value.

    To set MAXLOCKS to a value of n, issue the DB2 update db cfg for mydb command using MAXLOCKS n, where mydb is the name of the application database and n is a value between 1 and 100 inclusive.

    If lock escalations are causing performance concerns, you might need to increase the value of this parameter or the locklist parameter, which is described in the following paragraph. You can use the database system monitor to determine if lock escalations are occurring.

     

    DB2 locklist

    • Specifies the amount of storage that is allocated to the lock list.

    • To set: To view the current value of the locklist property for database mydb, issue the DB2 get db cfg for mydb command and look for the LOCKLIST value . To set LOCKLIST to a value of n, issue the DB2 update db cfg for mydb command using LOCKLIST n, where mydb is the name of the application database and n is a value between 4 and 60 000 inclusive.

    • Default value: Refer to the current database information for property default values per operating system.

    • Recommended value: If lock escalations are causing performance concerns, you might need to increase the value of this parameter or the maxlocks parameter, which is described in the previous paragraph. You can use the database system monitor to determine if lock escalations are occurring. Refer to the DB2 Administration Guide: Performance document for more details.



     

    Related tasks


    Tuning the application serving environment

     

    Related information

    DB2 v8.2 Information Center