Equivalent of LIMIT for DB2

Question

How do you do LIMIT in DB2 for iSeries?

I have a table with more than 50,000 records and I want to return records 0 to 10,000, and records 10,000 to 20,000.

I know in SQL you write LIMIT 0,10000 at the end of the query for 0 to 10,000 and LIMIT 10000,10000 at the end of the query for 10000 to 20,000

So, how is this done in DB2? Whats the code and syntax? (full query example is appreciated)

Answer 1

Using FETCH FIRST [n] ROWS ONLY:

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.perf/db2z_fetchfirstnrows.htm

SELECT LASTNAME, FIRSTNAME, EMPNO, SALARY   FROM EMP   ORDER BY SALARY DESC   FETCH FIRST 20 ROWS ONLY; 

To get ranges, you'd have to use ROW_NUMBER() (since v5r4) and use that within the WHERE clause: (stolen from here: http://www.justskins.com/forums/db2-select-how-to-123209.html)

SELECT code, name, address FROM (    SELECT row_number() OVER ( ORDER BY code ) AS rid, code, name, address   FROM contacts   WHERE name LIKE '%Bob%'    ) AS t WHERE t.rid BETWEEN 20 AND 25;