isolation-level "RR" on AS400/DB2 - HQL or Dialect

curio · **Joined:** Tue Oct 05, 2004 9:45 am **Posts:** 263

Hibernate version: 2.1.7
Hi there,

i hope someone can and want answer me ...

In same situations i need to do a 'select' with isolation-level repeatable read. Not always ... only sometimes.
On a AS400/DB2 you can do that by adding a 'with RR' to the select-clause.

I've tried this via HQL and a named query, but got :

Code:

Exception in thread "main" net.sf.hibernate.QueryException: unexpected token: RR [from com.test.hibernate.hql.data.TestWithRR with RR]
   at net.sf.hibernate.hql.FromParser.token(FromParser.java:101)
   at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
   at net.sf.hibernate.hql.PreprocessingParser.end(PreprocessingParser.java:142)
   at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:30)
   at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149)
   at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138)
   at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:295)
   at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1571)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1542)
   at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
   at com.test.hibernate.hql.WithRRTest.runTest(WithRRTest.java:42)
   at com.iks.hibernate.hql.WithRRTest.main(WithRRTest.java:34)

My question is if it's possible at all doing it, if the sql-parser don't know it.
Or can i do it by patching the AS400Dialect? But in which method i must do that? I think it's something like the "for update" ... But i've only found a method to decide whether "for update" is possible... but not how it looks like ...

thx!
curio

david · **Posted:** Mon Jan 24, 2005 9:03 pm

Cannot do it with HQL. Use a SQL query, eg, sess.createSQLQuery().
See

http://www.hibernate.org/hib_docs/reference/en/html/querysql.html

curio · **Joined:** Tue Oct 05, 2004 9:45 am **Posts:** 263

hi david,

thanks for your answer!!
I hoped that it's possible to do this in a not db-specific way using a Dialect ... but sure, hibernate couldn't handle all formats of each exisiting database ... i think that would be a little bit to much ;)

I'll do that with a sql-query ... so thanks for your hint!

gtx
curio

emmanuel · **Posted:** Tue Jan 25, 2005 9:11 am

Actually you can use
query.setLockMode("alias", LockMode.UPGRADE);
it will do a "for update with rr" in DB2
Not sure it fits your needs however

curio · **Joined:** Tue Oct 05, 2004 9:45 am **Posts:** 263

i've looked in the iseries/udb documentation and found that 'for update with rr' is working .. and tried it ... the pure select is working perfectly ... 'for update' is not really necessary, but i think it can't hurt ;)

so thanks a lot! I will try it with "query.setLockMode" ...

gtx
curio