Stored Procedure commit point

[Mark]

If a java applicaiton using the type 4 driver calls a DB2 stored
procedure, does the stored procedure need to do its own commit when
updates are completed? If the stored procedure does a commit or
rollback, does that affect the UOW for any SQL that was directly
issued by the java program before calling the stored procedure?

[Serge Rielau]

Mark wrote:

If a java applicaiton using the type 4 driver calls a DB2 stored
procedure, does the stored procedure need to do its own commit when
updates are completed? If the stored procedure does a commit or
rollback, does that affect the UOW for any SQL that was directly
issued by the java program before calling the stored procedure?

Commit operates on a transaction level.
From that imediately follows that a commit inside of a procedure will
commit whatever happened sinc ethe last commit/rollback independent on
whether that was inside the stored procedure or not.
Just the same a procedure will not implicitly commit.
It's the caller's responsibility.

Relation Information: Check out safepoints

Cheers
Serge

[robert]

Serge Rielau <sr*****@ca.ibm.com> wrote in message news:<2u*************@uni-berlin.de>...

Mark wrote:

If a java applicaiton using the type 4 driver calls a DB2 stored
procedure, does the stored procedure need to do its own commit when
updates are completed? If the stored procedure does a commit or
rollback, does that affect the UOW for any SQL that was directly
issued by the java program before calling the stored procedure? Commit operates on a transaction level.

from a jdbc point of view, it operates on the Connection. since the
SP is executing on the other end of the Connection from the java
caller, and perhaps on a different thread in the caller jvm; i've
always wondered why it's better to have commit in the caller. seems
logically sounder to put Everything about the transaction in the SP.
conflicts with other jdbc calls from the original caller (same or
different caller thread) would be handled from a concurrency standpoint
by DB2 server process. yes/no/maybe??
From that imediately follows that a commit inside of a procedure will
commit whatever happened sinc ethe last commit/rollback independent on
whether that was inside the stored procedure or not.
Just the same a procedure will not implicitly commit.
It's the caller's responsibility.

Relation Information: Check out safepoints

Cheers
Serge

[Knut Stolze]

robert wrote:

Serge Rielau <sr*****@ca.ibm.com> wrote in message
news:<2u*************@uni-berlin.de>...

Mark wrote:

> If a java applicaiton using the type 4 driver calls a DB2 stored
> procedure, does the stored procedure need to do its own commit when
> updates are completed? If the stored procedure does a commit or
> rollback, does that affect the UOW for any SQL that was directly
> issued by the java program before calling the stored procedure? Commit operates on a transaction level.

from a jdbc point of view, it operates on the Connection.

which is just the same. A connection has only a single transaction running
at a certain point in time.
since the
SP is executing on the other end of the Connection from the java
caller, and perhaps on a different thread in the caller jvm; i've
always wondered why it's better to have commit in the caller. seems
logically sounder to put Everything about the transaction in the SP.
A stored procedure call is just like any other statement in a transaction.
You could run a bunch of INSERTs, an SP call, followed by some UPDATEs and
other things - all in the same transaction. If the SP would commit, it
would possibly screw up the callers transaction. And sometimes you just
don't know the circumstances and context in which a SP might be called. So
the general suggestion is to leave the transactional control with the
client who initiated the transaction in the first place.
conflicts with other jdbc calls from the original caller (same or
different caller thread) would be handled from a concurrency standpoint
by DB2 server process. yes/no/maybe??

If multiple threads work on the same connection, then they execute within
the same transaction. You can start a new connection from each thread,
though.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena