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
 

 

function of Open cursor statement

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

New User


Joined: 24 Feb 2008
Posts: 48
Location: Kolkata

PostPosted: Tue Mar 25, 2008 4:31 pm    Post subject: function of Open cursor statement
Reply with quote

Hi,

could anyone please explain me the function of the OPEN CURSOR statement.
Apart from opening the cursor does it build the resultant table also?
Do we need to populate all the host variable values (that are used in the WHERE clause) before we open the cursor? If yes then If I am reading a file and fetching data from table do I need to open and close the cursor for each read? does not it reduces the program performance? Is there any work around?

thank you in advance
Back to top
View user's profile Send private message

manikant pathak

New User


Joined: 09 May 2005
Posts: 37
Location: bangalore

PostPosted: Tue Mar 25, 2008 5:07 pm    Post subject: Re: function of Open cursor statement
Reply with quote

abhishek mitra wrote:


could anyone please explain me the function of the OPEN CURSOR statement.
Apart from opening the cursor does it build the resultant table also? -

This statement open the cursor specified and build the resultant table. When you do FETCH the row from the result table are fetched to you one by one or multiple row (V8 and above) depending on the declaration of cursor.
Quote:

Do we need to populate all the host variable values (that are used in the WHERE clause) before we open the cursor?
- Not necessarily but if you will not initialize the host variable it may not get you the correct result.

Quote:

If yes then If I am reading a file and fetching data from table do I need to open and close the cursor for each read? does not it reduces the program performance? Is there any work around?
- If you are reading the value to be passed to the host variable from the file then probably this is the only way. Yes it does reduces the program performance but the only other way which I can think of is that if you know the max and min value of the host variables (getting read from the table) then you declare the cursor as

DECLARE CURSOR C1 FOR <SELECT QUERY> WHERE COL-1 BETWEEN :MIN-VALUE AND :MAX-VALUE;

It will select all the rows from the table and then you can put the Fetch query in a loop till sqlcode=+100 after which you will close the cursor.

thank you in advance[/quote]
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Mar 25, 2008 6:03 pm    Post subject:
Reply with quote

'IT DEPENDS'.


abhishek mitra,

you really have not explained what you are doing.

Does each input qsam record bring values that are unique and that require multiple rows from db2 to complete the business requirement?

what is the relationship between qsam and db2?

as far as a basic answer to your question: when you 'OPEN' a cursor, you are telling db2 to resolve your where clause with values contained in your host variables. If the values in the host variables are not initialized or set to a particular value, the result set may not be what you need.

You use a cursor when you need more than one row to satifisy a business requirement.

If you only need one row, either forget the cursor and do singleton selects or insure that the order of you cursor is the same as your input qsam and perform match logic.

Not knowing the effect of populating or not-populating host variables of a where clause prior to opening a cursor displays a fundemental lack of understanding.
Back to top
View user's profile Send private message
abhishek mitra

New User


Joined: 24 Feb 2008
Posts: 48
Location: Kolkata

PostPosted: Tue Mar 25, 2008 6:26 pm    Post subject:
Reply with quote

dbzTHEdinosauer wrote:
'IT DEPENDS'.


abhishek mitra,

you really have not explained what you are doing.

Does each input qsam record bring values that are unique and that require multiple rows from db2 to complete the business requirement?

what is the relationship between qsam and db2?

as far as a basic answer to your question: when you 'OPEN' a cursor, you are telling db2 to resolve your where clause with values contained in your host variables. If the values in the host variables are not initialized or set to a particular value, the result set may not be what you need.

You use a cursor when you need more than one row to satifisy a business requirement.

If you only need one row, either forget the cursor and do singleton selects or insure that the order of you cursor is the same as your input qsam and perform match logic.

Not knowing the effect of populating or not-populating host variables of a where clause prior to opening a cursor displays a fundemental lack of understanding.


Yes each of my QSAM record brings new /unique data and that need to be used to fetch one/multiple row(s) from the table.
and my intention was to know is there any work around rather than opening and closing the cursor for each record because that reduces program performance?
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 7 Fri May 19, 2017 9:35 pm
No new posts Getting -504 Cursor Name GTT-ARTS-CUR... Robin Sulsona DB2 2 Fri Mar 17, 2017 1:43 am
No new posts ROUNDED Problem with COMPUTE statement shalem COBOL Programming 11 Thu Feb 09, 2017 8:16 pm
No new posts VALIDATE NULL VALUE IN DB2 CASE STATE... useit DB2 5 Thu Feb 09, 2017 4:34 pm
No new posts Release of Adabas Cursor mohitsethi All Other Mainframe Topics 1 Mon Feb 06, 2017 8:36 pm


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