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.

Comments

Unknown said…
Hi Deven, I try to paginate SQL sentence on j2ee web application. In the last week I read a lot articles on Intenrnet and all refers to the function ROW_NUMBER(). I need to connect to DB2 UDB for AS/400 v05.01.0000 V5R1m0, and the function doesn't exist.
In IBM forums (http://www.ibm.com/developerworks/forums/thread.jspa?messageID=13741551) I read that this feature (row_number) is not provided in DB2 for iSeries. But is a few years old ...
Its necesary to install some software on AS/400 server to use row_number() ?
Any comments will be helpful, thanks anyway ...
This is very slow with my stats table holding several million rows. I miss PostgreSQL as well!!! It's also impossible to have a look at the table contraints or keys from within isql...
Deven Phillips said…
DB2's Navigator is a much better method for viewing things like indexes and keys.

Popular Posts