Hi,

I am using DB2v8.1 to do some POC. I want to execute a java stored procedure. I used the development centre tool to create the java stored procedure and also to installing in the JAR file manually by SQLJ.INSTALL, But failed in both.



There are two problems here:

1)The development centre tool works fine, if you want to just create a simple produre. But if you modify the java program by lets say, put import com.ibm.mq.* then it throws error package couldn't be found. I included the jar file in the CLASSPATH also.

package PKG110720044512120;

import java.sql.*; // JDBC classes
import java.io.*;
import com.ibm.mq.*;

public class PROCEDURE2
{
public static void pROCEDURE2 ( String empid ) throws SQLException, Exception
{
....................................
.............................................


DB2ADMIN.PROCEDURE2 - Build started.
DROP SPECIFIC PROCEDURE DB2ADMIN.SQL110820164732311
DB2ADMIN.PROCEDURE2 - Drop stored procedure completed.
C:\PROGRA~1\IBM\SQLLIB\java\jdk\bin\javac -classpath ".;C:\PROGRA~1\IBM\SQLLIB\java\db2java.zip;C:\PROG RA~1\IBM\SQLLIB\java\runtime.zip;C:\PROGRA~1\IBM\S QLLIB\java\sqlj.zip" PKG110720044512120\PROCEDURE2.java
PKG110720044512120\PROCEDURE2.java:8: package com.ibm.mq does not exist
import com.ibm.mq.*;
^
1 error
DB2ADMIN.PROCEDURE2 - Build failed.
DB2ADMIN.PROCEDURE2 - Roll back completed successfully.



2) If I manually create my java program and put the JAR file using the SQLJ.INSTALL command, it gives error

Below is the Procedure definition executed in db2admin schema:
CREATE PROCEDURE PROCEDURE4 (IN empid CHAR(5))
NOT DETERMINISTIC
LANGUAGE JAVA
EXTERNAL NAME 'SQLJAR1.PROCEDURE4.pROCEDURE4'
FENCED
THREADSAFE
PARAMETER STYLE JAVA

call procedure4('123')
SQL4304N Java stored procedure or user-defined function "DB2ADMIN.PROCEDURE4",
specific name "SQL110821133934913" could not load Java class
"SQLJAR1/procedure4", reason code "1". SQLSTATE=42724

SQL4304N Java stored procedure or user-defined function "DB2ADMIN.PROCEDURE4", specific name "SQL110821133934913" could not load Java class "SQLJAR1/procedure4", reason code "1 ".

Explanation:

The Java class given by the EXTERNAL NAME clause of a CREATE
PROCEDURE or CREATE FUNCTION statement could not be loaded. The
reason codes are:


1 The class was not found on the CLASSPATH.

2 The class did not implement the required interface
("COM.ibm.db2.app.StoredProc" or "COM.ibm.db2.app.UDF") or lacked
the Java "public" access flag.

3 The default constructor failed or was unavailable.

4 Could not load driver for "jdbc:default:connection".

5 Could not establish default context.

User Response:

Ensure that the compiled ".class" file is installed in the
CLASSPATH, for example under "sqllib/function". Ensure it
implements the required Java interfaces and is "public".

sqlcode : -4304

sqlstate : 42724