clp and auto-commit

[martynh]

What am I missing here?

Take a simple table defined as:
create table tab1 (col1 integer not null unique);

Then from the db2 command line processor I run the following commands

-- set auto commit off
update command options using c off;
-- check the command options
list command options;
-- insert a row
insert into tab1 values(1);
-- insert a duplicate row
insert into tab1 values(1);

Now, because there is a unique constraint on col1 we get an error on the second insert. This causes an implicit rollback.

Since I set auto-commit off, I would have expected my table to end up empty. However, I get one row. It is as if the first row is unaffected by the implicit rollback. This isn't the effect I want, I want the implicit rollback to remove the rows added if there is an error.

Thanks in advance,

Martyn Hodgson

 

[rbod]

The "update command options using c off" works only if you put it in a script and run the script.
From command line use the "+c" option to turn autocommit off.
Also, since both inserts are in your UOW, you will still see one row[after the 2nd insert] since it is in the buffer[other users will not see it though]. You need to do an explicit rollback.
db2 +c insert into tab1 values(1)
db2 +c insert into tab1 values(1)
db2 rollback work
db2 select * from tab1 -- empty table