Problem with a UDF

[Snick]

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_AR GS )
{
MCWPA algorithm(in1, in2);

*sim = algorithm.sim() ;

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
000042c0 d DW.ref.__gxx_pe rsonality_v0
0000480c A _DYNAMIC
00004904 A _GLOBAL_OFFSET_ TABLE_
00002b8c t _GLOBAL__I__ZN5 MCWPAC2EPKcS1_
w _Jv_RegisterCla sses
U _Unwind_Resume@ @GCC_3.0
00002b0e t _Z41__static_in itialization_an d_destruction_0 ii
0000284c T _ZN5MCWPA3simEv
00001c52 T _ZN5MCWPAC1EPKc S1_
0000239a T _ZN5MCWPAC1EPKc S1_b
000019bc T _ZN5MCWPAC2EPKc S1_
00001ee8 T _ZN5MCWPAC2EPKc S1_b
00001970 W _ZN5MCWPAD1Ev
00002e8c T _ZN6Window12n_a ccessibleEv
00002f0a T _ZN6Window4mark Ev
00002f60 T _ZN6Window4next Ev
00002e80 T _ZN6Window4size Ev
00002ed8 T _ZN6Window7patt ernEv
00002d52 T _ZN6WindowC1ESs
00002c24 T _ZN6WindowC2ESs
00002bec W _ZN9__gnu_cxx17 __normal_iterat orIPcSsEC1ERKS1 _
00002ba6 W _ZN9__gnu_cxx17 __normal_iterat orIPcSsEppEi
00002be2 W _ZNK9__gnu_cxx1 7__normal_itera torIPcSsEdeEv
U _ZNKSs4findEPKc j@@GLIBCPP_3.2
U _ZNKSs4findERKS sj@@GLIBCPP_3.2
U _ZNKSs4findEcj@ @GLIBCPP_3.2
U _ZNKSs4sizeEv@@ GLIBCPP_3.2
U _ZNKSs6lengthEv @@GLIBCPP_3.2
U _ZNKSs6substrEj j@@GLIBCPP_3.2
U _ZNSaIcEC1Ev@@G LIBCPP_3.2
U _ZNSaIcED1Ev@@G LIBCPP_3.2
U _ZNSolsEPFRSoS_ E@@GLIBCPP_3.2
U _ZNSolsEi@@GLIB CPP_3.2
U _ZNSolsEj@@GLIB CPP_3.2
U _ZNSs5beginEv@@ GLIBCPP_3.2
U _ZNSs5eraseEjj@ @GLIBCPP_3.2
U _ZNSs7replaceEj jPKc@@GLIBCPP_3 .2
U _ZNSs7replaceEj jRKSsjj@@GLIBCP P_3.2
U _ZNSsC1EPKcRKSa IcE@@GLIBCPP_3. 2
U _ZNSsC1ERKSs@@G LIBCPP_3.2
U _ZNSsC1Ev@@GLIB CPP_3.2
U _ZNSsD1Ev@@GLIB CPP_3.2
U _ZNSsaSEPKc@@GL IBCPP_3.2
U _ZNSsaSERKSs@@G LIBCPP_3.2
U _ZNSsaSEc@@GLIB CPP_3.2
U _ZNSspLEc@@GLIB CPP_3.2
00003210 V _ZNSt15basic_st reambufIcSt11ch ar_traitsIcEE13 _S_pback_sizeE
00003214 V _ZNSt15basic_st reambufIwSt11ch ar_traitsIwEE13 _S_pback_sizeE
U _ZNSt8ios_base4 InitC1Ev@@GLIBC PP_3.2
U _ZNSt8ios_base4 InitD1Ev@@GLIBC PP_3.2
00002c1c W _ZNSt8iteratorI St26random_acce ss_iterator_tag ciPcRcEC2Ev
U _ZSt4cout@@GLIB CPP_3.2
U
_ZSt4endlIcSt11 char_traitsIcEE RSt13basic_ostr eamIT_T0_ES6_@@ GLIBCPP_3.2
000049f8 b _ZSt8__ioinit
U
_ZStlsISt11char _traitsIcEERSt1 3basic_ostreamI cT_ES5_PKc@@GLI BCPP_3.2
U _ZdlPv@@GLIBCPP _3.2
U _Znwj@@GLIBCPP_ 3.2
000048f4 d __CTOR_END__
000048ec d __CTOR_LIST__
000048fc d __DTOR_END__
000048f8 d __DTOR_LIST__
00004660 r __FRAME_END__
00004900 d __JCR_END__
00004900 d __JCR_LIST__
000049f4 A __bss_start
U __cxa_atexit@@G LIBC_2.1.3
U __cxa_begin_cat ch@@CXXABI_1.2
U __cxa_end_catch @@CXXABI_1.2
w __cxa_finalize@ @GLIBC_2.1.3
000031b0 t __do_global_cto rs_aux
000016b0 t __do_global_dto rs_aux
000042b8 d __dso_handle
w __gmon_start__
U __gxx_personali ty_v0@@CXXABI_1 .2
00002b62 t __tcf_0
000049f4 A _edata
000049fc A _end
000031e4 T _fini
00001320 T _init
00001680 t call_gmon_start
000049f4 b completed.1
00001720 t frame_dummy
000042bc d p.0
U pow@@GLIBC_2.0
000017ea T similarity
U sqrt@@GLIBC_2.0
U strlen@@GLIBC_2 .0
U toupper@@GLIBC_ 2.0
--- 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

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!sim ilarity' 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.cliname 1, ofac.name, similarity(varc har(clients.cli name1, 255),
varchar(ofac.na me,255)) as sim from clients, ofac"
SQL0440N No authorized routine named "SIMILARITY " of type "FUNCTION"
having
compatible arguments was found. SQLSTATE=42884

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.

Does anybody know what is happening?

Thanks,

Tom

[Knut Stolze]

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_AR GS )
{
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!sim ilarity' 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.cliname 1, ofac.name, similarity(varc har(clients.cli name1, 255),
varchar(ofac.na me,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.cliname 1, ofac.name,
<schema>.simila rity(clients.cl iname1, 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.cliname 1, ofac.name,
similarity(clie nts.cliname1, ofac.name) AS sim
FROM clients, ofac

Personally, I always prefer the first appreach because it avoids
misunderstandin gs 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