IBM Mainframe Forum

Hi..

Can somebody explain what the below statement means?

Isolation of cursor stability does not mean row level locking. If you define your cursor WITH HOLD, you will hold your locks through COMMIT points and raise the potential for concurrency issues for the duration of your process.

I have got the above reply from my DBA for the suggestion that I provided as below.

Since the ISOLATION LEVEL defined for the plan MSB342T1 is CS, the lock will be held at the row level. Is that not?
So will there be any problem on table level/concurrency issues, on using WITH HOLD option with Isolation level as CS.

Please let me know if I am not clear with my question. I am asking people in this forum as the DBA here is not easy to move with.

Isnt this really a conversation you should be having with your DBA (.. you have one, seems many dont) - if its tough maybe they are just doing their job (and making you think/analyse/research the issue)

Why do you want to use WITH HOLD ?
Why doesnt (s)he not ?

...if you cant argue your corner - you lose!

In the first part he says your statement about with hold and row level locking is wrong
In the second part he points you to the possibility of a problem and tells you why.
i couldn't answer much better
except I think it should say "you will hold some of your locks through COMMIT points"

Actually, we had a cursor at the Group and Sub Group level and each of the members under them should be processed on some criteria. After processing every 1000 records successfully and writing them to another table, we issue a commit. But after issuing commit, the cursors are getting closed. And during fetch, the program fails with -501 Sqlcode. So to avoid that I suggested for declaring the cursor with "WITH HOLD" option. But he says locks will be held through commit points. I am still not clear with this. Because upon processing a row from the cursor, when we move to the next row in the cursor, the lock on the previous row will be released as the Isolation level is CS.

Can some body throw some light on this? Please let me know if I am missing something

Thanks in advance
Prasanna G

everything you say is true.
the difference is without HOLD a commit releases ALL locks, with hold you still lock at least on row/page (the one where you are positioned on)
Sometimes it is important that ALL locks are released, like during a switch-phase of an online reorg.
Depending on how long you are planning on keeping 1 lock on a table, the risk of a failing online reorg gets bigger.

i know its too late to reply as you might have got a solution already.
In you statement, you say you write to another table.
you Cursor must be fine if it is readonly cursor, but what about your other query that inserts or updates. The CS releases the lock on page only if its has not been updated, else it locks it till commit. So if now your cursor is updateable, you run a potential risk of locking all rows(or pages) your cursor has updated, even if isolation is CS.
the next thing you need to look at is the LOCKSIZE Parm in the tablespace create DDL. if its not PAGE (which is most of the time), your DBA is right.
Do let me know what you did finally

The cursor that I used was read only. The insertion happens in a different table. Because we didnt have much time to develop, we went ahead using WITH HOLD for the read only cursor.