Native SQL Stored Procedure abend when DELETE follows OPEN CURSOR statements.

Mark Stone

Native SQL Stored Procedure abend when DELETE follows OPEN CURSOR statements.
I have an existing SP that I'm migrating to Native SQL. It runs fine in
external mode but actually causes a DB2 abend when compiled and executed in
Native mode. I tracked the offending statement down to a DELETE statement
that follows a number of OPEN CURSOR statements. The intention being that
the delete does not occur until the client (in this case VB.NET) application
consumes the cursor data. The delete is a cleanup process for a table used
to communicate between the application and the SP.

If the DELETE is moved ahead of the OPEN CURSOR statements, the SP executes
to completion.

Has anyone else experienced such a weird issue, I do plan on providing the
information to IBM, but figured I'd float the issue here first to see if
anyone has similar issues.

Thanks

Mark Stone

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Todd Burch

Re: Native SQL Stored Procedure abend when DELETE follows OPEN CURSOR statements.
(in response to Mark Stone)
I'd suggest opening a PMR and providing the ABEND information. If
you would have placed it here, I'd have done a search for you
already. :)

Todd


On Dec 1, 2009, at 6:23 PM, Mark Stone wrote:

I have an existing SP that I'm migrating to Native SQL. It runs fine
in external mode but actually causes a DB2 abend when compiled and
executed in Native mode. I tracked the offending statement down to a
DELETE statement that follows a number of OPEN CURSOR statements. The
intention being that the delete does not occur until the client (in
this case VB.NET) application consumes the cursor data. The delete is
a cleanup process for a table used to communicate between the
application and the SP.

If the DELETE is moved ahead of the OPEN CURSOR statements, the SP
executes to completion.

Has anyone else experienced such a weird issue, I do plan on providing
the information to IBM, but figured I'd float the issue here first to
see if anyone has similar issues.

Thanks

Mark Stone



The IDUG DB2-L Listserv is only part of your membership in IDUG. If
you are not already an IDUG member, please register here.



_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Robert Catterall

Re: Native SQL Stored Procedure abend when DELETE follows OPEN CURSOR statements.
(in response to Todd Burch)
Does the DELETE statement in the stored procedure target any of the tables
that are targeted by one or more of the OPEN CURSOR statements in the same
stored procedure?

Robert


On Tue, Dec 1, 2009 at 7:23 PM, Mark Stone <[login to unmask email]> wrote:

> I have an existing SP that I'm migrating to Native SQL. It runs fine in
> external mode but actually causes a DB2 abend when compiled and executed in
> Native mode. I tracked the offending statement down to a DELETE statement
> that follows a number of OPEN CURSOR statements. The intention being that
> the delete does not occur until the client (in this case VB.NET)
> application consumes the cursor data. The delete is a cleanup process for a
> table used to communicate between the application and the SP.
>
> If the DELETE is moved ahead of the OPEN CURSOR statements, the SP executes
> to completion.
>
> Has anyone else experienced such a weird issue, I do plan on providing the
> information to IBM, but figured I'd float the issue here first to see if
> anyone has similar issues.
>
> Thanks
>
> Mark Stone
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community! ] < http://www.idug.org/db2-north-america-conference/index.html >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>



--
Robert Catterall
Catterall Consulting
www.catterallconsulting.com

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Mark Stone

Re: Native SQL Stored Procedure abend when DELETE follows OPEN CURSOR statements.
(in response to Robert Catterall)
No

Sent from my iPhone

On Dec 3, 2009, at 7:15 AM, Robert Catterall <[login to unmask email]>
wrote:

> Does the DELETE statement in the stored procedure target any of the
> tables that are targeted by one or more of the OPEN CURSOR
> statements in the same stored procedure?
>
> Robert
>
>
> On Tue, Dec 1, 2009 at 7:23 PM, Mark Stone <[login to unmask email]> wrote:
> I have an existing SP that I'm migrating to Native SQL. It runs fine
> in external mode but actually causes a DB2 abend when compiled and
> executed in Native mode. I tracked the offending statement down to a
> DELETE statement that follows a number of OPEN CURSOR statements.
> The intention being that the delete does not occur until the client
> (in this case VB.NET) application consumes the cursor data. The
> delete is a cleanup process for a table used to communicate between
> the application and the SP.
>
> If the DELETE is moved ahead of the OPEN CURSOR statements, the SP
> executes to completion.
>
> Has anyone else experienced such a weird issue, I do plan on
> providing the information to IBM, but figured I'd float the issue
> here first to see if anyone has similar issues.
>
> Thanks
>
> Mark Stone
>
>
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If
> you are not already an IDUG member, please register here.
>
>
>
>
> --
> Robert Catterall
> Catterall Consulting
> www.catterallconsulting.com
>
>
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If
> you are not already an IDUG member, please register here.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Raymond Bell

Re: Native SQL Stored Procedure abend when DELETE follows OPEN CURSOR statements.
(in response to Mark Stone)
<so_off-topic_it_hurts> Speaking of iPhones... http://www.youtube.com/watch?v=tUreshlWqNM </so_off-topic_it_hurts>

At least I waited ‘til Friday...


Raymond


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of [login to unmask email]
Sent: 03 December 2009 15:40
To: [login to unmask email]
Subject: Re: [DB2-L] Native SQL Stored Procedure abend when DELETE follows OPEN CURSOR statements.

No

Sent from my iPhone