Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2 - Error in implementing pagination logic in Stored proc

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: DB2 - Error in implementing pagination logic in Stored proc
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    Post subject:
Reply with quote

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

Senior Member


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

PostPosted: Thu May 27, 2010 7:18 pm    Post subject:
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    Post subject:
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

Senior Member


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

PostPosted: Thu May 27, 2010 9:35 pm    Post subject:
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: http://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    Post subject:
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    Post subject: Reply to: DB2 - Error in implementing pagination logic in St
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

Senior Member


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

PostPosted: Thu May 27, 2010 10:08 pm    Post subject:
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    Post subject:
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    Post subject:
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    Post subject: Reply to: DB2 - Error in implementing pagination logic in St
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

Site Director


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

PostPosted: Fri May 28, 2010 7:13 pm    Post subject:
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

Senior Member


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

PostPosted: Fri May 28, 2010 7:47 pm    Post subject: Re: Reply to: DB2 - Error in implementing pagination logic i
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts S922 Error yuvan ABENDS & Debugging 3 Fri Dec 02, 2016 6:58 pm
No new posts Invoke Webservice Fails with DFHPI100... divated CICS 2 Thu Nov 24, 2016 5:57 pm
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm
No new posts Access SQLCA fields inside a stored p... gangapd DB2 4 Thu Oct 27, 2016 10:20 am
No new posts Error during restore rename archanamuthukrishnan All Other Mainframe Topics 2 Fri Oct 14, 2016 3:30 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us