DB2 external java jar file stored procedure error

Question

I have been trying to call a java jar file from DB2 stored procedure as external jar. Below is my external jar file code.

package connection;
    public class Connect {

        static Connection con = null;
        static Statement stmt = null;

        public static void main(String[] args) throws SQLException {
                javastp("v1");
        }

        public static void javastp( String name) throws SQLException{
            try {
            con = DriverManager.getConnection( "jdbc:default:connection" );
            stmt = con.createStatement();
            stmt.executeUpdate("INSERT INTO SCHEMA_NAME.TEST(NAME, FLAG) VALUES ('"+name+"',true) "); 
            }catch (Exception e) {
                // ...
              }finally {
            //Close open resources
            if (stmt != null) stmt.close();
            if (con != null) con.close();  
            }
        }
    }

i install it using below command in db2 server

db2 call sqlj.install_jar('file:E:/jarpath../jarname.jar','jarname',0); 

Below is the stored procedure for DB2 external jar.

CREATE OR REPLACE PROCEDURE SCHEMA_NAME.PROC6()
  LANGUAGE java
  PARAMETER STYLE java 
  FENCED 
  EXTERNAL NAME 'jarname:connection.Connect.main'

The stored procedure executes successfully without errors.

But when i try to call the stored procedure using below,

CALL SCHEMA_NAME.PROC6() 

i get the below error

SQL Error [38503]: A stored procedure process has been terminated abnormally. Routine name: "SCHEMA_NAME.PROC6". Specific name: "SQL201016144125554".. SQLCODE=-1131, SQLSTATE=38503, DRIVER=4.8.86

The java jar works fine when executed directly in the command prompt and the records are being inserted into the table, but i cant seem to figure out why the external stored procedure gives me error when i try to call it that way.

Can somebody here please help me out as am stuck on this since a very long time and i have no background of DB2.

the db2level command gives the below output:

DB21085I  This instance or install (instance name, where applicable: "DB2")
uses "64" bits and DB2 code release "SQL10058" with level identifier
"0609010E".
Informational tokens are "DB2 v10.5.800.381", "s160901", "IP24000", and Fix
Pack "8".
Product is installed at "C:\PROGRA~1\IBM\SQLLIB" with DB2 Copy Name "DB2COPY1".

Linux Suse db2 version 11.1.2

Database server = DB2/LINUXX8664 11.1.2

The log is as below:

2020-10-20-09.24.56.746633+330 I4952989679E1230      LEVEL: Error
PID     : 31693                TID : 140179352315648 PROC : db2sysc 0
INSTANCE:              NODE : 000            DB   : 
APPHDL  :               APPID: 
AUTHID  :               HOSTNAME: 
EDUID   : 2768                 EDUNAME: db2agent () 0
FUNCTION: DB2 UDB, routine_infrastructure, sqlerWaitForFencedInvocation, probe:12115
MESSAGE : ZRC=0xFFFFFB38=-1224
          SQL1224N  The database manager is not able to accept new requests,
          has terminated all requests in progress, or has terminated the
          specified request because of an error or a forced interrupt. 

Answer 1

Error SQL1131N has the following description

The DB2 architecture is designed so that applications run in a different address space than the database server. Running applications in a different address space prevents application programming errors from overwriting database manager internal buffers or files, and prevents application errors from crashing the database manager. The fenced mode process (db2fmp) is responsible for executing fenced stored procedures and user-defined functions in a different address space than the database server.

This message is returned when the db2fmp process terminates abnormally while running the named routine. The db2fmp process could have terminated abnormally for many reasons, including the following reasons:

• There was a coding error, such as division by zero or an out-of-bounds pointer reference, in the implementation of the stored procedure or user-defined function that the db2fmp process was executing.

• Another process terminated the db2fmp process using a signal, such as the termination signal SIGTERM.

• SQLJ.INSTALL_JAR failed while installing a Java routine because the fenced user does not have permission to create or write to the necessary directories on the server.

Answer 2

Db2 is unforgiving when you make mistakes with external non-SQL code, in this case, it just throws an exception and expects you to sort it out.

Only use java for Db2 stored procedures when there is a compelling reason to NOT use SQL PL procedures.

You have these mistakes

• You cannot use a main() for a Db2 java stored procedure entry point
• The stored procedure entry point should be in your case the javastp() method
• The EXTERNAL NAME clause in the DDL must not reference main() but should use javastp instead
• The parameters in the create procedure line must match those of the method in number and type and sequence. (In your case that is (IN name varchar(255) or similar).