A few years ago, I worked as a consultant at a company that used DB2/400 as its main database platform.  The company did not have journaling ‘turned on’ so their database platform did not support transactions/commit control.  This did seem odd to me, but what I’ve found is that its pretty common that DB2/400 shops don’t use this feature. While this seemed like a mere oddity and an inconvenience for commit control, it actually caused a more measurable issue which was that we couldn’t use Hibernate, one of, if not the most common ORM framework. Hibernate requires transactions. This is a problem for anyone wanting to use a non-journaled DB2/400 instance… in particular me.

After a few hours of discovering this issue and looking into it, I found a simple work around, which was to set the hibernate property ‘hibernate.connection.isolation’ to 0 using the following XML in the config file:

<property name="hibernate.connection.isolation">0</property>

The problem for me arose when I needed to write applications using a shared connection coming from a WebSphere DataSource. I couldn’t find a way to tell the WebSphere DataSource to use the transaction isolation level of ‘0′ or ‘NONE’. There were a couple of things I found that seemed to indicate a way, but none of them worked. I had to suck it up and just write SQL for simple CRUD operations.

Since I took a job at that company, it has affected me in a more cumulative way. Most of the java apps we write are for WebSphere AS or WebSphere Portal, both of which can and should use WebSphere datasources. I knew that if I didn’t solve this problem, the team would spend a whole lot of time writing CRUD SQL and incur the expense of the time writing the SQL, the expense of fixing the errors introduced writing the SQL, and not be able to take advantage of the simplicity provided by using an ORM framework like Hibernate. So on my latest project I made it a priority to solve this problem. I first started with talking with some folks about getting journaling enabled on the DB2/400 files I was working with. After a few minutes of this conversation I decided it’d be better to make use of the ‘open’ part of the ‘open source’ code base of hibernate. After a few hours of internet research of this issue and looking at some of the Hibernate source I figured if I could just set the ‘autoCommit’ property of the javax.sql.Connection to true and set the transactionIsolation level to ‘NONE’ (or 0 if you’re into literal values) then I’d be set. I tested this by hacking into the actual connection object Hibernate was using right before my SQL statement executions. It worked so I was happy, but I didnt want us to have to do this ‘HACK’ everytime we wanted to use Hibernate with a DB2/400 data source. So I started looking at other ways. I found one.

The default WebSphere DataSource Helper for the iSeries Toolbox is the class com.winwholesale.db2400.hibernate.DB2AS400DataStoreHelper. After looking around enough I found that there were a few methods on the ‘helper’ that could allow me to solve our problem. First was the method that showed that the helper was actually the class that indicated the transaction isolation level, called (interesting enough) getIsolationLevel. I created a new DataSource Helper class that extends the normal AS400 one and returned: javax.sql.Connection.TRANSACTION_NONE

This accomplished half of what I needed, but I still had to deal with setting the autoCommit property to true. The helper made it simple to set this. It has a method called doConnectionSetup that allows you to do whatever you want to the connection before it’s used. So I added my autoCommit assignment there. Here is the total code of our new helper class:


import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.resource.ResourceException;
import com.ibm.websphere.appprofile.accessintent.AccessIntent;
import com.ibm.websphere.rsadapter.DB2AS400DataStoreHelper;

public class DB2400HibernateDSHelper extends DB2AS400DataStoreHelper {
public DB2400HibernateDSHelper( Properties props){
super(props);
}
public void doConnectionSetup(Connection connection) throws SQLException {
super.doConnectionSetup(connection);
connection.setAutoCommit( true );
}
public int getIsolationLevel(AccessIntent arg0) throws ResourceException {
return Connection.TRANSACTION_NONE;
}
}

As you can see, it is very simple.

Now, understand that this works for us based on the fact that the DataSource connections are not going to be able to used for transactions in our environment on any files. If you have an environment that uses journaling on some files but not others this would probably be a problem for you since we’re setting these properties on a connection level. So be sure to research the issue before you use this simple approach. If you find a better way, please let me know.

If this helps one person then it was worth it…. wait, it helped me. It was worth it.