Greetings,

I am working with Hibernate (V2) against a DB2 database running on an AS/400. The DB2 version is V4R5.

In the interests of optimizing some of the queries we are performing I recently added setFirstResult() and setMaxResult() parameters to one of our queries:

Query q = mgr.createQuery("from TransactionHistory hist where hist.account = ? and hist.effectiveDate between ? and ?");
q.setParameter(0, getAccountNumber());
GregorianCalendar cal = new GregorianCalendar();
cal.add(Calendar.DAY_OF_YEAR, -60);
Date pastDate = cal.getTime();
q.setParameter(1, pastDate);
q.setParameter(2, new Date());
q.setFirstResult(20);
q.setMaxResults(50);

When I run this query, I get the following SQL exception:

14:55:50,534 DEBUG JDBCExceptionReporter:36 - SQL Exception
java.sql.SQLException: [SQL0199] Keyword FETCH not expected. Valid tokens: FOR WITH ORDER UNION OPTIMIZE.

After doing a bit of googling I discovered that it is entirely possible that DB2 V4R5 for AS/400 doesn't support the fetch command. Assuming that this is true, is there an alternative means of paging this query? At the moment I am assuming that the answer (if it exists) lies in modifying the dialect (net.sf.hibernate.dialect.DB2400Dialect), but if there is something easier that I can do I would like to hear about it.

Alternatively, if there is a way to set up this version of DB2 to accept the FETCH command I would be interested in hearing about this as well.