Hi,

I am novice in DB2 database, I use commit whenever I do DML operation in Oracle to ensure that My transaction is permanent. But I see strange in DB2, Eventhough i don't commit explicitly, my transactions are permanent.

Kindly tell me what is the difference b/w SQL statement with commit and with out commit in DB2.

And also please tell, is there any problem if I don't commit explicitly my SQL statments...

Thanks,
Shahnaz.

Nothing is strange... its just that you are new to the vast DB2 world

which tool are you using for connecting to server?
Auto commit is enabled over it

For command line , you can chk it using
db2 LIST COMMAND OPTIONS

...... and disable if you dont require same

if you are running sqls on db2 command line.. specify "+c" while running SQL..


db2 +c "insert into t1 values(123)"

Thanks for the information.

autocommit, whether this is user specific or database specific.

if the autocommit is on then whatever the transaction we do then all will be permanent, please correct me if I am wrong.

Thanks,
Shahnaz.

Autocommit is an application setting, it has nothing to do with DB2. In most cases it is connection-specific.

When autocommit in CLP is on, a commit is issued by the application after each successful SQL statement. The same is true for JDBC applications, except the case of executeBatch().

Auto-commit is a property of the client, not the database server. Auto-commit is the default for the command line processor, and is a property that can be set in other interfaces such as java.

The command line processor options are defined in the Command Reference manual, and as mentioned above you can temporarily turn auto-commit off with the +c option. You could permanently turn it off (for your client machine) by changing your client configuration.

For real application programs with multiple SQL within a logical transaction, auto-commit is rarely used.

thanks all for your responses...

My DB client setting or whatever it is, is configured to "Auto commit <ON>". Is it better to user SQL statement with out commit?

Any consequenses?

Thanks,
Shahnaz.

Usually, when people submit scripts from a file or the command line to change a schema or update some data, auto-commit is preferred.

Usually when an application program is running, there are multiple SQL statements within one logical transaction, and they must all be successful or they must all backout (rollback), so auto-commit is normally not used in these situations and the application controls when the commit SQL command is issued.

But there are sometimes exceptions.

Thanks Marcus for the explanantion.

Just to clear my understanding, I have a SQL file where I have 3 to 4 SQL DML statements. Do you mean this as one logical transaction and I should use commit after all the SQL statments?

Thanks,
Shahnaz.

Normally when submitting DML via a file or script, auto-commit is used. However, if you suspect there is a possbility of one of the statements failing, and in that situation you want the other statements already run to ROLLBACK, then you will have to turn auto-commit off and do your own commit at the end.

Thanks so much for all of your explanations.