Function path error - SQLCODE -440

James Campbell

Function path error - SQLCODE -440

Hi,

I'm getting the SQL Error below and have tried everything and I'm stumped - can anyone help????? - all info is below.
Any suggestions would be appreciated.....


DSNT408I SQLCODE = -440, ERROR: NO FUNCTION BY THE NAME DEVDISD.JXFNFDT
HAVING COMPATIBLE ARGUMENTS WAS FOUND IN THE CURRENT PATH
Explanation: This occurs in a reference to routine (stored procedure or function) "<routine-name>", when DB2 cannot find a function or stored procedure it can use to implement the reference. There are several reasons why this could occur.
- "<routine-name>" was either incorrectly specified or does not exist in the database.
- A qualified reference was made, and the qualifier was incorrectly spelled.
- A user's SQL path does not contain the schema to which the desired function belongs, and an unqualified reference was used.
- The wrong number of arguments were included.
- For functions, the data types of one or more of the arguments is incorrect.

I have checked the following:


1. "<routine-name>" was either incorrectly specified or does not exist in the database.

It's in the database as shown below:
F E E C P Q S P E External
Sel Schema Name Specific Name O T Parms T A F S F L R T S Name
* * * * * * * * * * * * * * * *
---- -------- -------- ------------------ - - ------ - - - - - - - - - --------
DEVDISD JXFNFDT JXFNFDT E S 3 N E N D Y M N M D JXSPFDT


1. Qualifier incorrectly spelled - No see above
2. SQL path not correct - I used a qualified call so the path is irrelevant.

Here is the call:

SELECT DEVDISD.JXFNFDT('THD', '2008-12-02', '17.00.00') FROM SYSIBM.SYSDUMMY1;

1. Wrong number of arguments
No - here's the way db2 sees it:

Parameters for Function DEVDISD.JXFNFDT(JXFNFDT)

Line commands:
E - Data type I - Interpretation

S Parameter name T Ord Data type L T Max length Scale
* * * * * * * *
-- ------------------ - ------ -------------------- - - ----------- ------
C 0 CHAR N N 10 0
P 1 CHAR N N 3 0
P 2 CHAR N N 10 0
P 3 CHAR N N 8 0

1. For functions, the data types of one or more of the arguments is incorrect.
As you can see in 3 and 4 above the arguments are the correct.

Thanks for any help you may give....

Jim Campbell
Administrative Office of the Courts (AOC)
[login to unmask email]<mailto:[login to unmask email]>

The Metric Law of Least Resistance: "The more human effort required to calculate a metric, the less often (and less accurately) it will be calculated, until it is abandoned or ignored altogether."


______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Wayne Driscoll

Re: Function path error - SQLCODE -440
(in response to James Campbell)
I believe that DB2 treats the litterals as VARCHAR, not CHAR, and VARCHAR
cannot be promoted to CHAR (only the other way works). So either recreate
the procedure with the parms defined as VARCHAR (remember that CHAR gets
promoted to VARCHAR, so existing applications will still work), or CAST
the literals to CHAR, ie CHAR('THD')






"Campbell, Jim" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
01/14/2009 11:46 AM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
[DB2-L] Function path error - SQLCODE -440







Hi,

I?m getting the SQL Error below and have tried everything and I?m stumped
? can anyone help????? ? all info is below.
Any suggestions would be appreciated?..


DSNT408I SQLCODE = -440, ERROR: NO FUNCTION BY THE NAME DEVDISD.JXFNFDT
HAVING COMPATIBLE ARGUMENTS WAS FOUND IN THE CURRENT PATH
Explanation: This occurs in a reference to routine (stored procedure or
function) "<routine-name>", when DB2 cannot find a function or stored
procedure it can use to implement the reference. There are several reasons
why this could occur.
- "<routine-name>" was either incorrectly specified or does not exist in
the database.
- A qualified reference was made, and the qualifier was incorrectly
spelled.
- A user's SQL path does not contain the schema to which the desired
function belongs, and an unqualified reference was used.
- The wrong number of arguments were included.
- For functions, the data types of one or more of the arguments is
incorrect.

I have checked the following:

1. "<routine-name>" was either incorrectly specified or does not
exist in the database.

It?s in the database as shown below:
F E E C P Q S P E
External
Sel Schema Name Specific Name O T Parms T A F S F L R T S
Name
* * * * * * * * * * * * * * * *

---- -------- -------- ------------------ - - ------ - - - - - - - - -
--------
DEVDISD JXFNFDT JXFNFDT E S 3 N E N D Y M N M D
JXSPFDT

2. Qualifier incorrectly spelled ? No see above
3. SQL path not correct ? I used a qualified call so the path is
irrelevant.

Here is the call:

SELECT DEVDISD.JXFNFDT('THD', '2008-12-02', '17.00.00') FROM
SYSIBM.SYSDUMMY1;
4. Wrong number of arguments
No ? here?s the way db2 sees it:

Parameters for Function DEVDISD.JXFNFDT(JXFNFDT)

Line commands:
E - Data type I - Interpretation

S Parameter name T Ord Data type L T Max length Scale
* * * * * * * *
-- ------------------ - ------ -------------------- - - ----------- ------
C 0 CHAR N N 10 0
P 1 CHAR N N 3 0
P 2 CHAR N N 10 0
P 3 CHAR N N 8 0
5. For functions, the data types of one or more of the arguments is
incorrect.
As you can see in 3 and 4 above the arguments are the correct.

Thanks for any help you may give?.

Jim Campbell
Administrative Office of the Courts (AOC)
[login to unmask email]

The Metric Law of Least Resistance: ?The more human effort required to
calculate a metric, the less often (and less accurately) it will be
calculated, until it is abandoned or ignored altogether.?



IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia
IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register here.

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Fred Edgar

Re: Function path error - SQLCODE -440
(in response to Wayne Driscoll)
Jim, have you granted execute authority?

Fred

"Campbell, Jim" <[login to unmask email]> wrote:
Hi,

I’m getting the SQL Error below and have tried everything and I’m stumped – can anyone help????? – all info is below.
Any suggestions would be appreciated…..


DSNT408I SQLCODE = -440, ERROR: NO FUNCTION BY THE NAME DEVDISD.JXFNFDT
HAVING COMPATIBLE ARGUMENTS WAS FOUND IN THE CURRENT PATH
Explanation: This occurs in a reference to routine (stored procedure or function) "<routine-name>", when DB2 cannot find a function or stored procedure it can use to implement the reference. There are several reasons why this could occur.
- "<routine-name>" was either incorrectly specified or does not exist in the database.
- A qualified reference was made, and the qualifier was incorrectly spelled.
- A user's SQL path does not contain the schema to which the desired function belongs, and an unqualified reference was used.
- The wrong number of arguments were included.
- For functions, the data types of one or more of the arguments is incorrect.

I have checked the following:


"<routine-name>" was either incorrectly specified or does not exist in the database.

It’s in the database as shown below:
F E E C P Q S P E External
Sel Schema Name Specific Name O T Parms T A F S F L R T S Name
* * * * * * * * * * * * * * * *
---- -------- -------- ------------------ - - ------ - - - - - - - - - --------
DEVDISD JXFNFDT JXFNFDT E S 3 N E N D Y M N M D JXSPFDT


Qualifier incorrectly spelled – No see above
SQL path not correct – I used a qualified call so the path is irrelevant.

Here is the call:

SELECT DEVDISD.JXFNFDT('THD', '2008-12-02', '17.00.00') FROM SYSIBM.SYSDUMMY1;

Wrong number of arguments
No – here’s the way db2 sees it:

Parameters for Function DEVDISD.JXFNFDT(JXFNFDT)

Line commands:
E - Data type I - Interpretation

S Parameter name T Ord Data type L T Max length Scale
* * * * * * * *
-- ------------------ - ------ -------------------- - - ----------- ------
C 0 CHAR N N 10 0
P 1 CHAR N N 3 0
P 2 CHAR N N 10 0
P 3 CHAR N N 8 0

For functions, the data types of one or more of the arguments is incorrect.
As you can see in 3 and 4 above the arguments are the correct.

Thanks for any help you may give….

Jim Campbell
Administrative Office of the Courts (AOC)
[login to unmask email]

The Metric Law of Least Resistance: “The more human effort required to calculate a metric, the less often (and less accurately) it will be calculated, until it is abandoned or ignored altogether.”




---------------------------------
IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia
IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here.



______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Brian Bear

Re: Function path error - SQLCODE -440
(in response to Fred Edgar)
I seem to remember being forced to use VARCHARs for the i/o parms. When
I used CHARs I ran into problems. It's been a while so I don't recall
specifics.





Brian

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Campbell, Jim
Sent: Wednesday, January 14, 2009 12:46 PM
To: [login to unmask email]
Subject: [DB2-L] Function path error - SQLCODE -440



Hi,



I'm getting the SQL Error below and have tried everything and I'm
stumped - can anyone help????? - all info is below.

Any suggestions would be appreciated.....





DSNT408I SQLCODE = -440, ERROR: NO FUNCTION BY THE NAME DEVDISD.JXFNFDT

HAVING COMPATIBLE ARGUMENTS WAS FOUND IN THE CURRENT PATH

Explanation: This occurs in a reference to routine (stored procedure or
function) "<routine-name>", when DB2 cannot find a function or stored
procedure it can use to implement the reference. There are several
reasons why this could occur.

- "<routine-name>" was either incorrectly specified or does not exist in
the database.
- A qualified reference was made, and the qualifier was incorrectly
spelled.
- A user's SQL path does not contain the schema to which the desired
function belongs, and an unqualified reference was used.
- The wrong number of arguments were included.
- For functions, the data types of one or more of the arguments is
incorrect.



I have checked the following:



1. "<routine-name>" was either incorrectly specified or does not
exist in the database.



It's in the database as shown below:

F E E C P Q S P E
External

Sel Schema Name Specific Name O T Parms T A F S F L R T S
Name

* * * * * * * * * * * * * * * *


---- -------- -------- ------------------ - - ------ - - - - - - - - -
--------

DEVDISD JXFNFDT JXFNFDT E S 3 N E N D Y M N M D
JXSPFDT



2. Qualifier incorrectly spelled - No see above

3. SQL path not correct - I used a qualified call so the path is
irrelevant.



Here is the call:



SELECT DEVDISD.JXFNFDT('THD', '2008-12-02', '17.00.00') FROM
SYSIBM.SYSDUMMY1;

4. Wrong number of arguments

No - here's the way db2 sees it:



Parameters for Function DEVDISD.JXFNFDT(JXFNFDT)





Line commands:


E - Data type I - Interpretation





S Parameter name T Ord Data type L T Max length
Scale

* * * * * * *
*

-- ------------------ - ------ -------------------- - - -----------
------

C 0 CHAR N N 10
0

P 1 CHAR N N 3
0

P 2 CHAR N N 10
0

P 3 CHAR N N 8
0

5. For functions, the data types of one or more of the arguments is
incorrect.

As you can see in 3 and 4 above the arguments are the correct.



Thanks for any help you may give....



Jim Campbell
Administrative Office of the Courts (AOC)
[login to unmask email] <mailto:[login to unmask email]>



The Metric Law of Least Resistance: "The more human effort required to
calculate a metric, the less often (and less accurately) it will be
calculated, until it is abandoned or ignored altogether."




________________________________

IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia
< http://idug.org/lsAU >

IDUG.org < http://www.idug.org > was recently updated requiring members
to use a new password. You should have gotten an e-mail with the
temporary password assigned to your account. Please log in and update
your member profile. If you are not already an IDUG.org member, please
register here. < http://www.idug.org/component/juser/register.html >

*****************************************************************************
The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you
received this message in error, please contact the sender and delete
the material from any computer.
*****************************************************************************

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

James Campbell

Re: Function path error - SQLCODE -440
(in response to Brian Bear)
I found it buried way deep in the manuals - the issue was the data type. Since I was passing literals DB2 assumed the character field was variable. I changed the query to CAST the fields as CHAR(3), CHAR(10) and CHAR(8) and it worked....

I certainly learned a lesson and quite a lot about functions and the way they work...

Thanks to all for your assistance...

Jim Campbell
Sr. Database Administrator
Administrative Office of the Courts (AOC)
1206 S. Quince St. Olympia, WA 98501
360-704-4015 (voice plus voice-mail)
360-586-8869 (fax)
[login to unmask email]<mailto:[login to unmask email]>

The Metric Law of Least Resistance: "The more human effort required to calculate a metric, the less often (and less accurately) it will be calculated, until it is abandoned or ignored altogether."

The information transmitted is intended only for the addressee and may contain confidential, proprietary and/or privileged material. Any unauthorized review, distribution or other use of or the taking of any action in reliance upon this information is prohibited. If you receive this in error, please contact the sender and delete or destroy this message and any copies.

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Fred Edgar
Sent: Wednesday, January 14, 2009 10:24 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Function path error - SQLCODE -440

Jim, have you granted execute authority?

Fred

"Campbell, Jim" <[login to unmask email]> wrote:
[cid:[login to unmask email]
Hi,

I'm getting the SQL Error below and have tried everything and I'm stumped - can anyone help????? - all info is below.
Any suggestions would be appreciated.....


DSNT408I SQLCODE = -440, ERROR: NO FUNCTION BY THE NAME DEVDISD.JXFNFDT
HAVING COMPATIBLE ARGUMENTS WAS FOUND IN THE CURRENT PATH
Explanation: This occurs in a reference to routine (stored procedure or function) "<routine-name>", when DB2 cannot find a function or stored procedure it can use to implement the reference. There are several reasons why this could occur.
- "<routine-name>" was either incorrectly specified or does not exist in the database.
- A qualified reference was made, and the qualifier was incorrectly spelled.
- A user's SQL path does not contain the schema to which the desired function belongs, and an unqualified reference was used.
- The wrong number of arguments were included.
- For functions, the data types of one or more of the arguments is incorrect.

I have checked the following:


1. "<routine-name>" was either incorrectly specified or does not exist in the database.

It's in the database as shown below:
F E E C P Q S P E External
Sel Schema Name Specific Name O T Parms T A F S F L R T S Name
* * * * * * * * * * * * * * * *
---- -------- -------- ------------------ - - ------ - - - - - - - - - --------
DEVDISD JXFNFDT JXFNFDT E S 3 N E N D Y M N M D JXSPFDT


1. Qualifier incorrectly spelled - No see above
2. SQL path not correct - I used a qualified call so the path is irrelevant.

Here is the call:

SELECT DEVDISD.JXFNFDT('THD', '2008-12-02', '17.00.00') FROM SYSIBM.SYSDUMMY1;

1. Wrong number of arguments
No - here's the way db2 sees it:

Parameters for Function DEVDISD.JXFNFDT(JXFNFDT)

Line commands:
E - Data type I - Interpretation

S Parameter name T Ord Data type L T Max length Scale
* * * * * * * *
-- ------------------ - ------ -------------------- - - ----------- ------
C 0 CHAR N N 10 0
P 1 CHAR N N 3 0
P 2 CHAR N N 10 0
P 3 CHAR N N 8 0

1. For functions, the data types of one or more of the arguments is incorrect.
As you can see in 3 and 4 above the arguments are the correct.

Thanks for any help you may give....

Jim Campbell
Administrative Office of the Courts (AOC)
[login to unmask email]<mailto:[login to unmask email]>

The Metric Law of Least Resistance: "The more human effort required to calculate a metric, the less often (and less accurately) it will be calculated, until it is abandoned or ignored altogether."


________________________________
IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia < http://idug.org/lsAU >
IDUG.org <http://www.idug.org/> was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here.< http://www.idug.org/component/juser/register.html >




________________________________

IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia < http://idug.org/lsAU >

IDUG.org <http://www.idug.org> was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here.< http://www.idug.org/component/juser/register.html >

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html