Stored Procedure Error

Rick McClendon

Stored Procedure Error
The story of the Run away Procedure-

I have the procedure defined in SYSPROCEDURES
I have the procedure bound into a collection/plan
I have queried the catalog, the package is listed within the collection, and
the collection is listed for the plan. So all the packages within the
collection should be found in that plan.
I have (in the past month) ran the procedure passing the proper IN/OUT parm
and returned the single result set desired

The problem-
I have changed the IN/OUT parm area INTERNAL structure, not the length, just
the internal structure and I recompiled both the procedure and the calling
program to pass the same size PARM but with The new internal structure.

I recompiled the Cobol calling program with the new IN/OUT parm area.
Everything compiles fine.
I do a STOP PROCEDURE / START PROCEDURE to refresh the DB2 Established
address space

Then I execute the Cobol program that calls the stored procedure, thinking
it will work fine.
BUT, it executes the calling program and gets to and performs the S.P. call
statement, but never actually performs any statements within the stored
procedure. I go into INSIGHT and view the thread as it "hangs" out there
executing, and it seems to loop on some sort of INSERT statement with an
SQLCODE of -805. My stored procedure doesn't even have an INSERT statement
in it. I must manually cancel the Cobol calling program to kill the run-away
thread!

I have tried binding directly to the plan, instead of the collection with no
success.

Any Idea what is going on here?

Thanks!
Rick.

Venkat (PCA) Pillay

Re: Stored Procedure Error
(in response to Rick McClendon)
Rick

Do you give control to WLM to start/stop the SPAS ? In this case,
you have to issue
VARY APPLENV=xxx,RESUME operator command so that SPAS can refresh load
library.

HTH
Pillay

> -----Original Message-----
> From: McClendon, Rick [SMTP:[login to unmask email]
> Sent: Friday, October 08, 1999 9:49 AM
> To: [login to unmask email]
> Subject: Stored Procedure Error
>
> The story of the Run away Procedure-
>
> I have the procedure defined in SYSPROCEDURES
> I have the procedure bound into a collection/plan
> I have queried the catalog, the package is listed within the collection,
> and
> the collection is listed for the plan. So all the packages within the
> collection should be found in that plan.
> I have (in the past month) ran the procedure passing the proper IN/OUT
> parm
> and returned the single result set desired
>
> The problem-
> I have changed the IN/OUT parm area INTERNAL structure, not the length,
> just
> the internal structure and I recompiled both the procedure and the calling
> program to pass the same size PARM but with The new internal structure.
>
> I recompiled the Cobol calling program with the new IN/OUT parm area.
> Everything compiles fine.
> I do a STOP PROCEDURE / START PROCEDURE to refresh the DB2 Established
> address space
>
> Then I execute the Cobol program that calls the stored procedure, thinking
> it will work fine.
> BUT, it executes the calling program and gets to and performs the S.P.
> call
> statement, but never actually performs any statements within the stored
> procedure. I go into INSIGHT and view the thread as it "hangs" out there
> executing, and it seems to loop on some sort of INSERT statement with an
> SQLCODE of -805. My stored procedure doesn't even have an INSERT statement
> in it. I must manually cancel the Cobol calling program to kill the
> run-away
> thread!
>
> I have tried binding directly to the plan, instead of the collection with
> no
> success.
>
> Any Idea what is going on here?
>
> Thanks!
> Rick.