Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: Registering C based user defined function in DB2

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

  2. #2
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155

    just a suggestion

    I would never name any type of object or executable 'test'.
    Dick Brenholtz, Ami in Deutschland

  3. #3
    Join Date
    Nov 2007
    Posts
    265
    actually, i was just giving an example.. that's not the real name of the file.. thanks for the suggestion in any case!

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2user24 View Post
    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?
    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.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Nov 2007
    Posts
    265
    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?
    Last edited by db2user24; 04-16-10 at 12:28.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2user24 View Post
    Am I correct in assuming that c_time_diff is the shared library in this case?
    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.

  9. #9
    Join Date
    Nov 2007
    Posts
    265
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •