db2 生成uuid 博客分类: db2 db2uuid自定义函数User Defined Function
一、简单的方法
此种方法在快速大量生成时,会有重复
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
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)示例