|
View previous topic :: View next topic
|
| Author |
Message |
nal_satish
New User
Joined: 20 Jun 2005
Posts: 26
Location: Hyderabad
|
|
|
|
Hi ,
Can any one help me out regarding
Cursor with hold option
How does it work while using Commit operation
and For RollBack operation.
Syntax please...... |
|
| Back to top |
|
 |
ikumar
New User
Joined: 02 Aug 2005
Posts: 81
|
|
|
|
If you want to hold any cursor, you should have WITH HOLD option in declare cursor.
The syntax is:
EXEC SQL
DECLARE cursor-nameCURSOR WITH HOLD FOR
SELECT ....
END-EXEC.
Suppose in your logic, if you are using commit after certain number of updations/deletions, then cursor will get automatically closed. But you want to retain the cursor till the complete updation process gets over. In that case you have to use WITH HOLD option which prevents your cursor to get closed when commit operation occurs. |
|
| Back to top |
|
|
nal_satish
New User
Joined: 20 Jun 2005
Posts: 26
Location: Hyderabad
|
|
|
|
| My Question is How does it work for ROLLBACK operation |
|
| Back to top |
|
|
ikumar
New User
Joined: 02 Aug 2005
Posts: 81
|
|
|
|
If you issue ROLLBACK, the held cursor will be closed.
Cheers,
Kumar. |
|
| Back to top |
|
|
ak1972
New User
Joined: 25 Aug 2005
Posts: 7
|
|
|
|
| With Hold works only for Commit, does NOT work for rollback - and works only where DB2 is the Transaction manager |
|
| Back to top |
|
|
nal_satish
New User
Joined: 20 Jun 2005
Posts: 26
Location: Hyderabad
|
|
|
|
Ok thanks For Reply
Could u please Confirm this
Suppose i have 1000 Rows
I have used Cursor With Hold
After Updating 100 Records i have issued Commit -----> first Transaction
so the Cursor points to 101 record for next operation ( if i use with hold option)
after processing some(100 (first)+ 50) fifty more rows some problem encountered so i have issued a roll Back ======> Second Transaction
so the Fifty records processed will be roll backed
So when i try to perform open cursor operation after second Transaction
where it will point it will point to the 101 record or
again back to the initial i.e 1 row
Please Clarify me
Sorry if am casuing some inconvinience.....
Thanks & Regards,
Satish. |
|
| Back to top |
|
|
ikumar
New User
Joined: 02 Aug 2005
Posts: 81
|
|
|
|
It starts again from the beginning of the cursor, that is, from the first row onwards....you should have some kind of restrat logic in your application program to start your processing again from the 101th row.
search the forum for the restart logic, if you need it.
Cheers,
Kumar |
|
| Back to top |
|
|
|
|
