Importance of the CreateDefaultPackage, ReplacePackage and DynamicSections Option of the DB2 ODBC Driver

A DB2 package is a control structure on the DB2 server produced during program preparation that is used to execute SQL statements. The DB2 driver automatically creates all DB2 packages required at connection time. If a package already exists, the driver uses the existing package to establish a connection.

We can create a DB2 package automatically by specifying specific connection properties in the initial connection URL. Following connection properties should be used in our initial connection URL when you create a DB2 package:

CreateDefaultPackage: Its purpose is to determine whether the driver automatically creates required DB2 packages. For DB2(Linux/UNIX/Windows), this property must be used in conjunction with the ReplacePackage property. CreateDefaultPackage value can be set to either True or False. If set to true, the driver automatically creates required DB2 packages, even if they already exist. Existing DB2 packages are replaced by the new packages. If set to false, the driver determines if the required DB2 packages exist. If they do not, the driver automatically creates them.

ReplacePackage: Its purpose is to determine whether the current bind process will replace the existing DB2 packages used by the driver. For DB2( Linux/UNIX/Windows), this property must be used in conjunction with the CreateDefaultPackage property. ReplacePackage value can be set to either True or False. If set to true, the current bind process will replace the existing DB2 packages that are used by the driver. If set to false, the current bind process will not replace the existing DB2 packages.

DynamicSections: Its purpose is to set maximum number of prepared statements that the driver can have open at any one time. And its default value is 200 or the value can be set according to our requirements.

When executing the DCC(database configurator) we normally provide the following syntax for jdbc url

Syntax:  jdbc:wm:db2://<server>:<50000|port>;databaseName=<value>

Example: jdbc:wm:db2://dbhostname:50001;databaseName=dbname

When the above url gets executed by default DynamicSections value is set to 200 which means for a single user, the DSN can prepare a maximum of 200 statements in DB2 package without closing any of them.

The below error might occurs due to default DynamicSections value which is set to 200. This means that the application can issue 200 statements without closing any. During the 201st statement, the DB2 will throw this error.

Failed to export all data due to: Error while exporting assets: Cannot get all TPAs (0) java.sql.SQLException: [SoftwareAG][DB2 JDBC Driver]No more available statements. Please recreate your package with a larger dynamicSections value. 

 Stack Trace java.lang.Exception: Failed to export all data due to: Error while exporting assets: Cannot get all TPAs (0) java.sql.SQLException: [SoftwareAG][DB2 JDBC Driver]No more available statements. Please recreate your package with a larger dynamicSections value."

The Resolution to this problem is to Increase the DynamicSections value and rebind the package. Following syntax can be used

Syntax:jdbc:wm:db2://<server>:<50000|port>;databaseName=<value>;CreateDefaultPackage=true;ReplacePackage=true;DynamicSections=2000

Example: jdbc:wm:db2://dbhostname:50001;databaseName=dbname;CreateDefaultPackage=true;ReplacePackage=true;DynamicSections=2000

The above URL creates DB2 packages with 2000 dynamic sections by connecting to DB2 database. If any DB2 packages already exist, they will be replaced by the new ones being created. The first connect can take a long time. These parameters however can be removed after the first time.

Some points needs to be considered while increasing Dynamic Sections in the DB2 Package:

  • Creating DB2 packages with a large number of dynamic sections may exhaust certain server resources.
  • The creation of more dynamic sections will slow down the initial creation of the DB2 package.
  • Using DB2 packages with a large number of dynamic sections may impact application performance.
  • As the number of open sections increases, so does the likelihood that a deadlock situation may occur.