Hi,

I'm using DB2 8.2 UDB on a Linux platform and just wanted to double check how to register a C function in DB2.

I have a C file called test.c and after compiling the code, I get the 'test' executable file which I have moved to /home/db2inst/sqllib/function.

Next I have the SQL file that has this code --

CREATE FUNCTION test(startTime TIMESTAMP, endTime TIMESTAMP)
RETURNS double
LANGUAGE c
PARAMETER STYLE sql
NO SQL
NOT FENCED
DETERMINISTIC
RETURNS NULL ON NULL INPUT
NO EXTERNAL ACTION
EXTERNAL NAME 'test' @


If I create the SQL function AND move the 'test' executable to /home/db2inst/sqllib/function, is there anything else that I need to do to successfully call the function? Is creating the function the same as registering it? Or do I need to run another command to do that?

Thanks!!

I would never name any type of object or executable 'test'.

actually, i was just giving an example.. that's not the real name of the file.. thanks for the suggestion in any case!

Yes. These two steps should be sufficient. I haven't worked with C UDFs for a while, but I have a vague memory of external names consisting of two parts: the object file name and the function name itself. In your case it would probably be 'test.so!testFunction'. Also, make sure test.so is executable by the fenced user.

Nick is right. Building an executable is not the right thing - you need a shared library. And you should specify the function name (with C linkage) to tell DB2 which function should be called. You may want to have a look at the samples in sqllib/samples/c/ and also the "bldrtn" script in that directory. (Although I wouldn't base anything serious on this script, it gives you a good indication on how to compile and link the library.)

Thanks n_i and stolze.. I checked again and see the External name in the SQL file as --
EXTERNAL NAME 'c_time_diff!timediff_secs'

I looked at the C file and see timediff_secs as the function name in the C file and under /home/db2inst/sqllib/function I see the file named c_time_diff. Am I correct in assuming that c_time_diff is the shared library in this case?

Yes, that's correct. If you place the library under $HOME/sqllib/functions, you can just specify the base name. If you put the library somewhere else, you have to use the absolute path name so that DB2 knows where to find the lib.

This is not something you should assume. If it was you who compiled and linked it, you should know precisely what it is. If it wasn't you, you can always run "file /home/nrg1/sqllib/function/c_time_diff" to verify.

thank you n_i... I just ran the command that you suggested and I get this --

/home/db2inst/sqllib/function/c_time_diff: ELF 32-bit LSB shared object, Intel 80386, version 1 (SYSV), not stripped