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" <nomen@attglobal.net> wrote in message
news:25959da8.0409230502.6ff85263@posting.google.c om...[color=blue]
> 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,[/color]
http://publib.boulder.ibm.com/infoce...dex.jspsearch),[color=blue]
> 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" <myudkinATcompuserveDOTcom@nospam.org> wrote in message[/color]
news:<cir3ao$6sh$1@ngspool-d02.news.aol.com>...[color=blue][color=green]
> > Mixing CLI and embedded SQL is not my problem, we have only embedded[/color][/color]
SQL.[color=blue][color=green]
> > 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[/color][/color]
of[color=blue][color=green]
> > 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).[/color][/color]
I[color=blue][color=green]
> > would assume that your scenario 'one embedded sql "session/connect" and[/color][/color]
one[color=blue][color=green]
> > or more cli "sessions/connects".' would not be supported.
> >
> > ---
> >
> > With V8:[color=darkred]
> > > For DB2 Version 8, all Version 8 applications are multithreaded by[/color]
> > default,[color=darkred]
> > > and are capable of using multiple contexts. (The behavior of[/color][/color][/color]
pre-Version 8[color=blue][color=green][color=darkred]
> > > applications remains unchanged.)[/color]
> > and this is what leads me to think that what I want may work.
> >
> > The multithreading aspects of mixing CLI / embedded SQL are probably[/color][/color]
closely[color=blue][color=green]
> > related to my question, but you're not using that. My question is more[/color][/color]
on of[color=blue][color=green]
> > thread safety and synchronization within the embedded SQL API calls
> > generated by the preprocessor, as well as of contextual and[/color][/color]
transactional[color=blue][color=green]
> > management on a per thread basis.
> >
> > "Bernard Dhooghe" <nomen@attglobal.net> wrote in message
> > news:25959da8.0409210700.4d690db5@posting.google.c om...[color=darkred]
> > > "Mark Yudkin" <myudkinATcompuserveDOTcom@nospam.org> wrote in message[/color]
> > news:<ciogc5$d9r$1@ngspool-d02.news.aol.com>...[color=darkred]
> > > > The documentation is unclear (at least to me) on the permissibility[/color][/color][/color]
of[color=blue][color=green][color=darkred]
> > > > accessing DB2 (8.1.5) concurrently on and from Windows 2000 / XP /[/color][/color][/color]
2003,[color=blue][color=green][color=darkred]
> > > > with separate transactions scope, from separate threads of a[/color]
> > multithreaded[color=darkred]
> > > > program using embedded SQL. Since the threads do not need to share
> > > > transaction scopes, the sqleAttachToCtx family of APIs do not seem[/color][/color][/color]
to be[color=blue][color=green][color=darkred]
> > > > 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[/color]
> > other[color=darkred]
> > > > threads will be blocked until the first call completes, even if the
> > > > subsequent calls access database objects that are unrelated to the[/color][/color][/color]
first[color=blue][color=green][color=darkred]
> > > > call. In addition, all threads within a process share a commit[/color][/color][/color]
scope.[color=blue][color=green]
> > True[color=darkred]
> > > > concurrent access to a database can only be achieved through[/color][/color][/color]
separate[color=blue][color=green][color=darkred]
> > > > 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[/color]
> > default,[color=darkred]
> > > > and are capable of using multiple contexts. (The behavior of[/color][/color][/color]
pre-Version[color=blue][color=green]
> > 8[color=darkred]
> > > > applications remains unchanged.) If you want, you can use the[/color][/color][/color]
following[color=blue][color=green]
> > DB2[color=darkred]
> > > > APIs to use multiple contexts. Specifically, your application can[/color][/color][/color]
create[color=blue][color=green]
> > a[color=darkred]
> > > > context for a thread, attach to or detach from a separate context[/color][/color][/color]
for[color=blue][color=green]
> > each[color=darkred]
> > > > thread, and pass contexts between threads. If your application does[/color][/color][/color]
not[color=blue][color=green]
> > call[color=darkred]
> > > > any of these APIs, DB2 will automatically manage the multiple[/color][/color][/color]
contexts[color=blue][color=green]
> > for[color=darkred]
> > > > your application:
> > > > </quote>
> > > > exactly the opposite appears to be the case - in fact, it says it[/color][/color][/color]
will[color=blue][color=green]
> > do[color=darkred]
> > > > exactly what I want, by default.
> > > >
> > > > For CLI, the documentation confirms the multithreaded support:
> > > > <quote>
> > > > The DB2 CLI automatically uses multiple contexts to achieve[/color][/color][/color]
thread-safe,[color=blue][color=green][color=darkred]
> > > > concurrent database access on platforms that support[/color][/color][/color]
multi-threading.[color=blue][color=green][color=darkred]
> > > > </quote>
> > > > but does not indicate whether this applied to embedded SQL.
> > > >
> > > > By contrast, for COBOL, FORTRAN and REXX under "Embedded SQL", and[/color][/color][/color]
for[color=blue][color=green]
> > Perl,[color=darkred]
> > > > the the documentation clearly states: "COBOL/FORTRAN/REXX/Perl does[/color][/color][/color]
not[color=blue][color=green][color=darkred]
> > > > support multiple-thread database access", but no further explanation[/color][/color][/color]
is[color=blue][color=green][color=darkred]
> > > > given. For C/C++ the statement is not made. I know that the embedded[/color][/color][/color]
SQL[color=blue][color=green][color=darkred]
> > > > APIs (as documented in the precompiler downloadable documentation)[/color][/color][/color]
exist[color=blue][color=green]
> > in[color=darkred]
> > > > both C and generic styles - is the choice of API by the language[/color]
> > precompiler[color=darkred]
> > > > perhaps the relevant factor? (We're using IBM PL/I for Windows[/color][/color][/color]
2.1.12,[color=blue][color=green]
> > which[color=darkred]
> > > > has native multithreading, I haven't examined which API is being[/color][/color][/color]
used so[color=blue][color=green][color=darkred]
> > > > 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[/color][/color][/color]
