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_ARGS )
{
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_personality_v0
0000480c A _DYNAMIC
00004904 A _GLOBAL_OFFSET_TABLE_
00002b8c t _GLOBAL__I__ZN5MCWPAC2EPKcS1_
w _Jv_RegisterClasses
U _Unwind_Resume@@GCC_3.0
00002b0e t _Z41__static_initialization_and_destruction_0ii
0000284c T _ZN5MCWPA3simEv
00001c52 T _ZN5MCWPAC1EPKcS1_
0000239a T _ZN5MCWPAC1EPKcS1_b
000019bc T _ZN5MCWPAC2EPKcS1_
00001ee8 T _ZN5MCWPAC2EPKcS1_b
00001970 W _ZN5MCWPAD1Ev
00002e8c T _ZN6Window12n_accessibleEv
00002f0a T _ZN6Window4markEv
00002f60 T _ZN6Window4nextEv
00002e80 T _ZN6Window4sizeEv
00002ed8 T _ZN6Window7patternEv
00002d52 T _ZN6WindowC1ESs
00002c24 T _ZN6WindowC2ESs
00002bec W _ZN9__gnu_cxx17__normal_iteratorIPcSsEC1ERKS1_
00002ba6 W _ZN9__gnu_cxx17__normal_iteratorIPcSsEppEi
00002be2 W _ZNK9__gnu_cxx17__normal_iteratorIPcSsEdeEv
U _ZNKSs4findEPKcj@@GLIBCPP_3.2
U _ZNKSs4findERKSsj@@GLIBCPP_3.2
U _ZNKSs4findEcj@@GLIBCPP_3.2
U _ZNKSs4sizeEv@@GLIBCPP_3.2
U _ZNKSs6lengthEv@@GLIBCPP_3.2
U _ZNKSs6substrEjj@@GLIBCPP_3.2
U _ZNSaIcEC1Ev@@GLIBCPP_3.2
U _ZNSaIcED1Ev@@GLIBCPP_3.2
U _ZNSolsEPFRSoS_E@@GLIBCPP_3.2
U _ZNSolsEi@@GLIBCPP_3.2
U _ZNSolsEj@@GLIBCPP_3.2
U _ZNSs5beginEv@@GLIBCPP_3.2
U _ZNSs5eraseEjj@@GLIBCPP_3.2
U _ZNSs7replaceEjjPKc@@GLIBCPP_3.2
U _ZNSs7replaceEjjRKSsjj@@GLIBCPP_3.2
U _ZNSsC1EPKcRKSaIcE@@GLIBCPP_3.2
U _ZNSsC1ERKSs@@GLIBCPP_3.2
U _ZNSsC1Ev@@GLIBCPP_3.2
U _ZNSsD1Ev@@GLIBCPP_3.2
U _ZNSsaSEPKc@@GLIBCPP_3.2
U _ZNSsaSERKSs@@GLIBCPP_3.2
U _ZNSsaSEc@@GLIBCPP_3.2
U _ZNSspLEc@@GLIBCPP_3.2
00003210 V _ZNSt15basic_streambufIcSt11char_traitsIcEE13_S_pb ack_sizeE
00003214 V _ZNSt15basic_streambufIwSt11char_traitsIwEE13_S_pb ack_sizeE
U _ZNSt8ios_base4InitC1Ev@@GLIBCPP_3.2
U _ZNSt8ios_base4InitD1Ev@@GLIBCPP_3.2
00002c1c W _ZNSt8iteratorISt26random_access_iterator_tagciPcR cEC2Ev
U _ZSt4cout@@GLIBCPP_3.2
U
_ZSt4endlIcSt11char_traitsIcEERSt13basic_ostreamIT _T0_ES6_@@GLIBCPP_3.2
000049f8 b _ZSt8__ioinit
U
_ZStlsISt11char_traitsIcEERSt13basic_ostreamIcT_ES 5_PKc@@GLIBCPP_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@@GLIBC_2.1.3
U __cxa_begin_catch@@CXXABI_1.2
U __cxa_end_catch@@CXXABI_1.2
w __cxa_finalize@@GLIBC_2.1.3
000031b0 t __do_global_ctors_aux
000016b0 t __do_global_dtors_aux
000042b8 d __dso_handle
w __gmon_start__
U __gxx_personality_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!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

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_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