Snick wrote:

> Hello,
>
> I've been going through the DB2 application development manuals and
> have no idea what I'm doing wrong with my UDF.
>
> Basically, I created a C++ library that compares 2 strings and returns
> their similarity as a double. I wanted to use this function inside DB2
> so I created the following code:
>
> similarity.cpp ---
> #include "mcwpa.h"
> #include <sqludf.h>
>
> extern "C"
> SQL_API_RC SQL_API_FN similarity ( SQLUDF_VARCHAR *in1,
> SQLUDF_VARCHAR *in2,
> SQLUDF_DOUBLE *sim,
> SQLUDF_NULLIND *in1NullInd,
> SQLUDF_NULLIND *in2NullInd,
> SQLUDF_NULLIND *simNullInd,
> SQLUDF_TRAIL_ARGS )
> {
> MCWPA algorithm(in1, in2);

You don't handle errors here, do you?

> *sim = algorithm.sim();

You also might want to set the output null indicator here:

*simNullInd = 0;
>
> return 0;
> }
> --- end similarity.cpp
>
> I compiled this into a shared object called libdb2_sim.so:
>
> command output ---
> truth@tiamat2:~/mcwpa/src> nm libdb2_sim.so
[...]
> --- end command output
>
> I have a DB2 instance running at /u01/mercuryd, and placed the file in
> that directory. My fenced user "mdfenc" has read access to this
> directory. I also created a symlink:
>
> lrwxrwxrwx 1 mercuryd merciadm 27 2005-08-19 15:02
> /u01/mercuryd/sqllib/function/libdb2_sim -> /u01/mercuryd/libdb2_sim.so

So the lib is accessible from the sqllib/function/ directory - that's good.

> I then registered my UDF with the following command:
> db2 "create function similarity (v1 VARCHAR(255), v2 VARCHAR(255))
> returns DOUBLE EXTERNAL NAME 'libdb2_sim!similarity' LANGUAGE C
> PARAMETER STYLE SQL DETERMINISTIC FENCED THREADSAFE RETURNS NULL ON
> NULL INPUT NO SQL "
> DB20000I The SQL command completed successfully.
>
>
> Now my problem. Executing this SQL results in error, and I don't know
> why:
> truth@tiamat2:~/mcwpa/src> db2 "select clients.clinum,
> clients.cliname1, ofac.name, similarity(varchar(clients.cliname1, 255),
> varchar(ofac.name,255)) as sim from clients, ofac"
> SQL0440N No authorized routine named "SIMILARITY" of type "FUNCTION"
> having
> compatible arguments was found. SQLSTATE=42884

Do you execute the SELECT statement as the same user who also run the CREATE
FUNCTION statement? I have some doubts there because you said your
instance is named "mercuryd" and the user running the SELECT is named
"truth".

You have to remember that each object in DB2 (tables, triggers, procedures,
functions, ...) is always in a schema. (A schema is something like a
namespace.) Once you connected to a database, you can query the CURRENT
SCHEMA special register to see what the current schema will be for objects
that are created without an explicit schema name. So you might want to
check the schema name of your function:

SELECT routineschema FROM syscat.routines WHERE routinename = 'SIMILARITY'

Once you know that, you can either qualify your function name in the SELECT
statement with the schema name:

SELECT clients.clinum, clients.cliname1, ofac.name,
<schema>.similarity(clients.cliname1, ofac.name) AS sim
FROM clients, ofac

or you set the list of schemas that will be searched by DB2 to find an
appropriate function. Then you can call the function by its unqualified
name. This list is in the CURRENT FUNCTION PATH special register:

SET CURRENT FUNCTION PATH = CURRENT FUNCTION PATH, <schema>

SELECT clients.clinum, clients.cliname1, ofac.name,
similarity(clients.cliname1, ofac.name) AS sim
FROM clients, ofac

Personally, I always prefer the first appreach because it avoids
misunderstandings and you can't run into trouble if the user changes
his/her function path somewhere else.

> truth@tiamat2:~/mcwpa/src> db2 describe table clients
>
> Column Type Type
> name schema name Length
> Scale Nulls
> ------------------------------ --------- ------------------ --------
> ----- ------
> CLINUM SYSIBM CHARACTER 12
> 0 Yes
> CLIREPNUM SYSIBM CHARACTER 10
> 0 Yes
> CLINAME1 SYSIBM CHARACTER 30
> 0 Yes
> ...
>
> truth@tiamat2:~/mcwpa/src> db2 describe table ofac
>
> Column Type Type
> name schema name Length
> Scale Nulls
> ------------------------------ --------- ------------------ --------
> ----- ------
> ID SYSIBM BIGINT 8
> 0 No
> NAME SYSIBM VARCHAR 255
> 0 No
> ...
>
> I've also tried the SQL without converting the types to VARCHAR (the
> manual stated that everything would be promoted to VARCHAR
> automatically) with the same results.

Right, you shouldn't need the casts. I would leave them out because it
actually makes the query harder to understand.

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena