473,320 Members | 1,707 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Stored Procedure SQL0443N

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.

Nov 12 '05 #1
2 5499
ja*********@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
Nov 12 '05 #2
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

Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: dinesh prasad | last post by:
I'm trying to use a servlet to process a form, then send that data to an SQL server stored procedure. I'm using the WebLogic 8 App. server. I am able to retrieve database information, so I know my...
0
by: Nashat Wanly | last post by:
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET View products that this article applies to. This article was previously published under Q310070 For a Microsoft...
3
by: Rhino | last post by:
I've spent the last couple of hours trying to figure out how to debug a Java stored procedure and am just going in circles. The last straw came when I got "Cannot open input stream for default"...
4
by: Rhino | last post by:
Is it possible for a Java Stored Procedure in DB2 V7.2 (Windows) to pass a Throwable back to the calling program as an OUT parameter? If yes, what datatype should I use when registering the...
8
by: Thomasb | last post by:
With a background in MS SQL Server programming I'm used to temporary tables. Have just started to work with DB2 ver 7 on z/OS and stumbled into the concept of GLOBAL TEMPORARY TABLE. I have...
2
by: Dino L. | last post by:
How can I run stored procedure (MSSQL) ?
7
by: Dabbler | last post by:
I'm using an ObjectDataSource with a stored procedure and am getting the following error when trying to update (ExecuteNonQuery): System.Data.SqlClient.SqlException: Procedure or Function...
7
by: db2adm1 | last post by:
I am getting the following error while trying to alter a table's column...I am trying to increase the decimal length of the column from 5 to 6 using altobj procedure. SQL0443N Routine...
2
by: jed | last post by:
I have created this example in sqlexpress ALTER PROCEDURE . @annualtax FLOAT AS BEGIN SELECT begin1,end1,deductedamount,pecentageextra FROM tax
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.