db2 cursors and performance of the program

sandiptcs · New User Joined: 29 Nov 2006 Posts: 19 Location: amsterdam

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

priyesh.agrawal · Posted: Sun Mar 04, 2007 4:29 am

READ THIS

dick scherrer · Posted: Sun Mar 04, 2007 7:56 am

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

vini_srcna · Posted: Tue Mar 06, 2007 3:47 pm

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.