db2 用flyway创建java中实现的sql函数

uplii1fm  于 2022-11-07  发布在  DB2
关注(0)|答案(1)|浏览(242)

我试着创建一个函数：

--#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运行相同的文件：

]$ 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

我在dbeaver和flyway上使用了相同的驱动程序。我也试着在gradle上运行它，但出现了同样的错误。关于flyway的这个问题是什么原因导致的，有什么线索吗？
jar中还有其他函数，我尝试过的所有函数都存在上述问题
FWIW，底层函数为A Proposal for Proquints: Identifiers that are Readable, Spellable, and Pronounceable
编辑：
jar文件的安装方式通常与Mark使用gradle在calling-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|

但如果我尝试在我的工作站上从flyway对同一服务器/数据库运行它，我会得到错误：

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个案例中使用了相同的用户/密码（clp、dbeaver、flyway）
编辑2：
我做了一个小小的测试程序：

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跟踪与flyway的jdbc跟踪进行了比较。flyway跟踪如下所示：

[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

在请求.flush之前
但这是否相关我还不知道
编辑3：

[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

1条答案

按热度按时间
zxlwwiss

zxlwwiss1#

我想我已经找到了问题的原因，至少我现在可以创建函数而没有问题了。根本原因是Flyway看起来：

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作为Flyway的参数删除，下面的迁移将起作用：

--#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文件的原因。

--#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，这也可以通过clp复制：

[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设置模式和表，原因是Flyway在创建它们时使用了小写的带引号的标识符。（特别是在从sh查询表时）不幸的是，我还没有找到一种方法来影响Flyway在列的名称方面的行为，所以你还是要引用它们：

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

我试过手动创建表，但是Flyway无法处理它。我还没有试过，但是我可能会为此添加生成的列或视图。
至于模式名称，我有几个产品都运行自己的Flyway示例。对于它们的示例，使用schema +“_FLYWAY”感觉要容易得多。我不想把flyway表放在产品模式中，因为我在提交每个版本之前都要对模式运行验证脚本。
我还想补充的是，我只在Flyway community 8.4.1中尝试过此功能，在Flyway community 8.0.5中可能会出现一些其他错误，我是从该版本开始的
非常感谢mao和Mark的输入（这可能会被删除，但现在我已经添加了它：-）

赞(0）分享  回复(0）举报  2022-11-07
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com