Difference between Open and fetch statements in Cursor

sudhakarreddy.ibm · New User Joined: 20 Apr 2009 Posts: 15 Location: chennai

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

Raghu navaikulam · Posted: Mon May 11, 2009 11:52 pm

Hi sudhakarreddy.ibm

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

Regards
Raghu

dbzTHEdinosauer · Posted: Tue May 12, 2009 12:32 am

Raghu,

I don't think that you are wrong, but why?

Raghu navaikulam · Posted: Tue May 12, 2009 1:33 am

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

dbzTHEdinosauer · Posted: Tue May 12, 2009 2:03 am

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.