Java 存储过程 DB2 问题

Question

在我的类里面，我创建了下面的简单方法。这个类里面还有一个main函数。我正在从 DB2 存储过程调用 getRes()。

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");
}

我从 DB2 存储过程调用此函数。

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()

此存储过程正在成功执行。但我怀疑我的方法是否被执行。因为执行后测试表中没有任何条目。

以下是我部署此 jar 文件所遵循的步骤。1.从Eclipse导出jar。验证 jar 是否工作正常。2. 执行调用sqlj.install_jar()。验证了 SYSIBM.SYSJARCONTENTS 中的安装。3. 在数据库服务器上设置类路径 导出 CLASSPATH=/home/db2inse5/sqllib/function/jar/SchemaName/JarName.jar4.创建sp并调用sp。

预期输出是表测试中的条目。但虽然 sp 执行成功，但我没有得到预期的结果。

已更新-20200423我尝试提交数据库连接部分并将其替换为数据库服务器中的文件创建。这有效，所以我可以确认 jar 是从 SP 正确调用的，并且 jar 文件工作正常。但上面的部分还没有解决

public static void getRes() {
    System.out.println("Start");
            File myObj = new File("/opt/filename.txt");
            myObj.createNewFile();
}

Answer 1

获取Connection的错误方法Parameter style Java 中的对象常规。请阅读文档中的以下主题:Parameter style JAVA procedures .
您不能像在序数 jdbc 应用程序中那样获取它。不要使用Class.forName称呼。请改用以下内容:

con = DriverManager.getConnection("jdbc:default:connection");

了解Restrictions on external routines以及。不要使用System.out.println(...)在 Java 例程中。

示例

按 db2 实例所有者在数据库服务器上的原样执行此操作，无需任何修改。创建提到的目录结构。确保 db2profile 源自您的 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;}
    }
  }

}

有关如何构建和部署它的说明:

# 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;
=)