db2 生成uuid

爱被打了一巴掌 2022-04-06 10:46 881阅读 0赞

**一、简单的方法**

此种方法在快速大量生成时，会有重复

SELECT concat (hex (RAND ()), hex (RAND ())) as uuid FROM SYSIBM.SYSDUMMY1; --32位大写

select lower(hex(RAND())||hex(RAND())) as uuid from sysibm.sysdummy1;--32位小写

**二、自定函数，调用java生成uuid**

1.创建java类

import java.util.UUID;
    
    import COM.ibm.db2.app.UDF;
    
    public class UDFUUID extends UDF {
    
    	public static String uuid() {
    //		UUID uuid = UUID.randomUUID();
    //		String uid = uuid.toString().replaceAll("-", "");
    //		return uid;
    		return UUID.randomUUID().toString().replaceAll("-", "");
    	}
    
    }

然后用db2自带的jdk（比如$HOME/sqllib/java/jdk/bin）编译成class

查看DB2自带的jdk\_path

$ db2 get dbm cfg | grep -i jdk\_path

Java Development Kit installation path       (JDK\_PATH) = /home/db2inst4/sqllib/java/jdk64

/home/db2inst4/sqllib/java/jdk64/bin

(/opt/IBM/db2/V9.7/java/jdk64/bin)

\#/home/db2inst4/sqllib/java/jdk64/bin/java -c /home/myUserName/UDFUUID.java

db2 11.1

/home/db2inst4/sqllib/java/jdk64/bin/javac /home/myUserName/UDFUUID.java

2.创建自定义函数

第一种：

直接把UDFUUID.class拷贝到$HOME/sqllib/function下。

实例用户cp /home/myUserName/UDFUUID.class /home/db2inst4/sqllib/function/UDFUUID.class

不用重启数据库

然后执行：

drop function uuid;
    create function uuid()
        returns char(32)
        fenced
        variant
        no sql
        language java
        parameter style java
        external name 'UDFUUID!uuid';

第二种：

把此类打成jar包(最好用db2的jdk打包)，比如叫UUIDUDF.jar 并放在d盘，然后在命令行调用

Db2   call sqlj.install\_jar('file:d:\\customfunction.jar', 'UUIDUDFJAR')

然后执行

drop function uuid;

create function uuid()

RETURNS char(32)

LANGUAGE JAVA

PARAMETER STYLE JAVA

NOT DETERMINISTIC NO EXTERNAL ACTION NO SQL

EXTERNAL NAME 'UUIDUDFJAR:UDFUUID.uuid';

最后像使用一般函数一样使用，