Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2007

    Unanswered: UDF apps not returning data from Control Center but returns data when java is invoke

    Hi all,
    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:


    RETURNS Varchar(50)

    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 SQL

    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
    ("" or "") 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.

    User Response:
    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
    SQLCODE: -4304

    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.

  2. #2
    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/,, $JAVA_HOME/lib/, 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts