IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

DB2 - Error in implementing pagination logic in Stored proc


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sarat6900

New User


Joined: 02 Mar 2009
Posts: 12
Location: India

PostPosted: Thu May 27, 2010 5:35 pm
Reply with quote

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
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu May 27, 2010 6:11 pm
Reply with quote

line 7 of your SP is the problem.
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Thu May 27, 2010 7:18 pm
Reply with quote

It might be a possibility that "pasgination" is being handled at front-end, have you enquired about that?
Back to top
View user's profile Send private message
sarat6900

New User


Joined: 02 Mar 2009
Posts: 12
Location: India

PostPosted: Thu May 27, 2010 8:44 pm
Reply with quote

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
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Thu May 27, 2010 9:35 pm
Reply with quote

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,
Quote:
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.
So, there is a good possibility that when you want to scroll-up or down (back or forth, aka pagination) - your SP is waiting the response from front-end, for the "event(click)" and "the values of last row (so that query/cursor in your SP can understand where to point to fetch next values requested)" once you've displayed the "20 rows" on the page. If this way SP is coded, what you are wanting, actually, does not match the way SP is designed. So, how the procedure had been coded unless you tell us that - reply from Dick becomes sensible.

You might like to browse through this red Book: www.redbooks.ibm.com/redbooks/pdfs/sg247083.pdf
Back to top
View user's profile Send private message
sarat6900

New User


Joined: 02 Mar 2009
Posts: 12
Location: India

PostPosted: Thu May 27, 2010 10:01 pm
Reply with quote

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.
Back to top
View user's profile Send private message
sarat6900

New User


Joined: 02 Mar 2009
Posts: 12
Location: India

PostPosted: Thu May 27, 2010 10:03 pm
Reply with quote

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.
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Thu May 27, 2010 10:08 pm
Reply with quote

sarat6900 wrote:
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.
Does that mean it does not work well in production today?
Back to top
View user's profile Send private message
sarat6900

New User


Joined: 02 Mar 2009
Posts: 12
Location: India

PostPosted: Thu May 27, 2010 10:18 pm
Reply with quote

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu May 27, 2010 11:57 pm
Reply with quote

Quote:
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.



icon_lol.gif icon_rolleyes.gif
Back to top
View user's profile Send private message
sarat6900

New User


Joined: 02 Mar 2009
Posts: 12
Location: India

PostPosted: Fri May 28, 2010 11:03 am
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Fri May 28, 2010 7:13 pm
Reply with quote

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?
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Fri May 28, 2010 7:47 pm
Reply with quote

sarat6900 wrote:
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) ?
The answer, probably, which you want to listen is - you'll get records from 21 to 40. But, a forum member like me can't tell you what has changed from "production to test" - you've just "staged" the Sp in test, so there is no way it should behave differently, with the assumption "all the conditions" in production are kept intact.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri May 28, 2010 8:06 pm
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Error to read log with rexx CLIST & REXX 11
No new posts Error when install DB2 DB2 2
No new posts CLIST - Virtual storage allocation error CLIST & REXX 5
No new posts Error while running web tool kit REXX... CLIST & REXX 5
No new posts Getting Error while trying to establi... DB2 3
Search our Forums:

Back to Top