Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2007
    Posts
    72

    Unanswered: Anyone with example of DB2 UDB UDF calling Java/C++ routine for Double Metaphone Keys

    Hi all,


    I am in need of examples of a UDF scalar function that can call a Java or C++ routine to return the DoubleMetaphone keys for a name/word search or something like that. I have never written a UDF let alone one that call another routine so please provide as much as possible detail explanation of what the code is doing. This is rather urgent for a report I hae to prep for Monday. I will appreciate a good and helpful response soon.

    Thank you all very much

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Have a look at the samples that are shipped together with DB2. You'll find them in the sqllib/samples/ directory.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Mar 2007
    Posts
    72
    Hi Knut,
    When you said look in the samples directory, is there specific members to look at that? I have looked in the directory and with so many members (and I have looked a quite a few), I am sorry to say it's hard for me right now to tell which of those will have information relevant to my need viz: SQL UDF call a Java or C++ routine that returns double metaphone keys for a name/word search. Can you help further?

    Thanks

  4. #4
    Join Date
    Mar 2007
    Posts
    72
    Hello again,
    I have gotten this far and now I am having problem building/registering the following UDF using the DB2 UDB Development Center. Anyone see something wrong with it? When I change the code a little bit to say JAVA, it build successfully but when it says Language C, it fails to build. Is there a keyword(s) I should or shouldn't use for language = C? Here are the UDF code:

    LANGUAGE C: This fails
    CREATE FUNCTION DMPHONECppScalar ( CWORD VARCHAR(50))
    RETURNS Varchar(50)
    SPECIFIC DMPHONECppScalar
    EXTERNAL NAME 'CppUDFs!DoubleMetaphone'
    LANGUAGE C
    PARAMETER STYLE SQL
    DETERMINISTIC
    FENCED
    NOT NULL CALL
    NO SQL
    NO EXTERNAL ACTION

    LANGUAGE Java: This build successfully.
    CREATE FUNCTION DMPHONEJavaScalar ( JWORD VARCHAR(50))
    RETURNS Varchar(50)
    FENCED
    LANGUAGE JAVA
    PARAMETER STYLE JAVA
    SPECIFIC DMPHONEjAVAScalar
    EXTERNAL NAME 'javaUDFs.DoubleMetaphone'
    NO EXTERNAL ACTION
    CALLED ON NULL INPUT
    DETERMINISTIC
    NO SQL

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Since you neglected to tell us which DB2 version you are using on which platform, I'm guessing that it is DB2 LUW. There, you have a DB2 instance and that instance contains a directory sqllib/samples/. Beneath this, you will find subdirectories "cpp" , "c", or "java" distinguishing between the different languages in which you can implement your UDF. In the respective directory, you will find the C/C++/Java code for the UDFs as well as samples how to connect to DB2 and invoke a UDF. You will also find the necessary CREATE FUNCTION statements. Just search through the files with "findstr" or "grep".

    The samples will give you a head-start on how to write UDFs and get them invoked by DB2. The part that implements the specific logic you require is still something you have to come up on your own. But then, you should find some C/C++/Java code that implements what you need, so you only have to map input/output parameters to the interface provided by DB2.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by pagwu
    Hello again,
    I have gotten this far and now I am having problem building/registering the following UDF using the DB2 UDB Development Center. Anyone see something wrong with it?
    What's the error message you got? http://publib.boulder.ibm.com/infoce.../r0000917.html
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Mar 2007
    Posts
    72
    Thank you very much Knut for your showing where I need to go to find some relevant information quickly. You were right I have DB2 UDB v8.2 and currently starting to test DB2v9.5 in Linux and in Windows.

    About the UDF not building successfully in Development center, I get no error message. As I said, when the UDF is defined for Language Java, it builds ok in development center but as soon as I change language and parameter style to C and SQL respectively, the process get stuck in "Build In Progress" status. NO error message of any king. Just sits there! One more thing, as it turned out, I found out that our shop does not have Visual Studio toolset in place for the C++ routine that I am trying to register. That leads me to wonder, could DB2 be looking for something in the environment, not finding it, the process get lost...just a thought!

    So, that where we are as I write.

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Since you have problems with Development Center, I suggest that you issue the CREATE FUNCTION statement on the DB2 command line. If it works there, you know that it is a DevCtr problem and not a DB2 problem. Granted, that's no real solution but at least you can get to the point where your statements work as expected without the GUI interfering.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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