Recently, I have been working on a .NET application that uses DB2 as a database engine. On top of that, I needed transactions as I didn’t want for SQL statements to commit if one of them failed. Needless to say, DB2 uses it’s own principle to handling transactions.

Basic principle

Those of you, working with DB2 on a daily basis can easily skip this chapter. After some research, I have discovered that DB2, rather than bothering users with writing

BEGIN TRANSACTION

etc., is natively transactional. This might not be correct wording, but in DB2 everything is a transaction. Even a simple

SELECT * FROM myschema.mytable

statement. The trick in all this is, that server is pre-set to commit each and every statement at execution time.

ODBC connection

If you are using ODBC driver to connect to DB2 database, there are three ways to go about this.

One, you can nag your db admin to change AUTOCOMMIT variable settings to

AUTOCOMMIT=OFF

in DB2 server configuration, which is usually not a good option, if a server is already in production.

Second option is to set the AUTOCOMMIT variable settings to

AUTOCOMMIT=OFF

in db2cli.ini on your computer. This is an option only if you are creating a web application as otherwise, each and every computer will need to have this fixed in their .ini file.

Third option is easy and trivial, but quite difficult to come by. Simply add

AUTOCOMMIT=OFF

to connection string in your application and you are done.

Example:

dsn=mydb2;uid=mydb2user;pwd=mydb2pwd;autocommit=OFF

IBM DB2 data provider for .NET

There, however, is a good news. You can easily forget everything written, if you are using IBM DB2 data provider for .NET. Now, IBM encourages people to use this and discourages the use of ODBC, but, if you are running old version of DB2 (pre 9.x), .NET provider can act “funny” at times.

I did everything and it still doesn’t work?

No matter if you are using ODBC driver or .NET provider, there is one thing you have to keep in mind. No matter if AUTOCOMMIT is set to OFF, all statements will auto commit on connection close. You will have to use either OdbcTransaction or DB2Transaction class and their methods Commit and Rollback to manually commit or perform a rollback of all statements.

Simple example (without exception handling) in C#:

DB2Connection conn = null;
conn = new DB2Connection( connstring );
conn.Open();

DB2Transaction SQLtrans = null;
SQLtrans = conn.BeginTransaction();

DB2Command SQLcomm = new DB2Command();
SQLcomm.Connection = conn;
SQLcomm.Transaction = SQLtrans;

try {
    SQLcomm.CommandText = transactionQuery;
    SQLcomm.ExecuteNonQuery();
    SQLtrans.Commit();
}
catch (Exception ex) {
    SQLtrans.Rollback();
}

SQLcomm.Dispose();
SQLtrans.Dispose();
conn.Close();
conn.Dispose();