The Build Packages text box in the CDD import utility instructs the CONNX Db2 ODBC driver to build a user-specified number N of package sets for all isolation levels for two types of cursor behavior: Delete/Close (non-sticky) and Open ("hold" or "sticky"). This translates into the following:

N * 5 * 2 = 10 dynamic SQL packages (default for N is 1; maximum is 32)

where

The maximum number of dynamic SQL packages CONNX Db2 can build is 320.

Each package consists of 32 sections. The first package for isolation level No Commit with cursor behavior = CLOSE (CONXNC00) defines the following skeleton cursors:

Each skeleton cursor is used by the CONNX Db2 ODBC driver to maintain a separate dynamic SQL context, for example:

For each isolation level and cursor behavior, 1-32 identical SQL packages can be defined for use by the CONNX Db2 ODBC driver. This provides a theoretical maximum number of unique dynamic SQL contexts (32 * 32 = 1024) for each isolation level within a unit of work for cursor behavior = CLOSE, or for the life of each ODBC connection for cursor behavior = OPEN.

For example, an ODBC application could connect to the CONNX Db2 ODBC driver, set AutoCommit mode off, and process any mix of 1024 unique DML (SELECT, INSERT, UPDATE, DELETE) or DDL (CREATE, DROP) SQL statements within a unit of work or during the life of a connection.

For data source (CDD) cursor behavior = CLOSE, when the ODBC application terminates the unit of work via an ODBC 2.x or 3.x SQLTransact/SQLEndTran API function call, the CONNX Db2 ODBC driver issues a COMMIT or ROLLBACK and returns the package section contexts (section numbers and cursor names) to a pool for reuse.

For data source (CDD) cursor behavior = OPEN or PRESERVE ("hold" or "sticky" cursors), when the ODBC application calls SQLTransact/SQLEndTran, the CONNX Db2 ODBC driver issues a COMMIT/ROLLBACK and returns all non-cursor based contexts (INSERT/DELETE/UPDATE, etc.) to the free pool.

For ODBC AutoCommit mode (the default), the CONNX Db2 ODBC driver returns the skeleton cursors to the free pool using a least-frequently used (LFU) algorithm.

In practice, the CONNX Db2 ODBC driver uses only the first package from a package set to process dynamic SQL requests issued by an ODBC application. The choice of current dynamic SQL package set is determined by isolation level and commit mode (AutoCommit or otherwise), as set by the ODBC application (for example, Microsoft Access or Visual Basic) via the ODBC SQLSetConnectOption or SQLSetConnectAttr API functions, and by the data source cursor behavior (defined in the CONNX Db2 CDD). The extra packages for each package set are defined to support long-running ODBC applications which reuse the same connection, for example, a web server application using an anonymous login such as IUSR_CONNX.

The dynamic SQL packages and skeleton cursors are managed at the host on a per-connection basis. For instance, any number of CONNX Db2 ODBC client applications can prepare different SQL statements into section 1 of package CONXNC00. The separate SQL contexts for section 1 are managed by the IBM DRDA host program (the Application Server or AS).

The user specified number of packages per package set is set to 1 by default. You can adjust the number of packages per package set by adding or changing the following entry in the CONNX.INI file located in your Windows directory:

N must be a number between 1 and 32 inclusive.

After changing the Db2MaxPkgSets entry, from the same client machine, you can use the CONNX CDD Import application to build n CONNX Db2 Dynamic SQL Packages per package set by checking the Build Packages check box.

It is not necessary to redistribute the CONNX CDD file that is used during the import/build process after building additional CONNX Db2 dynamic SQL packages. The CONNX Db2 driver has auto-detect functionality which determines the current number of packages per package set at connect time.

The dynamic SQL packages created by clicking the Build Packages check box are named as follows (values are 0 <= z <= 9 or A <=x<=V):