1

For a query as simple as this one, DB2 is throwing SQL error for no reason when used in hibernate.

I think i have all the settings of DB2 for hibernate properties right, cause some other queries are running fine.

    SQLException for SQL [/* criteria query */ 

        select this_.col1,this_.col2,this2_.col1,this2_.col3
           from TABLE this_ 
          left outer join TABLE1 this2_ 
          on this_.TAB1_ID=this2_.ID 
          where this2_.ID=?

   ]; SQL state [36001]; error code [-243]; could not execute query using scroll; nested exception is org.hibernate.exception.GenericJDBCException: could not execute query using scroll

I tried looking up on the IBM site, it does not help at all, as the same test cases work fine on hsqldb.

Note: Hibernate generates the query for me i don't have query generated.

Any help would be really appreciated.

Thanks Sridhar.

shree dhar
  • 11
  • 3

1 Answers1

1

DB2 introduced kind of cursor called "sensitive cursor", that shall be able to see (some) changes in data made after cursor is opened. Alternatives are "asensitive" and "insensitive" cursor types.

If query returns read-only result set, this implies that sensitive cursor will not be able to see data changes, as it's result set is detached from actual data. DB2 returns the error -243 to signal that it cannot satisfy requested "sensitive" behaviour.

As your query includes join, it returns read-only result set, and hence you receive the error.

If you do not require such sensitiviness (e.g. you do not intend to update data), you can try setting JDBC property cursorSensitivity to TYPE_SCROLL_ASENSITIVE (that is int of value 2). This parameter can be coded in JDBC URL string like this:

jdbc:db2://hostname:port/dbname:cursorSensitivity=2;

See here for details (or better- see corresponding pages for you acctual DB2 version):

Community
  • 1
  • 1
DarkoS
  • 11
  • 1