Db2 LUW Java SP issue

Question

I am trying to run a Stored Procedure which has registered as an external Java Program to Db2. Whenever I call it, I keep getting the error:

SQL4304N Java stored procedure or user-defined function "RSARKAR.CREATE_USER", specific name "CREATE_USER" could not load Java class "/home/rsarkar/sqllib/function/jar/RSARKAR", reason code "". SQLSTATE=42724

The db2diag.log is showing the following messages ( with DIAGLEVEL 4 )

2023-01-03-22.10.02.119785+000 I27596E440 LEVEL: Info PID : 5363 TID : 139874111354624 PROC : db2fmp ( INSTANCE: rsarkar NODE : 000 DB : SAMPLE APPID : *LOCAL.rsarkar.230103220934 HOSTNAME: ip-172-31-20-141.us-west-1.compute.internal FUNCTION: DB2 UDB, BSU Java support, sqlejCallJavaRoutine_dll, probe:90 DATA #1 : String, 47 bytes Exception thrown during class loader loadClass:

2023-01-03-22.10.02.119949+000 I28037E448 LEVEL: Warning PID : 5363 TID : 139874111354624 PROC : db2fmp ( INSTANCE: rsarkar NODE : 000 DB : SAMPLE APPID : *LOCAL.rsarkar.230103220934 HOSTNAME: ip-172-31-20-141.us-west-1.compute.internal FUNCTION: DB2 UDB, BSU Java support, sqlejCallJavaRoutine_dll, probe:40 MESSAGE : Class loader loadClass failed. Possible out of memory in JAVA_HEAP_SZ

I've tried with JAVA_HEAP_SZ set to 1,000,000 but still get the same error.

Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 1000000

2023-01-03-22.10.02.120041+000 I28486E636 LEVEL: Warning PID : 5363 TID : 139874111354624 PROC : db2fmp ( INSTANCE: rsarkar NODE : 000 DB : SAMPLE APPID : *LOCAL.rsarkar.230103220934 HOSTNAME: ip-172-31-20-141.us-west-1.compute.internal FUNCTION: DB2 UDB, BSU Java support, sqlejCallJavaRoutine_dll, probe:40 MESSAGE : PATH to jar is in the form of sqllib/function/jar/. jar_id: DATA #1 : Hexdump, 17 bytes 0x00007F372B039C22 : 2252 4453 4442 2020 2022 2E44 4232 4A41 "RSARKAR ".DB2JA 0x00007F372B039C32 : 52 R

2023-01-03-22.10.02.120139+000 I29123E871 LEVEL: Warning PID : 5363 TID : 139874111354624 PROC : db2fmp ( INSTANCE: rsarkar NODE : 000 DB : SAMPLE APPID : *LOCAL.rsarkar.230103220934 HOSTNAME: ip-172-31-20-141.us-west-1.compute.internal FUNCTION: DB2 UDB, BSU Java support, sqlejCallJavaRoutine_dll, probe:40 MESSAGE : If not using JAR, then common paths are sqllib/function and sqllib/function/unfenced. Check that you have file .class or this jar: DATA #1 : Hexdump, 52 bytes 0x00007F372B03ADA0 : 2F68 6F6D 652F 7264 7364 622F 7371 6C6C /home/rsarkar/sqll 0x00007F372B03ADB0 : 6962 2F66 756E 6374 696F 6E2F 6A61 722F ib/function/jar/ 0x00007F372B03ADC0 : 5244 5344 422F 4442 324A 4152 2E6A 6172 RSARKAR/DB2JAR.jar 0x00007F372B03ADD0 : 3A44 6232 :Db2

2023-01-03-22.10.02.120232+000 I29995E766 LEVEL: Warning PID : 5363 TID : 139874111354624 PROC : db2fmp ( INSTANCE: rdsdb NODE : 000 DB : SAMPLE APPID : *LOCAL.rdsdb.230103220934 HOSTNAME: ip-172-31-20-141.us-west-1.compute.internal FUNCTION: DB2 UDB, BSU Java support, sqlejCallJavaRoutine_dll, probe:41 MESSAGE : Method missing from the class above: DATA #1 : Hexdump, 63 bytes 0x00007F372B03ADD5 : 6372 6561 7465 284C 6A61 7661 2F6C 616E create(Ljava/lan 0x00007F372B03ADE5 : 672F 5374 7269 6E67 3B4C 6A61 7661 2F6C g/String;Ljava/l 0x00007F372B03ADF5 : 616E 672F 5374 7269 6E67 3B4C 6A61 7661 ang/String;Ljava 0x00007F372B03AE05 : 2F6C 616E 672F 5374 7269 6E67 3B29 56 /lang/String;)V

2023-01-03-22.10.02.122131+000 I32501E843 LEVEL: Info PID : 5363 TID : 139874111354624 PROC : db2fmp ( INSTANCE: rsarkar NODE : 000 DB : SAMPLE APPID : *LOCAL.rsarkar.230103220934 HOSTNAME: ip-172-31-20-141.us-west-1.compute.internal FUNCTION: DB2 UDB, oper system services, sqlofica, probe:10 DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes sqlcaid : SQLCA sqlcabc: 136 sqlcode: -4304 sqlerrml: 70 sqlerrmc: RSARKAR.CREATE_USER CREATE_USER /home/rsarkar/sqllib/function/jar/RSARKAR sqlerrp : SQLEJEXT sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000 (4) 0x00000000 (5) 0x00000000 (6) 0x00000000 sqlwarn : (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11) sqlstate: 42724

The definition of the SP is as follows:

call sqlj.install_jar( 'file:/home/rsarkar/plugins/DB2.jar', 'DB2JAR'); call sqlj.refresh_classes();

CREATE OR REPLACE PROCEDURE RSARKAR.CREATE_USER ( USERID VARCHAR(128), PASS VARCHAR(128) DEFAULT NULL, GROUPS VARCHAR(256) DEFAULT NULL ) LANGUAGE JAVA SPECIFIC RDSADMIN.CREATE_USER PARAMETER STYLE JAVA DETERMINISTIC NO EXTERNAL ACTION NO SQL FENCED THREADSAFE EXTERNAL NAME 'DB2JAR:Db2.create';

I was expecting it to run but it keeps giving me error that it cannot find the method in the class.

I tried with a smaller test with a simple Java program and registered it as follows:

create or replace procedure MYJAVASP (in input char(6)) specific myjavasp dynamic result sets 0 deterministic language java parameter style java no dbinfo fenced threadsafe modifies sql data program type sub external name 'MYJAVASPJAR:MYJAVASP.my_JAVASP';

This one works fine without any issues. Any pointers or ideas are welcome.

The definition in Db2.java:

package db2.registry.sql.storedprocedure;

import db2.registry.User;
import db2.registry.UserRegistry;
import org.apache.commons.lang3.StringUtils;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Arrays;
import java.util.Collection;
import java.util.Optional;
import java.util.stream.Collectors;

import static db2.registry.utils.LineBasedFileUtils.DEFAULT_LOCALUSERS_FILE;

public class Db2 {
    public static void create(String username, String password, String groups) {
        UserRegistry.createUser(username, password, unpackGroups(groups), DEFAULT_LOCALUSERS_FILE);
    }

    public static void delete(String username) {
        UserRegistry.deleteUser(username, DEFAULT_LOCALUSERS_FILE);
    }
    
    public java.sql.ResultSet list() {
        Collection<User> users = UserRegistry.listUsers(DEFAULT_LOCALUSERS_FILE).values();
        return convertToListCallResultSet(users);
    }

    public static void modify(String username, String password, String groups) {
        UserRegistry.modifyUser(username, password, unpackGroups(groups), DEFAULT_LOCALUSERS_FILE);
    }
}

The create procedure statement: 

call sqlj.remove_jar( 'DB2JAR');
call sqlj.install_jar( 'file:/home/rsarkar/plugins/DB2.jar', 'DB2JAR');
call sqlj.refresh_classes();

CREATE OR REPLACE PROCEDURE RSARKAR.CREATE_USER
(
  USERID VARCHAR(128),
  PASS   VARCHAR(128) DEFAULT NULL,
  GROUPS VARCHAR(256) DEFAULT NULL
)
LANGUAGE JAVA
SPECIFIC RSARKAR.CREATE_USER
PARAMETER STYLE JAVA
DETERMINISTIC
NO EXTERNAL ACTION
NO SQL
FENCED THREADSAFE
EXTERNAL NAME 'DB2JAR:Db2.create';

Calling the Stored Procedure:

db2 "call rsarkar.create_user('testuser','xx','DBA')"