| Author |
Message |
Vincent M
*nix forums Guru
Joined: 22 Feb 2005
Posts: 1332
|
 Posted: Thu Jul 13, 2006 3:16 pm Post subject:
COMMIT statements in a stored procedure
|
|
|
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? |
|
| Back to top |
|
 |
Serge Rielau
*nix forums Guru
Joined: 29 Apr 2005
Posts: 1583
|
| Posted: Fri Jul 14, 2006 3:16 pm Post subject:
Re: COMMIT statements in a stored procedure
|
|
|
Mark A wrote:
| Quote: | 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/ondemandbusiness/conf2006/ |
|
| Back to top |
|
|
Vincent M
*nix forums Guru
Joined: 22 Feb 2005
Posts: 1332
|
| Posted: Fri Jul 14, 2006 3:45 pm Post subject:
Re: COMMIT statements in a stored procedure
|
|
|
"Serge Rielau" <srielau@ca.ibm.com> wrote in message
news:4hpqp2FntssU2@individual.net...
| Quote: | 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. |
|
| Back to top |
|
|
Ian
*nix forums Guru Wannabe
Joined: 02 Aug 2005
Posts: 262
|
| Posted: Fri Jul 14, 2006 7:57 pm Post subject:
Re: COMMIT statements in a stored procedure
|
|
|
Mark A wrote:
| Quote: | "Serge Rielau" <srielau@ca.ibm.com> wrote in message
news:4hpqp2FntssU2@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/infocenter/db2luw/v8/topic/com.ibm.db2.udb.doc/admin/r0000908.htm |
|
| Back to top |
|
|
Vincent M
*nix forums Guru
Joined: 22 Feb 2005
Posts: 1332
|
| Posted: Fri Jul 14, 2006 10:05 pm Post subject:
Re: COMMIT statements in a stored procedure
|
|
|
"Ian" <ianbjor@mobileaudio.com> wrote in message
news:44b7f649$1_4@newsfeed.slurp.net...
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. |
|
| Back to top |
|
|
Matt Emmerton
*nix forums addict
Joined: 21 Oct 2004
Posts: 76
|
| Posted: Sat Jul 15, 2006 3:32 am Post subject:
Re: COMMIT statements in a stored procedure
|
|
|
Mark A wrote:
| Quote: | "Ian" <ianbjor@mobileaudio.com> wrote in message
news:44b7f649$1_4@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/infocenter/db2luw/v8/topic/com.ibm.db2.udb.doc/admin/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 |
|
| Back to top |
|
|
Vincent M
*nix forums Guru
Joined: 22 Feb 2005
Posts: 1332
|
| Posted: Sat Jul 15, 2006 4:01 am Post subject:
Re: COMMIT statements in a stored procedure
|
|
|
"memmerto@yahoo.com" <matt@gsicomp.on.ca> wrote in message >
| Quote: | Driver types are different than connection types.
|
Really? |
|
| Back to top |
|
|
Knut Stolze
*nix forums Guru
Joined: 28 Jul 2005
Posts: 755
|
| Posted: Mon Jul 17, 2006 7:16 am Post subject:
Re: COMMIT statements in a stored procedure
|
|
|
Mark A wrote:
| Quote: | "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.
--
Knut Stolze
DB2 Information Integration Development
IBM Germany |
|
| Back to top |
|
|
Vincent M
*nix forums Guru
Joined: 22 Feb 2005
Posts: 1332
|
| Posted: Mon Jul 17, 2006 8:32 am Post subject:
Re: COMMIT statements in a stored procedure
|
|
|
"Knut Stolze" <stolze@de.ibm.com> wrote in message
news:e9fdg7$7e7$2@lc03.rz.uni-jena.de...
| Quote: | Yes, of course. Both are completely different things.
--
Knut Stolze
DB2 Information Integration Development
IBM Germany
|
You are a true genius! |
|
| Back to top |
|
|
Serge Rielau
*nix forums Guru
Joined: 29 Apr 2005
Posts: 1583
|
| Posted: Mon Jul 17, 2006 12:05 pm Post subject:
Re: COMMIT statements in a stored procedure
|
|
|
Mark A wrote:
| Quote: | "Knut Stolze" <stolze@de.ibm.com> wrote in message
news:e9fdg7$7e7$2@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/ondemandbusiness/conf2006/ |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
IBM DB2
