Follow these steps if you want to create MTK Java UDFs manually.
1. Locate mtkoradrop.udf in your MTK directory and drop MTK UDFs
$ db2 connect to sample $ db2 -td\! -f mtkoradrop.udf
2. Remove Jar if it was installed already. You can look at the ~/sqllib/function/jar directory to locate the name that you should use in REMOVE_JAR command. For example, if you see ORA directory under ~/sqllib/functions/jar and the name of the jar file is UDFJAR.jar, you should use the following command.
$ db2 "CALL SQLJ.REMOVE_JAR('ora.udfjar')"
3. Locate oraUDFs.jar file in your MTK install and change directory to that location.
$ db2 "CALL SQLJ.INSTALL_JAR('file:${PWD}/oraUDFs.jar','ora.udfjar')"
You should see this message after install of this jar file.
call sqlj.install_jar('file:/db2home/db2inst1/mtk/oraUDFs.jar','ora.udfjar')
DB20000I The CALL command completed successfully.
How do you know, where this jar file was installed?
$ ls -l ~/sqllib/function/jar/ drwxrwxr-x 2 db2inst1 db2grp 4096 2007-10-03 14:54 ORA $ ls -l ~/sqllib/function/jar/ORA/ total 64 -rw-r--r-- 1 db2inst1 db2grp 59866 2007-10-03 14:54 UDFJAR.jar
$ db2 “CALL SQLJ.REFRESH_CLASSES()”
4. Locate mtkora.udf file as it has definitions for creating the Oracle UDFs in DB2. It should be in your MTK install directory. Open this file and you should see this first function.
CREATE FUNCTION ORA.jversion() RETURNS varchar(15) EXTERNAL NAME 'ora.udfjar:com.ibm.mtk.udf.oracle.OraDB2UDFsv2.jversion' LANGUAGE java PARAMETER STYLE JAVA DETERMINISTIC FENCED NOT NULL CALL NO SQL NO EXTERNAL ACTION NO SCRATCHPAD NO FINAL CALL ALLOW PARALLEL NO DBINFO !
The external name defined in above file is ora.udfjar: for each java class that is referenced in the file. This should match with the CALL SQLJ.INSTALL_JAR(‘file:${PWD}/oraUDFs.jar’,’ora.udfjar’). You can use any name that you want and make global change in this file and accordingly install this JAR in using SQLJ.INSTALL_JAR stored procedure.
For example: If you change external name from ora.udfjar to ibm.functions, you will need to replace this name every where in mtkora.udf file.
CREATE FUNCTION ORA.jversion() RETURNS varchar(15) EXTERNAL NAME 'ibm.functions:com.ibm.mtk.udf.oracle.OraDB2UDFsv2.jversion' LANGUAGE java PARAMETER STYLE JAVA DETERMINISTIC FENCED NOT NULL CALL NO SQL NO EXTERNAL ACTION NO SCRATCHPAD NO FINAL CALL ALLOW PARALLEL NO DBINFO !
and then run db2 “CALL SQLJ.INSTALL_JAR(‘file:${PWD}/oraUDFs.jar’,’ibm.functions’)”.
If you are not changing the name, you do not need to go through the above steps as explained. This is just for convenience if you want to have a different name.
5. Run SQL script to create wrappers calls to the Java classes.
$ db2 -v -td\! -f mtkora.udf > udf.log
6. Check for any errors in udf.log file and if every thing went ok, you can check this function to make sure that the installation of JAR file was ok.
$ db2 "values ORA.jversion()" and it should return the version of the UDFs installed. 1 --------------- 27Sep2007_0010 $ db2 "values ora.version()" ---------------------------------------------------------------------------------------------------- UDB SQL UDF Version: 27Sep2007_0010, Java UDF Version: 27Sep2007_0010
If you are wondering about MTK, this is a migration tool kit from IBM that helps you to migrate your Oracle, MS SQL Server, Sybase etc databases to DB2 on LUW. Follow this link to get the free tool.