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? 11 4030
"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
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
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
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
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.
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.
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.
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. > >
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
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
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 This discussion thread is closed Replies have been disabled for this discussion. Similar topics
47 posts
views
Thread by mihai |
last post: by
|
16 posts
views
Thread by Robert Zurer |
last post: by
|
5 posts
views
Thread by sarge |
last post: by
|
9 posts
views
Thread by tommy |
last post: by
|
2 posts
views
Thread by Rich |
last post: by
|
55 posts
views
Thread by Sam |
last post: by
|
5 posts
views
Thread by sandy82 |
last post: by
|
2 posts
views
Thread by Pradnya Patil |
last post: by
|
7 posts
views
Thread by Ray |
last post: by
| | | | | | | | | | |