Deepak, I believe UDF is an IBM supplied class which identifies the user-written class as one of type UDF. You will find the UDF.class in the IBM supplied jar/zip file: db2java.zip, which is found under \\sqllib/java.
Abhijit -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, January 20, 2003 5:44 AM To: Sinha, Abhijit Cc: [EMAIL PROTECTED]; Ioffe, Dimitry; [EMAIL PROTECTED] Subject: RE: [DB2EUG] Writing UDF in C or JAVA Hi Abhijit, Thanx for Help.. The function sent by you is working ... But I am unable to understand what is in class UDF.. Please explain class UDF .. Thanx once again .. Regads Deepak Bansal "Sinha, Abhijit" To: "'[EMAIL PROTECTED]'" <Abhijit.Sinh <[EMAIL PROTECTED]>, "Ioffe, Dimitry" <[EMAIL PROTECTED]> [EMAIL PROTECTED]> cc: [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: RE: [DB2EUG] Writing UDF in C or JAVA 01/17/03 09:17 PM Following is an example of UDF in Java. Create a class in Java, say MyUdf.java. Compile it. Jar up the .class file into myudf.jar and put jar file in say, c:\MyUdfDir, or equivalent on Unix. Step 1 ====== import COM.ibm.db2.app.*; import COM.ibm.db2.jdbc.app.*; import java.sql.*; public class MyUdf extends UDF { public void myudf (String input, String output) throws SQLException, Exception { /**************************************************************** --> some dummy udf to output the input string ****************************************************************/ String myString = input; set (2, myString); // setting the output of the UDF return; } // end method } Step 2 ====== Put the following in a ".bat" file (NT) or ".sh" file (Unix) and execute. For NT, execute from DB2 command window. For Unix, change script appropriately. rem # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # rem # rem # This script installs the UDFs. rem # rem # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # set DB2PATH=C:\SQLLIB set JAVA_HOME=C:\jdk1.2.2 set TARGET_DATABASE=myDatabaseAlias set TARGET_USERID=myUserid set TARGET_PASSWORD=myPassword set TARGET_SCHEMA=myschema set FILE_PATH=c:\MyUdfDir @echo on db2 connect to %TARGET_DATABASE% user %TARGET_USERID% using %TARGET_PASSWORD% @echo off db2 "call sqlj.remove_jar("%TARGET_SCHEMA%.myudf")" db2 "call sqlj.install_jar("file:%FILE_PATH%/myudf.jar","%TARGET_SCHEMA%.myudf")" db2 "call sqlj.refresh_classes ( void )" db2 "connect reset" Step 3 ====== Now register the UDF to DB2 using commands: drop specific function myschema.MyUdf; CREATE FUNCTION myschema.MYUDF ( VARCHAR(50) ) RETURNS VARCHAR(50) SPECIFIC myschema.MYUDF EXTERNAL NAME 'MyUdf!myudf' LANGUAGE JAVA PARAMETER STYLE DB2GENERAL DETERMINISTIC FENCED NO SCRATCHPAD NO SQL NO EXTERNAL ACTION NO FINAL CALL ALLOW PARALLEL NO DBINFO ; Now, you are ready to use it in any SQL statement. Follow what Dimitry said in the last few sentences of his response. Abhijit Sinha Goldman Sachs New York -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, January 17, 2003 1:56 AM To: Ioffe, Dimitry Cc: [EMAIL PROTECTED]; '[EMAIL PROTECTED]'; [EMAIL PROTECTED] Subject: RE: [DB2EUG] Writing UDF in C or JAVA Hi List, Like Udf in C can anyone tell me how to write UDF using JAVA... Thanx in Anticipation "Ioffe, Dimitry" To: "'[EMAIL PROTECTED]'" <Dimitry.Ioffe <[EMAIL PROTECTED]>, [EMAIL PROTECTED] @gs.com> cc: Sent by: Subject: RE: [DB2EUG] Writing UDF in C or JAVA owner-db2eug@L ugwash.org 01/13/03 09:09 PM Please respond to "Ioffe, Dimitry" Deepak, following is a VERY basic example of C UDF for print(): in file prt.c: #include <stdio.h> #include <sqludf.h> #include <sqlca.h> #include <sqlda.h> #ifdef __cplusplus extern "C" #endif void SQL_API_FN print ( char *str, char *out, short *outnull, /* output NULL indicator */ char *sqlstate, /* SQL STATE */ char *funcname, /* function name */ char *specname, /* specific function name */ char *mesgtext) { /* message text insert */ strcpy (out, str); } then exec bldrtn -- script provided by IBM. Once you've compiled it you should check that all *so* obj. are being resolved correctly. For example this script will not work for Sol.8. Issue ldd prt (if you've compiled prt.c) and you should see that all *.so.* links are resolved. If not than you'll have to modify bldrtn script and add -RPathName where PathName=location of *.so.* objects not found. Modify EXTRA_LFLAG line and recompile. Providing you were able to compile this UDF without any errs you should copy/move it ~/sqllib/function dir. After that just issue: CREATE FUNCTION print (varchar(255)) RETURNS varchar(255) NOT FENCED DETERMINISTIC NO SQL NO EXTERNAL ACTION LANGUAGE C PARAMETER STYLE DB2SQL EXTERNAL NAME 'prt!print'; Note that file name in ~/sqllib/function dir should be identical to the one in first part of EXTERNAL NAME parameter and second part should be the entry point (exactly as coded) in source file. Once all has been compiled and setup you should be able to issue something like: select print('this is a test') from sysibm.sysdummy1 and it should return string 'this is a test'... For more info you can refer to manual. Regards, -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Sunday, January 12, 2003 7:30 AM To: [EMAIL PROTECTED] Subject: [DB2EUG] Writing UDF in C or JAVA Hi Experts, We re using version 7.1 of DB2 on AIX . We need some UDF for our application. As It will become very complex in SQL Language. Can anyone tell me how to write UDF in Programming Language C or JAVA ????? Thanx & Rgds Deepak Bansal ********* The information contained in this message is legally privileged and confidential information intended only for the use of the addressed individual or entity indicated in this message (or responsible for delivery of the message to such person). It must not be read, copied, disclosed, distributed or used by any person other than the addressee. Unauthorised use, disclosure or copying is strictly prohibited and may be unlawful. Opinions, conclusions and other information on this message that do not relate to the official business of any of the constituent companies of the TATA CONSULTANCY SERVICES shall be understood as neither given nor endorsed by the Group. If you have received this message in error, you should destroy this message and kindly notify the sender by e-mail. Thank you. ********* - ::: When replying to the list, please use 'Reply-All' and make sure ::: a copy goes to the list ([EMAIL PROTECTED]). *** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED] *** For more information, check http://www.db2eug.uni.cc - ::: When replying to the list, please use 'Reply-All' and make sure ::: a copy goes to the list ([EMAIL PROTECTED]). *** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED] *** For more information, check http://www.db2eug.uni.cc - ::: When replying to the list, please use 'Reply-All' and make sure ::: a copy goes to the list ([EMAIL PROTECTED]). *** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED] *** For more information, check http://www.db2eug.uni.cc - ::: When replying to the list, please use 'Reply-All' and make sure ::: a copy goes to the list ([EMAIL PROTECTED]). *** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED] *** For more information, check http://www.db2eug.uni.cc
