02-06-08, 21:31 #1Registered User
- Join Date
- Mar 2007
Unanswered: UDF apps not returning data from Control Center but returns data when java is invoke
First, when I run this command "java -classpath
/opt/ibm/db2/V9.1/java/commons-codec-1.3.jar:/opt/ ibm/db2/V9.1/java/ Dmetaphone Anyanso" in my Linux box, it executes successfully and I get the result that I want.
However, I am having a helluva time getting this function to work UDB Control center. Here is what I have been trying to do...I have a DB2 UDB (v8.2 & v9.1 Linux & Windows) UDF that I have registered as follows:
CREATE FUNCTION DMPHONEJavaScalar (JWORD VARCHAR(50))
PARAMETER STYLE JAVA
EXTERNAL NAME 'java -classpath /opt/ibm/db2/V9.1/java/commons-codec-1.3.jar:/opt/ ibm/db2/V9.1/java/Dmetaphone ' <<--- Similar to manual command
NO EXTERNAL ACTION
CALLED ON NULL INPUT
I have changed the EXTERNAL NAME many was like without the
'java -classpath" and without this section "/opt/ibm/db2/V9.1/java/commons-codec-1.3.jar" of the concatenated path. Whatever that I do I get the following error:
DB2INST1.DMPHONEJavaScalar - Build started.
Create user-defined function returns -449. DB2INST1.DMPHONEJavaScalar: 7:
[IBM][CLI Driver][DB2/LINUX] SQL0449N The statement defining routine
"DB2ADMIN.DMPHONEJAVASCALAR" contains an invalidly formatted library/function identification in the EXTERNAL NAME clause. LINE NUMBER=7. SQLSTATEB878 DB2INST1.DMPHONEJavaScalar - Build failed.
DB2INST1.DMPHONEJavaScalar - Roll back completed successfully.
When I specify "/opt/ibm/db2/V9.1/java/ Dmetaphone ', I am able to build successfully but when I go to run select statement that references the UDF, I get the following error:
SQL4304N Java stored procedure or user-defined function
"DB2ADMIN.DMPHONEJAVASCALAR", specific name "DMPHONEJAVASCALAR" could not load Java class "/opt/ibm/db2/V9", reason code "1". SQLSTATEB724
The Java class given by the EXTERNAL NAME clause of a CREATE
PROCEDURE or CREATE FUNCTION statement could not be loaded. The
reason codes are:
1 The class was not found on the CLASSPATH.
2 The class did not implement the required interface
("COM.ibm.db2.app.StoredProc" or "COM.ibm.db2.app.UDF") or lacked
the Java "public" access flag.
3 The default constructor failed or was unavailable.
4 Could not load driver for "jdbc:default:connection".
5 Could not establish default context.
Ensure that the compiled ".class" file is installed in the CLASSPATH, for example under "sqllib/function". Ensure it implements the required Java interfaces and is "public".
sqlcode : -4304
sqlstate : 42724
I have been fighting with this on and off for about a week now and it is driving me nuts. Can someone please please help me figure out what in the world I am missing here...
How can I tell where my CLASSPATH is? What is the proper syntax for UDF EXTERNAL NAME specification? What I have read and applied don't seem to work for me!
Thanks guys, I've been beating my head in on this one, and I'll wager a "Washington" that the the answer is so simple.
02-07-08, 05:44 #2Registered User
- Join Date
- Jan 2007
- Jena, Germany
The external name contains only the JAR or class file and the member in the class file you want to be invoked. DB2 will start the JVM for you and you must not specify the "java ..." stuff.
The JVM started by DB2 has a fixed class path, which includes: $DB2PATH/java/db2java.zip, sqlj.zip, $JAVA_HOME/lib/classes.zip, and $JAVA_HOME/classes. This is done to avoid starting the JVM with the wrong class path. Once the JVM is running, it switches to the class path set in the CLASSPATH environment variable.Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development