Hi all,

I stuck in a problem where i need to call a jave program in db2 and later schedule it.

what i did till now:

1. converted my java file to jar.

2. cataloged that jar in db2 using "SQLJ"."DB2_INSTALL_JAR"

3. tried to create db2 procedure to call that java class / jar in EXTERNAL NAME field.

FAILED HERE in step 3: becoz my java prog. don't have any method except main without in/out parameter ( means its a simple java class) .

after going through inbuilt examples at /opt/ibm/db2/V10.1/samples/java/jdbc/ i concluded that " its mandatory for java program to have a method and in/out or inout parrameter to be defined inside a db2 stored proc. Or it will produce error (i tried it).


Now i dont know what to do ? please suggest

I cant modify java program. Is there any way otherwise. java program definition is as below:

----------------------------------------------------------------
import java.io.*;
import java.net.*;
import java.util.*;
import java.sql.*;


//import java.text.NumberFormat;


public class dsc_expiry_update {

public static void main(String args[]){

Connection newCon = null;
Statement Statmt,stmt0;
PreparedStatement st1;
//PreparedStatement pstmt=null,pstmt1=null,pstmt2=null;
ResultSet Res=null,res1=null,res2=null,res3=null,rs0=null;

java.util.Date cur_date= new java.util.Date();

try
{

//Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
//newCon = DriverManager.getConnection("jdbc:db2:dbmstc", "db2inst1", "db2inst1");
Class.forName("com.ibm.db2.jcc.DB2Driver");
newCon = DriverManager.getConnection("jdbc:db2://localhost:50001/DBMSTC","db2instn", "mstc@123");
java.sql.Statement stmt=newCon.createStatement();
stmt.execute("set schema eproc");
stmt.close();
System.out.println("Connected successfully to eproc schema "+cur_date);



}catch(Exception E){
System.out.println( "[jdbcServlet.service1():" + E + "]" );
//return;
}// end try


try{
Statmt = newCon.createStatement();

try{

stmt0 = newCon.createStatement();
//rs0 = stmt0.executeQuery("select USER_REF_ID,REF_ID,USER_TYPE from EP_DIGITAL_CERT_INFO where STATUS='a' and (CERT_EXPIRY_TIME<current timestamp or DE_EXPIRY_TIME<current timestamp)");
rs0 = stmt0.executeQuery("select USER_REF_ID,REF_ID,USER_TYPE from EP_DIGITAL_CERT_INFO where STATUS='a' and CERT_EXPIRY_TIME<current timestamp");
while(rs0.next())
{
String s8 = rs0.getString(1);
String dscchrno = rs0.getString(2);
String usertp = rs0.getString(3);
if(usertp==null) usertp="";

st1= newCon.prepareStatement("update EP_DIGITAL_CERT_INFO set STATUS='e' where REF_ID="+dscchrno);
st1.executeUpdate();
st1=null;

if(usertp.equals("v"))
{
st1= newCon.prepareStatement("update EP_VENDOR_MASTER set DSC_STATUS='n' where REF_ID="+s8);
st1.executeUpdate();
}
else
{
st1= newCon.prepareStatement("update EP_PM_USERS set DSC_STATUS='n' where USER_REF_ID="+s8);
st1.executeUpdate();
}

System.out.println("Expired DSC Updated Done on -->"+cur_date+" for User_REF_ID= "+s8+" User Type= "+usertp+" DSC Chrono No-->"+dscchrno);
} //for end
rs0.close();
//System.out.println("Expired DSC Updated Done on -->"+cur_date);
}catch(SQLException sqe){
System.out.println("Expired DSC Updation failed :"+sqe+" on "+cur_date);
}
newCon.commit();
stmt0=null;

/************************************************** ******************************/

try{

stmt0 = newCon.createStatement();
rs0 = stmt0.executeQuery("select USER_REF_ID,REF_ID,DE_SERIAL_NO from EP_DIGITAL_CERT_INFO where STATUS='a' and DE_EXPIRY_TIME<current timestamp");
//rs0 = stmt0.executeQuery("select USER_REF_ID,SL_ID from EP_ENCRYPTION_CERT_STORE where DE_STATUS='a' and DE_EXPIRY_TIME<current timestamp");
while(rs0.next())
{
String s8 = rs0.getString(1);
String dscchrno = rs0.getString(2);
String desrlno = rs0.getString(3);
if(desrlno==null) desrlno="";

st1= newCon.prepareStatement("update EP_DIGITAL_CERT_INFO set STATUS='e' where REF_ID="+dscchrno);
st1.executeUpdate();
st1=null;


//st1= newCon.prepareStatement("update EP_ENCRYPTION_CERT_STORE set DE_STATUS='e' where SL_ID="+dscchrno);
st1= newCon.prepareStatement("update EP_ENCRYPTION_CERT_STORE set DE_STATUS='e' where USER_REF_ID="+s8+" and DE_SERIAL_NO='"+desrlno+"' and DE_STATUS='a' and DE_EXPIRY_TIME<current timestamp");
st1.executeUpdate();
st1=null;

System.out.println("Expired ENCRYPTION Updated Done on -->"+cur_date+" for User_REF_ID= "+s8+" ENCRYPTION SL ID-->"+dscchrno);
} //for end
rs0.close();
}catch(SQLException sqe){
System.out.println("Expired ENCRYPTION Updation failed :"+sqe+" on "+cur_date);
}
newCon.commit();
/************************************************** *******************************/


}catch(Exception es){
System.out.println("ss"+es);

}finally{
try{
newCon.close();
}catch(Exception nn){}


}//finally

}
}



--------------------------------

thanks

ssumit