External Stored procedures and UDFs (collectively referred to as routines) that utilize the database are susceptible to the same performance issues as any other application. Unlike other database applications, external routines are run in DB2 processes that are managed by an infrastructure. This infrastructure can be configured through the Database Manager Configuration or the dbm cfg.
In this blog post I will explain some of the dbm cfg parameters that are related to external routine performance, including KEEPFENCED, FENCED_POOL and NUM_IINTFENCED.
The primary setting that effects external routine performance is the KEEPFENCED parameter. When this parameter is set to YES. DB2 will keep the process used to execute a routine once the routine completes. DB2 places this process in a pool, which eliminates the overhead of starting a process the next time a routine is invoked. The dbm cfg also includes parameters that can be used to tune this pool.
The FENCED_POOL parameter controls the number of routine processes and threads that are kept in the pool. By default this is the same as the dbm cfg parameter MAX_COORDAGENTS. DB2 distinguishes between single process and threaded routines. If this parameter were set to 50 then a maximum of 50 process and 50 threads including their processes would be pooled for a total of 100 routine executions. The 51st threaded or process based routine would run successfully but the process or thread that ran it would be destroyed after it executed.
The NUM_INITFENCED parameter controls whether or not a non-thread process to run routines is created when DB2 starts.
How these parameters effect performance
It is generally recommended to run production systems with KEEPFENCED set to YES. This will reduce the time taken to invoke routines by eliminating the overhead of creating a new process. Also the create process operation is serial, so it is possible that if there is a sudden request for multiple processes the invocation of some routines may be delayed waiting for the opportunity to create a process.
By default if the KEEPFENCED parameter is set to YES, all routine processes and threads are pooled. Although most of the routine resources are freed when the process is pooled, it is using some resources. By pooling all the process and threads the size of the pool will be the high-water mark. This means that there will be enough processes and threads pooled to run the highest number of consecutively invoked routines since DB2 was restarted. Routine execution depends on workload, so if there was sudden and unusual need for routine processes and threads, there will be processes and threads in the pool that may be unnecessarily consuming system resources. In these situations you may wish to set the FENCED_POOL parameter to a number that reflects the common workload . The common workload can be determined by using the db2pd -fmp command. This command provides a snapshot of the currently running routine processes and threads including information on whether they are active or pooled. If the FENCED_POOL is set to too low a number then additional overhead will be to create the routine process which may result in a routine waiting to execute.
Your system may have a number of process based routines that always run, in these cases you may wish to set the dbm cfg parameter NUM_INITFENCED to the number of processes needed to consecutively run the expected routines. Although the processes will be pooled once started, there will be some additional overhead in starting them the first time a routine is called. By setting this parameter the processes will be started with the database and be ready to service the routines.
For additional information see the following.