Embedded SQL and Multithreading Question on 8.1.5

[Mark Yudkin]

The documentation is unclear (at least to me) on the permissibility of
accessing DB2 (8.1.5) concurrently on and from Windows 2000 / XP / 2003,
with separate transactions scope, from separate threads of a multithreaded
program using embedded SQL. Since the threads do not need to share
transaction scopes, the sqleAttachToCtx family of APIs do not seem to be
necessary.

<quote>
In the default implementation of threaded applications against a DB2
database, serialization of access to the database is enforced by the
database APIs. If one thread performs a database call, calls made by other
threads will be blocked until the first call completes, even if the
subsequent calls access database objects that are unrelated to the first
call. In addition, all threads within a process share a commit scope. True
concurrent access to a database can only be achieved through separate
processes, or by using the APIs that are described in this section.
</quote>
indicates no, I need separate processes.

But 2 paragraphs later:
<quote>
For DB2 Version 8, all Version 8 applications are multithreaded by default,
and are capable of using multiple contexts. (The behavior of pre-Version 8
applications remains unchanged.) If you want, you can use the following DB2
APIs to use multiple contexts. Specifically, your application can create a
context for a thread, attach to or detach from a separate context for each
thread, and pass contexts between threads. If your application does not call
any of these APIs, DB2 will automatically manage the multiple contexts for
your application:
</quote>
exactly the opposite appears to be the case - in fact, it says it will do
exactly what I want, by default.

For CLI, the documentation confirms the multithreaded support:
<quote>
The DB2 CLI automatically uses multiple contexts to achieve thread-safe,
concurrent database access on platforms that support multi-threading.
</quote>
but does not indicate whether this applied to embedded SQL.

By contrast, for COBOL, FORTRAN and REXX under "Embedded SQL", and for Perl,
the the documentation clearly states: "COBOL/FORTRAN/REXX/Perl does not
support multiple-thread database access", but no further explanation is
given. For C/C++ the statement is not made. I know that the embedded SQL
APIs (as documented in the precompiler downloadable documentation) exist in
both C and generic styles - is the choice of API by the language precompiler
perhaps the relevant factor? (We're using IBM PL/I for Windows 2.1.12, which
has native multithreading, I haven't examined which API is being used so
far).

Does anybody know what the reality is?

[Bernard Dhooghe]

"Mark Yudkin" <my***********************@nospam.org> wrote in message news:<ci**********@ngspool-d02.news.aol.com>...

The documentation is unclear (at least to me) on the permissibility of
accessing DB2 (8.1.5) concurrently on and from Windows 2000 / XP / 2003,
with separate transactions scope, from separate threads of a multithreaded
program using embedded SQL. Since the threads do not need to share
transaction scopes, the sqleAttachToCtx family of APIs do not seem to be
necessary.

<quote>
In the default implementation of threaded applications against a DB2
database, serialization of access to the database is enforced by the
database APIs. If one thread performs a database call, calls made by other
threads will be blocked until the first call completes, even if the
subsequent calls access database objects that are unrelated to the first
call. In addition, all threads within a process share a commit scope. True
concurrent access to a database can only be achieved through separate
processes, or by using the APIs that are described in this section.
</quote>
indicates no, I need separate processes.

But 2 paragraphs later:
<quote>
For DB2 Version 8, all Version 8 applications are multithreaded by default,
and are capable of using multiple contexts. (The behavior of pre-Version 8
applications remains unchanged.) If you want, you can use the following DB2
APIs to use multiple contexts. Specifically, your application can create a
context for a thread, attach to or detach from a separate context for each
thread, and pass contexts between threads. If your application does not call
any of these APIs, DB2 will automatically manage the multiple contexts for
your application:
</quote>
exactly the opposite appears to be the case - in fact, it says it will do
exactly what I want, by default.

For CLI, the documentation confirms the multithreaded support:
<quote>
The DB2 CLI automatically uses multiple contexts to achieve thread-safe,
concurrent database access on platforms that support multi-threading.
</quote>
but does not indicate whether this applied to embedded SQL.

By contrast, for COBOL, FORTRAN and REXX under "Embedded SQL", and for Perl,
the the documentation clearly states: "COBOL/FORTRAN/REXX/Perl does not
support multiple-thread database access", but no further explanation is
given. For C/C++ the statement is not made. I know that the embedded SQL
APIs (as documented in the precompiler downloadable documentation) exist in
both C and generic styles - is the choice of API by the language precompiler
perhaps the relevant factor? (We're using IBM PL/I for Windows 2.1.12, which
has native multithreading, I haven't examined which API is being used so
far).

Does anybody know what the reality is?

Hello,

I can not answer the question.

But I can add some comments on a question I have that maybe could help
to shade a light on both problems.

My goal was/is: how can I mix embedded sql and cli and keep separate
transactions for the cli connections and the embedded sql connection,
the process having just one thread but can have one embedded sql
"session/connect" and one or more cli "sessions/connects".

The DB2 LUW manual says:

"Considerations for mixing embedded SQL and DB2 CLI
It is possible, and sometimes desirable, to use DB2 CLI in conjunction
with embedded static SQL in an application. Consider the scenario
where the application developer wishes to take advantage of the ease
of use provided by the DB2 CLI catalog functions and maximize the
portion of the application's processing where performance is critical.
In order to mix the use of DB2 CLI and embedded SQL, the application
must comply with the following rules:

All connection management and transaction management must be performed
completely using either DB2 CLI or embedded SQL - never a mixture of
the two. Two options are available to the application:
it performs all connects and commits/rollbacks using DB2 CLI calls,
and then calls functions written using embedded SQL;
or it performs all connects and commits/rollbacks using embedded SQL,
and then calls functions that use DB2 CLI APIs, notably, a null
connection.
Query statement processing cannot straddle DB2 CLI and embedded SQL
interfaces for the same statement. For example, the application cannot
open a cursor using embedded SQL, and then call the DB2 CLI SQLFetch()
function to retrieve row data.
Since DB2 CLI permits multiple connections, the SQLSetConnection()
function must be called prior to executing any embedded SQL. This
allows the application to explicitly specify the connection under
which the embedded SQL processing is performed.

If the DB2 CLI application is multithreaded and also makes embedded
SQL calls or DB2 API calls, then each thread must have a DB2 context.

"

I do not understand this.

I understand that the embedded sql cursor can not be processed by cli
or the other way around. But why must connection management and
transaction management either be in embedded sql or either in CLI
(exclusively)?

My thinking was that embedded sql en cli have (should have?) their own
context. If cli sql is to be done, the process will first save the
context (whatever it may be), set it's own context, do its work (could
be a commit) and then restore the context to what was found before
working in this particular cli context (the Unix way: when needing a
special state, save the state, do the action, restore the state when
done). That would allow embedded sql and cli to be totally split (what
I needed).

My investigation was in 7.2. What I copied from the on-line manual is
(also) the 8.2 explanation.

So what exactly is a context in db2 (luw) used for, and based on it,
how to explain the constraint concerning connection management and
transaction management when both embedded sql en cli are used in one
program/process? It is based on a non multi-threaded process
environment, but maybe it could help to answer the original posting
also.

Bernard Dhooghe

[Mark Yudkin]

Mixing CLI and embedded SQL is not my problem, we have only embedded SQL.
Also, I know that under V7.2 and before, multithreading did not work "by
default". Rather, explicit context management (sqleAttachToCtx) was
required. apparently, up to 7.2, embedded SQL could not take advantage of
this functionality. That transaction and connection management can not
straddle the APIs also makes sense, as the techniques for managing these
differ - CLI place more of the responsibility on the user, embedded SQL
states its rules (see CONNECT (Type 2) / RELEASE / COMMIT and ROLLBACK). I
would assume that your scenario 'one embedded sql "session/connect" and one
or more cli "sessions/connects".' would not be supported.

---

With V8:

For DB2 Version 8, all Version 8 applications are multithreaded by default, and are capable of using multiple contexts. (The behavior of pre-Version 8
applications remains unchanged.) and this is what leads me to think that what I want may work.

The multithreading aspects of mixing CLI / embedded SQL are probably closely
related to my question, but you're not using that. My question is more on of
thread safety and synchronization within the embedded SQL API calls
generated by the preprocessor, as well as of contextual and transactional
management on a per thread basis.

"Bernard Dhooghe" <no***@attglobal.net> wrote in message
news:25**************************@posting.google.c om... "Mark Yudkin" <my***********************@nospam.org> wrote in message

news:<ci**********@ngspool-d02.news.aol.com>...

The documentation is unclear (at least to me) on the permissibility of
accessing DB2 (8.1.5) concurrently on and from Windows 2000 / XP / 2003,
with separate transactions scope, from separate threads of a multithreaded program using embedded SQL. Since the threads do not need to share
transaction scopes, the sqleAttachToCtx family of APIs do not seem to be
necessary.

<quote>
In the default implementation of threaded applications against a DB2
database, serialization of access to the database is enforced by the
database APIs. If one thread performs a database call, calls made by other threads will be blocked until the first call completes, even if the
subsequent calls access database objects that are unrelated to the first
call. In addition, all threads within a process share a commit scope. True concurrent access to a database can only be achieved through separate
processes, or by using the APIs that are described in this section.
</quote>
indicates no, I need separate processes.

But 2 paragraphs later:
<quote>
For DB2 Version 8, all Version 8 applications are multithreaded by default, and are capable of using multiple contexts. (The behavior of pre-Version 8 applications remains unchanged.) If you want, you can use the following DB2 APIs to use multiple contexts. Specifically, your application can create a context for a thread, attach to or detach from a separate context for each thread, and pass contexts between threads. If your application does not call any of these APIs, DB2 will automatically manage the multiple contexts for your application:
</quote>
exactly the opposite appears to be the case - in fact, it says it will do exactly what I want, by default.

For CLI, the documentation confirms the multithreaded support:
<quote>
The DB2 CLI automatically uses multiple contexts to achieve thread-safe,
concurrent database access on platforms that support multi-threading.
</quote>
but does not indicate whether this applied to embedded SQL.

By contrast, for COBOL, FORTRAN and REXX under "Embedded SQL", and for Perl, the the documentation clearly states: "COBOL/FORTRAN/REXX/Perl does not
support multiple-thread database access", but no further explanation is
given. For C/C++ the statement is not made. I know that the embedded SQL
APIs (as documented in the precompiler downloadable documentation) exist in both C and generic styles - is the choice of API by the language precompiler perhaps the relevant factor? (We're using IBM PL/I for Windows 2.1.12, which has native multithreading, I haven't examined which API is being used so
far).

Does anybody know what the reality is?

Hello,

I can not answer the question.

But I can add some comments on a question I have that maybe could help
to shade a light on both problems.

My goal was/is: how can I mix embedded sql and cli and keep separate
transactions for the cli connections and the embedded sql connection,
the process having just one thread but can have one embedded sql
"session/connect" and one or more cli "sessions/connects".

The DB2 LUW manual says:

"Considerations for mixing embedded SQL and DB2 CLI
It is possible, and sometimes desirable, to use DB2 CLI in conjunction
with embedded static SQL in an application. Consider the scenario
where the application developer wishes to take advantage of the ease
of use provided by the DB2 CLI catalog functions and maximize the
portion of the application's processing where performance is critical.
In order to mix the use of DB2 CLI and embedded SQL, the application
must comply with the following rules:

All connection management and transaction management must be performed
completely using either DB2 CLI or embedded SQL - never a mixture of
the two. Two options are available to the application:
it performs all connects and commits/rollbacks using DB2 CLI calls,
and then calls functions written using embedded SQL;
or it performs all connects and commits/rollbacks using embedded SQL,
and then calls functions that use DB2 CLI APIs, notably, a null
connection.
Query statement processing cannot straddle DB2 CLI and embedded SQL
interfaces for the same statement. For example, the application cannot
open a cursor using embedded SQL, and then call the DB2 CLI SQLFetch()
function to retrieve row data.
Since DB2 CLI permits multiple connections, the SQLSetConnection()
function must be called prior to executing any embedded SQL. This
allows the application to explicitly specify the connection under
which the embedded SQL processing is performed.

If the DB2 CLI application is multithreaded and also makes embedded
SQL calls or DB2 API calls, then each thread must have a DB2 context.

"

I do not understand this.

I understand that the embedded sql cursor can not be processed by cli
or the other way around. But why must connection management and
transaction management either be in embedded sql or either in CLI
(exclusively)?

My thinking was that embedded sql en cli have (should have?) their own
context. If cli sql is to be done, the process will first save the
context (whatever it may be), set it's own context, do its work (could
be a commit) and then restore the context to what was found before
working in this particular cli context (the Unix way: when needing a
special state, save the state, do the action, restore the state when
done). That would allow embedded sql and cli to be totally split (what
I needed).

My investigation was in 7.2. What I copied from the on-line manual is
(also) the 8.2 explanation.

So what exactly is a context in db2 (luw) used for, and based on it,
how to explain the constraint concerning connection management and
transaction management when both embedded sql en cli are used in one
program/process? It is based on a non multi-threaded process
environment, but maybe it could help to answer the original posting
also.

Bernard Dhooghe

[Bernard Dhooghe]

Mixing CLL and embedded SQL is supported, a limitation is that connect
and transaction management must either be done in embedded SQL or in
CLI.

(My) problem is to perfectly separate embedded sql (connect and
transactions) and CLI. By taking care of the contexts I would say, but
what
is the context in embedded sql and why is connection management and
transaction management not totally independent?

DB2 UDB (only V8?)allows embbeded sql multithreaded applications (from
the Info Center, http://publib.boulder.ibm.com/infoce...dex.jspsearch),
searching on: context.

'Concurrent Transactions' link as result of the search:

"
Concurrent Transactions

Sometimes it is useful for an application to have multiple independent
connections called concurrent transactions . Using concurrent
transactions, an application can connect to several databases at the
same time, and can establish several distinct connections to the same
database.

The context APIs that are used for multiple-thread database access
allow an application to use concurrent transactions. Each context
created in an application is independent from the other contexts. This
means you create a context, connect to a database using the context,
and run SQL statements against the database without being affected by
the activities such as running COMMIT or ROLLBACK statements of other
contexts.

For example, suppose you are creating an application that allows a
user to run SQL statements against one database, and keeps a log of
the activities performed in a second database. Because the log must be
kept up to date, it is necessary to issue a COMMIT statement after
each update of the log, but you do not want the user's SQL statements
affected by commits for the log. This is a perfect situation for
concurrent transactions. In your application, create two contexts: one
connects to the user's database and is used for all the user's SQL;
the other connects to the log database and is used for updating the
log. With this design, when you commit a change to the log database,
you do not affect the user's current unit of work.

Another benefit of concurrent transactions is that if the work on the
cursors in one connection is rolled back, it has no affect on the
cursors in other connections. After the rollback in the one
connection, both the work done and the cursor positions are still
maintained in the other connections.

"

and

the link:

How to use multiple context APIs on UNIX
shows an example of a C program with embedded sql (dynamic) and
multiple threads and contexts.

For embbedded sql, I would conclude from it that yes multi-context
(within multiple threads) is possible.
What I find confusing is the mixing in the documentation of
multi-thread asecpts and multi-context aspects. Several threads can
share a context, a single thread can have multiple contexts, so I
would assume that context and multi-thread is orthogonal to each
other.

As embedded sql has a context or can have multiple contexts, as cli
has contexts usage, I do not understand how there is still a
limitation on the connect/transaction managament when cli and embedded
sql is mixed (every context having it's commit scope).
Bernard Dhooghe



"Mark Yudkin" <my***********************@nospam.org> wrote in message news:<ci**********@ngspool-d02.news.aol.com>...

Mixing CLI and embedded SQL is not my problem, we have only embedded SQL.
Also, I know that under V7.2 and before, multithreading did not work "by
default". Rather, explicit context management (sqleAttachToCtx) was
required. apparently, up to 7.2, embedded SQL could not take advantage of
this functionality. That transaction and connection management can not
straddle the APIs also makes sense, as the techniques for managing these
differ - CLI place more of the responsibility on the user, embedded SQL
states its rules (see CONNECT (Type 2) / RELEASE / COMMIT and ROLLBACK). I
would assume that your scenario 'one embedded sql "session/connect" and one
or more cli "sessions/connects".' would not be supported.

---

With V8:

For DB2 Version 8, all Version 8 applications are multithreaded by

default,

and are capable of using multiple contexts. (The behavior of pre-Version 8
applications remains unchanged.)

and this is what leads me to think that what I want may work.

The multithreading aspects of mixing CLI / embedded SQL are probably closely
related to my question, but you're not using that. My question is more on of
thread safety and synchronization within the embedded SQL API calls
generated by the preprocessor, as well as of contextual and transactional
management on a per thread basis.

"Bernard Dhooghe" <no***@attglobal.net> wrote in message
news:25**************************@posting.google.c om...

"Mark Yudkin" <my***********************@nospam.org> wrote in message

news:<ci**********@ngspool-d02.news.aol.com>...

The documentation is unclear (at least to me) on the permissibility of
accessing DB2 (8.1.5) concurrently on and from Windows 2000 / XP / 2003,
with separate transactions scope, from separate threads of a multithreaded program using embedded SQL. Since the threads do not need to share
transaction scopes, the sqleAttachToCtx family of APIs do not seem to be
necessary.

<quote>
In the default implementation of threaded applications against a DB2
database, serialization of access to the database is enforced by the
database APIs. If one thread performs a database call, calls made by other threads will be blocked until the first call completes, even if the
subsequent calls access database objects that are unrelated to the first
call. In addition, all threads within a process share a commit scope. True concurrent access to a database can only be achieved through separate
processes, or by using the APIs that are described in this section.
</quote>
indicates no, I need separate processes.

But 2 paragraphs later:
<quote>
For DB2 Version 8, all Version 8 applications are multithreaded by default, and are capable of using multiple contexts. (The behavior of pre-Version 8 applications remains unchanged.) If you want, you can use the following DB2 APIs to use multiple contexts. Specifically, your application can create a context for a thread, attach to or detach from a separate context for each thread, and pass contexts between threads. If your application does not call any of these APIs, DB2 will automatically manage the multiple contexts for your application:
</quote>
exactly the opposite appears to be the case - in fact, it says it will do exactly what I want, by default.

For CLI, the documentation confirms the multithreaded support:
<quote>
The DB2 CLI automatically uses multiple contexts to achieve thread-safe,
concurrent database access on platforms that support multi-threading.
</quote>
but does not indicate whether this applied to embedded SQL.

By contrast, for COBOL, FORTRAN and REXX under "Embedded SQL", and for Perl, the the documentation clearly states: "COBOL/FORTRAN/REXX/Perl does not
support multiple-thread database access", but no further explanation is
given. For C/C++ the statement is not made. I know that the embedded SQL
APIs (as documented in the precompiler downloadable documentation) exist in both C and generic styles - is the choice of API by the language precompiler perhaps the relevant factor? (We're using IBM PL/I for Windows 2.1.12, which has native multithreading, I haven't examined which API is being used so
far).

Does anybody know what the reality is?

Hello,

I can not answer the question.

But I can add some comments on a question I have that maybe could help
to shade a light on both problems.

My goal was/is: how can I mix embedded sql and cli and keep separate
transactions for the cli connections and the embedded sql connection,
the process having just one thread but can have one embedded sql
"session/connect" and one or more cli "sessions/connects".

The DB2 LUW manual says:

"Considerations for mixing embedded SQL and DB2 CLI
It is possible, and sometimes desirable, to use DB2 CLI in conjunction
with embedded static SQL in an application. Consider the scenario
where the application developer wishes to take advantage of the ease
of use provided by the DB2 CLI catalog functions and maximize the
portion of the application's processing where performance is critical.
In order to mix the use of DB2 CLI and embedded SQL, the application
must comply with the following rules:

All connection management and transaction management must be performed
completely using either DB2 CLI or embedded SQL - never a mixture of
the two. Two options are available to the application:
it performs all connects and commits/rollbacks using DB2 CLI calls,
and then calls functions written using embedded SQL;
or it performs all connects and commits/rollbacks using embedded SQL,
and then calls functions that use DB2 CLI APIs, notably, a null
connection.
Query statement processing cannot straddle DB2 CLI and embedded SQL
interfaces for the same statement. For example, the application cannot
open a cursor using embedded SQL, and then call the DB2 CLI SQLFetch()
function to retrieve row data.
Since DB2 CLI permits multiple connections, the SQLSetConnection()
function must be called prior to executing any embedded SQL. This
allows the application to explicitly specify the connection under
which the embedded SQL processing is performed.

If the DB2 CLI application is multithreaded and also makes embedded
SQL calls or DB2 API calls, then each thread must have a DB2 context.

"

I do not understand this.

I understand that the embedded sql cursor can not be processed by cli
or the other way around. But why must connection management and
transaction management either be in embedded sql or either in CLI
(exclusively)?

My thinking was that embedded sql en cli have (should have?) their own
context. If cli sql is to be done, the process will first save the
context (whatever it may be), set it's own context, do its work (could
be a commit) and then restore the context to what was found before
working in this particular cli context (the Unix way: when needing a
special state, save the state, do the action, restore the state when
done). That would allow embedded sql and cli to be totally split (what
I needed).

My investigation was in 7.2. What I copied from the on-line manual is
(also) the 8.2 explanation.

So what exactly is a context in db2 (luw) used for, and based on it,
how to explain the constraint concerning connection management and
transaction management when both embedded sql en cli are used in one
program/process? It is based on a non multi-threaded process
environment, but maybe it could help to answer the original posting
also.

Bernard Dhooghe

[Mark Yudkin]

The "concurrent transaction" is exactly my scenario (in fact, the logging
example is exactly out problem). I don't need to mix CLI and embedded SQL,
rather I have only embedded SQL threads. I do understand the context APIs,
but not whether they work with embedded SQL (documentation is unclear).

If you go back to my original question, you'll notice that
1) the documentation states both thread allocation works, and that it
doesn't work, automatically
2) for most languages, multithreaded embedded SQL is documented as
unsupported (why?)
3) I'm using 8.1.5 on Windows XP / 2000, not Unix. The two O/S's have
different threading models.

To your question:

A thread can belong to only context at a time, but can switch between them.
Transaction scope is attached to the context. To understand the need for a
thread to switch contexts, consider the classical thread pool model for
handling more clients than there are threads (Win32 buzzword: I/O Completion
Ports)..

When using separate contexts (threads), mixing CLI and embedded SQL
transaction and connect scopes when each has its own context (thread) is a
superset of my question - can I mix embedded SQL and multi-threading at all?
At this point, we both have the same question, but nobody from IBM seems to
be looking in to give us an answer.

I'll try opening a support case with IBM.

"Bernard Dhooghe" <no***@attglobal.net> wrote in message
news:25**************************@posting.google.c om...

Mixing CLL and embedded SQL is supported, a limitation is that connect
and transaction management must either be done in embedded SQL or in
CLI.

(My) problem is to perfectly separate embedded sql (connect and
transactions) and CLI. By taking care of the contexts I would say, but
what
is the context in embedded sql and why is connection management and
transaction management not totally independent?

DB2 UDB (only V8?)allows embbeded sql multithreaded applications (from
the Info Center, http://publib.boulder.ibm.com/infoce...dex.jspsearch), searching on: context.

'Concurrent Transactions' link as result of the search:

"
Concurrent Transactions

Sometimes it is useful for an application to have multiple independent
connections called concurrent transactions . Using concurrent
transactions, an application can connect to several databases at the
same time, and can establish several distinct connections to the same
database.

The context APIs that are used for multiple-thread database access
allow an application to use concurrent transactions. Each context
created in an application is independent from the other contexts. This
means you create a context, connect to a database using the context,
and run SQL statements against the database without being affected by
the activities such as running COMMIT or ROLLBACK statements of other
contexts.

For example, suppose you are creating an application that allows a
user to run SQL statements against one database, and keeps a log of
the activities performed in a second database. Because the log must be
kept up to date, it is necessary to issue a COMMIT statement after
each update of the log, but you do not want the user's SQL statements
affected by commits for the log. This is a perfect situation for
concurrent transactions. In your application, create two contexts: one
connects to the user's database and is used for all the user's SQL;
the other connects to the log database and is used for updating the
log. With this design, when you commit a change to the log database,
you do not affect the user's current unit of work.

Another benefit of concurrent transactions is that if the work on the
cursors in one connection is rolled back, it has no affect on the
cursors in other connections. After the rollback in the one
connection, both the work done and the cursor positions are still
maintained in the other connections.

"

and

the link:

How to use multiple context APIs on UNIX
shows an example of a C program with embedded sql (dynamic) and
multiple threads and contexts.

For embbedded sql, I would conclude from it that yes multi-context
(within multiple threads) is possible.
What I find confusing is the mixing in the documentation of
multi-thread asecpts and multi-context aspects. Several threads can
share a context, a single thread can have multiple contexts, so I
would assume that context and multi-thread is orthogonal to each
other.

As embedded sql has a context or can have multiple contexts, as cli
has contexts usage, I do not understand how there is still a
limitation on the connect/transaction managament when cli and embedded
sql is mixed (every context having it's commit scope).
Bernard Dhooghe



"Mark Yudkin" <my***********************@nospam.org> wrote in message

news:<ci**********@ngspool-d02.news.aol.com>...

Mixing CLI and embedded SQL is not my problem, we have only embedded SQL. Also, I know that under V7.2 and before, multithreading did not work "by
default". Rather, explicit context management (sqleAttachToCtx) was
required. apparently, up to 7.2, embedded SQL could not take advantage of this functionality. That transaction and connection management can not
straddle the APIs also makes sense, as the techniques for managing these
differ - CLI place more of the responsibility on the user, embedded SQL
states its rules (see CONNECT (Type 2) / RELEASE / COMMIT and ROLLBACK). I would assume that your scenario 'one embedded sql "session/connect" and one or more cli "sessions/connects".' would not be supported.

---

With V8:

For DB2 Version 8, all Version 8 applications are multithreaded by

default,

and are capable of using multiple contexts. (The behavior of pre-Version 8 applications remains unchanged.)

and this is what leads me to think that what I want may work.

The multithreading aspects of mixing CLI / embedded SQL are probably closely related to my question, but you're not using that. My question is more on of thread safety and synchronization within the embedded SQL API calls
generated by the preprocessor, as well as of contextual and transactional management on a per thread basis.

"Bernard Dhooghe" <no***@attglobal.net> wrote in message
news:25**************************@posting.google.c om...

"Mark Yudkin" <my***********************@nospam.org> wrote in message

news:<ci**********@ngspool-d02.news.aol.com>...

> The documentation is unclear (at least to me) on the permissibility of > accessing DB2 (8.1.5) concurrently on and from Windows 2000 / XP / 2003, > with separate transactions scope, from separate threads of a

multithreaded

> program using embedded SQL. Since the threads do not need to share
> transaction scopes, the sqleAttachToCtx family of APIs do not seem to be > necessary.
>
> <quote>
> In the default implementation of threaded applications against a DB2
> database, serialization of access to the database is enforced by the
> database APIs. If one thread performs a database call, calls made by

other

> threads will be blocked until the first call completes, even if the
> subsequent calls access database objects that are unrelated to the first > call. In addition, all threads within a process share a commit scope.

True

> concurrent access to a database can only be achieved through

separate > processes, or by using the APIs that are described in this section.
> </quote>
> indicates no, I need separate processes.
>
> But 2 paragraphs later:
> <quote>
> For DB2 Version 8, all Version 8 applications are multithreaded by

default,

> and are capable of using multiple contexts. (The behavior of pre-Version 8

> applications remains unchanged.) If you want, you can use the

following DB2

> APIs to use multiple contexts. Specifically, your application can

create a

> context for a thread, attach to or detach from a separate context

for each

> thread, and pass contexts between threads. If your application does

not call

> any of these APIs, DB2 will automatically manage the multiple

contexts for

> your application:
> </quote>
> exactly the opposite appears to be the case - in fact, it says it

will do

> exactly what I want, by default.
>
> For CLI, the documentation confirms the multithreaded support:
> <quote>
> The DB2 CLI automatically uses multiple contexts to achieve

thread-safe, > concurrent database access on platforms that support multi-threading. > </quote>
> but does not indicate whether this applied to embedded SQL.
>
> By contrast, for COBOL, FORTRAN and REXX under "Embedded SQL", and for Perl,

> the the documentation clearly states: "COBOL/FORTRAN/REXX/Perl does

not > support multiple-thread database access", but no further explanation is > given. For C/C++ the statement is not made. I know that the embedded SQL > APIs (as documented in the precompiler downloadable documentation) exist in

> both C and generic styles - is the choice of API by the language

precompiler

> perhaps the relevant factor? (We're using IBM PL/I for Windows

2.1.12, which

> has native multithreading, I haven't examined which API is being

used so > far).
>
> Does anybody know what the reality is?

Hello,

I can not answer the question.

But I can add some comments on a question I have that maybe could help
to shade a light on both problems.

My goal was/is: how can I mix embedded sql and cli and keep separate
transactions for the cli connections and the embedded sql connection,
the process having just one thread but can have one embedded sql
"session/connect" and one or more cli "sessions/connects".

The DB2 LUW manual says:

"Considerations for mixing embedded SQL and DB2 CLI
It is possible, and sometimes desirable, to use DB2 CLI in conjunction
with embedded static SQL in an application. Consider the scenario
where the application developer wishes to take advantage of the ease
of use provided by the DB2 CLI catalog functions and maximize the
portion of the application's processing where performance is critical.
In order to mix the use of DB2 CLI and embedded SQL, the application
must comply with the following rules:

All connection management and transaction management must be performed
completely using either DB2 CLI or embedded SQL - never a mixture of
the two. Two options are available to the application:
it performs all connects and commits/rollbacks using DB2 CLI calls,
and then calls functions written using embedded SQL;
or it performs all connects and commits/rollbacks using embedded SQL,
and then calls functions that use DB2 CLI APIs, notably, a null
connection.
Query statement processing cannot straddle DB2 CLI and embedded SQL
interfaces for the same statement. For example, the application cannot
open a cursor using embedded SQL, and then call the DB2 CLI SQLFetch()
function to retrieve row data.
Since DB2 CLI permits multiple connections, the SQLSetConnection()
function must be called prior to executing any embedded SQL. This
allows the application to explicitly specify the connection under
which the embedded SQL processing is performed.

If the DB2 CLI application is multithreaded and also makes embedded
SQL calls or DB2 API calls, then each thread must have a DB2 context.

"

I do not understand this.

I understand that the embedded sql cursor can not be processed by cli
or the other way around. But why must connection management and
transaction management either be in embedded sql or either in CLI
(exclusively)?

My thinking was that embedded sql en cli have (should have?) their own
context. If cli sql is to be done, the process will first save the
context (whatever it may be), set it's own context, do its work (could
be a commit) and then restore the context to what was found before
working in this particular cli context (the Unix way: when needing a
special state, save the state, do the action, restore the state when
done). That would allow embedded sql and cli to be totally split (what
I needed).

My investigation was in 7.2. What I copied from the on-line manual is
(also) the 8.2 explanation.

So what exactly is a context in db2 (luw) used for, and based on it,
how to explain the constraint concerning connection management and
transaction management when both embedded sql en cli are used in one
program/process? It is based on a non multi-threaded process
environment, but maybe it could help to answer the original posting
also.

Bernard Dhooghe

[Bernard Dhooghe]

Indeed the information on non-support of multithreaded (I would say:
multi-context)embedded sql in Perl, Rexx, Cobol,Fortran is succinct:
stated as non supported, it is not explained why.

For embedded SQL (MF) Cobol, the MicroFocus Cobol compiler calls the
IBM precompiler functions through the (IBM) precompiler API, why would
multi-context in one ore more threads not be supported in MF Cobol -C
with embbedded SQL supports it, with the same precompiler API- as long
as the application takes care of the context management through the
API routines provided (that can be called from Cobol).
Bernard Dhooghe

"Mark Yudkin" <my***********************@nospam.org> wrote in message news:<cj**********@ngspool-d02.news.aol.com>...

The "concurrent transaction" is exactly my scenario (in fact, the logging
example is exactly out problem). I don't need to mix CLI and embedded SQL,
rather I have only embedded SQL threads. I do understand the context APIs,
but not whether they work with embedded SQL (documentation is unclear).

If you go back to my original question, you'll notice that
1) the documentation states both thread allocation works, and that it
doesn't work, automatically
2) for most languages, multithreaded embedded SQL is documented as
unsupported (why?)
3) I'm using 8.1.5 on Windows XP / 2000, not Unix. The two O/S's have
different threading models.

To your question:

A thread can belong to only context at a time, but can switch between them.
Transaction scope is attached to the context. To understand the need for a
thread to switch contexts, consider the classical thread pool model for
handling more clients than there are threads (Win32 buzzword: I/O Completion
Ports)..

When using separate contexts (threads), mixing CLI and embedded SQL
transaction and connect scopes when each has its own context (thread) is a
superset of my question - can I mix embedded SQL and multi-threading at all?
At this point, we both have the same question, but nobody from IBM seems to
be looking in to give us an answer.

I'll try opening a support case with IBM.

[Mark Yudkin]

You have now rephrased my original question, except that I also point that
out that in V8, context APIs should also be unnecessary. I wish somebody
here from IBM could answer the question.

I've now asked our DB2 support team to open a case in order to pose the
question to IBM DB2 support.

"Bernard Dhooghe" <no***@attglobal.net> wrote in message
news:25*************************@posting.google.co m...

Indeed the information on non-support of multithreaded (I would say:
multi-context)embedded sql in Perl, Rexx, Cobol,Fortran is succinct:
stated as non supported, it is not explained why.

For embedded SQL (MF) Cobol, the MicroFocus Cobol compiler calls the
IBM precompiler functions through the (IBM) precompiler API, why would
multi-context in one ore more threads not be supported in MF Cobol -C
with embbedded SQL supports it, with the same precompiler API- as long
as the application takes care of the context management through the
API routines provided (that can be called from Cobol).
Bernard Dhooghe

"Mark Yudkin" <my***********************@nospam.org> wrote in message

news:<cj**********@ngspool-d02.news.aol.com>...

The "concurrent transaction" is exactly my scenario (in fact, the logging example is exactly out problem). I don't need to mix CLI and embedded SQL, rather I have only embedded SQL threads. I do understand the context APIs, but not whether they work with embedded SQL (documentation is unclear).

If you go back to my original question, you'll notice that
1) the documentation states both thread allocation works, and that it
doesn't work, automatically
2) for most languages, multithreaded embedded SQL is documented as
unsupported (why?)
3) I'm using 8.1.5 on Windows XP / 2000, not Unix. The two O/S's have
different threading models.

To your question:

A thread can belong to only context at a time, but can switch between them. Transaction scope is attached to the context. To understand the need for a thread to switch contexts, consider the classical thread pool model for
handling more clients than there are threads (Win32 buzzword: I/O Completion Ports)..

When using separate contexts (threads), mixing CLI and embedded SQL
transaction and connect scopes when each has its own context (thread) is a superset of my question - can I mix embedded SQL and multi-threading at all? At this point, we both have the same question, but nobody from IBM seems to be looking in to give us an answer.

I'll try opening a support case with IBM.

[Bernard Dhooghe]

I think the back-end (the engine an application connects to, one or
several times) is not aware of threads. But it is of contexts. So my
understanding is that being it in a single-thread or in a
multi-threaded process the context is the reference for transaction
and serialization management (not the thread). At this time (LUW UDB
8.2) there is no way to detach the application from an sql request, it
is a sync processing. The only way to to have multiple requests
running or interfere with a running request, is to have another thread
doing it, with the same context or another context.

Bernard Dhooghe

"Mark Yudkin" <my***********************@nospam.org> wrote in message news:<cj**********@ngspool-d02.news.aol.com>...

You have now rephrased my original question, except that I also point that
out that in V8, context APIs should also be unnecessary. I wish somebody
here from IBM could answer the question.

I've now asked our DB2 support team to open a case in order to pose the
question to IBM DB2 support.

"Bernard Dhooghe" <no***@attglobal.net> wrote in message
news:25*************************@posting.google.co m...

Indeed the information on non-support of multithreaded (I would say:
multi-context)embedded sql in Perl, Rexx, Cobol,Fortran is succinct:
stated as non supported, it is not explained why.

For embedded SQL (MF) Cobol, the MicroFocus Cobol compiler calls the
IBM precompiler functions through the (IBM) precompiler API, why would
multi-context in one ore more threads not be supported in MF Cobol -C
with embbedded SQL supports it, with the same precompiler API- as long
as the application takes care of the context management through the
API routines provided (that can be called from Cobol).
Bernard Dhooghe

"Mark Yudkin" <my***********************@nospam.org> wrote in message

news:<cj**********@ngspool-d02.news.aol.com>...

The "concurrent transaction" is exactly my scenario (in fact, the logging example is exactly out problem). I don't need to mix CLI and embedded SQL, rather I have only embedded SQL threads. I do understand the context APIs, but not whether they work with embedded SQL (documentation is unclear).

If you go back to my original question, you'll notice that
1) the documentation states both thread allocation works, and that it
doesn't work, automatically
2) for most languages, multithreaded embedded SQL is documented as
unsupported (why?)
3) I'm using 8.1.5 on Windows XP / 2000, not Unix. The two O/S's have
different threading models.

To your question:

A thread can belong to only context at a time, but can switch between them. Transaction scope is attached to the context. To understand the need for a thread to switch contexts, consider the classical thread pool model for
handling more clients than there are threads (Win32 buzzword: I/O Completion Ports)..

When using separate contexts (threads), mixing CLI and embedded SQL
transaction and connect scopes when each has its own context (thread) is a superset of my question - can I mix embedded SQL and multi-threading at all? At this point, we both have the same question, but nobody from IBM seems to be looking in to give us an answer.

I'll try opening a support case with IBM.

[Mark Yudkin]

If embedded SQL is supported by the multi-threading API (e.g. if the
precompiler-generated APIs are thread-safe - using only thread local
storage), then the initial, default, 1 - 1 mapping of threads to context is
a documented function of DB2 V8:

<quote>
For DB2 Version 8, all Version 8 applications are multithreaded by default,
and are capable of using multiple contexts. (The behavior of pre-Version 8
applications remains unchanged.) If you want, you can use the following DB2
APIs to use multiple contexts. Specifically, your application can create a
context for a thread, attach to or detach from a separate context for each
thread, and pass contexts between threads. If your application does not call
any of these APIs, DB2 will automatically manage the multiple contexts for
your application.
</quote>

My question concerns the IF part of the above - which is unclear. I've
understood the THEN.

"Bernard Dhooghe" <no***@attglobal.net> wrote in message
news:25*************************@posting.google.co m...

I think the back-end (the engine an application connects to, one or
several times) is not aware of threads. But it is of contexts. So my
understanding is that being it in a single-thread or in a
multi-threaded process the context is the reference for transaction
and serialization management (not the thread). At this time (LUW UDB
8.2) there is no way to detach the application from an sql request, it
is a sync processing. The only way to to have multiple requests
running or interfere with a running request, is to have another thread
doing it, with the same context or another context.

Bernard Dhooghe

"Mark Yudkin" <my***********************@nospam.org> wrote in message

news:<cj**********@ngspool-d02.news.aol.com>...

You have now rephrased my original question, except that I also point that out that in V8, context APIs should also be unnecessary. I wish somebody
here from IBM could answer the question.

I've now asked our DB2 support team to open a case in order to pose the
question to IBM DB2 support.

"Bernard Dhooghe" <no***@attglobal.net> wrote in message
news:25*************************@posting.google.co m...

Indeed the information on non-support of multithreaded (I would say:
multi-context)embedded sql in Perl, Rexx, Cobol,Fortran is succinct:
stated as non supported, it is not explained why.

For embedded SQL (MF) Cobol, the MicroFocus Cobol compiler calls the
IBM precompiler functions through the (IBM) precompiler API, why would
multi-context in one ore more threads not be supported in MF Cobol -C
with embbedded SQL supports it, with the same precompiler API- as long
as the application takes care of the context management through the
API routines provided (that can be called from Cobol).
Bernard Dhooghe

"Mark Yudkin" <my***********************@nospam.org> wrote in message

news:<cj**********@ngspool-d02.news.aol.com>...

> The "concurrent transaction" is exactly my scenario (in fact, the

logging

> example is exactly out problem). I don't need to mix CLI and embedded

SQL,

> rather I have only embedded SQL threads. I do understand the

context APIs,

> but not whether they work with embedded SQL (documentation is

unclear). >
> If you go back to my original question, you'll notice that
> 1) the documentation states both thread allocation works, and that it > doesn't work, automatically
> 2) for most languages, multithreaded embedded SQL is documented as
> unsupported (why?)
> 3) I'm using 8.1.5 on Windows XP / 2000, not Unix. The two O/S's have > different threading models.
>
> To your question:
>
> A thread can belong to only context at a time, but can switch between them.

> Transaction scope is attached to the context. To understand the need

for a

> thread to switch contexts, consider the classical thread pool model

for > handling more clients than there are threads (Win32 buzzword: I/O

Completion

> Ports)..
>
> When using separate contexts (threads), mixing CLI and embedded SQL
> transaction and connect scopes when each has its own context

(thread) is a

> superset of my question - can I mix embedded SQL and multi-threading

at all?

> At this point, we both have the same question, but nobody from IBM

seems to

> be looking in to give us an answer.
>
> I'll try opening a support case with IBM.
>
>

[Bernard Dhooghe]

A context is something global for the client process, as it can be
passed from thread to thread if the client application runs multiple
threads.

In CLI "If your application does not call ...", then "The DB2 CLI
automatically uses multiple contexts to achieve thread-safe".

In embedded SQL there will be only one context if the extended calls
are not used to manage multiple context, and access will be serialized
if multiple threads are using the (hidden) context. To manage multiple
context, context apis must be used, there is no exec sql support for
it.

This is why in the example "dbthrds.sqc" (not present in the 7.2
samples)the management of multiple contexts is done with the context
API calls.

In 8.2, even with one thread as I tested, embedded sql can have
multiple connections when using the context apis, allowing to have
independent units of work, and independent commit boundaries, there is
no need for a more elaborate multiple thread model if separate
transactions scoping is the concern (and not multiple parallel sql
statements running for one client application).

Bernard Dhooghe

"Mark Yudkin" <my***********************@nospam.org> wrote in message news:<cj**********@ngspool-d02.news.aol.com>...

If embedded SQL is supported by the multi-threading API (e.g. if the
precompiler-generated APIs are thread-safe - using only thread local
storage), then the initial, default, 1 - 1 mapping of threads to context is
a documented function of DB2 V8:

<quote>
For DB2 Version 8, all Version 8 applications are multithreaded by default,
and are capable of using multiple contexts. (The behavior of pre-Version 8
applications remains unchanged.) If you want, you can use the following DB2
APIs to use multiple contexts. Specifically, your application can create a
context for a thread, attach to or detach from a separate context for each
thread, and pass contexts between threads. If your application does not call
any of these APIs, DB2 will automatically manage the multiple contexts for
your application.
</quote>

My question concerns the IF part of the above - which is unclear. I've
understood the THEN.

"Bernard Dhooghe" <no***@attglobal.net> wrote in message
news:25*************************@posting.google.co m...

I think the back-end (the engine an application connects to, one or
several times) is not aware of threads. But it is of contexts. So my
understanding is that being it in a single-thread or in a
multi-threaded process the context is the reference for transaction
and serialization management (not the thread). At this time (LUW UDB
8.2) there is no way to detach the application from an sql request, it
is a sync processing. The only way to to have multiple requests
running or interfere with a running request, is to have another thread
doing it, with the same context or another context.

Bernard Dhooghe

"Mark Yudkin" <my***********************@nospam.org> wrote in message

news:<cj**********@ngspool-d02.news.aol.com>...

You have now rephrased my original question, except that I also point that out that in V8, context APIs should also be unnecessary. I wish somebody
here from IBM could answer the question.

I've now asked our DB2 support team to open a case in order to pose the
question to IBM DB2 support.

"Bernard Dhooghe" <no***@attglobal.net> wrote in message
news:25*************************@posting.google.co m...
> Indeed the information on non-support of multithreaded (I would say:
> multi-context)embedded sql in Perl, Rexx, Cobol,Fortran is succinct:
> stated as non supported, it is not explained why.
>
> For embedded SQL (MF) Cobol, the MicroFocus Cobol compiler calls the
> IBM precompiler functions through the (IBM) precompiler API, why would
> multi-context in one ore more threads not be supported in MF Cobol -C
> with embbedded SQL supports it, with the same precompiler API- as long
> as the application takes care of the context management through the
> API routines provided (that can be called from Cobol).
>
>
> Bernard Dhooghe

[Mark Yudkin]

My question concerns multiple parallel SQL from separate threads, using the
embedded SQL API, with each thread having its own distinct transaction
context.

From your answer, I gather that there is no native multithreading support
for embedded SQL, and that the statement:
<quote>
For DB2 Version 8, all Version 8 applications are multithreaded by default,
and are capable of using multiple contexts. (The behavior of pre-Version 8
applications remains unchanged.)
</quote>
is misleading, in that the word "all" in "all Version 8 applications ... by
default" excludes embedded SQL. However, I can manually use the context APIs
to achieve my goal.

"Bernard Dhooghe" <no***@attglobal.net> wrote in message
news:25**************************@posting.google.c om...

A context is something global for the client process, as it can be
passed from thread to thread if the client application runs multiple
threads.

In CLI "If your application does not call ...", then "The DB2 CLI
automatically uses multiple contexts to achieve thread-safe".

In embedded SQL there will be only one context if the extended calls
are not used to manage multiple context, and access will be serialized
if multiple threads are using the (hidden) context. To manage multiple
context, context apis must be used, there is no exec sql support for
it.

This is why in the example "dbthrds.sqc" (not present in the 7.2
samples)the management of multiple contexts is done with the context
API calls.

In 8.2, even with one thread as I tested, embedded sql can have
multiple connections when using the context apis, allowing to have
independent units of work, and independent commit boundaries, there is
no need for a more elaborate multiple thread model if separate
transactions scoping is the concern (and not multiple parallel sql
statements running for one client application).

Bernard Dhooghe

"Mark Yudkin" <my***********************@nospam.org> wrote in message

news:<cj**********@ngspool-d02.news.aol.com>...

If embedded SQL is supported by the multi-threading API (e.g. if the
precompiler-generated APIs are thread-safe - using only thread local
storage), then the initial, default, 1 - 1 mapping of threads to context is a documented function of DB2 V8:

<quote>
For DB2 Version 8, all Version 8 applications are multithreaded by default, and are capable of using multiple contexts. (The behavior of pre-Version 8 applications remains unchanged.) If you want, you can use the following DB2 APIs to use multiple contexts. Specifically, your application can create a context for a thread, attach to or detach from a separate context for each thread, and pass contexts between threads. If your application does not call any of these APIs, DB2 will automatically manage the multiple contexts for your application.
</quote>

My question concerns the IF part of the above - which is unclear. I've
understood the THEN.

"Bernard Dhooghe" <no***@attglobal.net> wrote in message
news:25*************************@posting.google.co m...

I think the back-end (the engine an application connects to, one or
several times) is not aware of threads. But it is of contexts. So my
understanding is that being it in a single-thread or in a
multi-threaded process the context is the reference for transaction
and serialization management (not the thread). At this time (LUW UDB
8.2) there is no way to detach the application from an sql request, it
is a sync processing. The only way to to have multiple requests
running or interfere with a running request, is to have another thread
doing it, with the same context or another context.

Bernard Dhooghe

"Mark Yudkin" <my***********************@nospam.org> wrote in message

news:<cj**********@ngspool-d02.news.aol.com>...

> You have now rephrased my original question, except that I also point

that

> out that in V8, context APIs should also be unnecessary. I wish

somebody > here from IBM could answer the question.
>
> I've now asked our DB2 support team to open a case in order to pose the > question to IBM DB2 support.
>
> "Bernard Dhooghe" <no***@attglobal.net> wrote in message
> news:25*************************@posting.google.co m...
> > Indeed the information on non-support of multithreaded (I would say: > > multi-context)embedded sql in Perl, Rexx, Cobol,Fortran is succinct: > > stated as non supported, it is not explained why.
> >
> > For embedded SQL (MF) Cobol, the MicroFocus Cobol compiler calls the > > IBM precompiler functions through the (IBM) precompiler API, why would > > multi-context in one ore more threads not be supported in MF Cobol -C > > with embbedded SQL supports it, with the same precompiler API- as long > > as the application takes care of the context management through the > > API routines provided (that can be called from Cobol).
> >
> >
> > Bernard Dhooghe

[Bernard Dhooghe]

The manual tries to achieve conciseness, but I find also the result
misleading.

I will chase further my additional comment concerning the CLI/embedded
sql transaction handling and post later if I can get more information
if the manual is still correct when using the 8.1/2
SQL_CTX_MULTI_MANUAL capability setting in the sqleSetTypeCtx API.

Bernard Dhooghe

"Mark Yudkin" <my***********************@nospam.org> wrote in message news:<cj**********@ngspool-d02.news.aol.com>...

My question concerns multiple parallel SQL from separate threads, using the
embedded SQL API, with each thread having its own distinct transaction
context.

From your answer, I gather that there is no native multithreading support
for embedded SQL, and that the statement:
<quote>
For DB2 Version 8, all Version 8 applications are multithreaded by default,
and are capable of using multiple contexts. (The behavior of pre-Version 8
applications remains unchanged.)
</quote>
is misleading, in that the word "all" in "all Version 8 applications ... by
default" excludes embedded SQL. However, I can manually use the context APIs
to achieve my goal.

"Bernard Dhooghe" <no***@attglobal.net> wrote in message
news:25**************************@posting.google.c om...

A context is something global for the client process, as it can be
passed from thread to thread if the client application runs multiple
threads.

In CLI "If your application does not call ...", then "The DB2 CLI
automatically uses multiple contexts to achieve thread-safe".

In embedded SQL there will be only one context if the extended calls
are not used to manage multiple context, and access will be serialized
if multiple threads are using the (hidden) context. To manage multiple
context, context apis must be used, there is no exec sql support for
it.

This is why in the example "dbthrds.sqc" (not present in the 7.2
samples)the management of multiple contexts is done with the context
API calls.

In 8.2, even with one thread as I tested, embedded sql can have
multiple connections when using the context apis, allowing to have
independent units of work, and independent commit boundaries, there is
no need for a more elaborate multiple thread model if separate
transactions scoping is the concern (and not multiple parallel sql
statements running for one client application).

Bernard Dhooghe