Hello guys,
I'm looking for a way to keep cursors open after a COMMIT that were declared implicitly. That means that they were not declared WITH HOLD and will be closed by commiting. It is an application that is transferred from SQLServer, in which this problem is alleviated because other cursors are not closed after a commit by default. I'd be much obliged for an answer even if it's a definitive neative one,

tnx
Sam

You need to be more specific about the application code involved. Please provide details and explain why you cannot add WITH HOLD on the cursor declaration.

Hi, that's because the cursor is opened implicitly, you open a cursor implicitly when only one row is returned by a query.

So we don't use DECLARE CURSOR for that. So Ihope to add a kind of WITH HOLD to any open cursors at the time of COMMIT for any other cursor. I don't know what WITH HOLD does exactly, I mean, which data element, property or whatever is changed by it. If I knew that I could try to set it at the convenient time. Hopefully you get the problem now, otherwise I will explain more.
tnx
Sam

If you get all the rows of the cursor at one time, you don't need WITH HOLD.

You have not explained what exact problem you are having.

WITH HOLD is done at cursor declaration time.

I guess I can't explain the problem very well then, hmm that's a new problem. So here I have an application for db2 LUW. I's a conversion from sql server (doesn't close open cursors on commit by default). DB2 and Oracle do unless you use WITH HOLD with explicitly DECLARE CURSOR. Many users use our app and db at the same time. So it is possible that someone acquires locks with their implicitly opened cursor. I cannot easily change that and explicitly use DECLARE CURSOR instead. So given that there is an implicitly opened cursor and WITH HOLD wasn't used, it may occur that a COMMIT is executed by another process hence closing the implicit cursors and removing their locks. But I want to do the commit and tell the commit not to close other cursors or release their locks.
tnx
Sam

any who explicitly declare cursors - and are read only - should explicitly FOR READ ONLY.

a commit is a Unit-OF-Work thing, not system wide.

deleted message

user B cannot realease locks held by user A and vice-versa
Dave

ok but another process can, or at least another COMMIT that occurs after the implicit cursor has been opened

and I thought all this time that only mainframe rookies did not read manuals or bother to learn fundamentals.

Only user A issuing a commit (implicit or explicit) will release the locks held by user A, the same for user B or any other users.

well I'm not sure you guys are right, I took this project over from someone else who told me that other user's commit will release those locks too and mu testdb will be operational later this week
anyway we found another solution, which is to use a secondary connection to keep those other cursors' locks
it appears that it is also possible to change the behavior of COMMIT but not exactly the way I want it (precompiler options) and I don't know yet how it will affect the rest of the app
so it is rather unsatisfying, even though there is a certain benefit to releasing those other cursors' locks, it appears impossible to alter the behavior of the commit so that it works as if the implicit cursors were declared as DECLARE CURSOR WITH HOLD

WITH HOLD

Maintains resources across multiple units of work. The effect of the cursor attribute is as follows: v For units of work ending with COMMIT: – Open cursors defined WITH HOLD remain open. The cursor is positioned before the next logical row of the results table. If a DISCONNECT statement is issued after a COMMIT statement for a connection with WITH HOLD cursors, the held cursors must be explicitly closed or the connection will be assumed to have performed work (simply by having open WITH HELD cursors even though no SQL statements were issued) and the DISCONNECT statement will fail. – All locks are released, except locks protecting the current cursor position of open WITH HOLD cursors. The locks held include the locks on the table, and for parallel environments, the locks on rows where the cursors are currently positioned. Locks on packages and dynamic SQL sections (if any) are held. – Valid operations on cursors defined WITH HOLD immediately following a COMMIT request are: - FETCH: Fetches the next row of the cursor....