MVSFORUMS.com

prog_mario · Beginner Joined: 08 Sep 2007 Posts: 86 Topics: 27

I have a very big query in a store procedure and I need to return to the caller progam the results and the number of pages it produces (50 rows per pay). To solve this I run the same query using only a count to get this number, but the procedure becomes very heavy running the query twice. I'd like to know if there is any sql field that keeps the number of the rows retrieved.

dbzTHEdinosauer · Posted: Fri Dec 18, 2009 4:54 pm Post subject:

if you are doing a multirow fetch sqlerrd(3) will give you a count.
_________________
Dick Brenholtz
American living in Varel, Germany

prog_mario · Beginner Joined: 08 Sep 2007 Posts: 86 Topics: 27

No, I'm using fetch first 50 rows only.
_________________
The more I learn, the more I want to learn.

dbzTHEdinosauer · Posted: Sat Dec 19, 2009 11:21 am Post subject:

if you are doing singleton FETCHs, then you have to count them yourself with a cobol variable.

FETCH FIRST 50 ROWS ONLY is a optimization clause.
_________________
Dick Brenholtz
American living in Varel, Germany

schintala · Posted: Tue Dec 22, 2009 5:49 pm Post subject:

Are you going to display the result set of the query in web page paging. If so, we have similar situation come across in our application and we wrote them in array and tried to get the data 25 rows per page using paging logic.

jctgf · Beginner Joined: 05 Nov 2006 Posts: 89 Topics: 36

I think the most resource-consuming operation is the DB2 access.
Sometimes, creating an index will increase your sql performance a lot.
Other possible solution would be to store the rows selected with your Sql in a Temporary Storage (with AUXILIARY), and then use it to display the screen.
You'd run your Sql once only and store the rows retrieved in the TS.
As you fetch, you'd count the number of rows.
After filling the TS, you'd read it forward and backwards and display the screen.
Each item in the TS may contain the entire page you want to display instead of each line of the page.
We've done it here and seems to work pretty well.
If the number of rows is too big or unpredictable, make sure to use the TS AUXILIARY option, otherwise the application may cause some instability to Cics.
TS I/O (even using the AUXILIARY option) is faster that DB2 access.
Regards.