Multiple contexts, multiple Language Environment threads

When you combine the initialization file setting MULTICONTEXT=1 with the default setting THREADSAFE=1, your application can create multiple independent connections under multiple Language Environment® threads. With this capability, you can use a fixed number of Language Environment threads to implement complex Db2 ODBC server applications that handle multiple incoming work requests.

Applications that use both multiple contexts and multiple Language Environment threads require you to manage application resources. Use the Pthread functions or another internal mechanism to prevent different threads from using the same connection handles or statement handles. The following figure shows how an application can fail without a mechanism to serialize use of handles.

Figure 1. Example of improper serialization
Begin figure summary. This figure depicts two threads that call functions in a way that causes an error. Detailed description available.

The following figure shows a design that establishes a pool of connections. From this connection pool, you can map a Language Environment thread to each connection. This design prevents two Language Environment threads from using the same connection (or an associated statement handle) at the same time, but it allows these threads to share resources.

Figure 2. Model for multithreading with connection pooling (MULTICONTEXT=1)
Begin figure summary. This figure depicts the relationship between the parent thread, the connection pool, and child threads in a multithreading application. Detailed description available.
To establish a pool of connections (as Figure 2 depicts), include the following steps in your application:
  1. Designate a parent Language Environment thread. In Db2 ODBC, you designate a parent thread when you establish the environment with SQLAllocHandle(). This Language Environment thread that establishes the environment must persist for the duration of the application, so that Db2 language interface routines can remain resident in the Language Environment enclave.
  2. From the parent Language Environment thread, allocate:
    • m child threads, one for each application task
    • n connection handles. This is the connection pool.
  3. Execute each task on a separate child thread. Use the parent thread to dispatch these tasks to each child thread.
  4. When a child thread requires access to a database, use the parent thread to allocate one of the n connections from the connection pool to the child thread. Remove this connection handle from the pool by marking it as used.
  5. When you finish operating on a connection under a child thread, signal the parent thread to return this connection to the pool by marking it as free.
  6. To terminate your application, free all connection handles with SQLFreeHandle() and terminate all child threads with pthread_join() from the parent thread.
Connections move from one application thread to another as the connections in the pool are assigned to child threads, returned to the pool, and assigned again.

With this design, you can create more Language Environment threads than connections, if threads are also used to perform non-SQL related tasks. You can also create more connections than threads, if you want to maintain a pool of active connections but limit the number of active tasks that your application performs.

Db2 ODBC does not control access to other application resources such as bound columns, parameter buffers, and files. If Language Environment threads need to share resources in your application, you must implement a mechanism to synchronize this access.