The STACKSIZE configuration parameter and memory utilization

The STACKSIZE configuration parameter indicates the initial stack size for each thread. The database server assigns the amount of space that this parameter indicates to each active thread. This space comes from the virtual portion of database server shared memory. You can reduce the amount of shared memory that the database server adds dynamically.

To reduce the amount of shared memory that the database server adds dynamically, estimate the amount of the stack space required for the average number of threads that your system runs and include that amount in the value that you set for the SHMVIRTSIZE configuration parameter.

To estimate the amount of stack space that you require, use the following formula:

stacktotal = STACKSIZE * avg_no_of_threads

avg_no_of_threads: is the average number of threads. You can monitor the number of active threads at regular intervals to determine this amount. Use onstat -g sts to check the stack use of threads. A general estimate is between 60 and 70 percent of the total number of connections (specified in the NETTYPE parameters in your ONCONFIG file), depending on your workload.

The database server also executes user-defined routines (UDRs) with user threads that use this stack. Programmers who write user-defined routines should take the following measures to avoid stack overflow:

Do not use large automatic arrays.
Avoid excessively deep calling sequences.
For DB-Access only: Use mi_call to manage recursive calls.

If you cannot avoid stack overflow with these measures, use the STACK modifier of the CREATE FUNCTION statement to increase the stack for a particular routine.