commit and terminate

[fyi85@hotmail.com]

I have 8.1.5 on Windows 2003, when I do from CLP with auto commit off:
db2 update table set column=somethin g
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?

[fyi85@hotmail.com]

Re: commit and terminate

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

[Brian Tkatch]

Re: commit and terminate

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/core/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.

[fyi85@hotmail.com]

Re: commit and terminate

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?

[Bernd Hohmann]

Re: commit and terminate

fyi85@hotmail.c om wrote:
[color=blue]
> 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.[/color]

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

Different topic, different task.

Bernd

--
"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ämpf e
des 18. März in Berlin

[Brian Tkatch]

Re: commit and terminate

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.

[fyi85@hotmail.com]

Re: commit and terminate


Brian Tkatch wrote:[color=blue]
> 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.[/color]

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

[Brian Tkatch]

Re: commit and terminate

You're most welcome.

B.