Use Java UDF on AS400/iSeries

1. Create a Java program and put it in /qibm/userdata/os400/sqllib/function
2. Define UDF function
3. Use it in SQL

Note this Java UDF is about 30 times slower than its RPG counterpart.

Example:

/*
 * MrcUDFs.java   Created on Jul 24, 2008, 10:57:29 AM
//
//     The Java Regular Expressions library requires version 1.4 of 
//     the JDK to be installed AND active.
//
//
//     For the iSeries, this class must be placed in folder:
//     /QIBM/USERDATA/OS400/SQLLIB/FUNCTION
//
//     To Compile (assuming UDFs.java is in folder /myfolder):
//     
//     1. Start QShell  (QSH)
//     2. Use the javac command as follows (the Java file name is case sensitive!):
//        javac -d /qibm/userdata/os400/sqllib/function  UDFs.java
//
//     For optimal performance on the iSeries, issue the CRTJVAPGM
//     command from the command line:
//
//     CRTJVAPGM CLSF('/qibm/userdata/os400/sqllib/function/UDFs.class') 
//               OPTIMIZE(40)
 */
import COM.ibm.db2.app.*; // Class for using Java methods with SQL UDFs
//import com.ibm.db2.app.*; // this only compile on AS400
import java.math.BigDecimal; // For compatability with SQL DEC data type

public class MrcUDFs {
 
    public MrcUDFs() {
        
    }
 
    public static BigDecimal convertNumber(BigDecimal number) {
       
        double d = number.doubleValue();
        double converted = 0;
        if (d < 100000) {
            converted = d + 11;
        } else if (d < 100100) {
            converted = d + 22;
        } else if (d < 100200) {
            converted = d + 33;
        } else if (d < 100300) {
            converted = d + 44;
        } else if (d < 100800) {
            converted = d + 88;
        } else {
            converted = d + 99;
        }
        return new BigDecimal(converted);
    } 
}

/*                              
 CREATE FUNCTION MRCJAVALIB/cvt1(Amount DEC(6,0)) RETURNS DEC(6,0) 
 EXTERNAL NAME 'MrcUDFs.convertNumber'                            
 LANGUAGE Java                                                    
 PARAMETER STYLE Java                                             
 FENCED                                                           
 NO SQL                                                           
 RETURNS NULL ON NULL INPUT                                       
Function CVT1 was created in MRCJAVALIB.   

Now you can query it using:
SELECT ordnum, ORDLIN, PRDNO, MRCJAVALIB.CVT1(cusno) "
  " FROM MRCJAVALIB.ORDERHIS2  "
  + " ORDER BY MRCJAVALIB.cvt1(cusno)"   
  + " fetch first 20000 rows only";   
*/