To identify whether a cursor is in open or close state

nbalajibe · New User Joined: 28 Nov 2006 Posts: 75 Location: India

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)

enrico-sorichetti · Posted: Sat Nov 03, 2007 7:55 pm

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

TG Murphy · Posted: Mon Nov 05, 2007 7:24 pm

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.

nbalajibe · New User Joined: 28 Nov 2006 Posts: 75 Location: India

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?

dick scherrer · Posted: Mon Nov 05, 2007 10:15 pm

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. . .

TG Murphy · Posted: Tue Nov 06, 2007 1:42 am

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.