RE: UDF error: Routine x in xxxLIB not found with specified parameters.

Common pitfall for new UDF developers accepting character input is that they
test their UDFs with character literals. For example:

SELECT B('SOMETIME') FROM SYSIBM/SYSDUMMY1

and then they get the "not found" error message. This is caused by the fact
that DB2 implicitly casts character literals to a VARCHAR data type. Since
it then looks for a UDF with a VARCHAR signature, it fails to find it.

One correct but poor way to address it is to explicitly cast the character
literal to a CHAR data type, i.e.:

SELECT B(CHAR('SOMETIME')) FROM SYSIBM/SYSDUMMY1

Better way to handle this is to declare your UDF as accepting VARCHAR as
that UDF can handle both CHAR and VARCHAR inputs, due to DB2's data type
promotion rules (when you pass in a CHAR value in this setup, DB2 looks for
a UDF with a CHAR signature and when not found it then looks for VARCHAR
then CLOB etc.).

To summarize, try declaring your NEW_PARAMETER as VARCHAR(8) instead.

HTH, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: UDF error: Routine x in xxxLIB not found with specified parameters.

Last week the fine folks here helped me to create my first UDF. I was able
to create the UDF via CREATE FUNCTION and here is the generated SQL for
that function:

CREATE FUNCTION GPKLIB.B (
NEW_PARAMETER CHAR(8) CCSID 37 )
RETURNS CHAR(8) CCSID 37 CAST FROM CHAR(8) CCSID 37
LANGUAGE RPGLE
SPECIFIC GPKLIB.BSPECIFIC
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
DISALLOW PARALLEL
EXTERNAL NAME 'GPKLIB/B'
PARAMETER STYLE DB2SQL ;

Each time I try to use it it I receive the following error message:
Routine B in GPKLIB not found with specified parameters.

I've deleted and recreated this UDF multiple times (playing with the
parms) all with no luck at getting the UDF to return a value. Here is the
code I'm trying to execute.

Pgm C calls the UDF (Pgm B).

Pgm B calls Pgm A which calls a SQLRPGLE procedure to return a date. My
'proof of concept' goal is to see the return value from pgm A in the
*entry parm of PGM B at its return statement.

I've been following the other recent thread and changed the GetOIBPeriodBE
procedure to specify CONST on the parameters. If I directly run the RPG
pgm B in debug it works as expected and returns the proper value.

PGM C is my interpretation of what I see in the example at the beginning
of chapter 3.4.4 of the Stored Procedure, Triggers, UDF redbook (the call
proc1 (totsales, totcust) statement.


---------------------PGM
C-------------------------------------------------
C/EXEC SQL CALL GPKLIB/B ('00000000')
C/END-EXEC

C Eval *INLR = *ON
C Return


-------------------------------PGM
B----------------------------------------
C Call 'A'
C Parm Bparm 8

C Eval *INLR = *On
C Return


------------------------------PGM
A----------------------------------------
DA PR Extpgm('A')
D Date 8
DA PI
D Date 8
D DS
D RetValue_N 1 8 0
D RetValue_A 1 8
D GetOibPeriodBE PR 8 0
D LOC#_PR 1 CONST
D CLNO_PR 3 0 CONST
D PERIOD_PR 6 0 CONST
D BEGOREND_PR 1 CONST

C Eval RetValue_N =
GetOIBPeriodBE('0':0:200801:'B')
C Eval Date = RetValue_A
C Eval *INLR = *On
C Return

Apparently I'm missing something here. Is what I'm trying to do practical
or am I missing the point of what UDF's and (SQL/external) stored
procedures are used for? I thought a UDF was a way for me to access
existing (RPG) code from an SQL interface. I've been reading the Redbook
too but I can't seem to get past this error.

Thanks!

Regards, Jerry