The Importance of the Dynamic Sections Option of the iAnywhere Solutions DB2 ODBC Driver
The Dynamic Sections setting for an iAnywhere Solutions DB2 ODBC Driver DSN is the total number of (simultaneously) prepared statements available to each user connecting via the ODBC driver. If MobiLink server exceeds the Dynamic Sections value by having too many SQL statements prepared at the same time, an error will result. The default value for Dynamic Sections is 100, so it is quite easy for a MobiLink deployment to run across this limit, hence the importance of setting this option. The theoretical limit for Dynamic Sections is 65536, but may be significantly lower depending on the DB2 server resources.
Using cached prepared statements may perform better than using no cache (ie. -ps 0). Only appropriate testing of your deployment will determine if caching prepared statements offers any performance benefits. The simplest way to test if prepared-statement caching benefits your MobiLink deployment's performance is to run a performance test once with -ps 0 and once with -ps N, where N is a large number, say 10000. Use the -ps setting that gives the best performance. If the performance test (using a DB2 consolidated) works with -ps 0, but fails with -ps N, then you likely need to increase your Dynamic Sections setting. Determining the minimum required setting is done as follows.
The total number of prepared statements for a single user is limited by the Dynamic Sections setting in effect when you bind the package used by the iAnywhere Solutions DB2 driver. In the case of MobiLink server, the Dynamic Sections value is spread across all connections, including connections created by Java and .NET scripting logic and/or startup classes using DBConnectionContext or DBServerContext. Generally, you should set the size of the MobiLink server prepared-statement (PS) cache (set via the -ps option) to a value less than the Dynamic Sections value (DS) divided by the number (C) of connections being used (see the -cn option; or, if -cn is not specified, the number of worker threads specified by -w; or, if neither -cn nor -w is used, 5). In other words:
The -ps option should be at least as big as the number of SQL scripts (S) used across all script versions in your deployment, plus the number of SQL statements used by MobiLink server. You may safely assume the latter value is less than 100 (per connection). In other words:
To set the Dynamic Sections value on Windows
To set the Dynamic Sections value on Unix
Go to the Recommended ODBC Drivers for MobiLink page.