Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Open cursor takes lot of time

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

New User


Joined: 03 Nov 2009
Posts: 19
Location: chennai

PostPosted: Mon Mar 15, 2010 10:04 pm    Post subject: Open cursor takes lot of time
Reply with quote

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

Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Mon Mar 15, 2010 10:52 pm    Post subject:
Reply with quote

How many rows are there in each table?

How many rows are in the results?

What is a lot of time?
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1777
Location: Bloomington, IL

PostPosted: Mon Mar 15, 2010 11:58 pm    Post subject:
Reply with quote

See this thread.
Back to top
View user's profile Send private message
veeramanimurugesan

New User


Joined: 03 Nov 2009
Posts: 19
Location: chennai

PostPosted: Wed Mar 17, 2010 11:20 am    Post subject: Reply to: Open cursor takes lot of time
Reply with quote

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

New User


Joined: 03 Mar 2009
Posts: 26
Location: germany

PostPosted: Wed Mar 17, 2010 1:11 pm    Post subject:
Reply with quote

hi,

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

regards,
bauer


[/quote]
Back to top
View user's profile Send private message
manikawnth

New User


Joined: 07 Feb 2007
Posts: 61
Location: Mumbai

PostPosted: Wed Mar 17, 2010 2:39 pm    Post subject: @Veera
Reply with quote

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.
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: Wed Mar 17, 2010 7:52 pm    Post subject:
Reply with quote

Hello,

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

Quote:
Result Rows of inner join : 7,281,221
Ok, how many rows are in each table? Questions are asked for a reason and when you don't answer them, it just takes longer for someone to help. . .
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 Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
This topic is locked: you cannot edit posts or make replies. 00D3003B - time outs in DB2 when invo... chavinash2004 DB2 2 Mon Oct 09, 2017 4:39 pm
No new posts VSAM RLS Wait time blayek JCL & VSAM 2 Mon Oct 02, 2017 9:05 pm
No new posts Comparing 2 Files using Current time arunsoods SYNCSORT 9 Fri Sep 22, 2017 6:00 pm
No new posts Reply MQ open error Vignesh Sid COBOL Programming 6 Thu Sep 07, 2017 6:02 pm

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