DB2 - Error in implementing pagination logic in Stored proc

sarat6900 · New User Joined: 02 Mar 2009 Posts: 12 Location: India

Hi team,

In one of my Stored proc, we had implemented pagination logic ( To display pages in online application like google) by using the following logic.

1) Cursor to display 20 records at a time in one page

CURSOR cur1 WITH RETURN FOR
--------------------------------
FETCH FIRST 20 ROWS ONLY
FOR FETCH ONLY

2) To display data for Nth page, we will fetch data from the cursor (N-1)*20 times and return the cursor back to Web application

3) This logic is working fine in production environment. However, when we checkout out the code in test, the stored procedure doesnot return data for any page except the first page.

4) The package bind isolation is CS with CURRENT DATA - NO.

5) We used the same load that is installed in production and still we were not able to get any output from 2 page onwards. We did this to rule out any differences caused by compiler.

6) We could find a workaround by using ROW_NUMBER(). However, before we go and replace a currently working code in production, we would like to find the root cause of this discrepancy.

Any help is highly appreciated.

Thanks,
Sarat S

dbzTHEdinosauer · Posted: Thu May 27, 2010 6:11 pm

line 7 of your SP is the problem.

Anuj Dhawan · Posted: Thu May 27, 2010 7:18 pm

It might be a possibility that "pasgination" is being handled at front-end, have you enquired about that?

sarat6900 · New User Joined: 02 Mar 2009 Posts: 12 Location: India

Hi Anuj,

Pagination is handled at back end itself.

Say if there are 240 records returned, 20 rows per page are returned to front end.

When they click on second page records 21 to 40 are displayed. To implement this at the back end, we would fetch first 20 rows and skip it.

To confirm it is not issue from front end, we tested it with stored procedure builder also. Still we are facing the same issue in test region.

Thanks,
Sarat

Anuj Dhawan · Posted: Thu May 27, 2010 9:35 pm

If you are using IBM DB2 Store Procedure Builder, I suspect that "pagination" can be tested on that - it's been a while when I used it, but if memory serves well, for "pagination" you need to depend upon the "result set" from front-end.

And, as you said,

sarat6900 · New User Joined: 02 Mar 2009 Posts: 12 Location: India

Thanks a lot Anuj.

For each throw of page, front end calls SP by passing page number and number of records required in the page.

Say if user clicks Nth page , we will have page "N" and recs per page (20 in this case)

We will open the cursor, fetch it for (N-1)*20 records (to skip).

Using SP builder we passed input parameter as "N" and 20 except for the first page output is empty.

As a workaround we modified logic using ROW_NUMBER(). This method is working fine (displays data in all pages) in both SP builder and from front end.

sarat6900 · New User Joined: 02 Mar 2009 Posts: 12 Location: India

Hi Anuj,

The SP has an input parameters (among others) as below.

PAGE_NUM
PAGE_SIZE

So, the front end will pass the page number and page size whenever they invoke the SP. So, for eg: If page 2 data is required, the input parameters will be like below,

PAGE_NUM = 2
PAGE_SIZE = 20.

In Cobol SP, we calculate the number of rows to be skipped as

ROWS-TO-BE-SKIPPED = (PAGE-NUM - 1)* PAGE-SIZE.

So, we end up with a value 20 in this case. The fetch is executed 20 times,thus removing the first 20 records from the cursor and it is returned to the front end.
In production environment, the rows from 21-40 are returned, while in test an empty result set is returned.

Thanks for the link.We had used this redbook while we started learning Stored procs. I am afraid it doesn't have the solution that we are looking for.

Anuj Dhawan · Posted: Thu May 27, 2010 10:08 pm

sarat6900 · New User Joined: 02 Mar 2009 Posts: 12 Location: India

Hi Anuj,

Old logic is working fine in production..

We have checked out the code to make a change in SP that is no way related to pagination logic. Then only we noticed that there were issues with pagination in test region..

To understand what was causing this issue, We used the same load that is installed in production and still we were not able to get any output from 2 page onwards. We did this to rule out any differences caused by compiler.

We tried out a work around just to make sure even if this issue in test can be resolved, we have a solution in our hand.

Thanks,
Sarat.

dbzTHEdinosauer · Posted: Thu May 27, 2010 11:57 pm

sarat6900 · New User Joined: 02 Mar 2009 Posts: 12 Location: India

Rephrasing the question. Hoping it will help to get more clarity on the problem we are facing.

1. There is a table with 100 records to be displayed, 20 each at a time.
2. A cursor on this table is defined with FETCH FIRST 20 ROWS ONLY.
3. I opened the cursor and issued a fetch for 20 times ,Now, what could I expect in the resultset ?

a) A empty result set (Test scenario) or
b) Records from 21-40 (production scenario) ?

Thanks.

dick scherrer · Posted: Fri May 28, 2010 7:13 pm

Hello,

Everyone appears to understand your problem - no need to repeat it any more.

Something is different in the 2 environments. As you are the only one who has access to your system (we don't), you are the only one who can look at things and determine what is different.

When you post the difference(s), someone here will be able to explain why the executions are not consistent.

When the 20 fetches are done, is the data put into an array? If yes, move spaces to the array before the "second" page is read and see if anything is then put into the array. Might the code not always check the sql return code?

Anuj Dhawan · Posted: Fri May 28, 2010 7:47 pm

dbzTHEdinosauer · Posted: Fri May 28, 2010 8:06 pm

sarat6900,

why are you not using a FETCH ABSOLUTE(:host-variable) ?

do you actually fetch 199 rows
to skip thru the unneeded rows
if the page request is for page 10?

usually a
FETCH FIRST 20 ROWS ONLY clause
will cause a +100 when you attempt to fetch past the 'first .. only'.

are you sure the production db2 is exactly like test?

i have worked in environments where we had 7 on test and 8 in prod.
sometimes it made a difference.
like going from 6 to 7 required looking at every -811 routine and determine
if the routine relied on the sql code
or the actual data contained in the host variables, which were no longer being populated.