Call Level Interface Guide and Reference

Mixing Embedded SQL and DB2 CLI

It is possible, and sometimes desirable, for an application to use DB2 CLI in conjunction with embedded static SQL. Consider the scenario where the application developer wishes to take advantage of the ease of use provided by the DB2 CLI catalog functions and maximize the portion of the application's processing where performance is critical. In order to mix the use of DB2 CLI and embedded SQL, the application must comply to the following rules:

• All connection management and transaction management must be performed completely using either DB2 CLI or embedded SQL. Either the DB2 CLI application performs all the Connects and Commits/Rollback and calls functions written using embedded SQL; or an embedded SQL application performs all the Connects and Commits/Rollback and calls functions written in DB2 CLI which use a null connection (see "Writing a Stored Procedure in CLI" for details on null connections).

• Query statement processing must not and cannot straddle across DB2 CLI and embedded SQL interfaces for the same statement; for example, the application cannot open a cursor in an embedded SQL routine, and then call the DB2 CLI SQLFetch() function to retrieve row data.

Since DB2 CLI permits multiple connections, the SQLSetConnection() function must be called prior to making a function call to a routine written in embedded SQL. This allows the application to explicitly specify the connection under which the embedded SQL routine should perform its processing.

If the DB2 CLI application is multithreaded and also makes embedded SQL calls or DB2 API calls, then each thread must have a DB2 context. See "Writing Multi-Threaded Applications" for complete details.

[ DB2 List of Books | Search the DB2 Books ]