calling sqlj.install_jar from jdbc?

I have a python snippet that I try to port to java. The aim is Gradle but I could not get that to work, so I figured I would start with plain JDBC. The python code looks like this:

import argparse
import ibm_db
import sys

def main(hostname, database, username, password):

    connstr = f"DATABASE={database};HOSTNAME={hostname};PORT=50000;PROTOCOL=TCPIP;UID={username}; PWD={password}"
    conn = ibm_db.connect(connstr, "", "")
    s1 = ibm_db.prepare(conn, "call sqlj.install_jar('file:/tmp/commons.jar', 'StringUtil_TEST')")
    ibm_db.execute(s1, ())
    # print(ibm_db.fetch_tuple(s1)[0])
    ibm_db.commit(conn)
    sys.exit(0)

and works. The jar file ends up in ~/sqllib/function/jar/DB2INST1 on the target host as expected.

I’ve looked at several examples in java and try to mimic them, but I always end up with an error (most of them likely because I’m illiterate when it comes to java). The current code I have is:

   public static void main(String argv[])
    {
        try {
            Connection con = null;
            Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
            con = DriverManager.getConnection(<host>, <user>, <passwd>);
            CallableStatement callStmt1 = null;
            callStmt1 = con.prepareCall("call sqlj.install_jar('" + "file:/tmp/commons.jar" + "', 'StringUtil_TEST2')");

            // tried this as well 
            // callStmt1 = con.prepareCall("call sqlj.install_jar('" + "file:/tmp/commons.jar" + "', 'StringUtil_TEST2', 0)");

            callStmt1.execute();
        }
        catch (Exception e)
        {
            //System.out.println(e.);
            e.printStackTrace();
        }
    }
}

compiled as:

#> javac -cp db2jcc4.jar aa.java

and run as:

#> java -cp .:db2jcc4.jar Aa
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=SQLJ.INSTALL_JAR;PROCEDURE, DRIVER=4.29.24
    at com.ibm.db2.jcc.am.b7.a(b7.java:810)
    at com.ibm.db2.jcc.am.b7.a(b7.java:66)
    at com.ibm.db2.jcc.am.b7.a(b7.java:140)
    at com.ibm.db2.jcc.am.k9.c(k9.java:2844)
    at com.ibm.db2.jcc.am.k9.d(k9.java:2828)
    at com.ibm.db2.jcc.am.k9.a(k9.java:2254)
    at com.ibm.db2.jcc.am.k_.a(k_.java:8277)
    at com.ibm.db2.jcc.t4.ab.i(ab.java:204)
    at com.ibm.db2.jcc.t4.ab.e(ab.java:133)
    at com.ibm.db2.jcc.t4.p.d(p.java:63)
    at com.ibm.db2.jcc.t4.av.m(av.java:190)
    at com.ibm.db2.jcc.am.k9.am(k9.java:2249)
    at com.ibm.db2.jcc.am.k_.bs(k_.java:3778)
    at com.ibm.db2.jcc.am.k_.a(k_.java:4640)
    at com.ibm.db2.jcc.am.CallableStatement.b(CallableStatement.java:136)
    at com.ibm.db2.jcc.am.CallableStatement.a(CallableStatement.java:105)
    at com.ibm.db2.jcc.am.CallableStatement.execute(CallableStatement.java:95)
    at Aa.main(aa.java:28)

Meaning of

  SQL0440N  No authorized routine named "<routine-name>" of type
  "<routine-type>" having compatible arguments was found.

I would have expected that error to happen during prepare, but it happens during execute so I’m not sure what to make out of it.

Does anyone have a working snippet to share for calling sqlj.install_jar from jdbc?

Answer

Try this:

Connection con = null;
Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
con = DriverManager.getConnection(<host>, <user>, <passwd>);
DB2Connection db2con = (DB2Connection) con;
FileInputStream is = new FileInputStream("/tmp/commons.jar");
db2con.installDB2JavaStoredProcedure(is, (int) is.getChannel().size(), "StringUtil_TEST2");
...