DB2 beginner question regarding commit/rollback 
Author Message
 DB2 beginner question regarding commit/rollback

Hi

Maybe I'm misunderstanding something here, but here goes...

(I'm working from the Command line processor). If I enter the following:

   select * from mytable

-> 7 rows selected

   insert into mytable values(...)

   rollback

   select * from mytable

-> 8 rows selected (?)

Shouldn't the insert be removed when the rollback is performed? Is there any
way to turn off this autocommit otherwise what's the point?

regards
Lee Francis

--
In theory, there is no difference between
theory and practice. But, in practice, there is.
-- Jan L.A. van de Snepscheut



Tue, 10 Aug 2004 20:55:11 GMT
 DB2 beginner question regarding commit/rollback

Hi,

Yes, by default autocommit is on, so in your case insert was commited before
rollback. See the CLP option to turn autocommit off (+c).

Best regards, Dmitry
--

http://www.ispirer.com/products - Database migration utility for IBM DB2,
Oracle, MS SQL Server, Sybase and MySQL.



Quote:
> Hi

> Maybe I'm misunderstanding something here, but here goes...

> (I'm working from the Command line processor). If I enter the following:

>    select * from mytable

> -> 7 rows selected

>    insert into mytable values(...)

>    rollback

>    select * from mytable

> -> 8 rows selected (?)

> Shouldn't the insert be removed when the rollback is performed? Is there
any
> way to turn off this autocommit otherwise what's the point?

> regards
> Lee Francis

> --
> In theory, there is no difference between
> theory and practice. But, in practice, there is.
> -- Jan L.A. van de Snepscheut



Tue, 10 Aug 2004 20:58:15 GMT
 DB2 beginner question regarding commit/rollback
Thanks for your help! I got it working....

Quote:
> Yes, by default autocommit is on, so in your case insert was commited
before
> rollback. See the CLP option to turn autocommit off (+c).

From what I understand you do this by entering "db2 +c" at the command
prompt (Strange that the '+' character is used for a program argument
instead of the "standard" characters '-' or '/').

Is there any way this can be set to apply for all users on all databases as
default (a server setting maybe?)

regards
Lee Francis



Tue, 10 Aug 2004 22:07:35 GMT
 DB2 beginner question regarding commit/rollback
You can use teh follwing environment variable (as described in the Command
Ref).

DB2OPTIONS=+c

DB2 uses + to switch option on, - to switch them off...
(Yes I know for auto commit it's weird.....)

Cheers
Serge
--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Canada



Wed, 11 Aug 2004 00:13:58 GMT
 DB2 beginner question regarding commit/rollback
You can't set it at the server to apply to clients.  This is a client
setting.
I'd recommend you investigate seriously the DB" COMMAND REFERENCE manual
that should be online with your install.
This will discuss Command options and how to set them  for one time, the
session or permanent.
HTH,  Pierre.
Quote:

> Thanks for your help! I got it working....

>>Yes, by default autocommit is on, so in your case insert was commited

> before

>>rollback. See the CLP option to turn autocommit off (+c).

> From what I understand you do this by entering "db2 +c" at the command
> prompt (Strange that the '+' character is used for a program argument
> instead of the "standard" characters '-' or '/').

> Is there any way this can be set to apply for all users on all databases as
> default (a server setting maybe?)

> regards
> Lee Francis



Wed, 11 Aug 2004 01:59:06 GMT
 DB2 beginner question regarding commit/rollback
Well, there is a DB2SET variable called DB2OPTIONS.
"Sets command line processor options."

I just wonder what's it for. (??? hehe)
Maybe you can set the command line options for
a user,
a node,
an instance or
global level?

I think that could do the job but i never tried it, so...

PM



Quote:
> You can't set it at the server to apply to clients.  This is a client
> setting.
> I'd recommend you investigate seriously the DB" COMMAND REFERENCE manual
> that should be online with your install.
> This will discuss Command options and how to set them  for one time, the
> session or permanent.
> HTH,  Pierre.


> > Thanks for your help! I got it working....

> >>Yes, by default autocommit is on, so in your case insert was commited

> > before

> >>rollback. See the CLP option to turn autocommit off (+c).

> > From what I understand you do this by entering "db2 +c" at the command
> > prompt (Strange that the '+' character is used for a program argument
> > instead of the "standard" characters '-' or '/').

> > Is there any way this can be set to apply for all users on all databases
as
> > default (a server setting maybe?)

> > regards
> > Lee Francis



Thu, 12 Aug 2004 14:00:13 GMT
 DB2 beginner question regarding commit/rollback

Quote:
> DB2 uses + to switch option on, - to switch them off...
> (Yes I know for auto commit it's weird.....)

According to the program options (db2 ? options) you can also use the
following somewhat strange syntax:

db2 -c-

to do the same thing. This seems like the way it's meant to be done since
'+' isn't mentioned in the help options...

Anyway thanks for the help

regards
Lee Francis



Wed, 11 Aug 2004 20:42:04 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. DB2 beginner quiestion regarding ';'

2. Beginners questions regarding ADO 2.0

3. Beginner's questions regarding Delphi

4. Another newbie different begin,rollback,commit transaction question

5. newbie begin,commit, rollback transaction question

6. Questions about rollback and commit

7. commit and rollback questions

8. rollback/commit question

9. commit and rollback questions

10. DB2 Large roll back / commit question

11. beginners DB2 question

12. To (IBM) DB2 gurus: Using DB2 for logging / tracing (ROLLBACK issue)


 
Powered by phpBB® Forum Software