COMMIT statements in a stored procedure

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark A - 13 Jul 2006 16:16 GMT

Quoted from the: "Application Development Guide: Programming Server
Applications Version 8.2": (DB2 for LUW).

"Stored procedures cannot issue COMMIT or ROLLBACK statements if the stored
procedure was invoked from an application that established a type 2
connection to the database."

1. Is this accurate?

2. If a stored procedure is tested from the CLP (using type 2 connection to
a remote database), does that mean that no intermediate commits in the SP
are actually being done and that the only commit is done by auto-commit of
the client?

Reply to this Message

Serge Rielau - 14 Jul 2006 16:16 GMT

> Quoted from the: "Application Development Guide: Programming Server
> Applications Version 8.2": (DB2 for LUW).
[quoted text clipped - 4 lines]
>
> 1. Is this accurate?

I have no reason to disbelieve it

> 2. If a stored procedure is tested from the CLP (using type 2 connection to
> a remote database), does that mean that no intermediate commits in the SP
> are actually being done and that the only commit is done by auto-commit of
> the client?

The text above suggests that the COMMIT in the procedure will fail with
a negative SQLCODE.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message

Mark A - 14 Jul 2006 16:45 GMT

>> 2. If a stored procedure is tested from the CLP (using type 2 connection
>> to a remote database), does that mean that no intermediate commits in the
[quoted text clipped - 5 lines]
> Cheers
> Serge

It definitely does not fail with an SQL error in my test. I will do some
further testing.

Reply to this Message

Ian - 14 Jul 2006 20:57 GMT

>>> 2. If a stored procedure is tested from the CLP (using type 2 connection
>>> to a remote database), does that mean that no intermediate commits in the
[quoted text clipped - 8 lines]
> It definitely does not fail with an SQL error in my test. I will do some
> further testing.

Are you actually making a Type-2 Connection? (i.e. so your program
that calls the stored procedure can have 1 transaction that spans 2
separate databases)? See:

http://publib.boulder.ibm.com/infocenter/db2luw/v8/topic/com.ibm.db2.udb.doc/adm
in/r0000908.htm

Reply to this Message

Mark A - 14 Jul 2006 23:05 GMT

> Are you actually making a Type-2 Connection? (i.e. so your program
> that calls the stored procedure can have 1 transaction that spans 2
> separate databases)? See:
>
> http://publib.boulder.ibm.com/infocenter/db2luw/v8/topic/com.ibm.db2.udb.doc/adm
in/r0000908.htm

No, I am connecting to a single database with a type 2 driver, and that
explains it. Maybe that is not the best wording in the manual.

Reply to this Message

memmerto@yahoo.com - 15 Jul 2006 04:32 GMT

> > Are you actually making a Type-2 Connection? (i.e. so your program
> > that calls the stored procedure can have 1 transaction that spans 2
[quoted text clipped - 4 lines]
> No, I am connecting to a single database with a type 2 driver, and that
> explains it. Maybe that is not the best wording in the manual.

Driver types are different than connection types.

For example, JDBC defines four different driver types, two of which DB2
support (types 2 and 4).

--
Matt Emmerton

Reply to this Message

Mark A - 15 Jul 2006 05:01 GMT

"memmerto@yahoo.com" <matt@gsicomp.on.ca> wrote in message >

> Driver types are different than connection types.

Really?

Reply to this Message

Knut Stolze - 17 Jul 2006 08:16 GMT

> "memmerto@yahoo.com" <matt@gsicomp.on.ca> wrote in message >
>> Driver types are different than connection types.
>
> Really?

Yes, of course. Both are completely different things.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message