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
WINDOW said…
This is my first visit to your blog! I have a plan to start a new initiative in a community with the same niche. Your blog provided me with useful information to work. https://www.slajobs.com/digital-marketing-training-in-chennai/
WINDOW said…
Your valuable key points got my high appreciation. Keep up the good service. Thanks a bunch!Digital Marketing Training in Chennai | Digital Marketing Training Institute in Chennai
Anonymous said…
Great efforts put to find the list of articles that are very useful to know. I’m thoroughly enjoying your blog. And Good comments create relations. You’re doing great work. Keep it up.

Magento Development Training Course in Chennai Zuan Education

Selenium Training Course in Chennai Zuan Education

7 tips to start a career in digital marketing

“Digital marketing is the marketing of product or service using digital technologies, mainly on the Internet, but also including mobile phones, display advertising, and any other digital medium”. This is the definition that you would get when you search for the term “Digital marketing” in google. Let’s give out a simpler explanation by saying, “the form of marketing, using the internet and technologies like phones, computer etc”.

we have offered to the advanced syllabus course digital marketing for available join now

more details click the link now

[url]https://www.webdschool.com/digital-marketing-course-in-chennai.html[/url]

Thanks for this blog and unique blog



Web designing trends in 2020

When we look into the trends, everything which is ruling today’s world was once a start up and slowly begun getting into. But Now they have literally transformed our lives on a tremendous note. To name a few, Facebook, Whats App, Twitter can be a promising proof for such a transformation and have a true impact on the digital world.

we have offered to the advanced syllabus course web design and development for available join now

more details click the link now

[urlhttps://www.webdschool.com/web-development-course-in-chennai.html[/url]
w3webschool said…
Nice blog, it’s so knowledgeable, informative, and good looking site. I appreciate your hard work. Good job. Thank you for this wonderful sharing with us. Keep Sharing.
Digital Marketing Course In Kolkata
Web Design Course In Kolkata
adam said…
Thanks for the interesting blog that you have implemented here. Very helpful and innovative. Waiting for your next upcoming article.
Mobile app development training
App development training
Mobile app development course
Mobile development course
Mobile application development course
Learn mobile application development

divya said…
Thank you for your post, I look for such article along time, today i find it finally. this post give me lots of advise it is very useful for me kweep it up gys.
Ai & Artificial Intelligence Course in Chennai
PHP Training in Chennai
Ethical Hacking Course in Chennai Blue Prism Training in Chennai
UiPath Training in Chennai
Jayalakshmi said…
Thanks a lot for sharing such a good source with all, i appreciate your efforts taken for the same. I found this worth sharing and must share this with all.



Dot Net Training in Chennai | Dot Net Training in anna nagar | Dot Net Training in omr | Dot Net Training in porur | Dot Net Training in tambaram | Dot Net Training in velachery









Popular Posts