DB2 Connect tuning

 

Various parameters in the database manager configuration file can be used to tune DB2 Connect™.

RQRIOBLK

The RQRIOBLK parameter sets the maximum size of network I/O blocks. A larger block size might improve the performance of large requests. The block size does not usually affect the response time for small requests, such as a request for a single row of data.

A larger block size usually requires more memory on the DB2 Connect server. This increases the size of the working set and might cause large amounts of paging on small workstations.

Use the default DRDA® block size (32767) if it does not cause too much paging on executing your application. Otherwise, reduce the I/O block size until there is no paging. Once paging begins, a noticeable degradation of performance will occur. Use performance monitoring tools (such as the vmstat tool for Linux® and UNIX® operating systems) to determine whether paging is occurring on your system.

DIR_CACHE

The DIR_CACHE parameter determines whether directory information is cached. With caching (DIR_CACHE=YES), directory files are read and cached in memory to minimize the overhead of creating the internal directory structure and reading the directory files every time a connection is established.

Without caching (DIR_CACHE=NO), whenever you connect to a database the appropriate directory is read from a disk and then the search is performed. After the requested entries are found, all memory related to directory searches is freed.

With caching, a shared directory cache is built during db2start processing and freed when DB2® stops. This cache is used by all DB2 server processes (db2agent). Also, a private application directory cache is built when an application issues its first connect to a database and freed when the application ends.

Each cache provides an image of the system database directory, the database connection services directory and the node directory. The cache reduces connect costs by eliminating directory file I/O and minimizing directory searches.

If a cached directory is updated, the changes are not immediately propagated to the caches. If a directory entry is not found in a cache, the original directory is searched.

Caching increases the private memory that is needed for the life of an application. Without caching, this memory is needed only when a directory lookup is processed. Overall use of shared memory by DB2 increases slightly because directory information that is shared among database agents is moved to shared memory. The size of the memory required for a cache depends on the number of entries defined in each directory.

NUMDB

The behavior of DB2 Connect was unaffected by the NUMDB configuration parameter in previous versions, however, this changed starting with Version 8. This parameter indicates the maximum number of databases the clients can connect to through the DB2 Connect server. More specifically, the maximum number of different database aliases that can be catalogued on DB2 Connect server.

Other DB2 Connect parameters

The AGENTPRI and MAXAGENTS are deprecated in Version 9.5

Commands to update the value for MAXAGENTS will continue to work so that existing applications are not broken, but the values will be ignored. The parameter name will not appear in any configuration lists. In the past, the total number of agents allowed to be created on a given DB2 partition was controlled through the MAXAGENTS configuration parameter. You now have the ability to automate the configuration of agents.

By default, NUM_POOLAGENTS will be set to AUTOMATIC with a value of 100 as the default. Also by default, MAX_COORDAGENTS will be set to AUTOMATIC with a value of 200 as the default.

To send accounting strings from your client applications to the DB2 Connect server, use the API-specific means for setting accounting information. The API-specific means perform faster than setting the DB2ACCOUNT environment variable.

IBM® Data Server Driver for JDBC and SQLJ

com.ibm.db2.jcc.DB2BaseDataSource.clientAccountingInformation property

DB2 .NET Data Provider

DB2Connection.ClientAccountingInformation property

CLI/ODBC

ClientAcctStr CLI/ODBC configuration keyword

Embedded SQL (C, C++, and COBOL)

sqlesact function

If you do not need a tailored SQLCODE mapping file, you can improve performance by using the default SQLCODE mapping or turning off SQLCODE mapping. The default mapping file is imbedded in the DB2 Connect library; a tailored mapping file must be read from disk, which affects performance.