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

Theres these available options:-

DB2 has several strategies to cope with this problem.
You can use the "scrollable cursor" in feature.
In this case you can open a cursor and, instead of re-issuing a query you can FETCH forward and backward.
This works great if your application can hold state since it doesn't require DB2 to rerun the query every time.
You can use the ROW_NUMBER() OLAP function to number rows and then return the subset you want.
This is ANSI SQL 
You can use the ROWNUM pseudo columns which does the same as ROW_NUMBER() but is suitable if you have Oracle skills.
You can use LIMIT and OFFSET if you are more leaning to a mySQL or PostgreSQL dialect.  

Answer 2

Try this

SELECT * FROM
    (
        SELECT T.*, ROW_NUMBER() OVER() R FROM TABLE T
    )
    WHERE R BETWEEN 10000 AND 20000

Answer 3

Developed this method:

You NEED a table that has an unique value that can be ordered.

If you want rows 10,000 to 25,000 and your Table has 40,000 rows, first you need to get the starting point and total rows:

int start = 40000 - 10000;

int total = 25000 - 10000;

And then pass these by code to the query:

SELECT * FROM 
(SELECT * FROM schema.mytable 
ORDER BY userId DESC fetch first {start} rows only ) AS mini 
ORDER BY mini.userId ASC fetch first {total} rows only

Answer 4

You should also consider the OPTIMIZE FOR n ROWS clause. More details on all of this in the DB2 LUW documentation in the Guidelines for restricting SELECT statements topic:

• The OPTIMIZE FOR clause declares the intent to retrieve only a subset of the result or to give priority to retrieving only the first few rows. The optimizer can then choose access plans that minimize the response time for retrieving the first few rows.

Answer 5

Support for OFFSET and LIMIT was recently added to DB2 for i 7.1 and 7.2. You need the following DB PTF group levels to get this support:

• SF99702 level 9 for IBM i 7.2
• SF99701 level 38 for IBM i 7.1

See here for more information: OFFSET and LIMIT documentation, DB2 for i Enhancement Wiki

Answer 6

@elcool's solution is a smart idea, but you need to know total number of rows (which can even change while you are executing the query!). So I propose a modified version, which unfortunately needs 3 subqueries instead of 2:

select * from (
    select * from (
        select * from MYLIB.MYTABLE
        order by MYID asc 
        fetch first {last} rows only 
        ) I 
    order by MYID desc
    fetch first {length} rows only
    ) II
order by MYID asc

where {last} should be replaced with row number of the last record I need and {length} should be replaced with the number of rows I need, calculated as last row - first row + 1.

E.g. if I want rows from 10 to 25 (totally 16 rows), {last} will be 25 and {length} will be 25-10+1=16.