For each executable SQL statement in a context, the first run-time services call
always tries to obtain a latch. If it is successful, it continues processing. If not
(because an SQL statement in another thread of the same context already has
the latch), the call is blocked on a signaling semaphore until that semaphore is
posted, at which point the call gets the latch and continues processing. The
latch is held until the SQL statement has completed processing, at which time
it is released by the last run-time services call that was generated for that
particular SQL statement.
The net result is that each SQL statement within a context is executed as an
atomic unit, even though other threads may also be trying to execute SQL
statements at the same time. This action ensures that internal data structures
are not altered by different threads at the same time. APIs also use the latch
used by run-time services; therefore, APIs have the same restrictions as
run-time services routines within each context.
By default, all applications have a single context that is used for all database
access. While this is perfect for a single threaded application, the serialization
of SQL statements makes a single context inadequate for a multithreaded
application. By using the following DB2 APIs, your application can attach a
separate context to each thread and allow contexts to be passed between
Recommendations for Using Multiple Threads
Follow these guidelines when accessing a database from multiple thread
v Serialize alteration of data structures.
Applications must ensure that user-defined data structures used by SQL
statements and database manager routines are not altered by one thread
while an SQL statement or database manager routine is being processed in
another thread. For example, do not allow a thread to reallocate an SQLDA
while it was being used by an SQL statement in another thread.
v Consider using separate data structures.
It may be easier to give each thread its own user-defined data structures to
avoid having to serialize their usage. This guideline is especially true for
the SQLCA, which is used not only by every executable SQL statement, but
also by all of the database manager routines. There are three alternatives for
avoiding this problem with the SQLCA:
– Use EXEC SQL INCLUDE SQLCA, but add struct sqlca sqlca at the
beginning of any routine that is used by any thread other than the first
– Place EXEC SQL INCLUDE SQLCA inside each routine that contains
SQL, instead of in the global scope.
– Replace EXEC SQL INCLUDE SQLCA with #include "sqlca.h", then
add "struct sqlca sqlca" at the beginning of any routine that uses
Code Page and Country/Region Code Considerations for Multithreaded UNIX
On AIX, the Solaris Operating Environment, HP-UX, and Silicon Graphics
IRIX, changes have been made to the functions that are used for run-time
querying of the code page and country/region code to be used for a database
connection. These functions are now thread safe, but can create some lock
contention (and resulting performance degradation) in a multithreaded
application that uses a large number of concurrent database connections.
You can use the DB2_FORCE_NLS_CACHE environment variable to eliminate
the chance of lock contention in multithreaded applications. When
DB2_FORCE_NLS_CACHE is set to TRUE, the code page and country/region
code information is saved the first time a thread accesses it. From that point
on, the cached information will be used for any other thread that requests this
information. By saving this information, lock contention is eliminated, and in
certain situations a performance benefit will be realized.
You should not set DB2_FORCE_NLS_CACHE to TRUE if the application
changes locale settings between connections. If this situation occurs, the
original locale information will be returned even after the locale settings have
been changed. In general, multithreaded applications will not change locale
settings, which, ensures that the application remains thread safe.