文章/答案/技术大牛

发布

社区首页 >问答首页 >通过天桥创建用java实现的sql函数

问通过天桥创建用java实现的sql函数
EN

Stack Overflow用户

提问于 2022-01-11 21:56:00

回答 1查看 441关注 0票数 0

我正在尝试创建一个函数，比如：

--#SET TERMINATOR @
CREATE OR REPLACE FUNCTION NYA.QUINT_2_UINT( I VARCHAR(11))
  RETURNS INTEGER
  RETURNS NULL ON NULL INPUT
  FENCED THREADSAFE
  DETERMINISTIC
  NO SQL
  LANGUAGE JAVA
  PARAMETER STYLE JAVA
  EXTERNAL NAME
'StringUtil:se.uhr.nya.commons.db.procedures.Proquint!quint2uint'
  NO EXTERNAL ACTION
@

从天桥来的。jar文件已经安装在服务器上，我可以通过clp创建这个函数，没有问题：

~]$ db2 -v -td@ -f aa.sql 
CREATE OR REPLACE FUNCTION NYA.QUINT_2_UINT( I VARCHAR(11))
  RETURNS INTEGER
  RETURNS NULL ON NULL INPUT
  FENCED THREADSAFE
  DETERMINISTIC
  NO SQL
  LANGUAGE JAVA
  PARAMETER STYLE JAVA
  EXTERNAL NAME
'StringUtil:se.uhr.nya.commons.db.procedures.Proquint!quint2uint'
  NO EXTERNAL ACTION

DB20000I  The SQL command completed successfully.

我还尝试在本地机器上使用dbeaver进行创建，没有出现问题：

Updated Rows    0
Query   CREATE OR REPLACE FUNCTION NYA.QUINT_2_UINT( I VARCHAR(11))
      RETURNS INTEGER
      RETURNS NULL ON NULL INPUT
      FENCED THREADSAFE
      DETERMINISTIC
      NO SQL
      LANGUAGE JAVA
      PARAMETER STYLE JAVA
      EXTERNAL NAME
    'StringUtil:se.uhr.nya.commons.db.procedures.Proquint!quint2uint'
      NO EXTERNAL ACTION
Finish time Tue Jan 11 22:14:46 CET 2022

但是，如果我试图从天桥运行相同的文件：

]$ flyway -schemas=NYA_FLYWAY -table="FLYWAY_SCHEMA_HISTORY" -driver=${driver} -url=jdbc:db2://${host}:${port}/${db} -user=${username} -password=${passwd} -jarDirs=${jarDirs} -locations="filesystem:${upgradedir}" migrate
A new version of Flyway is available
Upgrade to Flyway 8.4.1: https://rd.gt/2X0gakb
Flyway Community Edition 8.0.5 by Redgate
Database: jdbc:db2://130.239.91.21:50000/NYA (DB2/LINUXX8664 11.5)
Successfully validated 36 migrations (execution time 00:00.027s)
Current version of schema "NYA_FLYWAY": 22.223.100.3
Migrating schema "NYA_FLYWAY" to version "22.223.100.4"
ERROR: Migration of schema "NYA_FLYWAY" to version "22.223.100.4" failed! Changes successfully rolled back.
ERROR: Migration V22.223.100.4__.sql failed
------------------------------------
SQL State  : 46008
Error Code : -20204
Message    : The user defined function or procedure "NYA.QUINT_2_UINT" was unable to map to a single Java method.. SQLCODE=-20204, SQLSTATE=46008, DRIVER=4.29.24
Location   : /home/lejo0004/Project/db-legacy/nya/src/main/resources/db/migration/V22.223.100/V22.223.100.4__.sql (/home/lejo0004/Project/db-legacy/nya/src/main/resources/db/migration/V22.223.100/V22.223.100.4__.sql)
Line       : 3
Statement  : CREATE OR REPLACE FUNCTION NYA.QUINT_2_UINT( I VARCHAR(11))
  RETURNS INTEGER
  RETURNS NULL ON NULL INPUT
  FENCED THREADSAFE
  DETERMINISTIC
  NO SQL
  LANGUAGE JAVA
  PARAMETER STYLE JAVA
  EXTERNAL NAME
'StringUtil:se.uhr.nya.commons.db.procedures.Proquint!quint2uint'
  NO EXTERNAL ACTION

Caused by: Migration V22.223.100.4__.sql failed
------------------------------------
SQL State  : 46008
Error Code : -20204
Message    : The user defined function or procedure "NYA.QUINT_2_UINT" was unable to map to a single Java method.. SQLCODE=-20204, SQLSTATE=46008, DRIVER=4.29.24
Location   : /home/lejo0004/Project/db-legacy/nya/src/main/resources/db/migration/V22.223.100/V22.223.100.4__.sql (/home/lejo0004/Project/db-legacy/nya/src/main/resources/db/migration/V22.223.100/V22.223.100.4__.sql)
Line       : 3
Statement  : CREATE OR REPLACE FUNCTION NYA.QUINT_2_UINT( I VARCHAR(11))
  RETURNS INTEGER
  RETURNS NULL ON NULL INPUT
  FENCED THREADSAFE
  DETERMINISTIC
  NO SQL
  LANGUAGE JAVA
  PARAMETER STYLE JAVA
  EXTERNAL NAME
'StringUtil:se.uhr.nya.commons.db.procedures.Proquint!quint2uint'
  NO EXTERNAL ACTION

Caused by: com.ibm.db2.jcc.am.SqlException: The user defined function or procedure "NYA.QUINT_2_UINT" was unable to map to a single Java method.. SQLCODE=-20204, SQLSTATE=46008, DRIVER=4.29.24

我用的是同样的司机和海狸和天桥。我还试着用同样的错误从gradle运行它。有什么线索可以说明什么会导致这一问题吗？

jar中还有其他函数，我尝试过的所有功能都与上面描述的问题相同。

，底层函数是关于Proquint的建议:可读、可拼和可读的标识符

编辑：

jar文件通常以类似的方式安装，就像使用gradle在调用sqlj-install-jar-from-jdbc中演示的那样。

但是为了消除任何错误，我手动将jarfile安装在服务器上，如下所示：

[db2inst1@nya-01 ~]$ db2 "call sqlj.install_jar('file:///opt/nya/users/db2inst1/STRINGUTIL.jar', 'STRINGUTIL')"
SQL20201N  The install, replace or remove of "DB2INST1.STRINGUTIL" failed as 
the jar name is invalid.  SQLSTATE=46002

[db2inst1@nya-01 ~]$ db2 "call sqlj.replace_jar('file:///opt/nya/users/db2inst1/STRINGUTIL.jar', 'STRINGUTIL')"
DB20000I  The CALL command completed successfully.

[db2inst1@nya-01 ~]$ db2 "call sqlj.refresh_classes()"
DB20000I  The CALL command completed successfully.

现在，在服务器上我可以：

[db2inst1@nya-01 ~]$ db2 connect

Database Connection Information
Database server        = DB2/LINUXX8664 11.5.6.0
SQL authorization ID   = DB2INST1
Local database alias   = EKLN_N11

[db2inst1@nya-01 ~]$ db2 -v -td@ -f aa.sql 
CREATE OR REPLACE FUNCTION NYA.QUINT_2_UINT( I VARCHAR(11))
  RETURNS INTEGER
  RETURNS NULL ON NULL INPUT
  FENCED THREADSAFE
  DETERMINISTIC
  NO SQL
  LANGUAGE JAVA
  PARAMETER STYLE JAVA
  EXTERNAL NAME
'StringUtil:se.uhr.nya.commons.db.procedures.Proquint!quint2uint'
  NO EXTERNAL ACTION

DB20000I  The SQL command completed successfully.

db2 "VALUES NYA.QUINT_2_UINT('aabbccddee')"

1          
-----------
         17

  1 record(s) selected.

在同一个服务器/数据库中使用我的工作站上的dbeaver也很好(因此jdbc本身似乎不是一个问题)：

CREATE OR REPLACE FUNCTION NYA.QUINT_2_UINT( I VARCHAR(11))
  RETURNS INTEGER
  RETURNS NULL ON NULL INPUT
  FENCED THREADSAFE
  DETERMINISTIC
  NO SQL
  LANGUAGE JAVA
  PARAMETER STYLE JAVA
  EXTERNAL NAME
'StringUtil:se.uhr.nya.commons.db.procedures.Proquint!quint2uint'
  NO EXTERNAL ACTION
@

VALUES NYA.QUINT_2_UINT('ddeeaabbccd') @

1      | 
-------+
1114113|

但是，如果我试图在我的工作站上针对同一个服务器/数据库从天桥上运行它，则会得到以下错误：

SQL State  : 46008
Error Code : -20204
Message    : The user defined function or procedure "NYA.QUINT_2_UINT" was unable to map to a single Java method.. SQLCODE=-20204, SQLSTATE=46008, DRIVER=4.29.24

java代码本身并不令人兴奋：

package se.uhr.nya.commons.db.procedures;

public class Proquint {
    ...
            static int quint2uint(String quint) {
            long res = 0;
            for (char c : quint.toCharArray()) {
                    int index = indexOf(uint2consonant, c);
                    if (index != -1) {
                            res <<= 4;
                            res += index;
                    } else {
                            index = indexOf(uint2vowel, c);
                            if (index != -1) {
                                    res <<= 2;
                                    res += index;
                            }
                    }
            }
            return (int) res;
    }

对于所有3种情况，我都使用了相同的用户/passwd (clp，dbeaver )。天桥)

EDIT2：

我做了一个小测试：

import java.sql.*;

public class tst {
 
        public static void main(String [] args) {
              String urlPrefix = "jdbc:db2:";
              String url;
              String user;
              String password;
              String dummy;
              Connection con;
              Statement stmt;
           ResultSet rs;
 
           System. out.println ("**** Enter class tst" );
           if (args.length !=3)
           {
             System. err.println ("Invalid value. First argument appended to " +
              "jdbc:db2: must specify a valid URL." );
             System. err.println ("Second argument must be a valid user ID." );
             System. err.println ("Third argument must be the password for the user ID.");
             System. exit(1);
           }
           url = urlPrefix + args[0];
           user = args[1];
           password = args[2];
           try {
              Class. forName("com.ibm.db2.jcc.DB2Driver");
              System. out.println("**** Loaded the JDBC driver" );
              con = DriverManager. getConnection(url, user, password);
              con.setAutoCommit( false);
              System. out.println("**** Created a JDBC connection to the data source");
              stmt = con.createStatement();
              System. out.println("**** Created JDBC Statement object" );
 
              String s = "CREATE OR REPLACE FUNCTION NYA.QUINT_2_UINT( I VARCHAR(11)) ";
              s += "RETURNS INTEGER ";
              s += "RETURNS NULL ON NULL INPUT ";
              s += "FENCED THREADSAFE ";
              s += "DETERMINISTIC ";
              s += "NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA ";
              s += "EXTERNAL NAME 'StringUtil:se.uhr.nya.commons.db.procedures.Proquint!quint2uint' ";
              s += "NO EXTERNAL ACTION";

              //stmt.executeUpdate(s);
              stmt.execute(s);

              System. out.println("**** Created function" );

              s = "values NYA.QUINT_2_UINT('aabbccddeeg')";
              rs = stmt.executeQuery(s);    
              while (rs.next()) {
                dummy = rs.getString(1);
                System. out.println("number = " + dummy);
              }
              System. out.println("**** Fetched all rows from JDBC ResultSet" );
              rs.close();
              System. out.println("**** Closed JDBC ResultSet" );
 
               // Close the Statement
               stmt.close();
               System. out.println("**** Closed JDBC Statement" );
 
               // Connection must be on a unit-of-work boundary to allow close
               con.commit();
               System. out.println ( "**** Transaction committed" );
 
               con.close();
               System. out.println("**** Disconnected from data source" );
               System. out.println("**** JDBC Exit from class tst - no errors" );
           }
           catch(ClassNotFoundException e) {
              System. err.println("Could not load JDBC driver" );
               System. out.println("Exception: " + e);
               e.printStackTrace();
           }
           catch(SQLException ex) {
              System. err.println("SQLException information" );
               while(ex!=null ) {
                 System. err.println ("Error msg: " + ex.getMessage());
                 System. err.println ("SQLSTATE: " + ex.getSQLState());
                 System. err.println ("Error code: " + ex.getErrorCode());
                 ex.printStackTrace();
                 ex = ex.getNextException(); // For drivers that support chained exceptions
               }
           }
       }
}

并将jdbc跟踪与用于天桥的jdbc跟踪进行了比较。天桥的痕迹看上去是：

[jcc][Time:2022-01-13-12:22:49.714][Thread:main][Statement@275fe372]setEscapeProcessing (false) called
[jcc][Thread:main][SystemMonitor:start] 
[jcc][Time:2022-01-13-12:22:49.714][Thread:main][Statement@275fe372]execute (CREATE OR REPLACE FUNCTION NYA.QUINT_2_UINT( I VARCHAR(11)) RETURNS INTEGER RETURNS NULL ON NULL INPUT FENCED THREADSAFE DETERMINISTIC NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'STRINGUTIL:se.uhr.nya.commons.db.procedures.Proquint!quint2uint' NO EXTERNAL ACTION) called
[jcc][Time:2022-01-13-12:22:49.714][Thread:main][Statement@275fe372]stmt_bidiTransform (CREATE OR REPLACE FUNCTION NYA.QUINT_2_UINT( I VARCHAR(11)) RETURNS INTEGER RETURNS NULL ON NULL INPUT FENCED THREADSAFE DETERMINISTIC NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'STRINGUTIL:se.uhr.nya.commons.db.procedures.Proquint!quint2uint' NO EXTERNAL ACTION) called
[jcc][Time:2022-01-13-12:22:49.714][Thread:main][Statement@275fe372]stmt_bidiTransform not enabled (CREATE OR REPLACE FUNCTION NYA.QUINT_2_UINT( I VARCHAR(11)) RETURNS INTEGER RETURNS NULL ON NULL INPUT FENCED THREADSAFE DETERMINISTIC NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'STRINGUTIL:se.uhr.nya.commons.db.procedures.Proquint!quint2uint' NO EXTERNAL ACTION) called
[jcc][t4][time:2022-01-13-12:22:49.714][Thread:main][tracepoint:1][Request.flush]

鉴于测试程序：

[jcc][Thread:main][SystemMonitor:start] 
[jcc][Time:2022-01-13-12:22:28.089][Thread:main][Statement@1372ed45]execute (CREATE OR REPLACE FUNCTION NYA.QUINT_2_UINT( I VARCHAR(11)) RETURNS INTEGER RETURNS NULL ON NULL INPUT FENCED THREADSAFE DETERMINISTIC NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'StringUtil:se.uhr.nya.commons.db.procedures.Proquint!quint2uint' NO EXTERNAL ACTION) called
[jcc][Time:2022-01-13-12:22:28.089][Thread:main][Statement@1372ed45]stmt_bidiTransform (CREATE OR REPLACE FUNCTION NYA.QUINT_2_UINT( I VARCHAR(11)) RETURNS INTEGER RETURNS NULL ON NULL INPUT FENCED THREADSAFE DETERMINISTIC NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'StringUtil:se.uhr.nya.commons.db.procedures.Proquint!quint2uint' NO EXTERNAL ACTION) called
[jcc][Time:2022-01-13-12:22:28.089][Thread:main][Statement@1372ed45]stmt_bidiTransform not enabled (CREATE OR REPLACE FUNCTION NYA.QUINT_2_UINT( I VARCHAR(11)) RETURNS INTEGER RETURNS NULL ON NULL INPUT FENCED THREADSAFE DETERMINISTIC NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'StringUtil:se.uhr.nya.commons.db.procedures.Proquint!quint2uint' NO EXTERNAL ACTION) called

>>> [jcc][t4] [time:2022-01-13-12:22:28.089][Thread:main]
>>> [tracepoint:10]SetClientPiggybackCommand: flowToServerNeeded() = true
>>> [jcc][t4] [time:2022-01-13-12:22:28.089][Thread:main]
>>> [tracepoint:10]SetClientPiggybackCommand: flowToServerNeeded() = true

[jcc][t4][time:2022-01-13-12:22:28.089][Thread:main][tracepoint:1][Request.flush]

例如，测试程序确实：

SetClientPiggybackCommand: flowToServerNeeded() = true

在Request.flush之前

但这是否相关我还不知道

EDIT3：

[db2inst1@nya-03 ~]$ ls -lR sqllib/function/
sqllib/function/:
totalt 0
lrwxrwxrwx. 1 root     db2iadm1 35 14 sep 14.15 db2json -> /opt/ibm/db2/V11.5/function/db2json*
lrwxrwxrwx. 1 root     db2iadm1 36 14 sep 14.15 db2psmds -> /opt/ibm/db2/V11.5/function/db2psmds*
lrwxrwxrwx. 1 root     db2iadm1 35 14 sep 14.15 db2rtsc -> /opt/ibm/db2/V11.5/function/db2rtsc*
lrwxrwxrwx. 1 root     db2iadm1 34 14 sep 14.15 fpeevm -> /opt/ibm/db2/V11.5/function/fpeevm*
drwxrwxr-x. 3 db2inst1 db2iadm1 22  1 mar  2021 jar/
lrwxrwxrwx. 1 root     db2iadm1 37 14 sep 14.15 libdb2u.a -> /opt/ibm/db2/V11.5/function/libdb2u.a*
drwxrwsr-t. 2 db2inst1 db2iadm1  6 23 feb  2021 routine/
lrwxrwxrwx. 1 root     db2iadm1 33 14 sep 14.15 tblpd -> /opt/ibm/db2/V11.5/function/tblpd*
drwxrwsr-t. 2 db2inst1 db2iadm1 37 14 sep 14.15 unfenced/

sqllib/function/jar:
totalt 0
drwxrwxr-x. 2 db2inst1 db2iadm1 28 13 jan 09.48 DB2INST1/

sqllib/function/jar/DB2INST1:
totalt 12
-rw-rw-r--. 1 db2inst1 db2iadm1 8934 13 jan 09.48 STRINGUTIL.jar

sqllib/function/routine:
totalt 0

sqllib/function/unfenced:
totalt 0
lrwxrwxrwx. 1 root db2iadm1 44 14 sep 14.15 asnqmon -> /opt/ibm/db2/V11.5/function/unfenced/asnqmon*
lrwxrwxrwx. 1 root db2iadm1 45 14 sep 14.15 db2gsead -> /opt/ibm/db2/V11.5/function/unfenced/db2gsead*

db2-luw

db2

flyway

回答 1

Stack Overflow用户

回答已采纳

发布于 2022-01-14 13:12:29

我想我已经找到了造成问题的原因，至少现在我可以在没有问题的情况下创建这个函数。根本原因是天桥似乎：

SET CURRENT_SCHEMA = <variable assigned via property -schemas>

当从实例所有者更改这一点时，db2无法找到jar文件。如果我将迁移更改为：

set CURRENT SCHEMA = 'DB2INST1' @

CREATE OR REPLACE FUNCTION NYA.QUINT_2_UINT( I VARCHAR(11)) 
RETURNS INTEGER 
RETURNS NULL ON NULL INPUT 
FENCED THREADSAFE DETERMINISTIC 
NO SQL 
LANGUAGE JAVA 
PARAMETER STYLE JAVA 
EXTERNAL NAME 'STRINGUTIL:se.uhr.nya.commons.db.procedures.Proquint!quint2uint' 
NO EXTERNAL ACTION @

一切都如期而至。我对必须在迁移中设置current_path并不感到兴奋(我也不确定它会对Flyway产生什么影响)，所以我将查看是否有另一个属性可以用于为FLYWAY_SCHEMA_HISTORY分配模式。但这暂时有效。

编辑：

如果我将-schemas作为天桥的参数移除，则可以执行以下迁移：

--#SET TERMINATOR @

values ('CURRENT SCHEMA', cast(CURRENT SCHEMA as varchar(100))) @

CREATE OR REPLACE FUNCTION NYA.QUINT_2_UINT( I VARCHAR(11)) RETURNS INTEGER RETURNS NULL ON NULL INPUT FENCED THREADSAFE DETERMINISTIC NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'STRINGUTIL:se.uhr.nya.commons.db.procedures.Proquint!quint2uint' NO EXTERNAL ACTION @

values NYA.QUINT_2_UINT('aabbccddeef') @

flyway -table="FLYWAY_SCHEMA_HISTORY" -driver=${driver} -url=jdbc:db2://${host}:${port}/${db} -user=${username} -password=${passwd} -jarDirs=${jarDirs} -locations="filesystem:${upgradedir}" migrate
Flyway is up to date
Flyway Community Edition 8.4.1 by Redgate
Database: jdbc:db2://130.239.91.235:50000/nyax (DB2/LINUXX8664 11.5)
----------------------------------------
Flyway Teams features are enabled by default for the next 27 days. Learn more at https://rd.gt/3A4IWym
----------------------------------------
Successfully validated 1 migration (execution time 00:00.017s)
Creating Schema History table "DB2INST1"."FLYWAY_SCHEMA_HISTORY" ...
Current version of schema "DB2INST1": << Empty Schema >>
Migrating schema "DB2INST1" to version "22.223.100.4"
+----------------+----------+
| 1              | 2        |
+----------------+----------+
| CURRENT SCHEMA | DB2INST1 |
+----------------+----------+

+-----+
| 1   |
+-----+
| 274 |
+-----+

如果我添加了一个特定的模式，如：

flyway -schemas=NYA_FLYWAY -table="FLYWAY_SCHEMA_HISTORY" -driver=${driver} -url=jdbc:db2://${host}:${port}/${db} -user=${username} -password=${passwd} -jarDirs=${jarDirs} -locations="filesystem:${upgradedir}" migrate
Flyway is up to date
Flyway Community Edition 8.4.1 by Redgate
Database: jdbc:db2://130.239.91.235:50000/nyax (DB2/LINUXX8664 11.5)
----------------------------------------
Flyway Teams features are enabled by default for the next 27 days. Learn more at https://rd.gt/3A4IWym
----------------------------------------
Creating schema "NYA_FLYWAY" ...
Creating Schema History table "NYA_FLYWAY"."FLYWAY_SCHEMA_HISTORY" ...
Current version of schema "NYA_FLYWAY": null
Migrating schema "NYA_FLYWAY" to version "22.223.100.4"
+----------------+------------+
| 1              | 2          |
+----------------+------------+
| CURRENT SCHEMA | NYA_FLYWAY |
+----------------+------------+

ERROR: Migration of schema "NYA_FLYWAY" to version "22.223.100.4" failed! Changes successfully rolled back.

CURRENT_SCHEMA被更改为NYA_FLYWAY，我认为这就是为什么Db2无法定位jar文件的原因。如果我在迁移中显式地设置了CURRENT_SCHEMA：

--#SET TERMINATOR @

set CURRENT_SCHEMA = 'DB2INST1' @

values ('CURRENT SCHEMA', cast(CURRENT SCHEMA as varchar(100))) @

...

它再次发挥预期的作用：

flyway -schemas=NYA_FLYWAY -table="FLYWAY_SCHEMA_HISTORY" -driver=${driver} -url=jdbc:db2://${host}:${port}/${db} -user=${username} -password=${passwd} -jarDirs=${jarDirs} -locations="filesystem:${upgradedir}" migrate
Flyway is up to date
Flyway Community Edition 8.4.1 by Redgate
Database: jdbc:db2://130.239.91.235:50000/nyax (DB2/LINUXX8664 11.5)
----------------------------------------
Flyway Teams features are enabled by default for the next 27 days. Learn more at https://rd.gt/3A4IWym
----------------------------------------
Successfully validated 2 migrations (execution time 00:00.018s)
Current version of schema "NYA_FLYWAY": null
Migrating schema "NYA_FLYWAY" to version "22.223.100.4"
+----------------+----------+
| 1              | 2        |
+----------------+----------+
| CURRENT SCHEMA | DB2INST1 |
+----------------+----------+

+-----+
| 1   |
+-----+
| 274 |
+-----+

此外，如果我更改了我的小测试程序，使其在创建函数之前将current_schema设置为'NYA_FLYWAY‘，则会得到相同的错误：

java -cp .:/home/lejo0004/db2jcc4.jar tst //nya-03.its.umu.se:50000/nyax db2inst1 $passwd
**** Enter class tst
**** Loaded the JDBC driver
**** Created a JDBC connection to the data source
**** Created JDBC Statement object
CURRENT SCHEMA = NYA_FLYWAY
SQLException information
Error msg: DB2 SQL Error: SQLCODE=-20204, SQLSTATE=46008, SQLERRMC=NYA.QUINT_2_UINT, DRIVER=4.29.24
SQLSTATE: 46008
Error code: -20204
com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-20204, SQLSTATE=46008, SQLERRMC=NYA.QUINT_2_UINT, DRIVER=4.29.24
    at com.ibm.db2.jcc.am.b7.a(b7.java:815)
    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.b(k9.java:2188)
    at com.ibm.db2.jcc.t4.ab.k(ab.java:444)
    at com.ibm.db2.jcc.t4.ab.c(ab.java:102)
    at com.ibm.db2.jcc.t4.p.b(p.java:38)
    at com.ibm.db2.jcc.t4.av.h(av.java:124)
    at com.ibm.db2.jcc.am.k9.ak(k9.java:2183)
    at com.ibm.db2.jcc.am.k9.a(k9.java:3387)
    at com.ibm.db2.jcc.am.k9.e(k9.java:1135)
    at com.ibm.db2.jcc.am.k9.execute(k9.java:1114)
    at tst.main(tst.java:61)

FWIW，也可以通过中电复制：

[db2inst1@nya-03 ~]$ db2 "set current_schema = 'DB2INST1'"
DB20000I  The SQL command completed successfully.
[db2inst1@nya-03 ~]$ db2 "CREATE OR REPLACE FUNCTION NYA.QUINT_2_UINT( I VARCHAR(11))
  RETURNS INTEGER
  RETURNS NULL ON NULL INPUT
  FENCED THREADSAFE
  DETERMINISTIC
  NO SQL
  LANGUAGE JAVA
  PARAMETER STYLE JAVA
  EXTERNAL NAME
'StringUtil:se.uhr.nya.commons.db.procedures.Proquint!quint2uint'
  NO EXTERNAL ACTION"
DB20000I  The SQL command completed successfully.
[db2inst1@nya-03 ~]$ db2 "set current_schema = 'NYA_FLYWAY'"
DB20000I  The SQL command completed successfully.
[db2inst1@nya-03 ~]$ db2 "CREATE OR REPLACE FUNCTION NYA.QUINT_2_UINT( I VARCHAR(11))
  RETURNS INTEGER
  RETURNS NULL ON NULL INPUT
  FENCED THREADSAFE
  DETERMINISTIC
  NO SQL
  LANGUAGE JAVA
  PARAMETER STYLE JAVA
  EXTERNAL NAME
'StringUtil:se.uhr.nya.commons.db.procedures.Proquint!quint2uint'
  NO EXTERNAL ACTION"
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL20204N  The user defined function or procedure "NYA.QUINT_2_UINT" was 
unable to map to a single Java method.  LINE NUMBER=10.  SQLSTATE=46008

有人可能会问，为什么我坚持为天桥设置模式和表。至于表名，原因是Flyway用小写引用的标识符来创建它们。每次需要查询表时(特别是在从sh查询表时)，都需要引用这些标识符，这是一种痛苦的体验。不幸的是，当涉及到列的名称时，我还没有找到影响Flyway行为的方法，所以您仍然需要引用它们如下：

db2 "select \"version\" from ..."

我尝试手动创建表，但是天桥无法处理它。我还没有尝试过，但是我可以为此添加生成的列或视图。

至于模式名称，我有几个产品运行自己的Flyway实例。对他们的实例使用模式+ "_FLYWAY“感觉要容易得多。我不想将天桥表放在产品模式中，因为我在每个提交的版本之前在模式上运行验证脚本。

我还想补充的是，我只在天桥社区8.4.1中尝试过这种方法，可能还有其他一些错误出现在我开始的8.0.5中。

非常感谢毛和标记的输入(这可能会被删除，但现在我添加了它：-)

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/70673926

复制

相似问题

问通过天桥创建用java实现的sql函数
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问通过天桥创建用java实现的sql函数EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问通过天桥创建用java实现的sql函数
EN