Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Is there drawbacks in using DB2 rowset cursors

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
VINAY PALLELA

New User


Joined: 28 Jan 2018
Posts: 9
Location: India

PostPosted: Tue Nov 26, 2019 1:05 am    Post subject: Is there drawbacks in using DB2 rowset cursors
Reply with quote

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

Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2310
Location: NY,USA

PostPosted: Tue Nov 26, 2019 1:24 am    Post subject:
Reply with quote

Quote:
But I don't understand why still people in my project still using normal single row cursors.
You better ask them icon_smile.gif

Quote:
My question is "Why can't we use every cursor as rowset cursor to improve the performance".
It is not necessarily always we deal with 1000 or more rows, so not all cursors are needed to be multi Row cursor. Performance is purely depends on Indexing/Where/JOINS used and itf that is messed up even RowSet won't help.

In Stored Procedures mostly it is used to get better response time per request and in batch it is not that great concerns.
Back to top
View user's profile Send private message
VINAY PALLELA

New User


Joined: 28 Jan 2018
Posts: 9
Location: India

PostPosted: Tue Nov 26, 2019 2:06 am    Post subject: Reply to: Is there drawbacks in using DB2 rowset cursors
Reply with quote

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

Senior Member


Joined: 21 Sep 2010
Posts: 2310
Location: NY,USA

PostPosted: Tue Nov 26, 2019 6:26 am    Post subject:
Reply with quote

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

New User


Joined: 28 Jan 2018
Posts: 9
Location: India

PostPosted: Tue Nov 26, 2019 10:11 am    Post subject: Reply to: Is there drawbacks in using DB2 rowset cursors
Reply with quote

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


Yes icon_smile.gif , I came to know about rowset cursor when I encounter a question in one interview. So I came back and read through different types of rowset cursors and used the one in my current program.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2255
Location: Hampshire, UK

PostPosted: Tue Nov 26, 2019 4:14 pm    Post subject: Reply to: Is there drawbacks in using DB2 rowset cursors
Reply with quote

I do not know why you posted in the General part of the forum when there is a perfectly good DB2 section. Topic moved.
Back to top
View user's profile Send private message
sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 632
Location: Maryland

PostPosted: Tue Nov 26, 2019 11:16 pm    Post subject:
Reply with quote

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:
Code:
bufsize = 1000
Do While ...
   "EXECIO" bufsize "DISKR HUGEDATA (STEM BUFFER."
   Do i = 1 to BUFFER.0
      Call ProcessData BUFFER.i
   End i
End /* While */

did not demonstrate any performance improvement against the simple
Code:
Do While ...
   "EXECIO 1 DISKR HUGEDATA (STEM BUFFER."
   IF RC = 2 Then Leave
   Call ProcessData BUFFER.1
End /* While */

but usually added more mess in the already initially messed code...
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2310
Location: NY,USA

PostPosted: Wed Nov 27, 2019 3:47 am    Post subject:
Reply with quote

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

Active Member


Joined: 29 Apr 2008
Posts: 632
Location: Maryland

PostPosted: Wed Nov 27, 2019 6:03 pm    Post subject:
Reply with quote

Rohit Umarjikar wrote:
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.

It is a theory, and it is clear.

But the real testing often discovered wrong initial assumptions. It would be nice to know about actual test results, if available.

As I said before, the same approach in I/O organization in REXX did not provide any visible performance improvement. There are also "fewer trips made across the application address space, and zOS I/O stuff, both ways thru REXX interpreter". It did not help from performance point of view...
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2310
Location: NY,USA

PostPosted: Wed Nov 27, 2019 11:03 pm    Post subject:
Reply with quote

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

New User


Joined: 28 Jan 2018
Posts: 9
Location: India

PostPosted: Fri Nov 29, 2019 1:11 am    Post subject: Reply to: Is there drawbacks in using DB2 rowset cursors
Reply with quote

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

********************************* TOP OF DATA ********
                                                     
EXECUTING = T98MO941MOP94110                         
                                                     
*                                                     
* PROGRAM MOP941  STARTING                           
* COMPILED 11/28/19 09.23.36                         
*                                                     
* START DATE = 11/28/19                               
* START TIME =  9:24:24                               
*                                                     
                                                     
************************************************     
INPUT PARM CARDS                                     
SUPPORT DC:007 SOURCE: WEST ORDER TYPE: REGO         
SUPPORT DC:007 SOURCE: WEST ORDER TYPE: WEAC         
SUPPORT DC:007 SOURCE: WEST ORDER TYPE: WEBM         
SUPPORT DC:007 SOURCE: WEST ORDER TYPE: WESH         
SUPPORT DC:007 SOURCE: WEST ORDER TYPE: WESB         
SUPPORT DC:007 SOURCE: WEST ORDER TYPE: 0005         
                                                     
                                                     
*************************************************     
**                                             **     
** NBR OF PARMS SKIPPED      : 000                   
** NBR OF COD RECORDS FETCHED: 0000059703             
** NBR OF COD RECORDS UPDATED: 0000059703             
**                                             **     
*************************************************     
                                                     
                                                     
*************************************                 
*** NORMAL  END OF PROGRAM MOP941 ***                 
*************************************                 
******************************** BOTTOM OF DATA ******


Code:

********************************* TOP OF DATA **********************************
 J E S 2  J O B  L O G  --  S Y S T E M  D 0 1    --  N O D E  D P X J E S 0 1 
                                                                               
---- THURSDAY,  28 NOV 2019 ----                                               
 TSS7000I HXVXP6 LAST-USED 28 NOV 19 03:42 SYSTEM=D01 FACILITY=TSO             
 TSS7001I COUNT=01865 MODE=FAIL LOCKTIME=NONE NAME=VINAY PALLELA               
 $HASP373 T98MO941 STARTED - INIT S    - CLASS 4        - SYS D01               
 IEF403I T98MO941 - STARTED - TIME=09.24.24                                     
 -                                      ----TIMINGS (MINUTES)-----             
 -STEPNAME PROCSTEP    RC   EXCP   CONN       TCB       SRB  CLOCK          SERV
 -SENTRY               00     10      0  0.000024  0.000000    0.0            54
 -MOP94110             00    539     46  0.040093  0.000009    0.0         88965
 IEF404I T98MO941 - ENDED - TIME=09.24.28                                       
 -T98MO941 ENDED.  NAME- UPDATE COD DT TYP   TOTAL TCB CPU TIME= 0.040117 TOTAL
 $HASP395 T98MO941 ENDED - RC=0000                                             
ATISTICS ------                                                                 
EXECUTION DATE                                                                 
S READ                                                                         
UT PRINT RECORDS                                                               
UT PUNCH RECORDS                                                               
UT SPOOL KBYTES                                                                 
TES EXECUTION TIME                                                             
******************************** BOTTOM OF DATA ********************************



Below are the results with ROWSET cursor for fetch and update

Code:

EXECUTING = T98MO941MOP94110                     
                                                 
*                                                 
* PROGRAM MOP994  STARTING                       
* COMPILED 11/28/19 10.00.28                     
*                                                 
* START DATE = 11/28/19                           
* START TIME = 10:01:27                           
*                                                 
                                                 
************************************************ 
INPUT PARM CARDS                                 
SUPPORT DC:007 SOURCE: WEST ORDER TYPE: REGO     
SUPPORT DC:007 SOURCE: WEST ORDER TYPE: WEAC     
SUPPORT DC:007 SOURCE: WEST ORDER TYPE: WEBM     
SUPPORT DC:007 SOURCE: WEST ORDER TYPE: WESH     
SUPPORT DC:007 SOURCE: WEST ORDER TYPE: WESB     
SUPPORT DC:007 SOURCE: WEST ORDER TYPE: 0005     
                                                 
                                                 
*************************************************
**                                             **
** NBR OF PARMS SKIPPED      : 000               
** NBR OF COD RECORDS FETCHED: 0000059703         
** NBR OF COD RECORDS UPDATED: 0000059703         
**                                             **
*************************************************
                                                 
                                                 
*************************************             
*** NORMAL  END OF PROGRAM MOP994 ***             
*************************************             
******************************** BOTTOM OF DATA **


Code:

 J E S 2  J O B  L O G  --  S Y S T E M  D 0 1    --  N O D E  D P X J E S
                                                                         
---- THURSDAY,  28 NOV 2019 ----                                         
 TSS7000I HXVXP6 LAST-USED 28 NOV 19 03:42 SYSTEM=D01 FACILITY=TSO       
 TSS7001I COUNT=01865 MODE=FAIL LOCKTIME=NONE NAME=VINAY PALLELA         
 $HASP373 T98MO941 STARTED - INIT R    - CLASS 4        - SYS D01         
 IEF403I T98MO941 - STARTED - TIME=10.01.27                               
 -                                      ----TIMINGS (MINUTES)-----       
 -STEPNAME PROCSTEP    RC   EXCP   CONN       TCB       SRB  CLOCK       
 -SENTRY               00     10      1  0.000023  0.000000    0.0       
 -MOP94110             00    539     46  0.019018  0.000009    0.0       
 IEF404I T98MO941 - ENDED - TIME=10.01.29                                 
 -T98MO941 ENDED.  NAME- UPDATE COD DT TYP   TOTAL TCB CPU TIME= 0.019041
 $HASP395 T98MO941 ENDED - RC=0000                                       
ATISTICS ------                                                           
EXECUTION DATE                                                           
S READ                                                                   
UT PRINT RECORDS                                                         
UT PUNCH RECORDS                                                         
UT SPOOL KBYTES                                                           
TES EXECUTION TIME                                                       
******************************** BOTTOM OF DATA **************************



Note: But i am sure i will not see the same % improvement in my original program, as I used ROWSET cursor only for fetch not for UPDATE. SQL in my current program is little complex one, joining 15 tables which includes inner, left outer joins and same table multiple times with alias names.. fetched almost 30 columns in select clause. I need to extract the date into a flat file afer after applying soem logics and update only one table.
Back to top
View user's profile Send private message
John Poulakos

Active User


Joined: 13 Jun 2012
Posts: 167
Location: United States

PostPosted: Fri Dec 06, 2019 9:32 pm    Post subject:
Reply with quote

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
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 Using Rowset fetch on Remote Table Anees Fatima COBOL Programming 3 Fri Apr 03, 2015 10:33 am
No new posts Impact of SQL error -354 on ROWSET ajayhegdemainframe DB2 2 Tue Oct 22, 2013 11:28 pm
No new posts Handling multiple cursors in cobol - ... Thirumurgann DB2 5 Thu Sep 15, 2011 8:02 am
No new posts Rowset -Multi Row fetch cvishu DB2 5 Tue Sep 13, 2011 6:28 pm
No new posts DB2 Select without using cursors in E... dick scherrer CA Products 3 Mon Aug 22, 2011 2:39 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us