|
View previous topic :: View next topic
|
| Author |
Message |
rakesh17684
New User
Joined: 08 Oct 2006
Posts: 61
Location: San Diego
|
|
|
|
Hi,
Just wanted to know which will be the better option in a cobol db2 program,in relevance to performance.
option A:
1. declare a cursor for a select query say A for a table 1.
2. open the cursor.
3. fetch a record.
4. update the same record with some new values then commit.
5. insert new record with few manipulations to fetched record then commit
6.close the cursor
7. repeat the above steps from 2 to 5.
8. close the cursor when no more records are available.
Now there might be a possibility that the record inserted might qualify for the query declared by the cursor so hence step 6 & 7. correct me if i am wrong on this.
option b
1. execute a static SQL for select query A for table 1, with fetch first 1 row only.
2. update table 1 ( for the same record returned by the select query) with some new values. then commit.
3.insert new record with few manipulations to fetched record. then commit.
4. repeat above steps till no more records are available. |
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
I would look at the different cursor options - scrollable, sensitive, etc...
and I would not commit after every update, or repeatedly close and open a cursor. There is no need to do so (after your research of above, you will understand). |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Quote: |
| 5. insert new record with few manipulations to fetched record then commit |
why not insure that one of these "manipulations" disqualifies the new ROW?
knowing and using the correct terminology will help in asking questions and reading/understanding documentation. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
If you were worried that a newly inserted row would become part of the cursor,
Why would closing the cursor and reopening prevent this?
you really need to read about cursor handling, concepts and options available
for your version of db2. |
|
| Back to top |
|
|
rakesh17684
New User
Joined: 08 Oct 2006
Posts: 61
Location: San Diego
|
|
|
|
its all about handling any given situation,
| Quote: |
| why not insure that one of these "manipulations" disqualifies the new ROW? |
business rules/data doesnt allow me.as far as the data we have now it might not happen.. but i am just making sure the program is foolproof.
| Quote: |
If you were worried that a newly inserted row would become part of the cursor,
Why would closing the cursor and reopening prevent this? |
with the knowledge of cursors, i had till yesterday. once i fetch a record and insert into the target table a new row, the next fetch wouldnt retrieve me the inserted row since it fetches from the temporary table.
but thanks for widening the knowledge on cursors i am using the other option now |
|
| Back to top |
|
|
|
|
