Is there drawbacks in using DB2 rowset cursors

VINAY PALLELA · New User Joined: 28 Jan 2018 Posts: 24 Location: India

I have used DB2 rowset cursor in my current program to fetch 1000 rows for each fetch statement. When I searched my production library I found only 5 programs which used rowset cursors and those are for very simple programs like extract the data from a table and load into PS file.
My program has some complex logic. We have rowset cursors from DB2 version8 and I see little extra code to declare the host variable arrays and some little extra program logic to handle SQLCODE +100 etc.. But I don't understand why still people in my project still using normal single row cursors.
I used rowset cursor on my own interest. I tried to research if there are drawbacks with rowset cursors and if that is the reason, not used in my project. But I did not find any disadvantages using rowset cursors.

My question is "Why can't we use every cursor as rowset cursor to improve the performance".

Please clarify my doubt.

Thanks in advance!!

Rohit Umarjikar · Posted: Tue Nov 26, 2019 1:24 am

VINAY PALLELA · New User Joined: 28 Jan 2018 Posts: 24 Location: India

Hi Rohit,

Thank you for the info.

My intention of performance improvement is same SQL query with single row fetch cursor and multi row fetch cursor where we can reduce the number of DB2 calls.

90% of my application deals with DB2, My IT lead never wants to use file processing in programs. But I never find any rowset cursors in my application programs triggered this question.

Rohit Umarjikar · Posted: Tue Nov 26, 2019 6:26 am

Vinay,
The common reason is most people don’t know these options available in Db2 and they continue to coding in traditional way to get work done. It’s good that you don’t think the same way as them. Other than this reason there is most likely no good reason why you don’t see much of a row set logic.

I have seen several of them in CICS and Db2 Stored Procedures and specially where paging involved.

VINAY PALLELA · New User Joined: 28 Jan 2018 Posts: 24 Location: India

Nic Clouston · Posted: Tue Nov 26, 2019 4:14 pm

I do not know why you posted in the General part of the forum when there is a perfectly good DB2 section. Topic moved.

sergeyken · Posted: Tue Nov 26, 2019 11:16 pm

Has anyone tested the actual difference in performance between single row FETCH, and rowset FETCH?

I did not try it in DB2, but from my experience, similar methods of "performance improvement", like using group read in REXX, similar to:

Rohit Umarjikar · Posted: Wed Nov 27, 2019 3:47 am

The fewer trips made across the application address spaces and DB2, the lower the overall usage of the machine cycles, thus helping in cpu and response time savings.

Though this require additional coding changes but that's one time.

sergeyken · Posted: Wed Nov 27, 2019 6:03 pm

Rohit Umarjikar · Posted: Wed Nov 27, 2019 11:03 pm

Why DB2 performance tuning is compared against REXX , they are two different thing and different way they work and bring back the data even though they make fewer trips the response time varies.

Second, I have used this many a times in Stored Procedures and in CICS programs and the response time is amazing than traditional fetch.

The performance improvement comes in play when you are dealing with more than 1k or 5k rows and not for 100-200 rows.

VINAY PALLELA · New User Joined: 28 Jan 2018 Posts: 24 Location: India

I practically tested this scenario today when i got some free time.

I have taken on of my simple program where it is used to fetch and update the records with single fetch row cursor based on some filters and update one of the column of same database.

I have created another version of the same program to use the ROWSET cursors to fetch and update 100 rows at a time.

I could see really good results.

CPU time taken to fetch and update 59703 records with Normal cursor CPU TIME= 0.040117
CPU time taken to fetch and update 59703 records with ROWSET cursor CPU TIME= 0.019041

Below are the results with the old traditional single row fetch and update cursor.

John Poulakos · Posted: Fri Dec 06, 2019 9:32 pm

I always measure DB2 performance by the number of SRBs. You don't get an accurate idea of DB2 performance with CPU time because DB2 requests are handled by ZIIP processors. In the example above, both methods issued 9 SRBs, so performance should be about the same. What you see as CPU time is the time it took to process the SRB for validity, not the time it took to actually do the work requested by the SRB