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

Have a look at the samples that are shipped together with DB2. You'll find them in the sqllib/samples/ directory.

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

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

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.

What's the error message you got? http://publib.boulder.ibm.com/infoce.../r0000917.html

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.

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.