SEO

I have just tried to migrate this stored procedure to another instance.
on the same server.

I have taken the executeable and copied the file to the new database.
Done the binds on the file and created the stored procedure.
My function is located in /home/db2inst9/sqllib/function/db2func

The two databases are identical versions(VS8 Fixpack 6a) just a
different database name(DMDB001 vs CNWDB001) and instances. db2inst3
works and db2inst9 does not.

This is a C program that came from this article:
http://www-128.ibm.com/developerwork...ein/index.html

Here is the Create procedure statement.

CREATE PROCEDURE drmdba.truncate
( IN schemaName VARCHAR(130), IN tableName VARCHAR(130) )
SPECIFIC truncate_table
DYNAMIC RESULT SETS 0
MODIFIES SQL DATA
NOT DETERMINISTIC
CALLED ON NULL INPUT
LANGUAGE C
EXTERNAL NAME 'db2func!truncate_table'
FENCED THREADSAFE
INHERIT SPECIAL REGISTERS
PARAMETER STYLE SQL
PROGRAM TYPE SUB
NO DBINFO
;

easdt1:/home/db2inst9>db2 call
"drmdba.truncate('CNPUSER','DEL_TIDCREW')"

SQL0443N Routine "DRMDBA.TRUNCATE" (specific name "TRUNCATE_TABLE")
has returned an error SQLSTATE with diagnostic text "Invalid schema
name 'CNPUSER'.". SQLSTATE=38000

I know the table exists in the the database and so does the schema.

I would like to know why I keep getting the error? Any help would be
appreciated.

Thanks in advance.

jafastinger@aep.com wrote:

> I have just tried to migrate this stored procedure to another instance.
> on the same server.
>
> I have taken the executeable and copied the file to the new database.
> Done the binds on the file and created the stored procedure.

What exactly did you bind and how did you do it?

> My function is located in /home/db2inst9/sqllib/function/db2func
>
> The two databases are identical versions(VS8 Fixpack 6a) just a
> different database name(DMDB001 vs CNWDB001) and instances. db2inst3
> works and db2inst9 does not.
>
> This is a C program that came from this article:
>
http://www-128.ibm.com/developerwork...ein/index.html
>
> Here is the Create procedure statement.
>
> CREATE PROCEDURE drmdba.truncate
> ( IN schemaName VARCHAR(130), IN tableName VARCHAR(130) )
> SPECIFIC truncate_table
> DYNAMIC RESULT SETS 0
> MODIFIES SQL DATA
> NOT DETERMINISTIC
> CALLED ON NULL INPUT
> LANGUAGE C
> EXTERNAL NAME 'db2func!truncate_table'
> FENCED THREADSAFE
> INHERIT SPECIAL REGISTERS
> PARAMETER STYLE SQL
> PROGRAM TYPE SUB
> NO DBINFO
> ;
>
> easdt1:/home/db2inst9>db2 call
> "drmdba.truncate('CNPUSER','DEL_TIDCREW')"
>
> SQL0443N Routine "DRMDBA.TRUNCATE" (specific name "TRUNCATE_TABLE")
> has returned an error SQLSTATE with diagnostic text "Invalid schema
> name 'CNPUSER'.". SQLSTATE=38000

The only reason where this error will be raised is if the schema name could
not be "unquoted" properly. So my guess is that you either modified the
code in some way or you did not bind the "functions.bnd" file to the new
database.

> I know the table exists in the the database and so does the schema.
>
> I would like to know why I keep getting the error? Any help would be
> appreciated.

You could try to set DB2's diagnostic level to 4, try to run the procedure
and then have a look at the (new) output from "db2diag".

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Thanks Knut,
We figured it out. We found out that it was a bind error but masked by
the first error in the stored procedure which was the SQL0443N with the
SQLSTATE 38000.

The problem was the person before me did not have the compiled code in
the same library as the bind file.
The compiled code had a newer version of the program. ( with no code
changes). This I believe gave us a different CON token( what I
remembered from the Mainframe I am pretty sure about this maybe you can
set me straight.) And did not match the contoken of the bind file. I
recompiled the code grabbing the bind file and the compiled code fromt
he same location and the Stored proc started working. Thanks for
trying to help us. Your posts are very helpful.

Thanks again
Jeff Fastinger
Lowly little DBA