Multithreaded CLI applications

The CLI driver supports concurrent execution of threads.
Concurrent execution means that two threads can run independently of each other (on a multi-processor computer they can run simultaneously). For example, an application can implement a database-to-database copy in the following scenario:
  • One thread connects to database A and uses SQLExecute() and SQLFetch() calls to read data from one connection into a shared application buffer.
  • The other thread connects to database B and concurrently reads from the shared buffer and inserts the data into database B.
If you are writing applications that use CLI calls and either embedded SQL or Db2® API calls, see the documentation for multithreaded mixed applications.

In contrast, if CLI serializes all function calls, only one thread may be executing a CLI function at a time. All other threads would have to wait until the current thread is done before it would get a chance to execute.

When to use multiple threads

The most common reason to create another thread in a CLI application is so a thread other than the one executing can be used to call SQLCancel() (to cancel a long running query for example).

Most GUI-based applications use threads in order to ensure that user interaction can be handled on a higher priority thread than other application tasks. The application can simply delegate one thread to run all CLI functions (with the exception of SQLCancel()). In this case there are no thread-related application design issues since only one thread will be accessing the data buffers that are used to interact with CLI.

Applications that use multiple connections, and are executing statements that may take some time to execute, should consider executing CLI functions on multiple threads to improve throughput. Such an application should follow standard practices for writing any multi-threaded application, most notably, those concerned with sharing data buffers.

Programming tips

Any resource allocated by CLI is guaranteed to be thread-safe. This is accomplished by using either a shared global or connection specific semaphore. At any one time, only one thread can be executing a CLI function that accepts an environment handle as input. All other functions that accept a connection handle (or a statement or descriptor allocated on that connection handle) will be serialized on the connection handle.

This means that once a thread starts executing a function with a connection handle, or child of a connection handle, any other thread will block and wait for the executing thread to return. The one exception to this is SQLCancel(), which must be able to cancel a statement currently executing on another thread. For this reason, the most natural design is to map one thread per connection, plus one thread to handle SQLCancel() requests. Each thread can then execute independently of the others.

If an object is shared across threads, application timing issues may arise. For example, if a thread is using a handle in one thread, and another thread frees that handle between function calls, the next attempt to use that handle would result in a return code of SQL_INVALID_HANDLE.

Note:
  1. Thread safety for handles only applies for CLI applications. ODBC applications may trap since the handle in this case is a pointer and the pointer may no longer be valid if another thread has freed it. For this reason, it is best when writing an ODBC application to follow the application model for multithreaded CLI applications.
  2. There may be platform or compiler specific link options required for multi-threaded applications. Refer to your compiler documentation for further details.