IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Open cursor taking lot of CPU time


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
murali3955

New User


Joined: 25 Nov 2009
Posts: 11
Location: Chennai

PostPosted: Thu Mar 04, 2010 6:21 pm
Reply with quote

Hi,

Can someone tell me the possible reasons for high CPU consumption for an OPEN cursor statement.

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

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Thu Mar 04, 2010 6:23 pm
Reply with quote

How you came to conclusion that OPEN cursor statement is taking lot of CPU?
Back to top
View user's profile Send private message
murali3955

New User


Joined: 25 Nov 2009
Posts: 11
Location: Chennai

PostPosted: Thu Mar 04, 2010 7:15 pm
Reply with quote

Using EXPLAIN statement i found that the open cursor statement is taking lot of time.

Please let me know the possible reasons for increased CPU.

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

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Thu Mar 04, 2010 9:37 pm
Reply with quote

Hello,

Quote:
Please let me know the possible reasons for increased CPU.
Poorly designed table(s)/index definitions or query syntax. Very high volume processed.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Mar 04, 2010 9:58 pm
Reply with quote

Quote:

Using EXPLAIN statement i found that the open cursor statement is taking lot of time.


?

The underlying problem is in your cursor definition. You cant fine tune an open statement.
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Fri Mar 05, 2010 10:35 am
Reply with quote

If you could post the how cursor is declared. People here might able to help you fine tuning it.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Mar 05, 2010 6:19 pm
Reply with quote

- An explain doesn't show execution times or cpu consumption. At best it gives a high estimated cost on the DECLARE statement.
Back to top
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Fri Mar 05, 2010 7:04 pm
Reply with quote

GuyC wrote:
- An explain doesn't show execution times or cpu consumption. At best it gives a high estimated cost on the DECLARE statement.

A lot of clues can tell you if your cursor takes time to open:
    No signs of program activity that indicates row processing have started
    Use of a DB2 activity monitoring tool
    A query EXPLAIN giving a high estimated cost, would be likely to also take relative long time to execute.
But do use EXPLAIN to identify if your query do not use indexes as intended, if an index is not created/not available, or if proper runstats for your tables are not available.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts To get the the current time DFSORT/ICETOOL 13
No new posts RC query -Time column CA Products 3
No new posts Calling an Open C library function in... CICS 1
No new posts C Compile time time stamps Java & MQSeries 10
No new posts Parallelization in CICS to reduce res... CICS 4
Search our Forums:

Back to Top