I wrote a UDF in Java for DB2 for i5/OS. A dummy code sample like below,
import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; /** * @author Yiyu Jia */ public class DummyUDF { boolean debugtableOn = true; public static String helloJia(String name)throws SQLException, Exception { //Connection con = DriverManager.getConnection("jdbc:default:connection"); return "hello" + name; } }
Then, we register it as DB2 user defined function as below,
CREATE FUNCTION YOURSCHEMA.DummyUDF ( NAME VARCHAR(500) ) RETURNS VARCHAR(1000) LANGUAGE JAVA SPECIFIC YOURSCHEMA.DummyUDF NOT DETERMINISTIC MODIFIES SQL DATA RETURNS NULL ON NULL INPUT EXTERNAL NAME 'DummyUDF.helloJia' PARAMETER STYLE JAVA ;
However, when I call it in either iNavigator or SQL stored procedure, I was reported error message, which says
SQL State: 42704
Vendor Code: -204
Message: [SQL0204] DummyUDF in *LIBL type *N not found. ...
The statement used to call DummyUDF is as below,
values(YOURSCHEMA/DummyUDF('Yiyu'));Since my iNavigator is configured to use system naming. I use "/" to link the schema name and function name. However, this wont work!After research and trying, I found it is extremely simply to solve this problem. That is, using SQL naming instead of System naming to call UDF though iNavigator is set to use system naming already. Below code works,
values(YOURSCHEMA.DummyUDF('Yiyu'));
No comments:
Post a Comment