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
 

 

To identify whether a cursor is in open or close state

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

New User


Joined: 28 Nov 2006
Posts: 75
Location: India

PostPosted: Sat Nov 03, 2007 7:07 pm    Post subject: To identify whether a cursor is in open or close state
Reply with quote

Hi,

How can we identify whether a particular cursor is in open or close state before issuing a fetch call to the cursor? (Something similar to %isopen in sql)
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10211
Location: italy

PostPosted: Sat Nov 03, 2007 7:55 pm    Post subject: Reply to: To identify whether a cursor is in open or close s
Reply with quote

By writing a program in the way it should...

Application/business programming is deterministic, not interrupt driven,
so at any stage of processing a program should always know the state of its resources ...

but for a badly written program, checking the sql status code AFTER the fetch
will give You the needed info
Back to top
View user's profile Send private message
TG Murphy

Active User


Joined: 23 Mar 2007
Posts: 149
Location: Ottawa Canada

PostPosted: Mon Nov 05, 2007 7:24 pm    Post subject:
Reply with quote

And resist the urge to use a variable that tracks the cursor status.

This can lead to troubles. We make the incorrect assumption that EXEC SQL CLOSE is the only way to close a cursor. However, a cursor can be closed if the transaction issues a ROLLBACK. When this happens your variable will still think the cursor is open.

I have seen this happen.
Back to top
View user's profile Send private message
nbalajibe
Warnings : 1

New User


Joined: 28 Nov 2006
Posts: 75
Location: India

PostPosted: Mon Nov 05, 2007 7:56 pm    Post subject:
Reply with quote

Hi,

In my case there is no Rollback performed in the chain since only my component is DB2.

In this case can I use a variable to track the status of the cursor?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Nov 05, 2007 10:15 pm    Post subject:
Reply with quote

Hello,

It would be better not doing this.

One day someone else may need to change the code and insert a rollback. Then, there may be an ugly surprise. . .

If you issue a fetch, and the cursor is not open i believe you will get a "-501" sqlcode. This should work in any situation.

Also, if a commit is issued, the cursor is closed. There are several sqlcodes that also close the cursor. . .
Back to top
View user's profile Send private message
TG Murphy

Active User


Joined: 23 Mar 2007
Posts: 149
Location: Ottawa Canada

PostPosted: Tue Nov 06, 2007 1:42 am    Post subject:
Reply with quote

Bbalajibe,

You are using DB2. Ask your DBA whether your DB2 has been configured to issue an automatic ROLLBACK in the event of SQLCODE -911 Deadlock/Timeout.

Where I work - DB2 responds to -911 by issuing a rollback.

So even though your code does not contain any rollback commands you can still get a rollback.

Of course this all depends on how your program responds to a -911. Does your program abend? Or does it resume processing? If it resumes processing then you have to worry about this issue.

If your logic happens to be a reusable sub-program that others call - then you have to assume that some of these callers will respond to -911 by resuming normal processing.
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 FTOPEN TEMP is failing due to dataset... mbenaud TSO/ISPF 7 Thu Jul 28, 2016 4:28 pm
No new posts Variable for cursor names ravikumar15 DB2 5 Sun Jul 17, 2016 7:08 am
No new posts Identify error records krsenthil85 DFSORT/ICETOOL 11 Thu Apr 28, 2016 9:45 pm
This topic is locked: you cannot edit posts or make replies. Secure Browse to open dataset sandip_mainframe TSO/ISPF 3 Tue Apr 26, 2016 11:18 am


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