Considerations for mixing embedded SQL and CLI

You can use CLI in conjunction with embedded static SQL in an application.
Consider the scenario where the application developer wants to take advantage of the ease of use provided by the CLI catalog functions and maximize the portion of the application's processing where performance is critical. In order to mix the use of CLI and embedded SQL, the application must comply with the listed rules:
  • All connection management and transaction management must be performed completely using either CLI or embedded SQL - never a mixture of the two. Two options are available to the application:
    • it performs all connects and commits/rollbacks using CLI calls, and then calls functions written using embedded SQL;
    • or it performs all connects and commits/rollbacks using embedded SQL, and then calls functions that use CLI APIs, notably, a null connection.
  • Query statement processing cannot straddle CLI and embedded SQL interfaces for the same statement. For example, the application cannot open a cursor using embedded SQL, and then call the CLI SQLFetch() function to retrieve row data.

Since CLI permits multiple connections, the SQLSetConnection() function must be called before executing any embedded SQL. This allows the application to explicitly specify the connection under which the embedded SQL processing is performed.

If the CLI application is multithreaded and also makes embedded SQL calls or Db2® API calls, then each thread must have a Db2 context.