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

 

N is the number of identical packages within each set

5 is the number of isolation levels (OS/400 only; 4 for DB2 OS/390, MVS, and UDB)

2 is the number of types of cursor behavior

 

320 is the maximum number of dynamic SQL packages built by CONNX DB2

 

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

 

DECLARE NC0001 CURSOR FOR S01

DECLARE NC0002 CURSOR FOR S02

DECLARE NC0003 CURSOR FOR S03

...

DECLARE NC0032 CURSOR FOR S32

 

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

 

Select col1 from table  maps to cursor 1

Select col1, col2 from table  maps to cursor 2

Insert into table (col1,col2) values ('row01', 1)  maps to cursor 3

Delete from table where col1 = 'row01'  maps to cursor 4

 

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 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.

 

Notes:

1. 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).

2. 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.

[DATABASES]

DB2MaxPkgSets=n

Where 1<=n<=32

3. 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.

4. It is not necessary to redistribute the CONNX CDD file 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.

 

Important: All CONNX DB2 dynamic SQL package names begin with 'CONX'; hence, the first package built with isolation level = READ_COMMITTED, Access Mode = READ_WRITE, and cursor behavior = del/close is named CONXCS00. The tenth package in this package set is CONXCS09; the thirty-second package in this set is CONXCS0V.

 

The dynamic SQL packages created by clicking the 'Build Packages' check box are named as follows:

CONNX DB2 Sample Packages

DB2 Platform	Transaction Isolation	Access Mode	Auto Commit	Cursor Behavior	Package Set Suffix
OS/400	READ_UNCOMMITTED	READ_ONLY	ON	DEL/CLOSE	NC0z
				PRESERVE	NC1z
			OFF	DEL/CLOSE	CH0z
				PRESERVE	CH1z
		READ_WRITE	ON	DEL/CLOSE	NC0z
				PRESERVE	NC1z
			OFF	DEL/CLOSE	CH0z
				PRESERVE	CH1z
Non-OS/400	READ_UNCOMMITTED	READ_ONLY	ON	DEL/CLOSE	CH0z
				PRESERVE	CH1z
			OFF	DEL/CLOSE	CH0z
				PRESERVE	CH1z
		READ_WRITE	ON	DEL/CLOSE	CH0z
				PRESERVE	CH1z
			OFF	DEL/CLOSE	CH0z
				PRESERVE	CH1z
All	READ_COMMITTED	READ_ONLY	ON	DEL/CLOSE	CS0z
				PRESERVE	CS1z
			OFF	DEL/CLOSE	CS0z
				PRESERVE	CS1z
		READ_WRITE	ON	DEL/CLOSE	CS0z
				PRESERVE	CS1z
			OFF	DEL/CLOSE	CS0z
				PRESERVE	CS1z
All	REPEATABLE READ	READ_ONLY	ON	DEL/CLOSE	AL0z
				PRESERVE	AL1z
			OFF	DEL/CLOSE	AL0z
				PRESERVE	AL1z
		READ_WRITE	ON	DEL/CLOSE	AL0z
				PRESERVE	AL1z
			OFF	DEL/CLOSE	AL0z
				PRESERVE	AL1z
All	SERIALIZABLE	READ_ONLY	ON	DEL/CLOSE	RR0z
				PRESERVE	RR1z
			OFF	DEL/CLOSE	RR0z
				PRESERVE	RR1z
		READ_WRITE	ON	DEL/CLOSE	RR0z
				PRESERVE	RR1z
			OFF	DEL/CLOSE	RR0z
				PRESERVE	RR1z
Where 0 <= z <= 9 or A <=x<=V