Can not get N first results of query with DB2 dialect (neither with setMaxResults nor with setFetchSize)

Description

I can't get the N first records from a select request using the setMaxResults method.
HB generates a request that can not be understood by DB2 (select * from ( select rownumber() over() as rownumber etc.... see first stacktrace below)
Moreover, using setFetchSize does not work (see second stack trace below the first one).

Is there a way to tell HB to append " fetch first 10 rows only" to the query (as a workaround) ? Because my DB2 can understand that one :

select *
from MYTABLE
where numseq = '2'
fetch first 10 rows only

thanks !

Fred

STACK WITH SETMAXRESULTS :

Hibernate: select * from ( select rownumber() over() as rownumber_, * from A165D.TB3PARCV where TVOY='D' ) as temp_ where rownumber_ <= ?
[01/12/06 12:03:17:516 CET] 2d68c035 SystemOut O 12:03:17,516 67422 WARN JDBCExceptionReporter (logExceptions, 71 ) - SQL Error: -104, SQLState: 42601
12:03:17,516 67422 WARN JDBCExceptionReporter (logExceptions, 71 ) - SQL Error: -104, SQLState: 42601
12:03:17,516 67422 ERROR JDBCExceptionReporter (logExceptions, 72 ) - ILLEGAL SYMBOL "(". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: , FROM INTO
12:03:17,516 67422 ERROR JDBCExceptionReporter (logExceptions, 72 ) - ILLEGAL SYMBOL "(". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: , FROM INTO
12:03:17,516 67422 WARN JDBCExceptionReporter (logExceptions, 71 ) - SQL Error: -516, SQLState: 26501
12:03:17,516 67422 WARN JDBCExceptionReporter (logExceptions, 71 ) - SQL Error: -516, SQLState: 26501
12:03:17,547 67453 ERROR JDBCExceptionReporter (logExceptions, 72 ) - THE DESCRIBE STATEMENT DOES NOT SPECIFY A PREPARED STATEMENT
12:03:17,547 67453 ERROR JDBCExceptionReporter (logExceptions, 72 ) - THE DESCRIBE STATEMENT DOES NOT SPECIFY A PREPARED STATEMENT
12:03:17,547 67453 WARN JDBCExceptionReporter (logExceptions, 71 ) - SQL Error: -514, SQLState: 26501
12:03:17,547 67453 WARN JDBCExceptionReporter (logExceptions, 71 ) - SQL Error: -514, SQLState: 26501
12:03:17,578 67484 ERROR JDBCExceptionReporter (logExceptions, 72 ) - THE CURSOR SQL_CURLN300C4 IS NOT IN A PREPARED STATE
12:03:17,578 67484 ERROR JDBCExceptionReporter (logExceptions, 72 ) - THE CURSOR SQL_CURLN300C4 IS NOT IN A PREPARED STATE
12:03:17,578 67484 ERROR WAction (execute, 56 ) - org.hibernate.exception.SQLGrammarException: could not execute query
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2153)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:117)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1607)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:121)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:169)
[...]
Caused by: com.ibm.db2.jcc.a.SqlException: ILLEGAL SYMBOL "(". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: , FROM INTO
at com.ibm.db2.jcc.a.cy.e(cy.java:1507)
at com.ibm.db2.jcc.a.cy.a(cy.java:1117)
at com.ibm.db2.jcc.a.cy.a(cy.java:1103)
at com.ibm.db2.jcc.b.bd.h(bd.java:131)
at com.ibm.db2.jcc.b.bd.a(bd.java:42)
at com.ibm.db2.jcc.b.r.a(r.java:31)
at com.ibm.db2.jcc.b.bs.g(bs.java:149)
at com.ibm.db2.jcc.a.cy.l(cy.java:1097)
at com.ibm.db2.jcc.a.cz.bb(cz.java:1554)
at com.ibm.db2.jcc.a.cz.d(cz.java:1986)
at com.ibm.db2.jcc.a.cz.S(cz.java:424)
at com.ibm.db2.jcc.a.cz.executeQuery(cz.java:407)
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java:426)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2150)
... 52 more

_____________________________________________________________________________________________________

STACK WITH SETFETCHSIZE() :

[01/12/06 12:20:57:250 CET] 28ea8033 SystemOut O 11:20:57,250 403719 ERROR WAction (execute, 56 ) - org.hibernate.MappingException: No Dialect mapping for JDBC type: 3
org.hibernate.MappingException: No Dialect mapping for JDBC type: 3
at org.hibernate.dialect.TypeNames.get(TypeNames.java:56)
at org.hibernate.dialect.TypeNames.get(TypeNames.java:81)
at org.hibernate.dialect.Dialect.getHibernateTypeName(Dialect.java:192)
at org.hibernate.loader.custom.CustomLoader.getHibernateType(CustomLoader.java:170)
at org.hibernate.loader.custom.CustomLoader.autoDiscoverTypes(CustomLoader.java:138)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1678)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2150)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:117)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1607)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:121)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:169)

Environment

DB2 V7 version = DSN07012
DB OS = zOS
IBM DB2 JDBC Universal Driver Architecture
Version du pilote JDBC : 2.1.34
Using dialect: org.hibernate.dialect.DB2Dialect

Activity

Show:
Fred
December 1, 2006, 3:29 PM

the java code is :
Criteria criteria = session.createCriteria(boClass);
[...]
bos = criteria.setMaxResults(300).list();

sames errors with SQLQuery :

String sql = "SELECT * from A165D.TB3PARCV where TVOY='D'";
SQLQuery query = session.getSession().createSQLQuery(sql);
query.setMaxResults(10);
// or query.setFetchSize(10);
List results = query.list();

Fred
December 18, 2006, 12:51 PM

well, I did not realize there is more than one DB2 dialects...
As a matter of fact, setting the dialect to DB2390 (instead of DB2) solves the isssue
sorry for the disturbance

Fred

Rejected

Assignee

Unassigned

Reporter

Fred

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure