Stored Procedure Error -Correction

Venkat (PCA) Pillay

Stored Procedure Error -Correction
I am sorry, I wrote RESUME in the last mail - should have been REFRESH

> -----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.