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 cursors and performance of the program

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

New User


Joined: 29 Nov 2006
Posts: 19
Location: amsterdam

PostPosted: Sat Mar 03, 2007 10:12 pm    Post subject: db2 cursors and performance of the program
Reply with quote

My program uses 10 cursors. There is one main cursor and the rest of the 9 cursors are opened, fetched and closed for each row retrieved from the main cursor.

We have tested the program using 200 records, but did not find any significant performance issue.

Please advise or let me know any literature that mentions the tips of using cursors in mainframe db2 programs to optimize performance of the program

Thanks
Back to top
View user's profile Send private message

priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

PostPosted: Sun Mar 04, 2007 4:29 am    Post subject: Re: db2 cursors and performance of the program
Reply with quote

READ THIS
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: Sun Mar 04, 2007 7:56 am    Post subject:
Reply with quote

Hello,

CURSORs are neither good nor bad - they are just a tool.

The performance of the cursor is largely dependent on how many times it is executed (look at the referred post) and/or how large the resulting "found set" that will be returned. The query within the cursor is where performance may be improved or degraded.

Sometimes, the best solution is using a cursor - other times not even using SQL is a better solution (again, see the referred post).

Keep us posted icon_smile.gif
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Tue Mar 06, 2007 3:47 pm    Post subject:
Reply with quote

Use EXPLAIN and figure out the performance of each query. Are the queries going for Index scan or Tablespace scan. Obviously 200 records cannot make much difference even if it goes to tablespace scan.
Get in touch with DBA to find the optimisation path. Are the indexes being used effectively..? Check the conditions in the WHERE predicate of all the queries. If applicable change some BIND parameters.
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 Writing a file using online program grvtomar PL/I & Assembler 3 Fri Jun 30, 2017 1:06 pm
No new posts pre-compiler for program in an HFS Pedro DB2 0 Fri Jun 23, 2017 11:13 pm
No new posts CONTIG for performance? JPVRoff JCL & VSAM 7 Fri Jun 09, 2017 8:39 am
No new posts Compiling program in endeavor for tra... RALAKKAL Compuware & Other Tools 4 Mon Jun 05, 2017 8:27 pm
No new posts COBOL DB2 program - zIIP eligible vasanthz COBOL Programming 7 Wed May 31, 2017 5:12 am


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