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" <esmith2112@hotmail.com> wrote in message
news:23658335.0309151726.651d463e@posting.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 cumulative APAR's in the FP10 documentation.

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

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.

Fan Ruo Xin <fanruox@sbcglobal.net> wrote in message news:<3F6677EF.9504CE6A@sbcglobal.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

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" <esmith2112@hotmail.com> wrote in message
news:23658335.0309161346.2abf6774@posting.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.