Emulating LIMIT and OFFSET on DB2 for the System i (AS400/iSeries)

    For some time now, I have been spoiled by being able to use the database of my choice for every project I worked on. Recently though, I have had to venture out of my comfort zone and I have found myself longing for my familiar stomping grounds of PostgreSQL and MySQL. One prime example was trying to do pagination on a DB2 database engine. In PostgreSQL, I would merely do the following:

SELECT * FROM <mytable> LIMIT 20 OFFSET 20

    This would return 20 rows while skipping the first 20 rows of the resultset. Very useful when you want to break things down into several pages worth of content. Unfortunately, there is no equivalent clause on the AS400. After much trying, I have discovered a solution which works for me. It involves using selecting into a cursor and then selecting against that cursor. A quick example would be the following:

SELECT 
    * 
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY <table field>) AS ROWNUM, * FROM <mytable>
) AS <mycursor>
WHERE 
    <mycursor>.ROWNUM>20 AND 
    <mycursor>.ROWNUM<=40

Sure, it's not the prettiest solution in the world; but assuming you've built your indexes properly and done all of your WHERE clauses and JOINs inside of the cursor definition, it only adds a few milliseconds to a query which returns hundreds of thousands of rows (in my experience).

So, if you need to paginate the results from a DB query, this may help you out.