Re: isolation level hints not working with DB2 v9.5

[Rajeev Jha <jh...@gmail.com>]

hello catalina
I did try this as well as removing order by clause from the query. But
still no luck! maybe I am missing something big here. Thanks for all
the help.

- rajeev.

On Tue, Apr 1, 2008 at 3:25 AM, catalina wei <ca...@gmail.com> wrote:
> Hi Rajeev,
> Could you try this property in your persistence.xml ?
>   <property name="openjpa.jdbc.DBDictionary"
>         value="db2(supportsLockingWithOrderClause=true)"/>
>
> Catalina
>
>
>
> On 3/30/08, Rajeev Jha <jh...@gmail.com> wrote:
> > hello catalina
> > would it be possible to send the relevant properties from your
> > persistence.xml file? is it possible to use DB2 isolation level hints
> > with default lock settings? (optimistic?) Like say, i have set the
> > default isolation level to ANSI read_commited in config file and then
> > I want to use read_uncommitted isolation level for some list queries?
> >
> > Here is the code I am using
> >
> >                OpenJPAEntityManagerSPI em = (OpenJPAEntityManagerSPI)
> > JPAUtil.getEntityManager();
> >                DBDictionary dict = ((JDBCConfiguration)
> > em.getConfiguration()).getDBDictionaryInstance();
> >
> >
> >                JPAUtil.beginTransaction();
> >
> >                String jql = " select test from Test11 test order by
> test.id   ";
> >                OpenJPAQuery q = (OpenJPAQuery) em.createQuery(jql);
> >                q.setHint("openjpa.FetchPlan.Isolation",
> IsolationLevel.READ_UNCOMMITTED);
> >                List list = q.getResultList();
> >                System.out.println(q.getQueryString());
> >
> >
> > Only relevant property from persistence.xml is
> >
> > <property name="openjpa.jdbc.DBDictionary"
> > value="org.apache.openjpa.jdbc.sql.DB2Dictionary"/>
> >
> > And here is what I see on console
> >
> > 1335930 SELECT t0.id, t0.prop1, t0.prop2 FROM TEST11 t0 ORDER BY t0.id ASC
> >
> > Thanks
> >
> > - rajeev.
> >
> >
> > On Fri, Mar 28, 2008 at 10:28 PM, catalina wei <ca...@gmail.com>
> wrote:
> > > Hi Rajeev,
> > > The product version in trace looked good.
> > > Now, have you followed the example in
> > > (openjpa-persistence-jdbc/src/test/java folder), the java source ?
> > > org.apache.openjpa.persistence.jdbc.TestIsolationLevelOverride
> > >
> > > Could you give it a try ?
> > > We were able to get correct SQL generation using DB2/NT  SQL09050 (v9.5)
> > > I do not see why it would fail with DB2/LINUXX8664  SQL09050 .
> > >
> > > I can take a look at your testcase if you can send it to me.
> > >
> > > Catalina
> > >
> > >
> > > On Fri, Mar 28, 2008 at 2:45 AM, Rajeev Jha <jh...@gmail.com>
> wrote:
> > > > Hello catalina
> > > >
> > > > I have pasted the trace from my environment. Please let me know if you
> > > > need more info. I can work around the issue by setting isolation
> > > > levels directly in DB2 session but this is one feature i want to use.
> > > >
> > > > Thanks
> > > >
> > > > - rajeev.
> > > >
> > >
> ================================================================================
> > > > INFO   [main] openjpa.jdbc.JDBC - Using dictionary class
> > > > "org.apache.openjpa.jdbc.sql.DB2Dictionary" (DB2/LINUXX8664 SQL09050
> > > > ,IBM DB2 JDBC Universal Driver Architecture 3.2.49).
> > > > 1954  vwc.model  TRACE  [main] openjpa.jdbc.JDBC - catalogSeparator: .
> > > > catalogTerm: null
> > > > databaseProductName: DB2/LINUXX8664
> > > > databaseProductVersion: SQL09050
> > > >
> > > > driverName: IBM DB2 JDBC Universal Driver Architecture
> > > > driverVersion: 3.2.49
> > > >
> > > >
> > >
> ===================================================================================
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > On Fri, Mar 28, 2008 at 4:12 AM, catalina wei <ca...@gmail.com>
> > > wrote:
> > > > > Hi Rajeev,
> > > > > Do you have trace info about your databaseProductName and
> > > > > databaseProductVersion for DB2 v9.5?
> > > > > Could you send that to me? It will help in diagnosing the problem.
> > > > >
> > > > > I am running DB2 v9.1,  I have this in trace:
> > > > >
> > > > > databaseProductName: DB2/NT
> > > > > databaseProductVersion: SQL09010
> > > > > driverName: IBM DB2 JDBC Universal Driver Architecture
> > > > > ...
> > > > >
> > > > >
> > > > > Catalina Wei
> > > > >
> > > >
> > >
> > >
> >
>
>