Professional Web Applications Themes

Mysterious Error on DELETE - IBM DB2

While running a script to delete some rows from a table, I encountered an error that made no sense in the context in which it was presented. I received the error: SQL0508N The cursor specified in the UPDATE or DELETE statement is not positioned on a row. What is mysterious to me are several points. First I am not using any declared cursors. The DELETE statement was executed from a Korn shell script submitted through the command line interpreter. Second, in the same script I have several other DELETE statements that are identical except for the table from which rows ...

  1. #1

    Default Mysterious Error on DELETE

    While running a script to delete some rows from a table, I encountered
    an error that made no sense in the context in which it was presented.
    I received the error: SQL0508N The cursor specified in the UPDATE or
    DELETE statement
    is not positioned on a row.

    What is mysterious to me are several points. First I am not using any
    declared cursors. The DELETE statement was executed from a Korn shell
    script submitted through the command line interpreter. Second, in the
    same script I have several other DELETE statements that are identical
    except for the table from which rows are being deleted. Those other
    delete statements completed successfully. I checked everything I could
    think of including all constraints, tablespace access, locking, etc.,
    and couldn't find anything.

    My (failed) statement is:

    delete from DB2ADMIN.EWM_TRNSFR_TRGGR
    where item_id in (
    select item_id from db2admin.ewm_case_tran
    where case_id in (
    select case_id from db2admin.ewm_case_tmp
    )
    );

    I have run this statement several times before with no errors. None of
    the suggestions from the error code made any sense in the context I am
    running. I tried running the same statement directly from the command
    line (as the only user on the system) and received the same error.

    I was able to work around this problem by generating an explicit list
    of "item_id's" and deleting them one by one (through another script).
    I encountered no errors during this process, so it appeared to me that
    all the data was accessible and intact.

    My platform is AIX 4.3.3 and version 7.2 FP5. Any insight would be
    very valuable.

    Thanks in advance,
    Evan
    Evan Smith Guest

  2. #2

    Default Re: Mysterious Error on DELETE


    "Evan Smith" <esmith2112hotmail.com> wrote in message
    news:23658335.0309151726.651d463eposting.google.c om...
    > While running a script to delete some rows from a table, I encountered
    > an error that made no sense in the context in which it was presented.
    > I received the error: SQL0508N The cursor specified in the UPDATE or
    > DELETE statement
    > is not positioned on a row.
    >
    > What is mysterious to me are several points. First I am not using any
    > declared cursors. The DELETE statement was executed from a Korn shell
    > script submitted through the command line interpreter. Second, in the
    > same script I have several other DELETE statements that are identical
    > except for the table from which rows are being deleted. Those other
    > delete statements completed successfully. I checked everything I could
    > think of including all constraints, tablespace access, locking, etc.,
    > and couldn't find anything.
    >
    > My (failed) statement is:
    >
    > delete from DB2ADMIN.EWM_TRNSFR_TRGGR
    > where item_id in (
    > select item_id from db2admin.ewm_case_tran
    > where case_id in (
    > select case_id from db2admin.ewm_case_tmp
    > )
    > );
    >
    > I have run this statement several times before with no errors. None of
    > the suggestions from the error code made any sense in the context I am
    > running. I tried running the same statement directly from the command
    > line (as the only user on the system) and received the same error.
    >
    > I was able to work around this problem by generating an explicit list
    > of "item_id's" and deleting them one by one (through another script).
    > I encountered no errors during this process, so it appeared to me that
    > all the data was accessible and intact.
    >
    > My platform is AIX 4.3.3 and version 7.2 FP5. Any insight would be
    > very valuable.
    >
    > Thanks in advance,
    > Evan
    The current level is FP 10 for version 7. I would look at the doc on FP's
    6-10 to see if there is an APAR that matches your problem. You can find a
    list of ulative APAR's in the FP10 doentation.


    Mark A Guest

  3. #3

    Default Re: Mysterious Error on DELETE



    Evan Smith wrote:
    > While running a script to delete some rows from a table, I encountered
    > an error that made no sense in the context in which it was presented.
    > I received the error: SQL0508N The cursor specified in the UPDATE or
    > DELETE statement
    > is not positioned on a row.
    >
    > What is mysterious to me are several points. First I am not using any
    > declared cursors. The DELETE statement was executed from a Korn shell
    > script submitted through the command line interpreter. Second, in the
    > same script I have several other DELETE statements that are identical
    > except for the table from which rows are being deleted. Those other
    > delete statements completed successfully. I checked everything I could
    > think of including all constraints, tablespace access, locking, etc.,
    > and couldn't find anything.
    >
    > My (failed) statement is:
    >
    > delete from DB2ADMIN.EWM_TRNSFR_TRGGR
    > where item_id in (
    > select item_id from db2admin.ewm_case_tran
    > where case_id in (
    > select case_id from db2admin.ewm_case_tmp
    > )
    > );
    >
    > I have run this statement several times before with no errors. None of
    > the suggestions from the error code made any sense in the context I am
    > running. I tried running the same statement directly from the command
    > line (as the only user on the system) and received the same error.
    >
    > I was able to work around this problem by generating an explicit list
    > of "item_id's" and deleting them one by one (through another script).
    > I encountered no errors during this process, so it appeared to me that
    > all the data was accessible and intact.
    The error means:
    select count(*) from db2admin.ewm_trnsfr_trggr
    where item_id in (
    select item_id from db2admin.ewm_case_tran
    where case_id in (
    select case_id from db2admin.ewm_case_tmp
    )
    );
    is equal to 0.

    I wonder before you use the workaround, the other processes inserted a
    couple of rows which happened to qualify the search conditions.
    >
    >
    > My platform is AIX 4.3.3 and version 7.2 FP5. Any insight would be
    > very valuable.
    >
    > Thanks in advance,
    > Evan
    Fan Ruo Xin Guest

  4. #4

    Default Re: Mysterious Error on DELETE

    Evan Smith wrote:
    > While running a script to delete some rows from a table, I encountered
    > an error that made no sense in the context in which it was presented.
    > I received the error: SQL0508N The cursor specified in the UPDATE or
    > DELETE statement
    > is not positioned on a row.
    >
    > What is mysterious to me are several points. First I am not using any
    > declared cursors. The DELETE statement was executed from a Korn shell
    > script submitted through the command line interpreter. Second, in the
    > same script I have several other DELETE statements that are identical
    > except for the table from which rows are being deleted. Those other
    > delete statements completed successfully. I checked everything I could
    > think of including all constraints, tablespace access, locking, etc.,
    > and couldn't find anything.
    >
    > My (failed) statement is:
    >
    > delete from DB2ADMIN.EWM_TRNSFR_TRGGR
    > where item_id in (
    > select item_id from db2admin.ewm_case_tran
    > where case_id in (
    > select case_id from db2admin.ewm_case_tmp
    > )
    > );
    >
    > I have run this statement several times before with no errors. None of
    > the suggestions from the error code made any sense in the context I am
    > running. I tried running the same statement directly from the command
    > line (as the only user on the system) and received the same error.
    >
    > I was able to work around this problem by generating an explicit list
    > of "item_id's" and deleting them one by one (through another script).
    > I encountered no errors during this process, so it appeared to me that
    > all the data was accessible and intact.
    >
    > My platform is AIX 4.3.3 and version 7.2 FP5. Any insight would be
    > very valuable.
    >
    > Thanks in advance,
    > Evan
    I think this is a known problem that should be fixed in the next
    fixpack. A workaround would be do a runstat on your table and the
    problem should give away (hopefully it will cause the compiler to choose
    a different access plan). If not, open a PMR against IBM.

    No Body Guest

  5. #5

    Default Re: Mysterious Error on DELETE

    Fan Ruo Xin <fanruoxsbcglobal.net> wrote in message news:<3F6677EF.9504CE6Asbcglobal.net>...
    >
    > The error means:
    > select count(*) from db2admin.ewm_trnsfr_trggr
    > where item_id in (
    > select item_id from db2admin.ewm_case_tran
    > where case_id in (
    > select case_id from db2admin.ewm_case_tmp
    > )
    > );
    > is equal to 0.
    >
    > I wonder before you use the workaround, the other processes inserted a
    > couple of rows which happened to qualify the search conditions.
    >
    Thanks for your input. I think that it actually is not the case. To
    generate my manual list of deletes I executed:

    select ITEM_ID from db2admin.ewm_trnsfr_trggr
    where item_id in (
    select item_id from db2admin.ewm_case_tran
    where case_id in (
    select case_id from db2admin.ewm_case_tmp
    )
    );

    and got 3500 ITEM_IDs back. I wrapped a 'DELETE FROM
    DB2ADMIN.EWM_TRNSFR_TRGGER WHERE ITEM_ID =' statement around each
    ITEM_ID returned and ran the delete script with 3500 success
    statements (no errors or warnings).

    Adressing Mark's comments, I did search the APARs and other technical
    sources on IBM's website and found nothing. I know FP5 has some
    problems, but I didn't see anything specific addressing this issue in
    any of the upgrades. (We're in the process of converting to version
    8.)

    I was just stumped trying to figure why DB2 was throwing this error in
    the context I was running. And even moreso when I was successfully
    able to complete the delete by building an explicit list of delete
    statements. It was an error I had never seen in over 6 years of doing
    this. Since we're still a few weeks out from the actual version 8
    upgrade, I was hoping to solve this before my next production run.
    Guess it's time to call my bodacious Big Blue bug-fix buddies.

    Thanks again for your input,
    Evan
    Evan Smith Guest

  6. #6

    Default Re: Mysterious Error on DELETE

    I have validated now that this is a bug with FP5. Was able to
    duplicate the same error on another FP5 server, and saw the error
    eliminated on a server with FP8. Still can't find any matching
    reference in the APARs for this.

    Regards,
    Evan
    Evan Smith Guest

  7. #7

    Default Re: Mysterious Error on DELETE

    "Evan Smith" <esmith2112hotmail.com> wrote in message
    news:23658335.0309161346.2abf6774posting.google.c om...
    > I have validated now that this is a bug with FP5. Was able to
    > duplicate the same error on another FP5 server, and saw the error
    > eliminated on a server with FP8. Still can't find any matching
    > reference in the APARs for this.
    >
    > Regards,
    > Evan
    Not all fixes have APAR's.


    Mark A Guest

Similar Threads

  1. Error in delete file using NET:FTP module
    By shellyshao in forum PERL Modules
    Replies: 3
    Last Post: February 14th, 11:18 PM
  2. SQL error in DELETE statement
    By stillwaiting in forum Coldfusion Database Access
    Replies: 2
    Last Post: November 19th, 04:03 AM
  3. Replies: 13
    Last Post: January 4th, 02:37 PM
  4. error when trying to delete object
    By Mikael Hellström in forum ASP Database
    Replies: 3
    Last Post: April 19th, 03:41 PM
  5. Delete error message problem
    By carlee in forum Microsoft Access
    Replies: 2
    Last Post: July 19th, 01:54 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139