MVSFORUMS.com

[Susan_Yang]

I met a deadlock issue when using following logic in my PLI application program. The error is -501 which is caused by deadlock issue when preparing DYN_CURSOR_B. When -911 happens, the CURSOR_A is forced to be closed and SQL is rolled back. Thus -501 happens next time when CURSOR_A is fetched again without being opened.

1. Open CURSOR_A.
2. Fetch
3. Do while SQL = 0
Fetch CURSOR_A
Prepare DYN_CURSOR_B
...
...
END

4. Close CURSOR_A

My question is how to control the program not to enforce the CURSOR_A to be closed when -911 issue happened? So that I can handle the -911 issue by retrying to prepare DYN_CURSOR_B more than once.

Can anybody help me on that? Thanks a lot!!

[jsharon1248]

Your correctly stated that DB2 closes all cursors and issues a ROLLBACK when it issues a -911 sqlcode. Nothing you can do about either after DB2 issues the -911.

It seems strange that you would get a -911 on a PREPARE. Post the DB2 messages; the problem might be something else.

[edkir98]

Try using a hold cursor.
DECLARE CURSOR_A WITH HOLD FOR
_________________
Thanks

[CraigG]

[Sqlcode]

Susan_Yang,

-911 would always CLOSE your cursor.

Try to check if your program has RESTART logic and in the RESTART step you might be missing opening of CURSOR.

Also, as mentioned by jsharon1248, please paste your error messages. It seems that Error is in the LOGICAL flow of program.


Thanks,