Db2 pagination with row number support

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

Hello
we are using below construct with sublist() to do pagination
==============================================

query.setFirstResult(pageNum * pageSize) .
setMaxResults(pageSize + 1) .
getResultList();
==============================================

This construct appends FETCH FIRST <N> ROWS ONLY to query. while this
is okay for selecting 3rd/4th page , I think if the user clicks 20th
page then we have to actually fetch 400 records (for page size 20)
Hibernate used to support row number construct for DB2 pagination (see
below) . I have looked in DB2Dictionary.java  but I can not find any
equivalent construct in openJPA 1.0.2 code.  Can I do anything to
remedy my situation or writing native queries is my only hope?

=============================================
select * FROM  (
	select test.* , rownumber() over
	(ORDER BY test.id) as row_next
				from Test11 test
				
) as temp where row_next between ?  and ?
==============================================

Thanks

- rajeev.