Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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: 1278
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
This topic is locked: you cannot edit posts or make replies. Full Time COBOL Software Development ... shally Mainframe Jobs 0 Fri Jan 20, 2017 5:24 am
No new posts CICS transaction slow response time vasanthz All Other Mainframe Topics 3 Thu Jan 19, 2017 1:31 am
No new posts DD DUMMY & CPU TIME Nileshkul Testing & Performance analysis 16 Mon Dec 19, 2016 11:35 pm
This topic is locked: you cannot edit posts or make replies. Get a job submitted itself every time... arunsoods JCL & VSAM 3 Tue Nov 22, 2016 4:05 pm
No new posts Check System time(Minute) Using TIME1... balaji81_k DFSORT/ICETOOL 5 Fri Nov 11, 2016 10:53 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us