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
 

 

Regarding Cursor withhold option

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

New User


Joined: 20 Jun 2005
Posts: 26
Location: Hyderabad

PostPosted: Fri Sep 30, 2005 12:21 pm    Post subject: Regarding Cursor withhold option
Reply with quote

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
View user's profile Send private message

ikumar

New User


Joined: 02 Aug 2005
Posts: 81

PostPosted: Fri Sep 30, 2005 12:48 pm    Post subject: Re: Regarding Cursor withhold option
Reply with quote

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
View user's profile Send private message
nal_satish

New User


Joined: 20 Jun 2005
Posts: 26
Location: Hyderabad

PostPosted: Fri Sep 30, 2005 4:20 pm    Post subject:
Reply with quote

My Question is How does it work for ROLLBACK operation
Back to top
View user's profile Send private message
ikumar

New User


Joined: 02 Aug 2005
Posts: 81

PostPosted: Fri Sep 30, 2005 5:37 pm    Post subject: Re: Regarding Cursor withhold option
Reply with quote

If you issue ROLLBACK, the held cursor will be closed.

Cheers,
Kumar.
Back to top
View user's profile Send private message
ak1972

New User


Joined: 25 Aug 2005
Posts: 7

PostPosted: Sat Oct 01, 2005 4:25 am    Post subject: Re: Regarding Cursor withhold option
Reply with quote

With Hold works only for Commit, does NOT work for rollback - and works only where DB2 is the Transaction manager
Back to top
View user's profile Send private message
nal_satish

New User


Joined: 20 Jun 2005
Posts: 26
Location: Hyderabad

PostPosted: Sat Oct 01, 2005 11:01 am    Post subject:
Reply with quote

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
View user's profile Send private message
ikumar

New User


Joined: 02 Aug 2005
Posts: 81

PostPosted: Sat Oct 01, 2005 1:46 pm    Post subject: Re: Regarding Cursor withhold option
Reply with quote

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
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 Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts BWO option in VSAM blayek CICS 3 Sat Nov 05, 2016 10:47 am
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm
No new posts Variable for cursor names ravikumar15 DB2 5 Sun Jul 17, 2016 7:08 am
No new posts Compiler option for override the exte... muralikrishnan_new COBOL Programming 9 Fri Jul 08, 2016 12:09 pm


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