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 taking lot of CPU time

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Open cursor taking lot of CPU time
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    Post subject:
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    Post subject: Reply to: Open cursor taking lot of CPU time
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

Site Director


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

PostPosted: Thu Mar 04, 2010 9:37 pm    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    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 Comparision with current time arunsoods DFSORT/ICETOOL 5 Thu Nov 09, 2017 10:37 am
This topic is locked: you cannot edit posts or make replies. Comparing current time with the time ... arunsoods SYNCSORT 1 Mon Oct 30, 2017 4:07 pm
No new posts -502 Error - Cursor already open anand jeyapaul DB2 7 Sun Oct 29, 2017 4:22 am
This topic is locked: you cannot edit posts or make replies. Incrementing current time field by 30... arunsoods DFSORT/ICETOOL 11 Thu Oct 26, 2017 3:22 pm
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm

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