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

Putting Auto Commit Off on the Server

P: n/a
Hi team i am using DB2 7.2 on Windows2000.
We have lot many SQL Procedure Calls, is there any way i can Turn off the
Auto Commit on that Database, the Problem i have is many of our Application
Dont Use Auto Commit = false, and if we have any Error in the SQL procedure
say after Executing 10 Update and Insert Statements the DB2 is commiting
all the Previous Statements, even though there was an Error, how can i
prevent this from the Database side, I know from Application side i can set
Auto Commit = False in the Programing but is there any this i can do on the
Server,
And also What are the side effects in doing So

Thanks
Ugra

--
Message posted via http://www.dbmonster.com
Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Ugrasena via DBMonster.com wrote:
Hi team i am using DB2 7.2 on Windows2000.
We have lot many SQL Procedure Calls, is there any way i can Turn off the
Auto Commit on that Database, the Problem i have is many of our Application
Dont Use Auto Commit = false, and if we have any Error in the SQL procedure
say after Executing 10 Update and Insert Statements the DB2 is commiting
all the Previous Statements, even though there was an Error, how can i
prevent this from the Database side, I know from Application side i can set
Auto Commit = False in the Programing but is there any this i can do on the
Server,
And also What are the side effects in doing So

Thanks
Ugra

I'm taking a guess here and assume that you are coming from a Sybase or
MS SQL Server background....

The DB2 server has no notion of auto-commit.
Any auto-commit you may be observing is driven by the client (e.g. CLP).

Do you do COMMIT inside of the stored procedure?
DB2 distinguishes between save points and transactions.
You can use nested savepoints (in DB2 V8.2) to roll back to arbitrary
points in the execution across "inner savepoints").
Transactions however are global. If you commit or rollback inside of a
procedure, that's it. There is NO nesting.

Hope that helps.
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2

P: n/a
Thanks for your reply.

--
Message posted via http://www.dbmonster.com
Nov 12 '05 #3

P: n/a
Hi there Can i Use Save points inside a SQLProcedure, need help in how to
rool back inside a SQLProcedure when an Exception Occurs, Once again we are
using 7.2 UDB on Win2000

Thanks
Ugra

--
Message posted via http://www.dbmonster.com
Nov 12 '05 #4

P: n/a
Ugrasena via DBMonster.com wrote:
Hi there Can i Use Save points inside a SQLProcedure, need help in how to
rool back inside a SQLProcedure when an Exception Occurs, Once again we are
using 7.2 UDB on Win2000

Thanks
Ugra

Presuming Savepoints are supported in V7.2 (I don't remember), then yes.
You can use them in an SQL Procedure. You won't be able to nest them
though. Nesting requires DB2 V8.2.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #5

P: n/a
Is there any thing like ON Error Rollback in SQL Procedures

Thanks
Ugra

--
Message posted via http://www.dbmonster.com
Nov 12 '05 #6

P: n/a
I have a SQL procedure call, which internally calls 3 other SQl Procedures,
now if i use SAVEPOINT can i let the Main procedure call to roll back to
that SavePoint, If possible what is the Statement i need to give I am
looking some thing like On error Roll back to Savepoint1 in the main
Procedure, please suggest me the best Option

Thanks
Ugra

--
Message posted via http://www.dbmonster.com
Nov 12 '05 #7

P: n/a
Ugrasena via DBMonster.com wrote:
Is there any thing like ON Error Rollback in SQL Procedures

Thanks
Ugra

Just define an exception handler and place a rollback inside.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.