C stored procedure SQL error.

[Craig]

I have a very basic C stored procedure written. It was working fine
with some bogus/kludge SQL (just to get a DBRM created).

I have now added the following to the SP, although it does nothing
yet:

int getInputCount (char* timeStamp)
{
/************************ Local Variables */
EXEC SQL BEGIN DECLARE SECTION;
long int count = 0;
EXEC SQL END DECLARE SECTION;

/************************* Function Start */
sprintf (varChar.stmt,
"SELECT COUNT(*) FROM BCAD.GLTT0 WHERE TT_TS = %s",
timeStamp);
varChar.len = strlen (varChar.stmt);

EXEC SQL PREPARE countStmt FROM :varChar;

/* Commented out more SQL statements here ... */

return count;
}


This compiles, prelinks, link-edits, binds fine (I think). But when
run thru a driver program I am getting the following SQL error from
the stored proc. I am capturing data from the sqlca struct
immediately after the EXEC SQL PREPARE statement.

I get:
ERR MSG = BCADDB2..CWFUTT01.1736263E13F97AC8 CVTUV001 01
SQL CODE = -805 which should say (expanded):
DBRM OR PACKAGE NAME BCADDB2..CWFUTT01.1736263E13F97AC8 NOT FOUND IN
PLAN CVTUV001. REASON 01. If this is true, why is the collection-id
part empty?

Any ideas on what is wrong? I have the stored proc bound into a DB2
package, I have the stored proc (loadlib output) bound into a DB2
plan. I run with a command that looks like:
RUN PROG(DRIVERVT) PLAN(CVTUV001) where my stored proc is bound into
the CVTUV001 DB2 plan.

What is that PREPARE statement doing to cause this error?

[Sean McKeough]

You're probably running with the old versoin of the library
(cached)...either build the new copy to a differently named lib, and use
alter procedure to change the external name, or bounce the instance.

Craig wrote:

> I have a very basic C stored procedure written. It was working fine
> with some bogus/kludge SQL (just to get a DBRM created).
>
> I have now added the following to the SP, although it does nothing
> yet:
>
> int getInputCount (char* timeStamp)
> {
> /************************ Local Variables */
> EXEC SQL BEGIN DECLARE SECTION;
> long int count = 0;
> EXEC SQL END DECLARE SECTION;
>
> /************************* Function Start */
> sprintf (varChar.stmt,
> "SELECT COUNT(*) FROM BCAD.GLTT0 WHERE TT_TS = %s",
> timeStamp);
> varChar.len = strlen (varChar.stmt);
>
> EXEC SQL PREPARE countStmt FROM :varChar;
>
> /* Commented out more SQL statements here ... */
>
> return count;
> }
>
>
> This compiles, prelinks, link-edits, binds fine (I think). But when
> run thru a driver program I am getting the following SQL error from
> the stored proc. I am capturing data from the sqlca struct
> immediately after the EXEC SQL PREPARE statement.
>
> I get:
> ERR MSG = BCADDB2..CWFUTT01.1736263E13F97AC8 CVTUV001 01
> SQL CODE = -805 which should say (expanded):
> DBRM OR PACKAGE NAME BCADDB2..CWFUTT01.1736263E13F97AC8 NOT FOUND IN
> PLAN CVTUV001. REASON 01. If this is true, why is the collection-id
> part empty?
>
> Any ideas on what is wrong? I have the stored proc bound into a DB2
> package, I have the stored proc (loadlib output) bound into a DB2
> plan. I run with a command that looks like:
> RUN PROG(DRIVERVT) PLAN(CVTUV001) where my stored proc is bound into
> the CVTUV001 DB2 plan.
>
> What is that PREPARE statement doing to cause this error?

[Craig]

Sean:

Thank you - bouncing DB2 did indeed work. We now think just executing
a STOP/START PROCEDURE in the future will work, or in the worst case,
bouncing the stored procedure address space (SPAS) will alleviate that
problem.


Sean McKeough <mckeough@nospam.ca.ibm.com> wrote in message news:<bekf6h$nae$2@hanover.torolab.ibm.com>...

> You're probably running with the old versoin of the library
> (cached)...either build the new copy to a differently named lib, and use
> alter procedure to change the external name, or bounce the instance.
>
> Craig wrote:
>

> > I have a very basic C stored procedure written. It was working fine
> > with some bogus/kludge SQL (just to get a DBRM created).
> >
> > I have now added the following to the SP, although it does nothing
> > yet:
> >
> > int getInputCount (char* timeStamp)
> > {
> > /************************ Local Variables */
> > EXEC SQL BEGIN DECLARE SECTION;
> > long int count = 0;
> > EXEC SQL END DECLARE SECTION;
> >
> > /************************* Function Start */
> > sprintf (varChar.stmt,
> > "SELECT COUNT(*) FROM BCAD.GLTT0 WHERE TT_TS = %s",
> > timeStamp);
> > varChar.len = strlen (varChar.stmt);
> >
> > EXEC SQL PREPARE countStmt FROM :varChar;
> >
> > /* Commented out more SQL statements here ... */
> >
> > return count;
> > }
> >
> >
> > This compiles, prelinks, link-edits, binds fine (I think). But when
> > run thru a driver program I am getting the following SQL error from
> > the stored proc. I am capturing data from the sqlca struct
> > immediately after the EXEC SQL PREPARE statement.
> >
> > I get:
> > ERR MSG = BCADDB2..CWFUTT01.1736263E13F97AC8 CVTUV001 01
> > SQL CODE = -805 which should say (expanded):
> > DBRM OR PACKAGE NAME BCADDB2..CWFUTT01.1736263E13F97AC8 NOT FOUND IN
> > PLAN CVTUV001. REASON 01. If this is true, why is the collection-id
> > part empty?
> >
> > Any ideas on what is wrong? I have the stored proc bound into a DB2
> > package, I have the stored proc (loadlib output) bound into a DB2
> > plan. I run with a command that looks like:
> > RUN PROG(DRIVERVT) PLAN(CVTUV001) where my stored proc is bound into
> > the CVTUV001 DB2 plan.
> >
> > What is that PREPARE statement doing to cause this error?