View previous topic :: View next topic
|
Author |
Message |
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 |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
line 7 of your SP is the problem. |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6248 Location: Mumbai, India
|
|
|
|
It might be a possibility that "pasgination" is being handled at front-end, have you enquired about that? |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6248 Location: Mumbai, India
|
|
|
|
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 |
|
|
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. |
|
Back to top |
|
|
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. |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6248 Location: Mumbai, India
|
|
|
|
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 |
|
|
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. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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. |
|
|
Back to top |
|
|
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. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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 |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6248 Location: Mumbai, India
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
|