about auto-commit - DB2 Database

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?

Nov 12 '05 #1

Subscribe Reply

7470

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?

Nov 12 '05 #2

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?

Nov 12 '05 #3

Similar topics

2716

Database Auto Count what about delete?

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 insert a row and give it the first available number? For example ID 1, 2, 4, 5, 6

PHP

4130

How about this goop?

by: George Hester | last post by:

Any ideas how to decipher it? <script language = "JavaScript"> var x =...

Javascript

13958

Problem with the width of DIV in IE6 using CSS and width:auto

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 page the way I want. Does anybody know a solution for this? First of all, the code I am using: CSS ------- body {

HTML / CSS

3347

Questions about Access from a .NET/C++ developer

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 speed. I like books with practical exercises, and also with test questions (like cert books) *2*

Microsoft Access / VBA

5072

What to do with AUTO-ID column when I add a new record to the data

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 do with the 1st column ? (Below I have a "1" in place for now). Also, Does the datase.AcceptChanges(); updates the changes to the database? Which command do I use to update the changes in dataset back to the Access database table? Thanks, Alpha...

C# / C Sharp

5044

Localization: user selectable UICulture w/ UICulture = auto

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 CurrentUICulture to what's specified in the querystring. Since I want to allow UICulture auto detecting, I add UICulture = "auto" to page directive on each page.

ASP.NET

2317

Please enlighten me about PyPy

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 PyPy project aims at producing a flexible and fast Python

Python

4051

About automatic database maintenance

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 I don´t see any activity regarding auto runstats. Also, the field STATS_TIME from syscat.tables shows that all my table statistics were last collected in March. What am I missing here ? Is

DB2 Database

2483

Question about Generics

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>> _queries; where DLinqQuery is a generic class that takes a type parameter

C# / C Sharp

3278

'auto' value question..

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 I change 'auto' to 0 for left and right margin values; I have to leave those at 'auto'.. so I would like to know what exactly means 'auto' -- what value it represents exactly (and does it apply for all elements/values you might apply 'auto' to?)

HTML / CSS

9621

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

10264

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

10106

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

10039

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

8937

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

6716

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5484

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

3610

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

2851

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General