DB2 Command Line Processor (CLP) - How to turn autocommit off within CLP?

[no_spam_for_gman]

Hi,

I can turn off autocommit within the command window but I cannot figure
out how to turn it off when you are in the IBM DB2 Command Line
Processor. Does anybody knows how? By the way I mean when you are
logged on and the prompt is: db2 =>
Here's an example how I do it from Command Window (option is c):

C:\Program Files\IBM\SQLLIB\BIN>db2 +c "delete from test.table1 where
field1=
1"
DB20000I The SQL command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2 rollback
DB20000I The SQL command completed successfully.

ROLLBACK WORKS

[Knut Stolze]

no_spam_for_gman wrote:

>
Hi,

I can turn off autocommit within the command window but I cannot figure
out how to turn it off when you are in the IBM DB2 Command Line
Processor. Does anybody knows how? By the way I mean when you are
logged on and the prompt is: db2 =>
Here's an example how I do it from Command Window (option is c):

C:\Program Files\IBM\SQLLIB\BIN>db2 +c "delete from test.table1 where
field1=
1"
DB20000I The SQL command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2 rollback
DB20000I The SQL command completed successfully.

Use the "UPDATE COMMAND OPTIONS" command. See:

db2 "? update command options"

UPDATE COMMAND OPTIONS USING {options ...}

options:
a {ON|OFF} Display SQLCA
c {ON|OFF} Auto-commit
e {ON {C|S} | OFF} Display SQLCODE/SQLSTATE
l {ON filename | OFF} Log commands in history file
n {ON|OFF} Remove new line character
o {ON|OFF} Display output
p {ON|OFF} Display db2 interactive prompt
r {ON filename | OFF} Save output report to file
s {ON|OFF} Stop execution on command error
v {ON|OFF} Echo current command
w {ON|OFF} Display FETCH/SELECT warning messages
z {ON filename | OFF} Save all output to file

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[no_spam_for_gman]

Is there a way to turn it off globally? Everytime I log in CLP it
would be off by default as oppose to me turning the option off each
time?

[Brian Tkatch]

no_spam_for_gman wrote:

Is there a way to turn it off globally? Everytime I log in CLP it
would be off by default as oppose to me turning the option off each
time?

You could use the DB2OPTIONS global variable.

You may want to read The "Command Reference". Both the options, and how
to set them are explain therein.

B.

[Gregor Kova�]

no_spam_for_gman wrote:

>
Hi,

I can turn off autocommit within the command window but I cannot figure
out how to turn it off when you are in the IBM DB2 Command Line
Processor. Does anybody knows how? By the way I mean when you are
logged on and the prompt is: db2 =>
Here's an example how I do it from Command Window (option is c):

C:\Program Files\IBM\SQLLIB\BIN>db2 +c "delete from test.table1 where
field1=
1"
DB20000I The SQL command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2 rollback
DB20000I The SQL command completed successfully.

ROLLBACK WORKS

Oh, well. In my opinion DB2 CLP is not good, to say the least. At least on
Linux it isn't. I rather use my bash shell to wrtie SQL commands. That way
I have all the goodies that my bash provides like searching, ... :))

Just my opinion.

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-