473,320 Members | 1,802 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

about auto-commit

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?
Nov 12 '05 #1
2 7434
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?


Nov 12 '05 #2
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?


Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Sims | last post by:
Hi, I have a table that give a unique ID to certain Articles. To give the ID i simply use the Auto count of the DB. But what happens when i delete a record? i am now missing an ID. How can i...
10
by: George Hester | last post by:
Any ideas how to decipher it? <script language = "JavaScript"> var x =...
1
by: Glabbeek | last post by:
I'm changing the layout of my site. Instead of using tables, I will use DIVs. It's working fine, except for 1 thing: In IE6 some DIVs are not the correct width. Mozilla and Opera are showing the...
20
by: Olav.NET | last post by:
I am a .NET/C++ developer who is supposed to do some work with Access. I do not know much about it except for the DB part. Questions: *1* I am looking for INTENSIVE books to get quickly up to...
6
by: Alpha | last post by:
I retrieve a table with only 2 columns. One is a auto-generated primary key column and the 2nd is a string. When I add a new row to the dataset to be updated back to the database. What should I...
5
by: Samuel | last post by:
Hi, I am running into a problem of mixing UICulture = auto and allowing users to select culture using a dropdown list. I am detecting a querystring, "setlang", and when found, setting the...
23
by: Ray | last post by:
Hello! I've been reading about PyPy, but there are some things that I don't understand about it. I hope I can get some enlightenment in this newsgroup :) First, the intro: <excerpt> "The...
1
by: Michel Esber | last post by:
Hello, Linux RedHat AS4 running DB2 V8 FP11. I have followed the docs at http://tinyurl.com/qckrn and enabled automatic statistics collection. It has been 2 days since I updated my DB cfg and...
11
by: hammad.awan_nospam | last post by:
Hello, I'm wondering if it's possible to do the following with Generics: Let's say I have a generic member variable as part of a generic class like this: List<DLinqQuery<TDataContext>>...
5
by: maya | last post by:
at work they decided to center divs thus: body {text-align:center} #content {width: 612px; text-align:left; margin: 0 auto 0 auto; } this works fine in IE & FF, EXCEPT in FF it doesn't work if...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.