Connectivity Error in external java function with JDBC connectivity

ratheeshnellikkal · #1 (**permalink**) 08-26-09, 09:56

Hi All,

I need to create a external jave user defined function in db2 (db2 9.5 and AIX 5.1).
In my java class I need to establish a jdbc connectivity with the database .
following is my java code

import java.sql.*;
public class TestDataInsert {
public static String doInsertData(int id,String pcdata){
Connection conn = null;
String driver="com.ibm.db2.jcc.DB2Driver";
String url="jdbc:db2://server_ip:50000/MYDB";
String sql="insert into MYSCHEMA.MYTAB values(?,?)";
String msg="data inserted";
int status=0;
/*get the database connection */

try {
Class.forName(driver);
}catch (Exception e){
msg=e.getLocalizedMessage();
}
try{
conn =DriverManager.getConnection(url,"db2inst1","pass@ 123");
System.out.println("after connection ");
PreparedStatement st=conn.prepareStatement(sql);
System.out.println("after prepared statement ");
st.setInt(1,id);
st.setString(2,pcdata);
status=st.executeUpdate();
if(status==1){
conn.commit();
conn.close();
msg="data inserted";
}
} catch (Exception ex) {
msg=ex.getLocalizedMessage();
}
return msg;
}
}

This java class uses two jar files inorder to do the JDBC connectivity.
Those are

db2jcc.jar and db2jcc_license_cu.jar

This java code is working properly from my java tool (Eclipse) and inserting data into the table also.
i have created the external java user defined function as follows.

CREATE FUNCTION "MYSCHEMA"."JAVAFUN_INS" (
"ID" INTEGER,
"NAME" VARCHAR(50) )
RETURNS VARCHAR(20)
SPECIFIC "INS"
LANGUAGE JAVA
EXTERNAL NAME 'TestDataInsert.doInsertData'
PARAMETER STYLE JAVA
DETERMINISTIC
NOT FENCED
THREADSAFE
NO EXTERNAL ACTION
NO SQL
RETURNS NULL ON NULL INPUT
NO SCRATCHPAD
NO FINAL CALL
ALLOW PARALLEL
NO DBINFO
INHERIT SPECIAL REGISTERS;

I involked the UDF as follows:

select MYSCHEMA.JAVAFUN_INS(1,'test') from sysibm.sysdummy1

It throws me an error as follows.

No suitable driver

But i have done the following settings also like,

I put the .class file in /home/db2inst1/sqllib/function
I put both the jar files in /home/db2inst1/sqllib/function

and I have set the classpath as follows

/home/db2inst1/sqllib/java/db2java.zip:/home/db2inst1/sqllib/java/db2jcc.jar:
/home/db2inst1/sqllib/java/sqlj.zip:/home/db2inst1/sqllib/function/db2jcc.jar:
/home/db2inst1/sqllib/function/db2jcc_license_cu.jar:/home/db2inst1/sqllib/java/db2jcc_license_cu.jar

Still it is unable to find the driver.

Can any one help me out?
Thanks in advance.

Ratheesh Nellikkal