Results 1 to 4 of 4

Thread: Help with -508

  1. #1
    Join Date
    Oct 2006
    Posts
    1

    Unhappy Unanswered: Help with -508

    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

  2. #2
    Join Date
    Jun 2006
    Posts
    471

    hold

    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.
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Dec 2005
    Posts
    273
    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
    Last edited by umayer; 10-27-06 at 06:00.

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •