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
 

 

Difference between Open and fetch statements in Cursor

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sudhakarreddy.ibm
Warnings : 1

New User


Joined: 20 Apr 2009
Posts: 15
Location: chennai

PostPosted: Mon May 11, 2009 6:56 pm    Post subject: Difference between Open and fetch statements in Cursor
Reply with quote

Hi all,

This is Sudhakar, i have a question and putting infront of you.
In DB2 Cursor process we have Declare, open, fetch and close statements.

OUt of these which will take higer time to process ?

Thanks in advance
Sudhakar
Back to top
View user's profile Send private message

Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 186
Location: chennai

PostPosted: Mon May 11, 2009 11:52 pm    Post subject: Reply to: Difference between Open and fetch statements in Cu
Reply with quote

Hi sudhakarreddy.ibm

I think it is OPEN - which uses most CPU time than others.

Regards
Raghu
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue May 12, 2009 12:32 am    Post subject:
Reply with quote

Raghu,

I don't think that you are wrong, but why?
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 186
Location: chennai

PostPosted: Tue May 12, 2009 1:33 am    Post subject: Reply to: Difference between Open and fetch statements in Cu
Reply with quote

Hi Dick

For OPEN- DB2 uses the select statement coded in the Declare Cursor statement to identify the set of row. Then put the rows in the result table.
Before putting the rows in the result table, DB2 has to find out the storage required to accommodate those rows in the result table. Put up a request to allocate the required storage(Bufferpool). The address of the allocated storage is to be passed to the application program. And the cursor is placed before the first row if rows are found as per the declare statement.

If an ORDER BY is specified in the Declare statement, DB2 has to sort the result table before Fetch.

Correct me if I am not correct.

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

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue May 12, 2009 2:03 am    Post subject:
Reply with quote

Raghu,

too much information! kidding aside, excellent answer.

My point was that DB2 on the mainframe and MS Server are different.

Since MS is dealing (with windows, multiple tasks) MS Server will initiate the 'formation of the result set' starting with the OPEN, then return to the application. Idea being, the application can do other things without hanging around for an OPEN CURSOR. The first FETCH will return to the application when the result set is built (or as soon as SQL Server knows what the first rows will be).

DB2 is different in that upon return to the application from the OPEN Sql, the result set has been built.

Now, whether DB2 v9 for the server works the same as mainframe DB2 or MS SQL I don't know. Nor do I know anything about ORACLE.
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 Getting -504 Cursor Name GTT-ARTS-CUR... Robin Sulsona DB2 2 Fri Mar 17, 2017 1:43 am
No new posts Release of Adabas Cursor mohitsethi All Other Mainframe Topics 1 Mon Feb 06, 2017 8:36 pm
No new posts Difference between TWALENG and TWASIZE Arunkumar Chandrasekaran CICS 3 Tue Jan 03, 2017 12:57 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts Sticky: difference between ... DUMMY ... and ... enrico-sorichetti JCL & VSAM 0 Mon Oct 17, 2016 4:31 pm


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