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: 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 Testing rerad cursor for status with ... John F Dutcher DB2 8 Fri May 19, 2017 9:35 pm
No new posts DSNACCOX (can it be run on 1 db/ts, t... SRICOBSAS DB2 5 Sat May 06, 2017 12:59 am
No new posts LISTIDR compiled date/time jerryte IBM Tools 3 Thu Apr 20, 2017 7:37 pm
No new posts Application not run by time HH:MM tri... jzhardy IBM Tools 1 Sun Apr 09, 2017 3:22 pm
No new posts Date and time format in CICS Chandru3183 CICS 2 Sat Mar 18, 2017 12:46 pm


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