This is how I’ve achieved Pagination in DB2

            From the knowledge transfer only I got to know this. Application uses DB2 as database. I have only zero experience with DB2. But that’s fine, after all it’s all DBMS concepts. So thought I can handle. Yeah 6 months back I was pretty new to Oracle. Now I’m little comfortable with that.

            But from the developer point of view the major different between Oracle and DB2 is resource availability. Any issues from oracle just copy and paste it in Google. It’s pretty easy to reach the solution. Yeah some exceptions will be there. But in terms of DB2 it’s horrible. I can say worst documentation on DB2 over internet. No issues with the concepts, but there is real problem is in getting DB2 specific key words.

            Let me share the experience on achieving pagination feature with DB2. Yeah I was asked to design a plan administration screen, where user enters the plan attributes and when clicking search it has to populate the plan table which satisfies the search criteria. But here the beauty is for any combination of inputs I get at least 5000 plans. But as per the requirement I can show only 100 plans per page. Yeah here the pagination comes.

            Before developing my plan administration screen I should know the existing implementations and I should follow the same. So when I was validating the existing pagination approach I was shocked. This is how it was implemented. Pagination was handled in java, when you click next, previous, last, first, or search by page no whatever you do query returns all the matching records and the pagination was manipulated in java.

            Gosh, DB2 is not as fast as Oracle too. When I asked the team to use rownum() function I got nice reply. Rownum() is applicable only with Oracle, but here we are talking about the historical DB2. After spending adequate points, nobody knows how to achieve rownum() concept in DB2. Google also doesn’t help us. But I was pretty sure their will be a solution.

            Two days later, gotta spark from hibernate ORM framework. Yes frameworks has pagination api’s inbuilt. Hibernation supports pagination and it supports DB2 too. Not much details from Hibernate DB2 dialect. So I started integrating hibernate with DB2. Integration process was horrible. Since DB2 is from IBM I was asked to pay to use their JDBC drivers. I was not ready to paisa from my pocket on this. So I took the driver from the weblogic driver directory. I know this driver will not work in standalone application. But who cares it’s my POC.

One of the cool features from Hibernate is,

 <property name="hibernate.show_sql">true</property>.

            Yeah every Hibernate actions, respective SQL queries will be generated. And we can toggle printing the queries. So I made in on the fired the pagination action to the DB2.

As expected action failed saying “Invalid JDBC driver”. But it prints the below query before exception.

select * from ( select rownumber() over() as num, PLAN_NAME from REG.PLAN ) as temp where num between 10 and 20;

Here is the solution, we have a function called rownumber in DB2 to achieve pagination. J


P.S - Pagination is the process of dividing information (content) into discrete pages