I am trying to accomplish pagination in Java class (DAO) by writing SQL invoked on DB2 8.x running on Z/OS (mainframe).
I would like to pass parameter to SQL to retrieve a chunk of records e.g. One SQL will retrieve rows 1 through 250 (incluside) Next SQL will retrieve rows 251 through 500 and so on.
I would like to write SQL as preparedstatement as:
SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY empno ASC) AS rownumber,empno FROM edwarde.employee ) AS foo WHERE rownumber between ? and ?;
? are start and end row numbers which will be set programmatically in Java in Preparedstatement. Obviously, multiple calls will be made to database; each time with differetnt parameters.
My questions are: 1) Has anybody had success in using syntax above to get chunks of records in DB2 in Z/OS? 2) If I do not need ordering, can I remove ORDER by cluase? If I remove ORDER BY clause, probably there will be improvements in performance.