Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 10374
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 -502 Error - Cursor already open anand jeyapaul DB2 7 Sun Oct 29, 2017 4:22 am
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Reply MQ open error Vignesh Sid COBOL Programming 6 Thu Sep 07, 2017 6:02 pm
No new posts Could we identify the called modules ... elixir1986 COBOL Programming 5 Fri Jul 14, 2017 1:53 am
No new posts Identify top 30 big tables or indexes... ashek15 DB2 0 Fri Jun 16, 2017 10:01 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us