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.
mikewitters.com 
I arrived at the same solution and found this post while testing it. I am getting classpath errors as the cusom helper is inside my EAR. I know that I can solve it by placing it ext dir, or by putting it in classpath but I am just wondering what you did…
I simply made a jar with my helper and added it to the classpath of the application server instances that needed access to it.
Thanks Mike, it certainly helped me as I am in the same situation you were in before.
I am a newbie to Hibernate so please excuse my naievity; one thing I couldn’t understand is why you felt you need to override the default behavior ( extending DB2AS400DataStoreHelper) in order to set the isolation level and auto-commit properties when you could’ve achieved this – I think!- by having the following XML in the config file:
true
0
Wouldn’t that achieve the same? What I am missing here?
Thanks again..
-Dan
Your XML tags didn’t show up, so I’m not sure exactly what tags you are asking about. Setting the isolation level and auto-commit in the hibernate properties files don’t affect what settings you have when using an application server datasource. The datasource is configured in WebSphere and it wouldn’t get along with Hibernate from the perspective of agreeing on how transactions should ( or shouldn’t in my case) be handled.
I thought it was odd, too, but had many conversations on the mailing lists and forums and found that it just wouldn’t work ‘out of the box’.
There might be a much more simple way now.
Oh sorry, maybe the XMl snippet was parsed by the renderer.
I meant these two XMl entries:
0
true
I see your point now, it is DS configuration as set by WebSphere, not Hibernate.
I enclosed it by <h;code> tags!
..anyway, properies like these:
hibernate.connection.isolation=0
connection.autocommit=true