How does IBM DB2 parameter tuning work?

This article tells you the correct operation tips for optimizing IBM DB2 parameters. Before you start, please shut down the IBM Directory Server. Switch the context to the owner of the DB2 instance on the IBM directory server, which is generally a ldapdb2 user. For example:

1. In a Unix system, enter:

Su-ldapdb2

2. In Windows, enter:

Db2cmd

SetDB2INSTANCE = ldapdb2

To tune IBM DB2 parameters and run the db2-tunings.sh script, you can download the following here:

Http://www3.software.ibm.com/ibmdl/pub/software/tivoli_support/misc/Security/AMeB /_

Am5.1/tuning_guide_scripts.tar

Or use the ftp address:

Ftp://ftp.software.ibm.com/software/tivoli_support/misc/Security/AMeB/am5.1/tuning_guide_scripts.tar

The user name and password must be registered on the Web page.

The file system owner of this script must be the DB2 instance owner, which is generally the ldapdb2 user. The file system group should be the same as the instance owner (generally dbsysadm ). The script must be used in the context of the DB2 instance owner.

The ibmdefaultbp and ldapbp parameters in the script control the buffer pool of DB2. The db2_turnings.sh script sets the recommended size of the DB2 buffer pool. Other optional settings are described in the script annotations. The performance of the IBM Directory Server increases with the growth of the DB2 buffer pool. However, in most cases, with this IBM DB2 parameter adjustment method, you can foresee a performance improvement of no more than 10%.

The DB2 buffer pool is where DB2 caches data tables and indexes. DB2 users use indexes to quickly find the table row item to be retrieved during queries. For more information, see IBMDirectoryServerTuningGuide.

Display and verify current settings

Enter the following command to display the results of the current DB2 parameter adjustment settings:

 
 

  
  
db2getdatabaseconfigurationforldapdb2|\  
  
  
egrep'DBHEAP|SORTHEAP|MAXLOCKS|MINCOMMIT|UTIL_HEAP_SZ|APPLHEAPSZ'  
  
  
db2connecttoldapdb2  
  
  
db2"selectbpname,npages,pagesizefromsyscat.bufferpools"  
  
  
db2terminate  
 
 

If a heap configuration parameter is too low, some functional problems may occur. Enter the following command to display the current heap parameter settings:

 
 

  
  
db2getdbcfgforldapdb2|grepHEAP 
 
 

The following is an output example of Multiple Heap Parameters Using recommended values:

 
 

  
  
Databaseheap(4KB)(DBHEAP)=1200  
  
  
Utilitiesheapsize(4KB)(UTIL_HEAP_SZ)=5000  
  
  
Maxappl.controlheapsize(4KB)(APP_CTL_HEAP_SZ)=128  
  
  
Sortlistheap(4KB)(SORTHEAP)=2500  
  
  
SQLstatementheap(4KB)(STMTHEAP)=2048  
  
  
Defaultapplicationheap(4KB)(APPLHEAPSZ)=2048  
  
  
Statisticsheapsize(4KB)(STAT_HEAP_SZ)=4384  
 
 

If a heap parameter is smaller than the minimum value, enter the following command to increase it to the minimum value:

 
 

  
  
db2updatedbcfgforldapdb2usingparm_nameparm_value 
 
 

Here, parm_name is the text in the third column (without parentheses) in the output, and parm_value is the value in the last column.

If the heap IBM DB2 parameter settings are too high or too low, the IBM Directory Server prompts a problem with failure. In this case, you need to view the cli. error file of IBMDirectoryServerV4.1 (IDS4.1), The db2cli. log File of IBMTivoliDirectoryServerV5.2 (IDSv5.2), or other files. In the IDSv4.1 system, the default directory of this file is in/var/ldap/of Solaris and/tmp of AIX. In IDSv5.1 and later systems, the default directories of Solaris and AIX are/var/ldap.

Note that db2look can provide enough information about the database and its configuration under a command. Example:

 
 

  
  
db2look-dldapdb2-uldapdb2-p-ooutput_file 
 
 

Output_file is the file location where the results are stored.

Warning about running the IBM Directory Server

DB2 parameter adjustment uses db2terminate. If the IBM Directory Server slapd or ibmslapd process is running when the command is issued, it will block some of the server's functions. All cached searches seem to respond normally. Other searches will simply become "no result" or "error message. The recovery will restart the IBM Directory Server. Therefore, it is best to disable the IBM Directory Server when adjusting DB2 parameters.

Warning about buffer pool memory usage

If any buffer pool is set too high, DB2 cannot start because it lacks enough memory. If this problem occurs, there will be a core dump file, but there is usually no error message.

In the AIX system, the system error log reports a memory allocation failure. To view this log, enter:

Errpt-a | more

Using a large buffer pool size to restore a database in the system will cause recovery failure. Check the http://publib.boulder.ibm.com/infocenter/tivihelp/v2r1/topic/com.ibm.itame3.doc _ 5.1/am51_perftune116.htm # idtrouble to solve this problem.

If DB2 cannot be started because the buffer pool is too large, adjust the IBM DB2 parameters again.

Warning about MINCOMMIT

Do not set MINCOMMIT to a value other than 1. The latest db2_tunings.sh script correctly sets this value to 1. Previous versions are all set to 25. If the value is not 1, the update operation times out and the update speed may be affected.