We have a situation while locking a row in a stored procedure.
Our database is running under Oracle compatibility mode in DB2.
The problem is multiple users are trying to select the same set of records from a table. These records are fetched one by one using a cursor.
To overcome this, we are selecting a row and locking it using the FOR UPDATE clause, so that others users will not be able to update this row.
The lock timeout is set to 1, due to which other users are getting the -911 error.
In the next fetch, an error 501(cursor is closed) is coming, to overcome which we are repoening the cursor and fetching the records again.
Is there any way to prevent the cursor from closing on the 911 error ?
The above logic has also been implemented in Oracle 10g by using the FOR UPDATE NO WAIT clause while locking the row.
Is there any equivalent for the NO WAIT option in DB2 which will not result in closure of the cursor ?
You could try to implement an exception handler in the stored procedure that does not roll back the transaction.
The 911 exception is being handled, but still the cursor is getting closed resulting in 501 error for the next fetch.
A point which i need to mention is that the stored procedure is written in oracle and is running under oracle compatibility mode in DB2. The exception handler syntax of oracle is present in the procedure.
With DB2, even if you have an exception handler to catch the -911 (rc 2 or 68) the dbms has already rolled back the transaction. Rollback closes all open cursors. So if you want your cursors to stay opened, choose a design that avoids or very significantly reduces the -911 frequency , by whatever strategy. Additionally if the code assumes that cursors remain opened after -911 then that code has to be re-written.