|
View previous topic :: View next topic
|
| Author |
Message |
stly
Warnings : 1
New User
Joined: 25 Jul 2005
Posts: 93
|
|
|
|
I have a read onlycursor.
After fetching the record i have to update another table.After updating the record i do not have any commit statement.Di it will effect the cursor.I mean the cursor will be closed or will be in normal state.
I mean do i need to declare cursor with hold option. |
|
| Back to top |
|
 |
the_gautam
Active User
Joined: 05 Jun 2005
Posts: 165
Location: Bangalore
|
|
|
|
| If you are not committing the Cursor1 and you are updating a table which is not related to Cursor1 then, i dont see any need of declaring the Cursor1 with HOLD option. |
|
| Back to top |
|
|
stly
Warnings : 1
New User
Joined: 25 Jul 2005
Posts: 93
|
|
|
|
| the_gautam wrote: |
| If you are not committing the Cursor1 and you are updating a table which is not related to Cursor1 then, i dont see any need of declaring the Cursor1 with HOLD option. |
U mean that After Update if do not give commit then No need to declare teh cursor with hold option.
Suppose in taht case if any abend occured during the cursor process u mena that all the updated records are rolled back.. |
|
| Back to top |
|
|
the_gautam
Active User
Joined: 05 Jun 2005
Posts: 165
Location: Bangalore
|
|
|
|
| After reading your post, i thought you are talking about 2 different tables. Table1 is updated and the cursor is declared for Table2. thats why i said that the cursor for table2 has nothing to do with table1. |
|
| Back to top |
|
|
vasanthkumarhb
Active User
Joined: 06 Sep 2007
Posts: 275
Location: Bang,iflex
|
|
|
|
Hi,
Adding to Gautam post,
you are post is little bit confusing, see you are fetching read only table and move to the respective host variables and updating the another table.
Cursor can be declared for one table not for multiple tables at a time, you can update another table with out any worry, if you want to use with hold option you can use, it just keeps cursor to be opened after close cursor command.
But after close cursor command all the change made to table is committed. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
So as not to flame anyone directly:
CURSORs can be based on one table, two,3 or many. The same limit of number of tables referenced in a select statement is also applied to a cursor. You can not define an UPDATE cursor with and explicit or implicit JOIN.
A COMMIT affects every table in your Unit-of-Work.
If you issue COMMITs while using a CURSOR (and one should if the cursor will be large - more than several thousand rows), you need to use the WITH HOLD option, to prevent the cursor from closing at COMMIT.
ONLY a COMMIT can make changes permanent. CLOSEing a CURSOR only releases the result table, has no affect on db2's decision to make changes permenant or not. |
|
| Back to top |
|
|
stly
Warnings : 1
New User
Joined: 25 Jul 2005
Posts: 93
|
|
|
|
Actually my doubt is when the records will be committed if i do not specify anf commit.
ie after the update statement or after closing the cursor. |
|
| Back to top |
|
|
stly
Warnings : 1
New User
Joined: 25 Jul 2005
Posts: 93
|
|
|
|
| Can any one throw some lite on this.. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
stly,
first of all, records have nothing to do with DB2 - rows.
and it is light not lite.
second, if you do not have an explicit COMMIT in your run-unit, then MVS will cause db2 to be called at successful eoj and db2 will issue a commit. |
|
| Back to top |
|
|
stly
Warnings : 1
New User
Joined: 25 Jul 2005
Posts: 93
|
|
|
|
| dbzTHEdinosauer wrote: |
stly,
first of all, records have nothing to do with DB2 - rows.
and it is light not lite.
second, if you do not have an explicit COMMIT in your run-unit, then MVS will cause db2 to be called at successful eoj and db2 will issue a commit. |
Thanks Dick.... thanks for correction also  |
|
| Back to top |
|
|
|
|
