一、简单的方法
此种方法在快速大量生成时,会有重复
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';
最后像使用一般函数一样使用,
values uuid();
select uuid() from SYSIBM.SYSDUMMY1;
参考:
db2 uuid
http://blog.csdn.net/qqqwwwqw/article/details/8055413
DB2基于JAVA的自定义函数(user-defined-function)示例
http://blog.csdn.net/qingsong3333/article/details/60879273
DB2 还可以创建java语言的AES加密解密自定义函数。自定义函数可以有入参。