Implementing Java stored procedure IBM DB2 for i (AS400)

Question

I created the Java stored procedure as follows, able to generate a .class file using AS400 Qshell command interpreter.

    import java.sql.*;

    public class sample 
    {
        public sample(){
            super();
        }

        /**
         * @param args
         */
        public static void Test(int test) throws SQLException, Exception {
            // TODO Auto-generated method stub
            try
            {   Class.forName ("com.ibm.as400.access.AS400JDBCDriver").newInstance ();

                String url  = "jdbc:as400://ilava;naming=system;prompt=false;user=IPGUI;password=IPGUI;libraries=IPSRFILI,IPTSFILI,IPWMFILI,IPPAFILI,IPASFILI,IPSAFILI,IPTSUTL;translate binary=true";          
                Connection con = DriverManager.getConnection(url);          
                PreparedStatement ps = con.prepareStatement("INSERT INTO IPTSFILI.TEMP VALUES ('IP', 'WELCOME TO SQLJ')");
                ps.executeUpdate();
                ps.close();
                con.close();                
            } 
            catch (Exception e)
            {  
                System.out.println (e);
                System.exit(1);             
            }                       
    }       
 }

created the respective store procedure as

CREATE procedure IPTSFILI.SPSAMPLE ( 
    IN TEST INTEGER ) 
    LANGUAGE JAVA 
    SPECIFIC IPTSFILI.SPSAMPLE 
    DETERMINISTIC 
    MODIFIES SQL DATA 
    CALLED ON NULL INPUT 
    EXTERNAL NAME 'sample!Test' 
    PARAMETER STYLE JAVA ;

When the procedure is getting called (Using callable Statement in JAVA) it gives below error as

java.sql.SQLException: [SQL4304]  Java stored procedure or user-defined function SPSAMPLE, specific name SPSAMPLE could not load Java class Ä?_ÑÂ_À¦ÀÂÄ/øøàâàÊÑÎÁÊ for reason code 3.
    at com.ibm.as400.access.JDError.throwSQLException(JDError.java:687)
    at com.ibm.as400.access.JDError.throwSQLException(JDError.java:653)
    at com.ibm.as400.access.AS400JDBCStatement.commonExecute(AS400JDBCStatement.java:920)
    at com.ibm.as400.access.AS400JDBCPreparedStatement.execute(AS400JDBCPreparedStatement.java:1018)
    at Exec.main(Exec.java:23)

Placed the .class file in the QIBM\UserData\OS400\SQLLib\Function directory.

Any suggestions on how to solve this?

Answer 1

You might not specify the procedure in a correct way. Looking at the documentation of OS400 and Java stored proc (see Chap 7) I think you are missing several steps here. I advise to take the code from the doc and run it, then replace with your own implementation.