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, that handle cannot be used by any other thread until the function has returned. 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 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. |
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 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.
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. This is not the case, however, 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).
A mixed multi-threaded application should follow one of these approaches:
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.
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.