[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3644?page=c... ] Ricardo Fernandes commented on HHH-3644: ---------------------------------------- Fortunately I've found a way around this issue by using Hibernate's Interceptors. The idea is to create a specific DB2 interceptor which transform the queries before they are sent to the database. The interceptor code goes like this: /** * A class for intercepting the calls to a DB2 database in order * to change the query string for including some special instructions * useful in DB2, according to the following rules:<br> * <ul> * <li>FOR READ ONLY - added to all select queries</li> * <li> WITH XX - where XX stands for one of the 4 different isolation * levels known by DB2: CS, RS, RR or UR * </li> * </ul> * @author Ricardo Fernandes * */ public class DB2Interceptor extends EmptyInterceptor { /** */ private static final long serialVersionUID = 1L; @Override public String onPrepareStatement(String str) { // Transform the original query into lower case String compstr = str.toLowerCase(); // Check if we're dealing with a simple select statement if (compstr.matches("^select.*") && !compstr.matches(".*for update.*")) { // We are, so add the 'for read only' clause if (!compstr.matches(".*for read only.*")) { str += " for read only"; } // Checks whether the 'client' asked for a specific isolation level // to be applied to this query TransactionManager manager = ThreadTools.getTransactionManager(); if (manager != null && manager.getIsolationLevel() != TransactionManager.ISOLATION_DEFAULT) { String suffix = null; String expr = null; switch(manager.getIsolationLevel()) { case TransactionManager.ISOLATION_READ_COMMITTED: // The same as CURSOR STABILITY (CS) in the DB2 world suffix = " with cs "; expr = ".*with cs.*"; break; case TransactionManager.ISOLATION_REPEATABLE_READ: // The same as READ STABILITY (RS) in the DB2 world suffix = " with rs "; expr = ".*with rs.*"; break; case TransactionManager.ISOLATION_SERIALIZABLE: // The same as REPEATABLE READ (RR) in the DB2 world suffix = " with rr "; expr = ".*with rr.*"; break; case TransactionManager.ISOLATION_UNCOMMITTED_READ: suffix = " with ur "; expr = ".*with ur.*"; break; default: // This is empty break; } // Check if we should apply the isolation level to the query if (suffix != null && !compstr.matches(expr)) { str += suffix; } } } return str; } } TransactionManager is a class used for storing state information for the current execution thread. In this case it holds the isolation level that should be applied to the current query. It also has a set of constants for defining the isolation level. ThreadTools is wrapper for a Hashtable associated with the current thread. HibernateSingleton is a singleton for storing application scope variables (such as the sessionFactory) A sample usage of these classes might be: public List sampleQuery(String classname) List list = null; try { ThreadTools.getTransactionManager().setIsolationLevel(TransactionManager.ISOLATION_UNCOMMITTED_READ); Criteria criteria = HibernateSingleton.instance().getSessionFactory().getCurrentSession().createCriteria(classname); list = criteria.list(); } catch (Exception e) { log.error("Error executing query for " + classname, e); throw new DatabaseException("Error executing query for " + classname, e); } finally { // Reset the isolation level ThreadTools.getTransactionManager().resetIsolationLevel(); } return list; } The interceptor might be associated with the hibernate's session factory on startup. Something like this: ... // Configure the session factory Configuration configuration = new Configuration().configure("hibernate.cfg.xml"); configuration.setProperty("hibernate.transaction.factory_class", "org.hibernate.transaction.JDBCTransactionFactory"); configuration.setProperty("hibernate.current_session_context_class", "thread"); configuration.setInterceptor(new DB2Interceptor()); sessionFactory = configuration.buildSessionFactory(); HibernateSingleton.instance().setSessionFactory(sessionFactory); ... Hope this may help others having the same issues that I had. Best regards, Ricardo Fernandes -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3644?page=c... ] Paul Benedict commented on HHH-3644: ------------------------------------ The first commenter includes a "custom" interceptor. That's a good workaround, but I believe the original description is asking for built-in support. I agree that would be fabulous for DB2 developers. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3644?page=c... ] radhakrishna commented on HHH-3644: ----------------------------------- Can you also post the thread tools and the transaction manager, or the related code -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3644?page=c... ] Julien Kronegg commented on HHH-3644: ------------------------------------- Fine tuning: you do not have the same isolation level for the whole persistence unit, you can do it for one single query. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3644?page=c... ] tushar commented on HHH-3644: ----------------------------- Hello, Is this issue resolved yet? As i seen in latest hibernate release[3.6.5] i am not able to see the changes ? @Ricardo Fernandes can you please paste the code of TransactionManager and ThreadTools class, this will be so helpfull for me .. so can you please provide the code -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.... - For more information on JIRA, see: http://www.atlassian.com/software/jira