DB2 CLI supports concurrent execution of threads on the following platforms:
On any other platform that supports threads, DB2 CLI is guaranteed to be thread safe by serializing all calls to DB2 CLI. In other words, DB2 CLI is always reentrant.
| Note: | If you are writing applications that use DB2 CLI calls and either embedded SQL or DB2 API calls, see Multi-Threaded Mixed Applications. |
Concurrent execution means that two threads can run independently of each other (on a multi-processor machine they may run simultaneously). For example, an application could implement a database-to-database copy in the following way:
In contrast, if DB2 CLI serializes all function calls, only one thread may be executing a DB2 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.
The most common reason to create another thread in a DB2 CLI application is so that a thread other than the one executing can be used to call SQLCancel() (to cancel a long running query for example).
| Note: | This method should be used for any platform which supports threads rather than using the asyncronous SQL model (which was designed for non-threaded operating systems such a Windows 3.1). If your application cannot make use of multi-threading then see Asynchronous Execution of CLI. |
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 DB2 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 DB2 CLI.
Applications that use multiple connections, and are executing statements that may take some time to execute, should consider executing DB2 CLI functions on multiple threads to improve throughput. Such an application should follow standard practices for writing any multi-thread application, most notable those concerning sharing data buffers. The following section discusses in more detail what DB2 CLI guarantees and what the application must guarantee in order to write a more complex multi-threaded application.
Any resource allocated by DB2 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 DB2 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.
As an 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: | This only applies for DB2 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 to follow the model below. |
| Note: | There may be platform or compiler specific link options required for multi-threaded applications. Refer to the Application Building Guide for complete details. |
The following is intended as an example:
This model allows the master thread to have more threads than connections if the threads are also used to perform non-SQL related tasks, or more connections than threads if the application wants to maintain a pool of active connections to various databases, but limit the number of active tasks.
Most importantly, this ensures that two threads are not trying to use the same connection or statement handle at any one time. Although DB2 CLI controls access to its resources, the application resources such as bound columns and parameter buffers are not controlled by DB2 CLI, and the application must guarantee that a pointer to a buffer is not being used by two threads at any one time. Any deferred arguments must remain valid until the column or parameter has been unbound.
If it is necessary for two threads to share a data buffer, the application must implement some form of synchronization mechanism. For example, in the database-to-database copy scenario mentioned above, the use of the shared buffer must be synchronized by the application.
The application must be aware of the possibility of creating deadlock situations with shared resources in the database and the application.
DB2 can detect deadlocks at the server and rollback one or more transactions to resolve them. An application may still deadlock if:
In this case the DB2 Server is only going to see a lock, not a deadlock, and unless the database LOCKTIMEOUT configuration setting is changed, the application will wait forever.
The model suggested above avoids this problem by not sharing application resources between threads once a thread starts executing on a connection.
It is possible that an existing mulit-threaded DB2 CLI application ran successfully using the serialized version of DB2 CLI (prior to version 5), yet suffers synchronization problems when run using DB2 CLI version 5 or later.
In this case the DISABLEMULTITHREAD CLI/ODBC configuration keyword can be set to 1 in order to force DB2 CLI to serialize all function calls. If this is required, the application should be analyzed and corrected.
It is possible for a multi-threaded application to mix CLI calls with DB2 API calls and embedded SQL. Which type of call comes first determines the best way to organize the application:
DB2 CLI Calls First
The DB2 CLI driver automatically calls the DB2 context APIs to allocate and manage contexts for the application. This means that any application that calls SQLAllocEnv() before calling any other DB2 API or embedded SQL will be initialized with the context type set to SQL_CTX_MULTI_MANUAL.
In this case the application should allow DB2 CLI to allocate and manage all contexts. Use DB2 CLI to allocate all connection handles and to perform all connections. Call the SQLSetConnect() function in each thread prior to calling any embedded SQL. DB2 APIs can be called after any DB2 CLI function has been called in the same thread.
DB2 API Calls or Embedded SQL First
The DB2 CLI driver does not automatically call the DB2 context APIs if the application calls the DB2 API or embedded SQL before a CLI function.
This means that any thread that calls a DB2 API or embedded SQL must be attached to a context, otherwise the call will fail with an SQLCODE of SQL1445N. This can be done by calling the DB2 API sqleAttachToCtx() which will explicitly attach the thread to a context, or by calling any DB2 CLI function (SQLSetConnection() for example).
In this case, the application must explicitly manage all contexts.
Use the context APIs to allocate and attach to contexts prior to calling DB2 CLI functions (SQLAllocEnv() will use the existing context as the default context). Use the SQL_ATTR_CONN_CONTEXT connection attribute to explicitly set the context that each DB2 CLI connection should use.
See Appendix B, Migrating Applications for details on running existing mixed applications.