about auto-commit

[janet]

HI,

I had a question on auto-commit in DB2 EEE V8 on AIX V5.

Here is my example..

there are two script A.sh , B.SQL

A.sh is following:
db2 +c -tvf B.SQL

B.SQL is following:

create index test_ind on test (a asc);
commit;
drop index test_ind;
I run the A.sh in following way in DB2 EEE V7 and V8
../A.sh

I got different result for V7 and V8

V7:

the index test_ind was dropped after run the scripts.

V8:

the index is still there after run the scripts..
Any idea about it?

[Philip Sherman]

I can't tell you for sure why it deleted the index on V7 but the V8
manual (Command reference) states that the "+c" option turns off auto
commit. With auto commit off; the delete was never committed and the
index retains some existence because the task deleting it could issue a
rollback and restore the index.

On my 8.1 Linux system with FP7 installed; the index is not accessible
with SQL after the DROP is executed. If the index is queried from the
same task that drops it; the index is not found. If queried from another
task; then the query waits until a commit/rollback has been executed.

I ran the sequence of commands from a file, as you did, and obtained the
exact same results as manually issuing the commands from a terminal session.

When I attempt to run this from an executable script (a.sh); it fails
because there is no connection to the database. This is expected because
executing a script establishes a new task which does not have a
connection to the database. Adding a "db2 connect to ......." statement
to the script made it work and LEFT THE INDEX WHEN IT TERMINATED!!!

A bit more checking showed that the db2 connection to a.sh still existed
after a.sh terminated. The connection disappeared a couple of seconds
later. I modified a.sh, adding a "db2 terminate" to close the connection
before the task terminated. Now, the index is gone when a.sh completes.

Conclusion:
When the task created by executing a.sh terminated without closing the
connection to the database; UDB interpreted this as an application crash
and rolled back the uncommitted work. I was able to duplicate this from
a terminal session by issuing the same commands and forcing the
operating system to close the terminal session before a COMMIT was
issued. V7 may have interpreted the task close as a normal termination
and issued a COMMIT instead of a ROLLBACK.

You can fix this by adding a 'db2 commit' command to the a.sh script or
adding the commit to the statements in B.SQL.

Philip Sherman
janet wrote:

HI,

I had a question on auto-commit in DB2 EEE V8 on AIX V5.

Here is my example..

there are two script A.sh , B.SQL

A.sh is following:
db2 +c -tvf B.SQL

B.SQL is following:

create index test_ind on test (a asc);
commit;
drop index test_ind;
I run the A.sh in following way in DB2 EEE V7 and V8
./A.sh

I got different result for V7 and V8

V7:

the index test_ind was dropped after run the scripts.

V8:

the index is still there after run the scripts..
Any idea about it?

[esthershensh]

HI, Philip

Thanks for your reply..It posted couple days without reply, I think
maybe nobody interestd.

Yes, I would add commit to the end of scripts to make it finished..

I posted this question just because I don't understand how DB2 handle
this condition...

I'm not completely agree with what you said following:

When the task created by executing a.sh terminated without closing the

connection to the database; UDB interpreted this as an application crash and rolled back the uncommitted work.

I did the test , run the same scripts from db2 command line like
following:

db2 +c -tvf B.sh

I found the index was dropped..
what's the difference? Actually, I just try to figure out how DB2
handle commit or roll-back in those kind of condition...
Any suggestion is welcomed.


Philip Sherman wrote: I can't tell you for sure why it deleted the index on V7 but the V8
manual (Command reference) states that the "+c" option turns off auto commit. With auto commit off; the delete was never committed and the
index retains some existence because the task deleting it could issue a rollback and restore the index.

On my 8.1 Linux system with FP7 installed; the index is not accessible with SQL after the DROP is executed. If the index is queried from the same task that drops it; the index is not found. If queried from another task; then the query waits until a commit/rollback has been executed.

I ran the sequence of commands from a file, as you did, and obtained the exact same results as manually issuing the commands from a terminal session.
When I attempt to run this from an executable script (a.sh); it fails because there is no connection to the database. This is expected because executing a script establishes a new task which does not have a
connection to the database. Adding a "db2 connect to ......." statement to the script made it work and LEFT THE INDEX WHEN IT TERMINATED!!!

A bit more checking showed that the db2 connection to a.sh still existed after a.sh terminated. The connection disappeared a couple of seconds later. I modified a.sh, adding a "db2 terminate" to close the connection before the task terminated. Now, the index is gone when a.sh completes.
Conclusion:
When the task created by executing a.sh terminated without closing the connection to the database; UDB interpreted this as an application crash and rolled back the uncommitted work. I was able to duplicate this from a terminal session by issuing the same commands and forcing the
operating system to close the terminal session before a COMMIT was
issued. V7 may have interpreted the task close as a normal termination and issued a COMMIT instead of a ROLLBACK.

You can fix this by adding a 'db2 commit' command to the a.sh script or adding the commit to the statements in B.SQL.

Philip Sherman
janet wrote:

HI,

I had a question on auto-commit in DB2 EEE V8 on AIX V5.

Here is my example..

there are two script A.sh , B.SQL

A.sh is following:
db2 +c -tvf B.SQL

B.SQL is following:

create index test_ind on test (a asc);
commit;
drop index test_ind;
I run the A.sh in following way in DB2 EEE V7 and V8
./A.sh

I got different result for V7 and V8

V7:

the index test_ind was dropped after run the scripts.

V8:

the index is still there after run the scripts..
Any idea about it?