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
- Run the ODBC Data Source Administrator
- Open a DB2 DSN
- Choose the Bind tab
- Set the Dynamic Sections value
- If required, set the Package and Package Owner values
- Choose Create Package
To set the Dynamic Sections value on Unix
- Edit the .odbc.ini file.
- Locate a DB2 DSN.
- Set the DynamicSections value.
- If required, set the Package and PackageOwner values.
- Run the bind19 utility, as noted in the documentation, in ODBC Drivers for MobiLink and Remote Data Access > iAnywhere Solutions ODBC Driver for DB2 > Configuring data sources.
Go to the Recommended ODBC Drivers for MobiLink page.