By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,199 Members | 1,714 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,199 IT Pros & Developers. It's quick & easy.

COMMIT statements in a stored procedure

P: n/a
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?
Jul 13 '06 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Mark A wrote:
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?
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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jul 14 '06 #2

P: n/a
"Serge Rielau" <sr*****@ca.ibm.comwrote in message
news:4h***********@individual.net...
>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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
It definitely does not fail with an SQL error in my test. I will do some
further testing.
Jul 14 '06 #3

P: n/a
Ian
Mark A wrote:
"Serge Rielau" <sr*****@ca.ibm.comwrote in message
news:4h***********@individual.net...
>>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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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/infoce...n/r0000908.htm


Jul 14 '06 #4

P: n/a
"Ian" <ia*****@mobileaudio.comwrote in message
news:44**********@newsfeed.slurp.net...
>
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/infoce...n/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.
Jul 14 '06 #5

P: n/a

Mark A wrote:
"Ian" <ia*****@mobileaudio.comwrote in message
news:44**********@newsfeed.slurp.net...

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/infoce...n/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.
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

Jul 15 '06 #6

P: n/a
"me******@yahoo.com" <ma**@gsicomp.on.cawrote in message >
Driver types are different than connection types.
Really?
Jul 15 '06 #7

P: n/a
Mark A wrote:
"me******@yahoo.com" <ma**@gsicomp.on.cawrote in message >
>Driver types are different than connection types.

Really?
Yes, of course. Both are completely different things.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Jul 17 '06 #8

P: n/a
"Knut Stolze" <st****@de.ibm.comwrote in message
news:e9**********@lc03.rz.uni-jena.de...
Yes, of course. Both are completely different things.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
You are a true genius!
Jul 17 '06 #9

P: n/a
Mark A wrote:
"Knut Stolze" <st****@de.ibm.comwrote in message
news:e9**********@lc03.rz.uni-jena.de...
>Yes, of course. Both are completely different things.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

You are a true genius!
*rofl*

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jul 17 '06 #10

This discussion thread is closed

Replies have been disabled for this discussion.