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?