DB2 7.2 Java UDF not in Classpath

Question

I've created a Java class implementing a Levenshtein Distance algorithm in Java in order to use it in a DB2 UDF.

Apparently, under DB2, there are two ways of registering a java UDF, either by copying the .class file under QIBM/UserData/OS400/SQLLib/Function or copying a JAR and then using SQLJ.INSTALL_JAR.

Now, my Java source code looks like this:

package FUNCTIONS;
public class LEVENSHTEIN {

public static int levenshteinDistance (String lhs, String rhs) {
    
    int len0 = lhs.length() + 1;                                                     
    int len1 = rhs.length() + 1;                                                     
                                                                                    
    // the array of distances                                                       
    int[] cost = new int[len0];                                                     
    int[] newcost = new int[len0];                                                  
                                                                                    
    // initial cost of skipping prefix in String s0                                 
    for (int i = 0; i < len0; i++) cost[i] = i;                                     
                                                                                    
    // dynamically computing the array of distances                                  
                                                                                    
    // transformation cost for each letter in s1                                    
    for (int j = 1; j < len1; j++) {                                                
        // initial cost of skipping prefix in String s1                             
        newcost[0] = j;                                                             
                                                                                    
        // transformation cost for each letter in s0                                
        for(int i = 1; i < len0; i++) {                                             
            // matching current letters in both strings                             
            int match = (lhs.charAt(i - 1) == rhs.charAt(j - 1)) ? 0 : 1;             
                                                                                    
            // computing cost for each transformation                               
            int cost_replace = cost[i - 1] + match;                                 
            int cost_insert  = cost[i] + 1;                                         
            int cost_delete  = newcost[i - 1] + 1;                                  
                                                                                    
            // keep minimum cost                                                    
            newcost[i] = Math.min(Math.min(cost_insert, cost_delete), cost_replace);
        }                                                                           
                                                                                    
        // swap cost/newcost arrays                                                 
        int[] swap = cost; cost = newcost; newcost = swap;                          
    }                                                                               
                                                                                    
    // the distance is the cost for transforming all letters in both strings        
    return cost[len0 - 1];                                                          
}

}

In the latest documentation I've read it says that it has to respect the package structure, so I copied my LEVENSHTEIN.class under QIBM/UserData/OS400/SQLLib/Function/FUNCTIONS. Also tried just copying it under Function, just in case I misunderstood.

Also created a JAR and registered it like

CALL SQLJ.INSTALL_JAR('file:/QIBM/UserData/OS400/SQLLib/Function/testMain.jar','JARFUNCTIONS',0);

Ways I tried to register the UDF:

CREATE OR REPLACE FUNCTION DEBUG.LV( 
LHS VARCHAR(255) , 
RHS VARCHAR(255) ) 
RETURNS INTEGER   
LANGUAGE JAVA 
SPECIFIC DEBUG.LV 
NOT DETERMINISTIC 
READS SQL DATA 
CALLED ON NULL INPUT 
EXTERNAL NAME 'JARFUNCTIONS:FUNCTIONS.LEVENSHTEIN.levenshteinDistance' 
PARAMETER STYLE JAVA ; 

CREATE OR REPLACE FUNCTION DEBUG.LEVENSHTEIN( 
LHS VARCHAR(255) , 
RHS VARCHAR(255) ) 
RETURNS INTEGER   
LANGUAGE JAVA 
SPECIFIC DEBUG.LEVENSHTEIN 
NOT DETERMINISTIC 
READS SQL DATA 
CALLED ON NULL INPUT 
EXTERNAL NAME 'FUNCTIONS.LEVENSHTEIN.levenshteinDistance' 
PARAMETER STYLE JAVA ; 

CREATE OR REPLACE FUNCTION DEBUG.LEVENSHTEIN( 
LHS VARCHAR(255) , 
RHS VARCHAR(255) ) 
RETURNS INTEGER   
LANGUAGE JAVA 
SPECIFIC DEBUG.LEVENSHTEIN 
NOT DETERMINISTIC 
READS SQL DATA 
CALLED ON NULL INPUT 
EXTERNAL NAME 'LEVENSHTEIN.levenshteinDistance' 
PARAMETER STYLE JAVA ;

And all of these tell me that it couldn't find the class under the classpath and to make sure the .class compiled file is under /QIBM/UserData/OS400/SQLLib/Function and that it implements the necessary interfaces and is public.

From what I've read, using JAVA style parameters, I don't have to extend UDF. Also, UDF in my db2_classes is a class and not an interface, so I have to extend it not implement it. Also tried doing that, nothing changes.

Also saw this style of declaring so tried this too:

CREATE OR REPLACE FUNCTION DEBUG.LEVENSHTEIN( 
LHS VARCHAR(255) , 
RHS VARCHAR(255) ) 
RETURNS INTEGER   
LANGUAGE JAVA 
SPECIFIC DEBUG.LEVENSHTEIN 
NOT DETERMINISTIC 
READS SQL DATA 
CALLED ON NULL INPUT 
EXTERNAL NAME 'LEVENSHTEIN!levenshteinDistance' 
PARAMETER STYLE JAVA ;

This one tells me that the name LEVENSHTEIN could not be identified from the external name LEVENSHTEIN!levenshteinDistance and that the external name must be package.subpackage.class.method

I'm under Security Administrator in the Database, in theory I can create files and folders (I have created one folder under /Function and it did get created)

I've gone through a dozen redbooks and References for i, tried different things, so much so I can't even remember everything I've tried.

Any help is greatly appreciated. Thanks

Later Edit: Also tried with an empty constructor, also extending UDF and using a constructor calling to super(). So far, no luck

@ThorbjørnRavnAndersen the ones that I posted, there are no errors while copying nor while declaring the UDF. The only errores are when calling the UDF, which are "not found in classpath" or "could not be identified" — Manu Andrei, Sep 13 '21 at 08:04
Do you find your class in `select * from sysibm.SYSJARCONTENTS where JAR_ID = 'JARFUNCTIONS'` ? — nfgl, Sep 15 '21 at 17:52
@nfgl I do, I can see FUNCTIONS.LEVENSHTEIN there, however where it says class source I got a "-". Not sure if that's good or not. — Manu Andrei, Sep 16 '21 at 07:32
That just means `NULL`, I got your function working here, and got NULL to. Did you try to use the method from a callable class outside SQL using the JRE indicated by `select java_home from qsys2.JVM_INFO ji where job_name = QSYS2.job_name` ? — nfgl, Sep 16 '21 at 08:08
@nfgl I don't get any results to that Query. However, using a declared java function that has external name java.lang.System.getProperty and running SELECT DEBUG.GETPROPERTY('java.home') FROM SYSIBM.SYSDUMMY1; I get '/QOpenSys/QIBM/ProdData/JavaVM/jdk80/32bit/jre' — Manu Andrei, Sep 16 '21 at 08:26

score 0 · Answer 1 · answered Sep 13 '21 at 08:21

0

Wrong java method declaration. You forgot the static modifier. Refer to the Java user-defined scalar functions topic.

Parameter style Java

The Java parameter style is the style specified by the SQLJ Part 1: SQL Routines standard. When coding a Java UDF, use the following conventions.

The Java method must be a public static method.

The Java method must return an SQL compatible type. The return value is the result of the method.

The parameters of the Java method must be SQL compatible types.

The Java method may test for a SQL NULL for Java types that permit the null value.

answered Sep 13 '21 at 08:21

Mark Barinstein

11,456
2
8
16

Just changed it to static int and re-registered it, same error, you are right though, I had removed it to try without it as well and forgot to put it back – Manu Andrei Sep 13 '21 at 08:40
Try `CALL SQLJ.REFRESH_CLASSES ()` after the class / jar redeployment. – Mark Barinstein Sep 13 '21 at 08:44
I did, dropped the function, called SQLJ.REPLACE_JAR, then SQLJ.REFRESH_CLASSES() and then re-created the function, using CALL SQLJ.REPLACE_JAR('file:/QIBM/UserData/OS400/SQLLib/Function/testMain.jar','JARFUNCTIONS'); And EXTERNAL NAME 'JARFUNCTIONS:FUNCTIONS.LEVENSHTEIN.levenshteinDistance' Still getting an error saying could not load Java class FUNCTIONS/LEVENSHTEIN error code 1 - Class not found in CLASSPATH – Manu Andrei Sep 13 '21 at 08:47
Do you really see the `/QIBM/UserData/OS400/SQLLib/Function/jar/YOUR_SCHEMA/JARFUNCTIONS.jar` file (`YOUR_SCHEMA` is specific to your environmen) after `CALL SQLJ.xxx_JAR (...)`? How to you call this `DEBUG.LEVENSHTEIN` function exactly? – Mark Barinstein Sep 13 '21 at 10:02
Yes, I can see the JAR over there, so it seems to be registered. Also checked in SYSIBM.ROUTINES the full external name and it does include my user ANDREI.JARFUNCTIONS:FUNCTIONS.LEVENSHTEIN.levenshteinDistance I have two UDFs defined, one with the JAR and one with the CLASS, I call both the same way, tried both SELECT DEBUG.LEVENSHTEIN('kitten','sitting') FROM MAESTROS.ARTICULO FETCH FIRST 1 ROWS ONLY; And just in case SELECT DEBUG.LEVENSHTEIN(VARCHAR('kitten'),VARCHAR('sitting')) FROM MAESTROS.ARTICULO FETCH FIRST 1 ROWS ONLY; – Manu Andrei Sep 13 '21 at 10:23
With both DEBUG.LEVENSHTEIN (which is the .class external name) and DEBUG.LV (the JAR). Both are in the 'DEBUG' schema. – Manu Andrei Sep 13 '21 at 10:25
Just in case: do you have the `FUNCTIONS/LEVENSHTEIN.class` structure in the jar file and not, say, just `LEVENSHTEIN.class` at the root level? – Mark Barinstein Sep 13 '21 at 11:30
I do, I opened up the JAR with WinRAR and I have a folder called FUNCTIONS and inside it there is the LEVENSHTEIN.class – Manu Andrei Sep 13 '21 at 12:33

DB2 7.2 Java UDF not in Classpath

1 Answers1