|
View previous topic :: View next topic
|
| Author |
Message |
abhishek mitra
New User
Joined: 24 Feb 2008
Posts: 48
Location: Kolkata
|
|
|
|
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 |
|
 |
manikant pathak
New User
Joined: 09 May 2005
Posts: 37
Location: bangalore
|
|
|
|
| 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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
'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 |
|
|
abhishek mitra
New User
Joined: 24 Feb 2008
Posts: 48
Location: Kolkata
|
|
|
|
| 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 |
|
|
|
|
