Open cursor takes lot of time

veeramanimurugesan · New User Joined: 03 Nov 2009 Posts: 19 Location: chennai

Hi,

I am running a cobol+db2 program. I have table A and B. I need to fetch all rows that are common in these 2 tables for processing.

So i have a cursor declared like,

SELECT
A.CLI_NO,
A.FIELD_A,
B.FIELD_B
FROM
Table1 A
INNER JOIN
Table2 B
ON
A.CLI_NO = B.CLI_NO

Table1 - Key is Cli no + another key
Table2 - Key is Cli no

Once i fetch the fields, i do some checking on Table1 FIELD_A and update table B. The open cursor in this case is taking a lot of time. I checked it by putting a display statement before and after the open cursor.

Any suggestions/ideas to improve the sql?

Craq Giegerich · Posted: Mon Mar 15, 2010 10:52 pm

How many rows are there in each table?

How many rows are in the results?

What is a lot of time?

Akatsukami · Posted: Mon Mar 15, 2010 11:58 pm

See this thread.

veeramanimurugesan · New User Joined: 03 Nov 2009 Posts: 19 Location: chennai

Result Rows of inner join : 7,281,221

Sorry for being generic.. A lot of time is approx 15 mins for open cursor statement..

By anyway can we fine tune the query or implement query in a different way for the requirement?

bauer · New User Joined: 03 Mar 2009 Posts: 28 Location: germany

hi,

did you use EXPLAIN ?
Are the statustics data up to date? Runstats done?

regards,
bauer

[/quote]

manikawnth · New User Joined: 07 Feb 2007 Posts: 61 Location: Mumbai

How did u come to know its taking 15mins of time using DISPLAY?
Also is it CPU time (which includes connection time) or SQL execution time?
I would suggest you use a monitoring tool like Omegamon DB2 or Strobe or some main view tools.
Kindly post the indexes and corresponding columns along with the query.
It may not be just OPEN cursor of a JOIN query taking lot of CPU time.

dick scherrer · Posted: Wed Mar 17, 2010 7:52 pm

Hello,

It is best to answer all of the questions asked - not just pick and choose. . .