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.
Pavel Co Ebele said…
Great Article. Thank you for sharing! Really an awesome post for every one.

IEEE Final Year projects Project Centers in Chennai are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation. For experts, it's an alternate ball game through and through. Smaller than expected IEEE Final Year project centers ground for all fragments of CSE & IT engineers hoping to assemble. Final Year Project Domains for IT It gives you tips and rules that is progressively critical to consider while choosing any final year project point.

Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Spring Framework Corporate TRaining the authors explore the idea of using Java in Big Data platforms.
Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai
WINDOW said…
I have to voice my passion for your kindness giving support to those people that should have guidance on this important matter.
Digital Marketing Training in Chennai
amit tavva said…
keep up the good work. this is an Assam post. this to helpful, i have reading here all post. i am impressed. thank you. this is our digital marketing training center. This is an online certificate course
digital marketing training in bangalore | https://www.excelr.com/digital-marketing-training-in-bangalore

Popular Posts