In my class I have created simple method below. This class also has a main function in it. I am calling getRes() from my DB2 stored proc.
public static void getRes() {
System.out.println("Start");
try{
Class.forName("com.ibm.db2.jcc.DB2Driver");
con = DriverManager.getConnection(
"jdbc:db2://url:50003/DB","user","Password");
Statement stmt=con.createStatement();
stmt.executeUpdate("INSERT INTO schema.TEST(ID) VALUES(1)");
} catch(Exception e){
System.out.println(e);
} finally {
if(con!=null) {
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
System.out.println("End");
}
I am calling this function from DB2 stored proc.
CREATE OR REPLACE PROCEDURE schema.sp_TEST1()
FENCED
MODIFIES SQL DATA
EXTERNAL NAME 'connection.Connect.getRes()'
LANGUAGE JAVA
PARAMETER STYLE JAVA;
call schema.sp_TEST1()
This stored procedure is getting executed successfully. But I doubt my method is executed or not. Because there is no entry made in the test table after execution.
Below are the steps I have followed to deploy this jar file. 1. Exported the jar from Eclipse. Verified if the jar is working fine. 2. Executed call sqlj.install_jar(). Verified the installation in SYSIBM.SYSJARCONTENTS. 3. SET the class path on db server export CLASSPATH=/home/db2inse5/sqllib/function/jar/SchemaName/JarName.jar 4.created the sp and called the sp.
Expected output is an entry made in table test. But I am not getting the expected result though the sp execution is successful.
Updated-20200423 I tried committing the database connection part and replaced it with a file creation in db server. This worked so I can confirm that the jar was correctly called from the SP and jar file worked correctly. But the above part is not resolved yet
public static void getRes() {
System.out.println("Start");
File myObj = new File("/opt/filename.txt");
myObj.createNewFile();
}
Wrong method of getting a Connection object in a Parameter style Java routine. Please read the following topic in the documentation: Parameter style JAVA procedures.
You must not get it as in an ordinal jdbc application. Don't use the Class.forName call. Use the following instead:
con = DriverManager.getConnection("jdbc:default:connection");
Read about Restrictions on external routines as well. Don't use System.out.println(...) inside a Java routine.
Do this as is on the database server from the db2 instance owner without any modifications. Create the directory structure mentioned. Be sure, that db2profile is sourced in your session.
ExecAny.java:
package ru.ibm.db2udf;
import com.ibm.db2.jcc.DB2Diagnosable;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.logging.FileHandler;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.logging.SimpleFormatter;
public class ExecAny
{
/*
--SQL statement for the class file placement w/o jar:
--${DB2_HOME}/function/ru/ibm/db2udf/ExecAny.class
--Uncomment the commented out line instead
--if you placed this class into jar file and deployed it.
CREATE OR REPLACE PROCEDURE EXEC_ANY
(
SQL VARCHAR(4000)
, LOG VARCHAR(512) DEFAULT NULL
)
LANGUAGE Java
EXTERNAL NAME 'ru.ibm.db2udf.ExecAny.exec'
--EXTERNAL NAME 'EXEC_ANY:ru.ibm.db2udf.ExecAny.exec'
FENCED THREADSAFE
MODIFIES SQL DATA
PARAMETER STYLE JAVA;
Usage:
call exec_any('declare global temporary table test(i int) on commit preserve rows not logged', '/tmp/exec_any.txt');
call exec_any('insert into session.test values 1', '/tmp/exec_any.txt');
-- W/o logging
call exec_any('insert into session.test values 2');
select * from session.test;
*/
public static void exec (String sql, String logfile) throws Exception
{
Logger logger = null;
FileHandler fhdl = null;
Connection con = null;
CallableStatement cst = null;
try
{
if (logfile != null && ! "".equals((logfile=logfile.trim())))
{
logger = Logger.getAnonymousLogger();
fhdl = new FileHandler(logfile, true);
fhdl.setFormatter(new SimpleFormatter());
logger.addHandler(fhdl);
logger.setLevel(Level.INFO);
logger.info("***");
}
con = DriverManager.getConnection("jdbc:default:connection");
con.setAutoCommit(false);
if (logger != null) logger.info("Trying to prepare: " + sql);
cst = con.prepareCall(sql);
if (logger != null) logger.info("Prepared: " + sql);
cst.execute();
if (logger != null) logger.info("Executed: " + sql);
}
catch (SQLException ex)
{
if (logger != null)
{
while (ex != null)
{
if (ex instanceof DB2Diagnosable)
{
DB2Diagnosable db2ex = (DB2Diagnosable) ex;
com.ibm.db2.jcc.DB2Sqlca sqlca = db2ex.getSqlca();
if (sqlca != null)
logger.severe("\nSQLCODE: " + sqlca.getSqlCode() + "\nMESSAGE: " + sqlca.getMessage());
else
logger.severe("\nError code: " + ex.getErrorCode() + "\nError msg : " + ex.getMessage());
}
else
logger.severe("\nError code (no db2): " + ex.getErrorCode() + "\nError msg (no db2): " + ex.getMessage());
logger.log(Level.SEVERE, ex.getMessage(), ex);
ex = ex.getNextException();
}
}
}
finally
{
if (fhdl != null) {fhdl.close(); fhdl = null;}
if (cst != null) {cst.close(); cst = null;}
if (con != null) {con.close(); con = null;}
}
}
}
Instruction on how to build and deploy it:
# Suppose we are on a Db2 server and !!!db2profile is sourced in the session!!!
# java sources are in source
# java classes are in classes
$ ls -l
drwxr-xr-x classes
drwxr-xr-x source
drwxr-xr-x sql
$ find source -name '*.java'
source/ru/ibm/db2udf/ExecAny.java
# Compile
$ (cd source; ${DB2_HOME}/java/jdk64/bin/javac -d ../classes ru/ibm/db2udf/ExecAny.java)
# Create jar
$ ${DB2_HOME}/java/jdk64/bin/jar cvf exec_any.jar -C classes ru/ibm/db2udf/ExecAny.class
# Deploy jar
$ db2 connect to mydb
#$ db2 "call sqlj.replace_jar('file:${PWD}/exec_any.jar', 'EXEC_ANY')"
$ db2 "call sqlj.install_jar('file:${PWD}/exec_any.jar', 'EXEC_ANY')"
$ db2 "call sqlj.refresh_classes()"
# Jar file must appear in the ${DB2_HOME}/function/jar directory
$ find ${DB2_HOME}/function/jar -name '*.jar'
.../sqllib/function/jar/<USER_NAME>/EXEC_ANY.jar
# SP CREATE and CALL commands
$ cat sql/exec_any.sql
CREATE OR REPLACE PROCEDURE EXEC_ANY
(
SQL VARCHAR(4000)
, LOG VARCHAR(512) DEFAULT NULL
)
LANGUAGE Java
EXTERNAL NAME 'EXEC_ANY:ru.ibm.db2udf.ExecAny.exec'
FENCED THREADSAFE
MODIFIES SQL DATA
PARAMETER STYLE JAVA;
(=
-- W/ logging to some file accessible by the db2 instance fenced user on the server
-- $ stat -c "%U" ${DB2_HOME}/ctrl/.fencedID
call exec_any('declare global temporary table test(i int) on commit preserve rows not logged', '/tmp/exec_any.txt');
call exec_any('insert into session.test values 1', '/tmp/exec_any.txt');
-- W/o logging
call exec_any('insert into session.test values 2');
select * from session.test;
=)