DB2 Using LIMIT and OFFSET

Question

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_COMPATIBILITY_VECTOR variable to MYS.

Now I have been googling and saw you can use the db2set to set this variable. But I have not been able to find out where to type this command in?

I am developing on a windows machine, and I have the iSeries installed, and I have access to the IBM Mid Range Machine via the iSeries 5250 emulator.

I know this must be a real noob question, but How do I go about changing DB2_COMPATIBILITY_VECTOR variable to MYS?

Answer 1

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

Answer 2

As of IBM i 7.1 TR11 or IBM i 7.2 TR3, normal modern paging with LIMIT / OFFSET is now supported:

SELECT SalesOrderId,OrderDate,DueDate,ShipDate,
       Status,CustomerId,SubTotal,TaxAmt
  FROM SalesOrderHeader SOH
 WHERE CustomerId=@CustomerId
ORDER BY SalesOrderId DESC
 LIMIT @ROWS_PER_PAGE      -- Variable = 10
OFFSET @PAGE_START_ROW     -- Variable = 10 * Page Number

See this article for details...