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
 
Difference between time spent on OPEN vs FETCH

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

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Fri Oct 25, 2013 11:52 pm    Post subject: Difference between time spent on OPEN vs FETCH
Reply with quote

Hi all,

We are in the process of trying to reduce the run time of long running batch jobs. We used APA to profile the long running steps. The APA reports shows a high CPU and Wait time on FETCH statement of a dynamic cursor. The corresponding OPEN statement consumption is <10% of the total consumption of FETCH statement.

I am trying to understand the difference between the time spent on the OPEN and FETCH. Does this mean that the cursor was resolved faster (as the OPEN only took 10% of the time) ? Will this imply that implementing multi row fetch (which might reduce the total number of database calls) improve the performance of this fetch statements ? Or will I still have to go ahead with fine tuning the query to improve its access path ?

Please advice/guide me on which would be the best approach.
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: Mon Oct 28, 2013 12:29 am    Post subject:
Reply with quote

Hello,

From what little has been posted, I cannot make a suggestion about which would be better.

You need to provide much more detail.

How was the 10% determined? What about the result of an explain?

Is the test environment like the prod environment as far as volume and table/key definition?

Keep in mind that different Opens require more resources.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Oct 28, 2013 1:02 pm    Post subject:
Reply with quote

If the query involves an intermediate resultset, then it is resolved at OPEN . If not then each FETCH is taking CPU.
=
If all qualifying rows are being searched (and sorted) before the first row can be returned, it will be the OPEN that takes time.
If rows can be returned to the program as soon as DB2 finds them, then the FETCH will take the time.

Note that not all ORDER BY/GROUP BY cause an intermediate resultset, some/most are resolved by using the correct indexes.
Not using an order by or group by does not mean there will be no intermediate rseultset. it all depends on the accesspath.
Back to top
View user's profile Send private message
Ed Goodman

Active Member


Joined: 08 Jun 2011
Posts: 556
Location: USA

PostPosted: Mon Oct 28, 2013 7:22 pm    Post subject:
Reply with quote

Is there any chance that during the APA measurment session, the open was done only once, while the fetch was done thousands/millions of times?

This would result in a skewed view of the percentages.

ie, a FETCH that is taking 1/1000 the CPU of an OPEN would show as equal CPU if it ran 1000 times during the measurement.

When you look at the overall cpu/wait for he entire job, where is your problem?
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. 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 Difference in SORT & ICETOOL Mohan Kothakota DFSORT/ICETOOL 5 Fri Sep 22, 2017 4:56 pm
No new posts Fetch Previous month & year in MM... girishb2 DFSORT/ICETOOL 3 Thu Sep 21, 2017 9:54 pm

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