I am developing a Java Web service allow paging when fetching big data set from a DB2 Database on a IBM Mid Range Machine (AS400).
For example; if there are 10000 records in a data set, I want to fetch them in 1000 blocks at a time.
I found this article that explains that I can use LIMIT, and OFFSET. But I need to set the
DB2 for Linux Unix Windows (LUW) and DB2 for iSeries are different products. Likely, DB2 for iSeries does not support DB2_COMPATIBILITY_VECTOR. I'm not able to find mention of it in the iSeries Information Center.
Instead of LIMIT, you can use the FETCH FIRST 10 ROWS ONLY clause.
Instead of LIMIT and OFFSET, you should be able to use a subselect with the ROW_NUMBER olap function. Something like this:
SELECT emp.EMPNO, emp.SALARY FROM ( SELECT EMPNO, SALARY, ROW_NUMBER() OVER(ORDER BY SALARY DESC) as row_number FROM EMPLOYEE ) emp WHERE emp.row_number > 10 AND emp.row_number <= 20