Hi,

I need your help resolving an issue for a friend of mine. She is in the process of writting a progarm to delete rows from 4 tables. She has nested cursors in her program, first cursor is to fetch data from a parent table, which will be used to fetch and delete from other Child tables. She has declared her cursors with " WITH HOLD", but when she commits her work on child table and trying to delete from parent table she is getting sql code of -508. How do i go about fixing this issue.

Please help me.

Thanks
Rookie Dba

the -508 means that you are deleting where the cursor is positioned
you should declare the cursor :
declare cursor ... for update
in that case the cursor is really positioned and no buffering is done.

High rookiedba,

if you COMMIT WORK, you must first FETCH a row before you can do a DELETE WHERE CURRENT OF.

so if your logic is:

FETCH CURSOR
loop:
if n DELETES done then COMMIT
DELETE WHERE CURRENT OF CURSOR
FETCH CURSOR
end loop

you will receive an SQLCODE -508


an other reason for -508 might be:
Two Deletes without a new fetch

FETCH CURSOR
DELETE WHERE CURRENT OF CURSOR
DELETE WHERE CURRENT OF CURSOR


third reason might be:
if the row, the cursor currently refers to, is deleted by an other statement ( or even by a cascading delete ).



your problem might also be a logic like:

FETCH parent-cursor
FETCH child-cursor
DELETE WHERE CURRENT OF child-cursor
COMMIT
DELETE WHERE CURRENT OF parent-cursor

as mentioned above, after a commit a new fetch is necessary before issuing a DELETE WHERE CURRENT OF.
So changing the logic might help:

FETCH parent-cursor
FETCH child-cursor
DELETE WHERE CURRENT OF child-cursor
DELETE WHERE CURRENT OF parent-cursor
COMMIT

This kind of problem can often be avoided by replacing one of the cursors (the inner one here) by a plain "DELETE FROM tbl WHERE ....", where the dots are the condition originally in the inner cursor declaration.