There are a number of configuration parameters at both the database and database manager levels that can impact the throughput on your system. These parameters are categorized in the following groups:
For an introduction to DB2's memory management, see "How DB2 Uses Memory".
The following parameters affect the database global memory allocated on your system:
See "How DB2 Uses Memory" for information about how database global memory relates to the rest of the memory allocated by the database manager.
Each database has at least one buffer pool (IBMDEFAULTBP, which is created when the database is created), and can have more. All buffer pools reside in global memory, which is available to all applications using the database. The memory is allocated on the machine where the database is located. If the buffer pools are large enough to keep the required data in memory, less disk activity will occur. Conversely, if the buffer pools are not large enough, the overall performance of the database can be severely curtailed and the database manager can become I/O-bound as a result of a high amount of disk activity (I/O) required to process the data your application requires.
The buffpage parameter controls the size of a buffer pool when the CREATE BUFFERPOOL or ALTER BUFFERPOOL statement was run with NPAGES -1; otherwise, the buffpage parameter is ignored and the buffer pool will be created with the number of pages specified by the NPAGES parameter.
To determine whether the buffpage parameter is active for a buffer pool, do a:
SELECT * from SYSCAT.BUFFERPOOLS.
Each buffer pool that has an NPAGES value of -1 uses buffpage.
Notes:
There is a trade-off between the buffer pool size and the memory allocations of other system users. Memory requirements of database servers are so important on multi-user high transaction rate servers, that database servers and file or communication servers are often separated and reside on different machines.
All buffer pools are allocated when the first application connects to the database, or when the database is explicitly activated. As an application requests data out of the database, pages containing that data are transferred to one of the buffer pools from disk. (Note that database data is stored in pages within the tables on the disk.) Pages are not written back to disk until the page is changed and one of the following occurs:
Recommendations:
Page swapping results when there is not enough memory to hold the page that is being accessed. The result is that the page is written ("swapped") to temporary disk storage to make room for the other page. When the page on the temporary disk storage is needed, it is "swapped back" into memory.
If the total size of the buffer pool (or buffer pools) is increased, you may also need to increase dbheap.
You may use the database system monitor to calculate the buffer pool hit ratio, which can help you tune your buffer pools. See the System Monitor Guide and Reference.
There is one database heap per database, and the database manager uses it on behalf of all applications connected to the database. It contains control block information for tables, indexes, table spaces, and buffer pools. It also contains space for the event monitor buffers, the log buffer, (logbufsz) and the catalog cache (catalogcache_sz). Therefore, the size of the heap will be dependent on the number of control blocks stored in the heap at a given time. The control block information is kept in the heap until all applications disconnect from the database.
The minimum amount the database manager needs to get started is allocated at the first connection. The data area is expanded as needed up to the maximum specified by dbheap.
Recommendation: This value will need to be increased when an application receives an error indicating that there is not enough storage available in the database heap to process the statement.
You may use the database system monitor to track the highest amount of memory that was used for the database heap. See the db_heap_top (maximum database heap allocated) monitor element description in the System Monitor Guide and Reference for more information.
When setting this parameter, you should consider:
This parameter indicates the maximum amount of space that the catalog cache can use from the database heap (dbheap). The catalog cache is used to store table descriptor information that is used when a table, view or alias is referenced during the compilation of an SQL statement.
Use of this cache can help improve performance of binding SQL statements (including dynamic SQL), if the same tables, views, or aliases have been referenced in previous statements.
Running any DDL statements against a table will purge that table's entry in the catalog cache. Otherwise a table entry is kept in the cache until space is needed for a different table, but it will not be removed from the cache until any units of work referencing that table have completed.
Recommendation: Start with the default value and tune it by using the database system monitor.
See the System Monitor Guide and Reference for information about the following monitor elements:
These database system monitor elements can help you determine whether you should adjust this configuration parameter. When tuning this parameter, you should increase it in small increments, for example, two pages at a time.
| Note: | The catalog cache only exists at the catalog node in a multinode environment. |
In general, more cache space is required if a unit of work contains several dynamic SQL statements or if you are binding packages that contain a lot of static SQL statements.
When you set the size of the catalog cache, also consider the size of the log files (logbufsz), because both catalogcache_sz and logbufsz are allocated from the database heap (dbheap).
This parameter allows you to specify the amount of the database heap (defined by the dbheap parameter) to use as a buffer for log records before writing these records to disk. The log records are written to disk when one of the following occurs:
This parameter must also be less than or equal to the dbheap parameter. Buffering the log records will result in more efficient logging file I/O because the log records will be written to disk less frequently and more log records will be written at each time.
Recommendation: Increase the size of this buffer area if there is considerable read activity on a dedicated log disk, or there is high disk utilization. When increasing the value of this parameter, you should also consider the dbheap parameter since the log buffer area uses space controlled by the dbheap parameter.
You may use the database system monitor to determine how much of the log buffer space is used for a particular transaction (or unit of work).
For more information see the log_space_used (unit of work log space used) monitor element description in the System Monitor Guide and Reference.
When you set the log buffer size, also consider the size of the catalog cache (catalogcache_sz), because both logbufsz_sz and catalogcache_sz are allocated from the database heap (dbheap).
This parameter indicates the maximum amount of memory that can be used simultaneously by the BACKUP, RESTORE and LOAD and load recovery utilities.
Recommendation: Use the default value unless your utilities run out of space, in which case you should increase this value. If memory on your system is constrained, you may wish to lower the value of this parameter to limit the memory used by the database utilities. If the parameter is set too low, you may not be able to concurrently run utilities. You need to set this parameter large enough to accommodate all of the buffers that you want to allocate for the concurrent utilities.
This parameter specifies the size of the buffer used when backing up the database if the buffer size is not explicitly specified when calling the backup utility. For more information about the backup utility, see the Command Reference .
When backing up a database, the data is first copied to an internal buffer. Data is then written from this buffer to the backup media when the buffer is full.
Tuning this buffer size can help improve the performance of the backup utility as well as minimize the impact on the performance of other concurrent database operations.
This parameter specifies the size of the buffer used when restoring the database if a buffer size is not explicitly specified when calling the restore utility. For more information about the restore utility, see the Command Reference .
When restoring a database, the data is first copied from the backup media to an internal buffer. Data is then written from this buffer to the target database media when the buffer is full.
Tuning this buffer size can help improve the performance of the restore database utility as well as minimize the impact on the performance of other concurrent database operations.
This parameter indicates the amount of storage that is allocated to the lock list. There is one lock list per database and it contains the locks held by all applications concurrently connected to the database. Locking is the mechanism that the database manager uses to control concurrent access to data in the database by multiple applications. Both rows and tables can be locked.
Each lock requires 32 or 64 bytes of the lock list, depending on whether other locks are held on the object:
When the percentage of the lock list used by one application reaches maxlocks, the database manager will perform lock escalation, from row to table, for the locks held by the application (described below). Although the escalation process itself does not take much time, locking entire tables (versus individual rows) decreases concurrency, and overall database performance may decrease for subsequent accesses against the affected tables. Suggestions of how to control the size of the lock list are:
You can also use the LOCKSIZE parameter of the ALTER TABLE statement to control how locking is done for a specific table. For details, refer to the SQL Reference.
Use of the Repeatable Read isolation level may result in an automatic table lock. For more information on isolation levels, see Chapter 10. "Application Considerations".
Once the lock list is full, performance can degrade since lock escalation will generate more table locks and fewer row locks, thus reducing concurrency on shared objects in the database. Additionally there may be more deadlocks between applications (since they are all waiting on a limited number of table locks), which will result in transactions being rolled back. Your application will receive an SQLCODE of -912 when the maximum number of lock requests has been reached for the database.
Recommendation: If lock escalations are causing performance concerns you may need to increase the value of this parameter or the maxlocks parameter. You may use the database system monitor to determine if lock escalations are occurring.
For more information see the lock_escals (lock escalations) monitor element description in the System Monitor Guide and Reference.
The following steps may help in determining the number of pages required for your lock list:
(512 * 32 * maxappls) / 4096
where 512 is an estimate of the average number of locks per application and 32 is the number of bytes required for each lock against an object that has an existing lock.
(512 * 64 * maxappls) / 4096
where 64 is the number of bytes required for the first lock against an object.
You may use the database system monitor to determine the maximum number of locks held by a given transaction.
For more information see the locks_held_top (maximum number of locks held) monitor element description in the System Monitor Guide and Reference. .
This information can help you validate or adjust the estimated number of locks per application. In order to perform this validation, you will have to sample several applications, noting that the monitor information is provided at a transaction level, not an application level.
You may also want to increase locklist if maxappls is increased, or if the applications being run perform infrequent commits.
You should consider rebinding applications (using the REBIND PACKAGE command) after changing this parameter.
For more information on application performance and influencing query optimization, see Part 3. "Tuning Application Performance".
This parameter is allocated out of the database global memory, and is used for caching static and dynamic SQL statements on a database. In a partitioned database system, there is one package cache for each database partition.
Caching packages allows the database manager to reduce its internal overhead by eliminating the need to access the system catalogs when reloading a package; or, in the case of dynamic SQL, eliminating the need for compilation. Sections are kept in the package cache until one of the following occurs:
This caching of the section for a static or dynamic SQL statement can improve performance especially when the same statement is used multiple times by applications connected to a database. This is particularly important in a transaction processing application.
By taking the default (-1) in a server or partitioned database environment, the value used to calculate the page allocation is eight times the value specified for the maxappls configuration parameter. The exception to this occurs if eight times maxappls is less than 32. In this situation, the default value of -1 will set pckcachesz to 32.
Recommendation: When tuning this parameter, you should consider whether the extra memory being reserved for the package cache might be more effective if it was allocated for another purpose, such as the bufferpool. For this reason, you should use benchmarking techniques when tuning this parameter.
Tuning this parameter is particularly important when several sections are used initially and then only a few are run repeatedly. If the cache is too large, memory is wasted holding copies of the initial sections.
See the System Monitor Guide and Reference for information about the following monitor elements:
These database system monitor elements can help you determine whether you should adjust this configuration parameter.
| Note: | The package cache is a working cache, so you cannot set this parameter to zero. There must be sufficient memory allocated in this cache to hold all sections of the SQL statements currently being executed. If there is more space allocated than currently needed, then sections are cached. These sections can simply be executed the next time they are needed without having to load or compile them. |
This parameter specifies the size of the buffer used when auditing the database. For more information about the audit facility, refer to Chapter 5. "Auditing DB2 Activities".
The default value for this parameter is zero (0). If the value is zero (0), the audit buffer is not used. If the value is greater than zero (0), space is allocated for the audit buffer where the audit records will be placed when they are generated by the audit facility. The value times 4 KB pages is the amount of space allocated for the audit buffer. The audit buffer cannot be allocated dynamically; DB2 must be stopped and then restarted before the new value for this parameter takes effect.
By changing this parameter from the default to some value larger than zero (0), the audit facility writes records to disk asynchronously compared to the execution of the statements generating the audit records. This improves DB2 performance over leaving the parameter value at zero (0). The value of zero (0) means the audit facility writes records to disk synchronously with (at the same time as) the execution of the statements generating the audit records. The synchronous operation during auditing decreases the performance of applications running in DB2.
The following parameter specifies the work area that is used by all agents (both coordinating and subagents) that work for an application:
This parameter determines the maximum size, in 4 KB pages, for the application control shared memory. Application control heaps are allocated from this shared memory.
One application control heap is allocated for each application at the database where the application is active (or, in the case of a partitioned database system, at each database partition where the application is active). The heap is allocated during connect processing by the first agent to receive a request for the application at the database (or database partition). The heap is required to share information between agents working on behalf of the same application (in a partitioned database environment, the sharing occurs at the database partition level: sharing does not occur across database partitions).
Notes:
Recommendation: Initially, start with the default value. You may have to set the value higher if you are running complex applications, or if you have a system that contains a large number of database partitions.
The following parameters affect the amount of memory used for each database agent:
See "How DB2 Uses Memory" for information about how the private agent memory relates to the rest of the memory allocated by the database manager.
This parameter defines the maximum number of private memory pages to be used for private sorts, or the maximum number of shared memory pages to be used for shared sorts. If the sort is a private sort, then this parameter affects agent private memory. If the sort is a shared sort, then this parameter affects the database shared memory. Each sort has a separate sort heap that is allocated as needed, by the database manager. This sort heap is the area where data is sorted. If directed by the optimizer, a smaller sort heap than the one specified by this parameter is allocated using information provided by the optimizer.
Recommendation:
Private and shared sorts use memory from two different memory sources. The size of the shared sort memory area is statically predetermined (and not preallocated) at the time of the first connection to a database based on the value of sheapthres. The size of the private sort memory area is unrestricted.
The sheapthres parameter is used differently for private and shared sorts:
Examples of those operations that use the sort heap include: hash joins and operations where the table is in memory.
Explicit definition of the threshold prevents the database manager from using excessive amounts of memory for large numbers of sorts.
Recommendation: Ideally, you should set this parameter to a reasonable multiple of the largest sortheap parameter you have in your database manager instance. This parameter should be at least two times the largest sortheap defined for any database within the instance.
If you are doing private sorts and your system is not memory constrained, an ideal value for this parameter can be calculated using the following steps:
(typical number of concurrent agents running against the database) * (sortheap, as defined for that database)
For information about performing sorts in an SMP environment, see "Parallel Sort Strategies".
You should use benchmarking techniques to tune this parameter to find the proper balance between sort performance and memory usage. See Chapter 19. "Benchmark Testing" for more information. Also see "Sorting" for more information on sorting.
You can use the database system monitor to track the sort activity.
For more information see the following monitor element description in the System Monitor Guide and Reference:
The statement heap is used as a work space for the SQL compiler during compilation of an SQL statement. This parameter specifies the size of this work space.
This area does not stay permanently allocated, but is allocated and released for every SQL statement handled. Note that for dynamic SQL statements, this work area will be used during execution of your program; whereas, for static SQL statements, it is used during the bind process but not during program execution.
Recommendation: In most cases the default value of this parameter will be acceptable. If you have very large SQL statements and the database manager issues an error (that the statement is too complex) when it attempts to optimize a statement, you should increase the value of this parameter in regular increments (such as 256 or 1024) until the error situation is resolved.
64 [ 16 - 60 000 ] (multinode)
This parameter defines the number of private memory pages available to be used by the database manager on behalf of a specific agent or subagent.
The heap is allocated when an agent or subagent is initialized for an application. The amount allocated will be the minimum amount needed to process the request given to the agent or subagent. As the agent or subagent requires more heap space to process larger SQL statements, the database manager will allocate memory as needed, up to the maximum specified by this parameter.
| Note: | In a partitioned database environment, the application control heap (app_ctl_heap_sz) is used to store copies of the executing sections of SQL statements for agents and subagents. SMP subagents, however, use applheapsz, as do agents in all other environments. |
Recommendation: Increase the value of this parameter if your applications receive an error indicating that there is not enough storage in the application heap.
The application heap (applheapsz) is allocated out of agent private memory.
This parameter indicates the maximum size of the heap used in collecting statistics using the RUNSTATS command.
Recommendation: The default value is appropriate when no distribution statistics are collected or when distribution statistics are only being collected for relatively narrow tables. The minimum value is not recommended when distribution statistics are being gathered, as only tables containing 1 or 2 columns will fit in the heap.
You should adjust this parameter based on the number of columns for which statistics are being collected. Narrow tables, with relatively few columns, require less memory for distribution statistics to be gathered. Wide tables, with many columns, require significantly more memory. If you are gathering distribution statistics for tables which are very wide and require a large statistics heap, you may wish to collect the statistics during a period of low system activity so you do not interfere with the memory requirements of other users.
This parameter specifies the maximum amount of memory that can be allocated for the query heap. A query heap is used to store each query in the agent's private memory. The information for each query consists of the input and output SQLDA, the statement text, the SQLCA, the package name, creator, section number, and consistency token. This parameter is provided to ensure that an application does not consume unnecessarily large amounts of virtual memory within an agent.
The query heap is also used as the source of memory for the memory allocated for blocking cursors. This memory consists of a cursor control block and a fully resolved output SQLDA.
The initial query heap allocated will be the same size as the application support layer heap, as specified by the aslheapsz parameter. The query heap size must be greater than or equal to two (2), and must be greater than or equal to the aslheapsz parameter. If this query heap is not large enough to handle a given request, it will be reallocated to the size required by the request (not exceeding query_heap_sz). If this new query heap is more than 1.5 times larger than aslheapsz, the query heap will be reallocated to the size of aslheapsz when the query ends.
Recommendation: In most cases the default value will be sufficient. As a minimum, you should set query_heap_sz to a value at least five times larger than aslheapsz. This will allow for queries larger than aslheapsz and provide additional memory for three or four blocking cursors to be open at a given time.
If you have very-large LOBs, you may need to increase the value of this parameter so the query heap will be large enough to accommodate those LOBs.
This parameter indicates the number of pages to allocate for the memory used by DB2 Connect and the DRDA Application Server Support Feature. The following items affect the amount of memory allocated out of this heap:
Recommendation: Use the default value unless you receive an error code indicating that you do not have enough DRDA heap.
This parameter is common to both fenced and unfenced User Defined Functions (UDFs). For a fenced UDF, it specifies the default allocation for memory to be shared between the database process and the UDF. In a single-partition database environment, there is only one shared memory set. In a partitioned database environment, there is a shared memory set for each database partition server, and all application agents and sub-agents running on that server use the same shared memory set.
For an unfenced UDF it specifies the size of the private memory set. In a single-partition database environment, the heap is allocated from private memory. In a partitioned database environment, the heap is allocated from the Application Global memory for each database partition server and all agents and subagents running on behalf of the application on that database partition server use the same shared memory set.
For both fenced and unfenced UDFs, this memory is used to pass data to a UDF and back to a database.
If no UDFs are used in applications, the memory is not allocated. If both fenced and unfenced UDFs are running in the same application, two memory allocations result: one for fenced UDFs, and one for unfenced UDFs.
For more information about user-defined functions, see the Embedded SQL Programming Guide and the SQL Reference .
Recommendation: The default setting should be adequate for all cases not involving the passing of LOB data to a UDF. For cases which pass LOB data to a UDF, you may need to increase the amount of memory allocated. You should set the value of this parameter at least 2 pages larger than the size of the input arguments and the result of the external function.
| Note: | The memory requirement for UDFs tends to be additive, so the number of UDFs referenced in an application will affect the optimal setting for this parameter. |
The agent stack is the virtual memory that is allocated by DB2 for each agent. This memory is committed when it is required to process an SQL statement. You can use this parameter to optimize memory utilization of the server for a given set of applications. More complex queries will use more stack space, compared to the space used for simple queries.
This parameter does not apply to UNIX-based platforms.
Recommendation: In most cases you should be able to use the default stack size. Only if your environment includes many highly complex queries should you need to increase the value of this parameter. If the stack size is not large enough to process your SQL statement, an error entry will be logged to the db2diag.log file, and an SQL code will be issued. You need to increase agent_stack_sz and restart the database instance.
You may be able to reduce the stack size in order to make more address space available to other clients, if your environment matches the following:
The agent stack size and the number of concurrent clients are inversely related: a larger stack size reduces the potential number of concurrent clients that can be running. This occurs because address space is limited on the OS/2 and Windows NT platforms. For example, on OS/2, assume that you have 400MB of address space (though the amount depends on the config.sys file). If you set the value for agent_stack_sz to 1MB, you will not be able to get more than 400 agents. (In fact, because of other requirements for address space, such as buffer pools, you will probably get far fewer agents.) This means that if you have set maxagents to a larger value (for example, 5000), you will never approach this limit.
This parameter specifies the number of pages that the database server process will reserve as private virtual memory, when a database manager instance is started (db2start). If the server requires more private memory, it will try to obtain more from the operating system when required.
This parameter does not apply to UNIX-based systems.
Recommendation: Use the default value.
You should only change the value of this parameter if you want to commit more memory to the database server. This action will save on allocation time. You should be careful, however, that you do not set that value too high, as it can impact the performance of non-DB2 applications.
This parameter is used to determine the amount of unused agent private memory that will be kept allocated, ready to be used by new agents that are started. It does not apply to UNIX-based platforms.
When an agent is terminated, instead of automatically deallocating all of the memory that was used by that agent, the database manager will only deallocate excess memory allocations, which is determined by the following formula:
Private memory allocated - (private memory used + priv_mem_thresh)
If this formula produces a negative result, no action will be taken.
The following table provides an example to illustrate when memory will be
allocated and deallocated. This example uses 100 as an arbitrary
setting for priv_mem_thresh.
| Description of Action | Memory Allocated | Memory Used |
|---|---|---|
| A number of agents are running and have allocated memory. | 1000 | 1000 |
| A new agent is started and uses 100 pages of memory. | 1100 | 1100 |
| A agent using 200 pages of memory terminates. (Notice that 100 pages of memory is freed, while 100 pages is kept allocated for future possible use.) | 1000 | 900 |
| A agent using 50 pages of memory terminates. (Notice that 50 pages of memory is freed and 100 extra pages are still allocated, compared to what is being used by the existing agents.) | 950 | 850 |
| A new agent is started and requires 150 pages of memory. (100 of the 150 pages are already allocated and the database manager only needs to allocate 50 additional pages for this agent.) | 1000 | 1000 |
A value of "-1", will cause this parameter to use the value of the min_priv_mem parameter.
Recommendation: When setting this parameter, you should consider the client connection/disconnection patterns as well as the memory requirements of other processes on the same machine.
If there is only a brief period during which many clients are concurrently connected to the database, a high threshold will prevent unused memory from being decommitted and made available to other processes. This case results in poor memory management which can affect other processes which require memory.
If the number of concurrent clients is more uniform and there are frequent fluctuations in this number, a high threshold will help to ensure memory is available for the client processes and reduce the overhead to allocate and deallocate memory.
The following parameters affect the amount of memory that is allocated to allow data to be passed between your application and agent processes:
See "How DB2 Uses Memory" for information about how this agent/application shared memory relates to the rest of the memory allocated by the database manager.
The application support layer heap represents a communication buffer between the local application and its associated agent. This buffer is allocated as shared memory by each database manager agent that is started.
If the request to the database manager, or its associated reply, do not fit into the buffer they will be split into two or more send-and-receive pairs. The size of this buffer should be set to handle the majority of requests using a single send-and-receive pair. The size of the request is based on the storage required to hold:
In addition to this communication buffer, this parameter is also used to determine the I/O block size when a blocking cursor is opened. This memory for blocked cursors is allocated out of the application's private address space, so you should determine the optimal amount of private memory to allocate for each application program. If the database client cannot allocate space for a blocking cursor out of an application's private memory, a non-blocking cursor will be opened.
The data sent from the local application is received by the database manager into a set of contiguous memory allocated from the query heap. The aslheapsz parameter is used to determine the initial size of the query heap (for both local and remote clients). The maximum size of the query heap is defined by the query_heap_sz parameter.
Recommendation: If your application's requests are generally small and the application is running on a memory constrained system, you may wish to reduce the value of this parameter. If your queries are generally very large, requiring more than one send and receive request, and your system is not constrained by memory, you may wish to increase the value of this parameter.
Use the following formula to calculate the number of pages for aslheapsz:
aslheapsz >= ( sizeof(input SQLDA)
+ sizeof(each input SQLVAR)
+ sizeof(output SQLDA)
+ 250 ) / 4096
You should also consider the effect of this parameter on the number and potential size of blocking cursors. Large row blocks may yield better performance if the number or size of rows being transferred is large (for example, if the amount of data is greater than 4096 bytes). However, there is a trade-off in that larger record blocks increase the size of the working set memory for each connection.
Larger record blocks may also cause more fetch requests than are actually required by the application. You can control the number of fetch requests using the OPTIMIZE FOR clause on the SELECT statement in your application. For more information about the OPTIMIZE FOR clause, see "Quickly Retrieving the First Few Rows Using OPTIMIZE FOR n ROWS".
This parameter specifies the size of the communication buffer between remote applications and their database agents on the database server. When a database client requests a connection to a remote database, this communication buffer is allocated on the client. On the database server, a communication buffer of 32767 bytes is initially allocated, until a connection is established and the server can determine the value of rqrioblk at the client. Once the server knows this value, it will reallocate its communication buffer if the client's buffer is not 32767 bytes.
In addition to this communication buffer, this parameter is also used to determine the I/O block size at the database client when a blocking cursor is opened. This memory for blocked cursors is allocated out of the application's private address space, so you should determine the optimal amount of private memory to allocate for each application program. If the database client cannot allocate space for a blocking cursor out of an application's private memory, a non-blocking cursor will be opened.
Recommendation: For non-blocking cursors, a reason for increasing the value of this parameter would be if the data (for example, large object data) to be transmitted by a single SQL statement is so large that the default value is insufficient.
You should also consider the effect of this parameter on the number and potential size of blocking cursors. Large row blocks may yield better performance if the number or size of rows being transferred is large (for example, if the amount of data is greater than 4096 bytes). However, there is a trade-off in that larger record blocks increase the size of the working set memory for each connection.
Larger record blocks may also cause more fetch requests than are actually required by the application. You can control the number of fetch requests using the OPTIMIZE FOR clause on the SELECT statement in your application. For more information on the OPTIMIZE FOR clause, see "Quickly Retrieving the First Few Rows Using OPTIMIZE FOR n ROWS".
This parameter specifies the size of the communication buffer between DOS/Windows applications and their database agents on the database server. This parameter is similar to the rqrioblk parameter, except it allows you to set a different value for blocks used with DOS/Windows clients. In a DB2 configuration file, you can set both the rqrioblk parameter (used for OS/2 clients) and the dos_rqrioblk parameter (used for DOS clients).
In addition to this communication buffer, this parameter is also used to determine the I/O block size at the database client when a blocking cursor is opened. This memory for blocked cursors is allocated out of the application's private address space, so you should determine the optimal amount of private memory to allocate for each application program. If the database client cannot allocate space for a blocking cursor out of an application's private memory, a non-blocking cursor will be opened.
Recommendation: For non-blocking cursors, a reason for increasing the value of this parameter would be if the data (for example, large object data) to be transmitted by a single SQL statement is so large that the default value is insufficient.
You should also consider the effect of this parameter on the number and potential size of blocking cursors. Large row blocks may yield better performance if the number or size of rows being transferred is large (for example, if the amount of data is greater than 4096 bytes). However, there is a trade-off in that larger record blocks increase the size of the working set memory for each connection.
Larger record blocks may also cause more fetch requests than are actually required by the application. You can control the number of fetch requests using the OPTIMIZE FOR clause on the SELECT statement in your application. For more information on the OPTIMIZE FOR clause, see "Quickly Retrieving the First Few Rows Using OPTIMIZE FOR n ROWS".
The following parameters affect memory that is allocated and used at an instance level:
This parameter determines the amount of the memory, in pages, to allocate for database system monitor data. Memory is allocated from the monitor heap when you perform database monitoring activities such as taking a snapshot, turning on a monitor switch, resetting a monitor, or activating an event monitor.
A value of zero prevents the database manager from collecting database system monitor data.
Recommendation: The amount of memory required for monitoring activity depends on the number of monitoring applications (applications taking snapshots or event monitors), which switches are set, and the level of database activity.
The following formula provides an approximation of the number of pages required for the monitor heap:
( number of monitoring applications + 1 ) *
( number of databases *
(800 + ( number of tables accessed * 20 )
+ ( ( number of applications connected + 1) *
(200 + (number of table spaces * 100) ) ) ) )
/ 4096
If the available memory in this heap runs out, one of the following will occur:
By setting dir_cache to "yes" the database, node and DCS directory files will be cached in memory. The use of the directory cache reduces connect costs by eliminating directory file I/O and minimizing the directory searches required to retrieve directory information. There are two types of directory caches:
| Note: | Only the private cache is applicable to Windows, Windows 95, Windows NT, and Macintosh environments. |
For private caches, when an application issues its first connect, each directory file is read and the information is cached in private memory for this application. The cache is used by the application process on subsequent connect requests and is maintained for the life of the application process. If a database is not found in the private cache, the directory files are searched for the information, but the cache is not updated. If the application modifies a directory entry, the next connect within that application will cause the cache for this application to be refreshed. The private cache for other applications will not be refreshed. When the application process terminates, the cache is freed. (To refresh the directory cache used by a command line processor session, issue a db2 terminate command.)
For shared caches, when a database manager instance is started (db2start), each directory file is read and the information is cached in shared memory. This cache is used by some of the database manager processes and is maintained until the instance is stopped (db2stop). If a directory entry is not found in this cache, the directory files are searched for the information. This shared cache is never refreshed during the time the instance is running.
Recommendation: Use directory caching if your directory files do not change frequently and performance is critical.
In addition, on remote clients, directory caching can be beneficial if your applications issue several different connection requests. In this case, caching reduces the number of times a single application must read the directory files.
Directory caching can also improve the performance of taking database system monitor snapshots. In addition, you should explicitly reference the database name on the snapshot call, instead of using database aliases.
| Note: | Errors may occur when performing snapshot calls if directory caching is turned on and if databases are cataloged, uncataloged, created, or dropped after the database manager is started. |
This parameter determines the maximum size of the heap that is used by the Java interpreter.
There is one heap for each DB2 process (one for each agent or subagent on UNIX-based platforms, and one for each instance in other platforms), and there is also one heap for each fenced UDF and fenced stored procedure process. In all situations, only the agents or processes that run Java UDFs or stored procedures ever allocate this memory. On partitioned database systems, the heap is multiplied by the number of database partition servers.
The following parameters influence how locking is managed in your environment:
See also "Maximum Storage for Lock List (locklist)".
"Locking" provides a general overview of how the database manager uses locking to maintain data integrity.
A deadlock occurs when two or more applications connected to the same database wait indefinitely for a resource. The waiting is never resolved because each application is holding a resource that the other needs to continue.
The deadlock check interval defines the frequency at which the database manager checks for deadlocks among all the applications connected to a database.
Notes:
Recommendation: Increasing this parameter decreases the frequency of checking for deadlocks, thereby increasing the time that application programs must wait for the deadlock to be resolved.
Decreasing this parameter increases the frequency of checking for deadlocks, thereby decreasing the time that application programs must wait for the deadlock to be resolved but increasing the time that the database manager takes to check for deadlocks. If the deadlock interval is too small, it can decrease run-time performance, because the database manager is frequently performing deadlock detection. If this parameter is set lower to improve concurrency, you should ensure that maxlocksand locklist are set appropriately to avoid unnecessary lock escalation, which can result more lock contention and as a result, more deadlock situations.
Lock escalation is the process of replacing row locks with table locks, reducing the number of locks in the list. This parameter defines a percentage of the lock list held by an application that must be filled before the database manager performs escalation. When the number of locks held by any one application reaches this percentage of the total lock list size, lock escalation will occur for the locks held by that application. Lock escalation also occurs if the lock list runs out of space.
The database manager determines which locks to escalate by looking through the lock list for the application and finding the table with the most row locks. If after replacing these with a single table lock, the maxlocks value is no longer exceeded, lock escalation will stop. If not, it will continue until the percentage of the lock list held is below the value of maxlocks. The maxlocks parameter multiplied by the maxappls parameter cannot be less than 100.
Recommendation: When setting maxlocks, you should consider the size of the lock list (locklist):
maxlocks = 100 *
(512 locks per application
* 32 bytes per lock
* 2) / (locklist * 4096 bytes)
This sample formula allows any application to hold twice the average number of locks.
You can increase maxlocks if few applications run concurrently since there will not be a lot of contention for the lock list space in this situation.
You may use the database system monitor to help you track and tune this configuration parameter.
For more information see the locks_held_top (maximum number of locks held) monitor element description in the System Monitor Guide and Reference .
The control of lock escalation through this parameter is important to the optimizer since it uses this parameter to determine access paths. You should consider rebinding applications (using the REBIND PACKAGE command) after changing this parameter.
This parameter specifies the number of seconds that an application will wait to obtain a lock. This helps avoid global deadlocks for applications.
If you set this parameter to 0, locks are not waited for. In this situation, if no lock is available at the time of the request, the application immediately receives a -911.
If you set this parameter to -1, lock timeout detection is turned off. In this situation a lock will be waited for (if one is not available at the time of the request) until either of the following:
Recommendation: In a transaction processing (OLTP) environment, you can use an initial starting value of 30 seconds. In a query-only environment you could start with a higher value. In both cases, you should use benchmarking techniques to tune this parameter.
The value should be set to quickly detect waits that are occurring because of an abnormal situation, such as a transaction that is stalled (possibly as a result of a user leaving their workstation). You should set it high enough so valid lock requests do not time-out because of peak workloads, during which time, there is more waiting for locks.
You may use the database system monitor to help you track the number of times an application (connection) experienced a lock timeout or that a database detected a timeout situation for all applications that were connected. For more information see the locks_timeouts (number of lock timeouts) monitor element description in the System Monitor Guide and Reference.
High values of the lock_timeout monitor element can be caused by:
For more information on the use of this parameter see "Lock Waits and Timeouts".
The following parameters can influence I/O and storage costs related to the operation of your database:
Asynchronous page cleaners will write changed pages from the buffer pool (or the buffer pools) to disk before the space in the buffer pool is required by a database agent. This means that the agents will not wait for a changed page to be written out, before being able to read a page, and your application's transactions should run faster.
You may use this parameter to specify the level (percentage) of changed pages at which the asynchronous page cleaners will be started, if they are not currently active. When the page cleaners are started, they will build a list of the pages to write to disk. Once they have completed writing those pages to disk, they will become inactive again and wait for the next trigger to start.
In a read-only (for example, query) environment, these page cleaners are not used.
Recommendation: For databases with a heavy update transaction workload, you can generally ensure that there are enough clean pages in the buffer pool by setting the parameter value to be equal-to or less-than the default value. A percentage larger than the default can help performance if your database has a small number of very large tables.
This parameter allows you to specify the number of asynchronous page cleaners for a database. These page cleaners write changed pages from the buffer pool to disk before the space in the buffer pool is required by a database agent. This means that the agents will not wait for changed pages to be written out, before being able to read a page. As a result, your application's transactions should run faster.
If you set the parameter to zero (0), no page cleaners are started and as a result, the database agents will perform all of the page writes from the buffer pool to disk. This parameter can have a significant performance impact on a database stored across many physical storage devices, since in this case there is a greater chance that one of the devices will be idle. If no page cleaners are configured, your applications may encounter periodic log full conditions.
If the applications for a database primarily consist of transactions that update data, an increase in the number of cleaners will speed up performance. Increasing the page cleaners will also decrease recovery time from soft failures, such as power outages, because the contents of the database on disk will be more up-to-date at any given time.
Recommendation: Consider the following factors when setting the value for this parameter:
Environments with high update transaction rates may require more page cleaners to be configured.
Environments with large buffer pools may also require more page cleaners to be configured.
You may use the database system monitor to help you tune this configuration parameter using information from the event monitor about write activity from a buffer pool:
For more information see the following monitor elements descriptions in the System Monitor Guide and Reference:
I/O servers are used on behalf of the database agents to perform prefetch I/O and asynchronous I/O by utilities such as backup and restore. This parameter specifies the number of I/O servers for a database. No more than this number of I/Os for prefetching and utilities can be in progress for a database at any time. An I/O server waits while an I/O operation that it initiated is in progress. Non-prefetch I/Os are scheduled directly from the database agents and as a result are not constrained by num_ioservers.
Recommendation: In order to fully exploit all the I/O devices in the system, a good value to use is generally one or two more than the number of physical devices on which the database resides. It is better to configure additional I/O servers, since there is minimal overhead associated with each I/O server and any unused I/O servers will remain remain idle.
For more information, see "Prefetching Data into the Buffer Pool" and "Configuring I/O Servers for Prefetching and Parallel I/O".
This parameter indicates whether sorting of index keys will occur during index creation. Performance of index creation is enhanced by performing a sort first, particularly for indexes with low cluster ratios or cluster factors. Performance of queries can also be better if indexes are created with a sort. The cost of this performance enhancement is the increased disk space required for the sort, which could require twice the amount of space as creating an index without performing an initial sort.
Recommendation: Use the default setting ("Yes"), unless you do not have enough disk space. Note that the disk space required for this sort is approximately equal to the amount of space needed to SELECT the columns of the index from the table with an ORDER BY clause on those columns.
If you have a symmetric multiprocessor (SMP) environment and specify "No" for this parameter, the multiple processing that is possible in an SMP environment is not used during index creation.
The database manager can monitor I/O and if sequential page reading is occurring the database manager can activate I/O prefetching. This type of sequential prefetch is known as sequential detection. You may use the seqdetect configuration parameter to control whether the database manager should perform sequential detection.
If this parameter is set to "no", prefetching takes place only if the database manager knows it will be useful, for example table sorts, table scans, or list prefetch.
Recommendation: In most cases, you should use the default value for this parameter. Try turning sequential detection off, only if other tuning efforts were unable to correct serious query performance problems.
When a table space is created, PREFETCHSIZE n can be optionally specified, where n is the number of pages the database manager will read if prefetching is being performed. If you do not specify the prefetch size on the CREATE TABLESPACE statement, the database manager uses the value given by this parameter.
For more information, see "Prefetching Data into the Buffer Pool".
Recommendation: Using system monitoring tools, you can determine if your CPU is idle while the system is waiting for I/O. Increasing the value of this parameter may help if the table spaces being used do not have a prefetch size defined for them.
This parameter provides the default for the entire database, and it may not be suitable for all table spaces within the database. For example, a value of 32 may be suitable for a table space with an extent size of 32 pages, but not suitable for a table space with an extent size of 25 pages. Ideally, you should explicitly set the prefetch size for each table space.
To help minimize I/O for table spaces defined with the default extent size (dft_extent_sz), you should set this parameter as a factor or whole multiple of the value of the dft_extent_sz parameter. For example, if the dft_extent_sz parameter is 32, you could set dft_prefetch_sz to 16 (a factor of 32) or to 64 (a whole multiple of 32). If the prefetch size is a multiple of the extent size, the database manager may perform I/O in parallel, if the following conditions are true:
This parameter, which only applies to SMS table spaces, indicates the number of containers that will be created within the default table spaces. This parameter will show the information used when you created your database, whether it was specified explicitly or implicitly on the CREATE DATABASE command. The CREATE TABLESPACE statement does not use this parameter in any way.
For more information, see "Database Physical Directories".
When a table space is created, EXTENTSIZE n can be optionally specified, where n is the extent size. If you do not specify the extent size on the CREATE TABLESPACE statement, the database manager uses the value given by this parameter.
For more information see "Designing and Choosing Table Spaces".
Recommendation: In many cases, you will want to explicitly specify the extent size when you create the table space. Before choosing a value for this parameter, you should understand how you would explicitly choose an extent size for the CREATE TABLESPACE statement. For more information see "Table Space Impact on Query Optimization".
This parameter specifies the number of pages in each of the extended memory segments in the database. There are platform-dependent considerations when setting this configuration parameter.
Recommendation: This parameter only has an effect when extended storage is available, and is used as shown by the num_estore_segs parameter. When specifying the number of pages to be used in each extended memory segment, you should also consider the number of extended memory segments by reviewing and modifying the num_estore_segs parameter. For more information about extended storage, see "Extending Memory".
This parameter specifies the number of extended storage memory segments available for use by the database.
The default is no extended storage memory segments.
Recommendation: Only use this parameter to establish the use of extended storage memory segments if your platform environment has more memory than the maximum address space and you wish to use this memory. When specifying the number of segments, you should also consider the size of the each of the segments by reviewing and modifying the estore_seg_sz parameter.
When both the num_estore_segs and estore_seg_sz configuration parameters are set, you should specify which bufferpools will use the extended memory through the CREATE/ALTER BUFFERPOOL statements. For more information about extended storage, see "Extending Memory".
The following parameters can influence the number of applications that can be run concurrently and achieve optimal performance:
This parameter specifies the maximum number of concurrent applications that can be connected (both local and remote) to a database. Since each application that attaches to a database causes some private memory to be allocated, allowing a larger number of concurrent applications will potentially use more memory.
The value of this parameter must be equal to or greater than the sum of the connected applications, plus the number of these same applications that may be concurrently in the process of completing a two-phase commit or rollback. Then add to this sum the anticipated number of indoubt transactions that might exist at any one time. For more information on indoubt transactions, see "Recovering from Problems During Two-Phase Commit".
When an application attempts to connect to a database, but maxappls has already been reached, an error is returned to the application indicating that the maximum number of applications have been connected to the database.
In a partitioned database environment, this is the maximum number of applications that can be concurrently active against a database partition. This parameter limits the number of active applications against the database partition on a database partition server, regardless of whether the server is the coordinator node for the application or not. The catalog node in a partitioned database environment requires a higher value for maxappls than is the case for other types of environments because, in the partitioned database environment, every application requires a connection to the catalog node.
Recommendation: Increasing the value of this parameter without lowering the maxlocks parameter or increasing the locklist parameter could cause you to reach the database limit on locks (locklist) rather than the application limit and as a result cause pervasive lock escalation problems.
To a certain extent, the maximum number of applications is also governed by maxagents. An application can only connect to the database, if there is an available connection (maxappls) as well as an available agent (maxagents). In addition, the maximum number of applications is also controlled by the max_coordagents configuration parameter, because no new applications (that is, coordinator agents) can be started if max_coordagents has been reached.
This parameter is used by the SQL optimizer to help estimate how much buffer pool will be available at run-time for the access plan chosen. Increasing this parameter can influence the optimizer to choose an access plan for queries that will be more conservative in its buffer pool usage.
Recommendation: When running DB2 in a multi-user environment, particularly with complex queries and a large buffer pool, you may want the SQL optimizer to know that multiple query users are using your system so that the optimizer should be more conservative in assumptions of buffer pool availability.
When setting this parameter, you should estimate the number of heavy query applications that typically use the database. This estimate should exclude all light OLTP applications. If you have trouble estimating this number, you can multiply the following:
As with adjusting other configuration parameters that affect the optimizer, you should adjust this parameter in small increments. This allows you to minimize path selection differences.
You should consider rebinding applications (using the REBIND PACKAGE command) after changing this parameter.
This parameter specifies the maximum number of file handles that can be open for each database agent. If opening a file causes this value to be exceeded, some files in use by this agent are closed. If maxfilop is too small, the overhead of opening and closing files so as not to exceed this limit will become excessive and may degrade performance.
Both SMS table spaces and DMS table space file containers are treated as files in the database manager's interaction with the operating system, and file handles are required. More files are generally used by SMS table spaces compared to the number of containers used for a DMS file table space. Therefore, if you are using SMS table spaces, you will need a larger value for this parameter compared to what you would require for DMS file table spaces.
You can also use this parameter to ensure that the overall total of file handles used by the database manager does not exceed the operating system limit by limiting the number of handles per agent to a specific number; the actual number will vary depending on the number of agents running concurrently.
This parameter defines the maximum number of files that can be opened by all agents and other threads executing in a single database manager instance. If opening a file causes this value to be exceeded, an error is returned to your application.
| Note: | This parameter does not apply to UNIX-based platforms. |
Recommendation: When setting this parameter, you should consider the number of file handles that could be used for each database in the database manager instance. To estimate an upper limit for this parameter:
maxappls * maxfilop
If a new database is created, you should re-evaluate the value for this parameter.
You should also validate the total file handles that may be used on your system does not exceed the system maximum using the following formula:
(sum of maxtotfilop for all instances on machine) + (estimate of file handles required by other applications) <= 65535
This parameter controls the priority given both to all agents, and to other database manager instance processes and threads, by the operating system scheduler. In a partitioned database environment, this also includes both coordinating and parallel agents, the parallel system controllers, and the FCM daemons. This priority determines how CPU time is given to the DB2 processes, agents, and threads relative to the other processes and threads running on the machine. When the parameter is set to -1, no special action is taken and the database manager is scheduled in the normal way that the operating system schedules all processes and threads. When the parameter is set to a value other than -1, the database manager will create its processes and threads with a static priority set to the value of the parameter. Therefore, this parameter allows you to control the priority with which the database manager processes and threads will execute on your machine.
You can use this parameter to increase database manager throughput. The values for setting this parameter are dependent on the operating system on which the database manager is running. For example, in a UNIX-based environment, numerically low values yield high priorities. When the parameter is set to a value between 41 and 125, the database manager creates its agents with a UNIX static priority set to the value of the parameter. This is important in UNIX-based environments because numerically low values yield high priorities for the database manager, but other processes (including applications and users) may experience delays because they cannot obtain enough CPU time. You should balance the setting of this parameter with the other activity expected on the machine.
In an OS/2 environment, higher numeric values yield higher priorities.
For more guidance on using priorities in your operating environment, see the Quick Beginnings book for your platform.
Recommendation: The default value should be used initially. This value provides a good compromise between response time to other users/applications and database manager throughput.
If database performance is a concern, you can use benchmarking techniques to determine the optimum setting for this parameter. You should take care when increasing the priority of the database manager because performance of other user processes can be severely degraded especially when the CPU utilization is very high. Increasing the priority of the database manager processes and threads can have significant performance benefits.
| Note: | If you set this parameter to a non-default value on UNIX-based platforms, you cannot use the governor to alter agent priorities. |
The maximum number of database manager coordinator agents that can be concurrently executing a database manager transaction. This parameter is used to control the load on the system during periods of high simultaneous application activity. For example, you may have a system requiring a large number of connections but with a limited amount of memory to serve those connections. Adjusting this parameter can be useful in such an environment, where a period of high simultaneous activity could cause excessive operating system paging.
This parameter does not limit the number of applications that can have connections to a database. It only limits the number of database manager agents that can be processed concurrently by the database manager at any one time, thereby limiting the usage of system resources during times of peak processing.
A value of -1 indicates that the limit is max_coordagents.
Recommendation: In most cases the default value for this parameter will be acceptable. In cases where the high concurrency of applications is causing problems, you can use benchmark testing to tune this parameter to optimize your performance.
400 [ 1 - 64 000 ] on Partitioned Database Server with local and remote clients
This parameter indicates the maximum number of database manager agents, whether coordinating agents or subagents, available at any given time to accept application requests. If you want to limit the number of coordinating agents, use the max_coordagents parameter.
This parameter can be useful in memory constrained environments to limit the total memory usage of the database manager, because each additional agent requires additional memory.
Recommendation: The value of maxagents should be at least the sum of the values for maxappls in each database allowed to be accessed concurrently. If the number of databases is greater than the numdb parameter, then the safest course is to use the product of numdb with the largest value for maxappls.
Each additional agent requires some resource overhead that is allocated at the time the database manager is started.
For partitioned database environments and environments in which intra_parallel is set to "Yes", the default is maxagents - num_initagents; otherwise, the default is maxagents. This ensures that, in non-partitioned database environments, max_coordagents always equals maxagents, unless the system is configured for intra-partition parallelism.
If you do not have a partitioned database environment, and have not enabled the intra_parallel parameter, max_coordagents must equal maxagents.
This parameter determines the maximum number of coordinating agents that can exist at one time on a server in a partitioned or non-partitioned database environment.
One coordinating agent is acquired for each local or remote application that connects to a database or attaches to an instance. Requests that require an instance attachment include CREATE DATABASE, DROP DATABASE, and Database System Monitor commands.
Using the default, the value for a server with a non-partitioned database and local clients is the larger of maxagents/50 or max_querydegree.
Using the default, the value for a server with a non-partitioned database and local and remote clients is the larger of maxagents/50 x max_querydegree or maxagents - max_coordagents.
Using the default, the value for an database partition server is the larger of maxagents/10 x max_querydegree or maxagents - max_coordagents.
This parameter is a guideline for how large you want the agent pool to grow (and replaces the max_idleagents parameter that was used in DB2 Version 2).
The agent pool contains subagents and idle agents. Idle agents can be used as parallel subagents or as coordinating agents. If more agents are created than is indicated by the value of this parameter, they will be terminated when they finish executing their current request, rather than be returned to the pool.
If the value for this parameter is 0, agents will be created as needed, and may be terminated when they finish executing their current request. If the value is maxagents, and the pool is full of associated subagents, the server cannot be used as a coordinator node, because no new coordinating agents can be created.
Recommendation: If you run a decision-support environment in which few applications connect concurrently, set num_poolagents to a small value to avoid having an agent pool that is full of idle agents.
If you run a transaction-processing environment in which many applications are concurrently connected, increase the value of num_poolagents to avoid the costs associated with the frequent creation and termination of agents.
This parameter determines the initial number of idle agents that are created in the agent pool at DB2START time.
The following parameters can affect the Database Application Remote Interface (DARI) applications:
| Note: | The term DARI refers to stored procedures. |
This parameter indicates whether or not a DARI process is kept after a DARI call is complete. DARI processes are created as separate system entities in order to isolate user-written DARI code from the database manager agent process. This parameter is only applicable on database servers.
If keepdari is set to no, a new DARI process is created and destroyed for each DARI invocation. If keepdari is set to yes, a DARI process is reused for subsequent DARI calls. When the database manager is stopped, all outstanding DARI processes will be terminated.
Setting this parameter to yes will result in additional system resources being consumed by the database manager for each DARI process that is activated, up to the value contained in the maxdari parameter. This is only true when no existing DARI process is available to process a subsequent DARI call. This parameter is ignored if maxdari is set to 0.
Recommendation: In an environment in which the number of DARI requests is large relative to the number of non-DARI requests, and system resources are not constrained, then this parameter can be set to yes. This will improve the DARI performance by avoiding the initial DARI process creation overhead since an existing DARI process will be used to process the call.
For example, in an OLTP debit-credit banking transaction application, the code to perform each transaction could be performed in a stored procedure which executes in a DARI process. In this application, the main workload is performed out of DARI processes. If this parameter is set to no, each transaction incurs the overhead of creating a new DARI process, resulting in a significant performance reduction. If, however, this parameter is set to yes, each transaction would try to use an existing DARI process, which would avoid this overhead.
This parameter indicates the maximum number of DARI process that may reside at the database server. Once this limit is reached, no new DARI requests may be invoked. This parameter is only applicable on database servers.
There can be no more than one DARI process active per coordinating agent, so the maximum number of DARI processes is also dictated by the maximum number of coordinating agents (max_coordagents).
Recommendation: If your environment features the use of the DARI facility within the database manager, then this parameter can be used to ensure that an appropriate number of DARI processes are available to handle the DARI calls made at any one time within the database manager.
If the parameter is set to -1, the maximum number of DARI processes will be the same as the value set in the max_coordagents parameter.
If you find that the default value is not appropriate for your environment because an inappropriate amount of system resource is being given to DARI processes which is affecting performance of the database manager, the following may be useful in providing a starting point for tuning this parameter:
maxdari = # of applications allowed to make DARI calls at one time
If keepdari is set to yes, then each DARI process that is created will continue to exist and use system resources even after the DARI call has been processed and returned to the agent.
If your environment is tightly constrained and you cannot afford the process resources associated with DARI, you can disable DARI by setting this parameter to zero (0).