commit and terminate

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

fyi85@hotmail.com - 20 Dec 2005 15:09 GMT

I have 8.1.5 on Windows 2003, when I do from CLP with auto commit off:
db2 update table set column=something
and then
db2 terminate
and then
db2 connect to db
db2 select updated column from table
the update is committed anyways, whereas the docs state that the update
is supposed to be rolled back (auto commit is off)
Does this indicate a bug or a mistake in the docs?
If it is not a mistake in the docs, what is the point of having a
switch for auto commit if the terminate command over-rides the setting?

Reply to this Message

fyi85@hotmail.com - 21 Dec 2005 15:31 GMT

I guess the documentation at this link must be wrong? Who knows?
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.
udb.doc/ad/c0004859.htm

Reply to this Message

Brian Tkatch - 21 Dec 2005 16:50 GMT

That documnetation is talking about the gerneric termination of a
transaction.

The command you used is TERMINATE, which is a very specific command,
detailed here:
<URL:http://publib.boulder.ibm.com/infocenter/db2luw/v8/topic/com.ibm.db2.udb.doc/cor
e/r0001973.htm>

The usage note states: If an application is connected to a database, or
a process is in the middle of a unit of work, TERMINATE causes the
database connection to be lost. An internal commit is then performed.

B.

Reply to this Message

fyi85@hotmail.com - 21 Dec 2005 21:24 GMT

I dont mean to debate the issue, but could you explain what the
difference would be between 'generic termination' and the use of the
'terminate' command. If I understand correctly, (which obvioulsy I do
not lol), if I code an application and do an update as the last
transaction before coding 'terminate', and I do not give the commit or
rollback command before coding the terminate command, then presumably
that last update will be lost on Windows platform.
If I do the same thing from CLP, the update is committed because of the
internal commit.
Seems confusing to me and without much logic, especially considering
that the CLP has the command option to over ride the auto commit
setting.
So my question remains, why give the option to over ride the commit
behaviour if the behaviour is hard coded by default in the terminate
command?

Reply to this Message

Bernd Hohmann - 21 Dec 2005 21:34 GMT

> transaction before coding 'terminate', and I do not give the commit or
> rollback command before coding the terminate command, then presumably
[quoted text clipped - 4 lines]
> that the CLP has the command option to over ride the auto commit
> setting.

You have to seperate "application terminates (ends) connection to
database" from "issue TERMINATE command in CLP".

Different topic, different task.

Bernd

Signature

"Ja, alles meine Herren" sprach Fürst Lichnowsky. "Ooch det roochen?"
"Ja, auch das Rauchen." "Ooch im Tiergarten?" "Ja, auch im Tiergarten
darf geraucht werden, meine Herren." Und so endeten die Barrikadenkämpfe
des 18. März in Berlin

Reply to this Message

Brian Tkatch - 22 Dec 2005 15:43 GMT

The AUTOCOMMIT option treats any subsequent SQL statements as the last
statement in the current (or new) transaction. If it is successful, it
and every statement since the start of the last transaction are
COMMITted. If it fails, it and every statement since the start of the
last transaction are ROLLBACKed (should that be ROLLedBACK?). Note,
therefore, that AUTOCOMMIT can issue a ROLLBACK too. It does not mean
that each statement is COMMITed.

Ultimately, it is good practice to explicitly end transactions with
either COMMIT or ROLLBACK. Note though, CONNECT RESET is a SQL
statement (not a CLP command) that issues an implicit COMMIT.

TERMINATE is a CLP command (not a SQL statement) to close the backend
process. If there is an open transaction, it will end, the question is
how. Is the default a ROLLBACK or a COMMIT? As it just so happens,
TERMINATE does it with COMMIT.

B.

Reply to this Message

fyi85@hotmail.com - 23 Dec 2005 02:50 GMT

> The AUTOCOMMIT option treats any subsequent SQL statements as the last
> statement in the current (or new) transaction. If it is successful, it
[quoted text clipped - 14 lines]
>
> B.

Yes, that is a very good distinction, I mean terminate being a CLP
command executable and not an sql statement. So that does make sense to
me, as does your suggestion that each and every transaction be
explicitly committed or rolled back. I also get your point about
connect reset, and also thank you for pointing out that autocommit can
also induce a rollback if the statement fails.
Thanks for the help and the explanation

Reply to this Message

Brian Tkatch - 23 Dec 2005 17:39 GMT

You're most welcome.

B.

Reply to this Message