java.lang.UnknownError when calling a Java stored procedure

Problem summary:

I developed a java class, compile it, and register it as a Java stored
procedure. This java class contains 4 static methods (denoted as m1, m2, m3,
and m4): m1 is a utility method; m2 is registered with the "create stored
procedure" procedure; m2 calls m3 or m4 depending on the value of input
parameters.

I encounter an java.lang.UnknownError when I run this stored procedure at
50% chance! It means, sometimes I can get the result I want, and sometimes
it generates an error.

I wonder if anyone has some insights about this problem. Many thanks!


More detailed information:

When I run the corresponding java stored procedure from the "Run SQL
scripts" window, it generates following error message:

-------------------------------------------------
Java stored procedure or user-defined function IDIGPROC.JAVAMTDCOMPARISON5,
specific name JAVAMTDCOMPARISON5 aborted with an exception
"java.lang.UnknownError". Cause . . . . . : The Java stored procedure or
user-defined function aborted with a Java exception. If SQJAVA component
trace is on, then the component trace for the job contains a Java stack
traceback for the aborted method. Recovery . . . : Debug the Java method
to eliminate the exception.-------------------------------------------------

When I call the same java stored procedure through a client java class, it
generates following error message:
---------------------------------------------------------------------------
java.sql.SQLException: [SQL4302] Java stored procedure or user-defined
function IDIGPROC.JAVAMTDCOMPARISON5, specific name JAVAMTDCOMPARISON5
aborted with an exception "java.lang.UnknownError".
at
com.ibm.as400.access.JDError.createSQLExceptionSubClass(JDError.java:811)
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:665)
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:635)
at
com.ibm.as400.access.AS400JDBCStatement.commonExecute(AS400JDBCStatement.java:917)
at
com.ibm.as400.access.AS400JDBCPreparedStatement.execute(AS400JDBCPreparedStatement.java:828)
at vip.Client.main(Client.java:46)
----------------------------------------------------------------------------



------------------------------------------------------------------------------------------------------------
My pseudo java code:
------------------------------------------------------------------------------------------------------------

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.StringTokenizer;


public class salecomparison {

// a utility method
public static String parseList(String s, String delim) {
String parsedString = "";
String aToken;
StringTokenizer st = new StringTokenizer(s, delim);
while (st.hasMoreTokens()) {
aToken = st.nextToken().trim();
parsedString += "'" + aToken + "',";

}
parsedString = parsedString.trim();
parsedString = parsedString.substring(0, parsedString.length() - 1);
System.out.println(parsedString);
return parsedString;
}


// this method is used to register a java stored procedure
public static void comparison(
String listDist, BigDecimal dateThrough, String rptype, String
mtype, String orderStr, String order, int num, ResultSet[] rs)
throws SQLException, Exception {
Connection con =
DriverManager.getConnection("jdbc:default:connection");
Statement stmt = null;
String distStr = null;
listDist = listDist.trim();
if (listDist.length() > 1) {// distributor info is provided. e.g.
listDist = "1332,2343,3432"

distStr = parseList(listDist, ","); // distStr = "
'1332','2343','3432' "

} else {// no distributor filter
}


rptype = rptype.trim();
String sql = "";
if (rptype.equalsIgnoreCase("FULLMON")) {
sql = fullMon(distStr, dateThrough, mtype, orderStr, order,
num);
} else if (rptype.equalsIgnoreCase("ACTSELLDAY")) {
sql = actSellDay(con, distStr, dateThrough, mtype, orderStr,
order,num);
} else {
}

// the debugtable is predefined. It is used to keep track of the
actual sql query has been run.
PreparedStatement traceStmt = null;
String traceStr = " INSERT INTO IDIGPROC.DEBUGTABLE VALUES (?,?,
CURRENT TIMESTAMP )" ;
traceStmt = con.prepareStatement(traceStr);
traceStmt.setString(1, "MTDcomparison5 "+listDist+" "+dateThrough+"
"+rptype);
traceStmt.setString(2, sql);
traceStmt.executeUpdate();
traceStmt.close();
//////////////////////////////////////////////////


stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs[0] = stmt.executeQuery(sql);

}


public static String fullMon(
String distStr, BigDecimal dateThrough, String mtype, String
orderStr, String order, int num) {

String sql;

// this part generates the sql statement


sql = ".....";


//////////

return sql;

}

public static String actSellDay(
Connection con, String distStr, BigDecimal throughdate, String
mtype, String orderStr, String order, int num)
throws SQLException, Exception {

String sql;

// some preparing work here.


// need access database to get the variable tdsfm value. This
variable will be used in generating
// the sql query


String query = "SELECT TDSFM FROM SRSGUSDIG.DIMTIME WHERE TYEAR = ?
AND TMON = ? AND TDAY = ?";
PreparedStatement ps = con.prepareStatement(query);
ps.setBigDecimal(1, new BigDecimal(yearTY));
ps.setBigDecimal(2, new BigDecimal(monthTY));
ps.setBigDecimal(3, new BigDecimal(dayTY));
ResultSet rs1 = ps.executeQuery();
BigDecimal tdsfm = null;
if (rs1.next()) {
tdsfm = rs1.getBigDecimal(1);
}
rs1.close();



// this part generates the sql statement

sql = ".................."

//-----------------------------
return sql;

}

}
-----------------------------------------------------------------------------------------------------------------------


Many thanks,
yan zhang