DB2 FUNCTION【DB2中增加自定义函数】

蒦嗳伱

1.SQL语句:直接利用SQL语句创建DB2函数

SET SCHEMA DB2INFO;

SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","DB2INFO";

CREATE FUNCTION "DB2INFO"."CONVERTYMD"
("SSRC" VARCHAR(30),
  "SMOD" VARCHAR(30),
  "ICYCLE" INTEGER,
  "STYPE" VARCHAR(2)
)
  RETURNS VARCHAR(30)
  SPECIFIC "DB2INFO"."SQL100205164226500"
  LANGUAGE SQL
  NOT DETERMINISTIC
  READS SQL DATA
  STATIC DISPATCH
  CALLED ON NULL INPUT
  EXTERNAL ACTION
  INHERIT SPECIAL REGISTERS
  BEGIN ATOMIC
    DECLARE i_pos INTEGER;
    DECLARE s_res VARCHAR(30);
    DECLARE s_mods VARCHAR(30);
    DECLARE s_date VARCHAR(30);
    DECLARE S_DAY VARCHAR(2);
    DECLARE S_LAST_DAY VARCHAR(2);
    DECLARE S_YYYYMM VARCHAR(30);
    SET S_RES = UCASE(RTRIM(SSRC));
    SET S_MODS = UCASE(RTRIM(SMOD));
    SET I_POS = LOCATE(S_MODS,S_RES);
    IF I_POS=0 THEN
      RETURN 'NULL';
    END IF;
    IF UCASE(RTRIM(STYPE))='W' THEN
      SET S_DATE = RTRIM(CHAR(CURRENT DATE - (ICYCLE*7) DAYS,ISO));
      RETURN REPLACE(S_RES,S_MODS,SUBSTR(S_DATE,1,LENGTH(S_MODS)));
    END IF;
    IF UCASE(RTRIM(STYPE))='D' THEN
      SET S_DATE = RTRIM(CHAR(CURRENT DATE - ICYCLE DAYS,ISO));
      RETURN REPLACE(S_RES,S_MODS,SUBSTR(S_DATE,1,LENGTH(S_MODS)));
    END IF;
    IF(UCASE(RTRIM(STYPE))='H') THEN
      SET S_DATE = RTRIM(CHAR(date(CURRENT TIMESTAMP - ICYCLE HOURS),ISO))||
        ' '|| RTRIM(CHAR(time(CURRENT TIMESTAMP - ICYCLE HOURS)));
      RETURN REPLACE(S_RES,S_MODS,SUBSTR(S_DATE,1,LENGTH(S_MODS)));
    END IF;
    IF UCASE(RTRIM(STYPE))='M' THEN
      SET S_DAY = RTRIM(SUBSTR(S_RES,LENGTH(S_MODS)+2,2));
      SET S_LAST_DAY = RTRIM(CHAR(DAY(CURRENT DATE - (ICYCLE-1) MONTHS -DAY(
        CURRENT DATE -(ICYCLE-1) MONTHS) days)));
      SET S_YYYYMM = SUBSTR(RTRIM(CHAR(CURRENT DATE - ICYCLE MONTHS,ISO)),1,
        LENGTH (S_MODS));
      IF int(S_DAY)>int(S_LAST_DAY) THEN
        SET S_RES = REPLACE(S_RES,S_DAY,S_LAST_DAY);
      END IF;
      RETURN REPLACE(S_RES,S_MODS,S_YYYYMM);
    END IF;
    RETURN 'NULL';
  END;

使用方法：select convertymd(20100617) from sysibm.sysdummy1;
******************************************************************************************************
2.C语言
2.1:在UNIX下编写C程序然后编译成可执行文件 application_id,chmod 文件权限后在执行第二步，可执行文件放在/home/db2inst1/sqllib/function/ DB2的安装目录
2.2:创建DB2函数
CREATE FUNCTION "DB2INFO"."APPLICATION_ID" ( )
  RETURNS VARCHAR(128)
  SPECIFIC "DB2INFO"."APPLID"
  EXTERNAL NAME 'application_id!getApplicationId'
  LANGUAGE C
  PARAMETER STYLE SQL
  DETERMINISTIC
  NOT FENCED
  THREADSAFE
  CALLED ON NULL INPUT
  NO SQL
  NO EXTERNAL ACTION
  NO SCRATCHPAD
  NO FINAL CALL
  ALLOW PARALLEL
  DBINFO
  STATIC DISPATCH
  INHERIT SPECIAL REGISTERS;
******************************************************************************************************
3.Java类 【跨平台WINDOWS/UNIX/LINUX】
3.1:编写java类程序然后编译成可执行文件UserFunction.class*,chmod 文件权限后在执行第二步,JAVA类文件放在/home/db2inst1/sqllib/function/ DB2的安装目录
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import COM.ibm.db2.app.*;
/**
* DB2用户自定义函数java实现
*
* @author jason.cheng
*/
public class UserFunction extends UDF {
        
        private static String NULL_VALUE = null;
        
        public static String dateToFormat(String data){
                return patternDate(data);
        }
        
        public static String getReplace(String data){
                return patternReplace(data);
        }
        
        public static String patternDate(String data) {

                String[] pattern_YYYYMMDD = {
                                "(2//d{3}(((0[13578]|1[02])(0[1-9]|[12][0-9]|3[01]))|((0[469]|11)(0[1-9]|[12][0-9]|30))|(02(0[1-9]|[1][0-9]|2[0-8]))))",
                                "YYYYMMDD" };
                String[] pattern_YYYYMM = { "((2//d{3}(0[1-9]|1[0-2])))", "YYYYMM" };
                String[] pattern_YYYY = { "(2//d{3})", "YYYY" };
                String[] pattern_MMDD = {
                                "(((0[13578]|1[02])(0[1-9]|[12][0-9]|3[01]))|((0[469]|11)(0[1-9]|[12][0-9]|30))|(02(0[1-9]|[1][0-9]|2[0-8])))",
                                "MMDD" };
                String[] pattern_DD = { "((0|1|2)[0-9]|3[0-1])$", "DD" };

                Pattern YYYYMMDD = Pattern
                                .compile("(([^0-9]|^)2//d{3}(((0[13578]|1[02])(0[1-9]|[12][0-9]|3[01]))|((0[469]|11)(0[1-9]|[12][0-9]|30))|(02(0[1-9]|[1][0-9]|2[0-8]))))((?=[^0-9])|$)");
                Pattern YYYYMM = Pattern
                                .compile("(([^0-9]|^)(2//d{3}(0[1-9]|1[0-2])))((?=[^0-9])|$)");
                Pattern YYYY = Pattern.compile("([^0-9]|^)(2//d{3})((?=[^0-9])|$)");
                Pattern MMDD = Pattern
                                .compile("([^0-9]|^)(((0[13578]|1[02])(0[1-9]|[12][0-9]|3[01]))|((0[469]|11)(0[1-9]|[12][0-9]|30))|(02(0[1-9]|[1][0-9]|2[0-8])))((?=[^0-9])|$)");
                Pattern DD = Pattern.compile("(_(0|1|2)[0-9]|3[0-1])$");

               
                if (YYYYMMDD.matcher(data).find())
                        return replace(data, pattern_YYYYMMDD);
                else if(YYYYMM.matcher(data).find())
                        return replace(data,pattern_YYYYMM);
                else if(YYYY.matcher(data).find())
                        return replace(data,pattern_YYYY);
                else if(MMDD.matcher(data).find())
                        return replace(data, pattern_MMDD);
                else if(DD.matcher(data).find())
                        return replace(data, pattern_DD);
                else{
                        return data;
                }
        }
        
        
        public static String patternReplace(String data) {

                String[] pattern_YYYYMMDD = {
                                "(2//d{3}(((0[13578]|1[02])(0[1-9]|[12][0-9]|3[01]))|((0[469]|11)(0[1-9]|[12][0-9]|30))|(02(0[1-9]|[1][0-9]|2[0-8]))))",
                                "YYYYMMDD" };
                String[] pattern_YYYYMM = { "((2//d{3}(0[1-9]|1[0-2])))", "YYYYMM" };
                String[] pattern_YYYY = { "(2//d{3})", "YYYY" };
                String[] pattern_MMDD = {
                                "(((0[13578]|1[02])(0[1-9]|[12][0-9]|3[01]))|((0[469]|11)(0[1-9]|[12][0-9]|30))|(02(0[1-9]|[1][0-9]|2[0-8])))",
                                "MMDD" };
                String[] pattern_DD = { "((0|1|2)[0-9]|3[0-1])$", "DD" };

                Pattern YYYYMMDD = Pattern
                                .compile("(([^0-9]|^)2//d{3}(((0[13578]|1[02])(0[1-9]|[12][0-9]|3[01]))|((0[469]|11)(0[1-9]|[12][0-9]|30))|(02(0[1-9]|[1][0-9]|2[0-8]))))((?=[^0-9])|$)");
                Pattern YYYYMM = Pattern
                                .compile("(([^0-9]|^)(2//d{3}(0[1-9]|1[0-2])))((?=[^0-9])|$)");
                Pattern YYYY = Pattern.compile("([^0-9]|^)(2//d{3})((?=[^0-9])|$)");
                Pattern MMDD = Pattern
                                .compile("([^0-9]|^)(((0[13578]|1[02])(0[1-9]|[12][0-9]|3[01]))|((0[469]|11)(0[1-9]|[12][0-9]|30))|(02(0[1-9]|[1][0-9]|2[0-8])))((?=[^0-9])|$)");
                Pattern DD = Pattern.compile("(_(0|1|2)[0-9]|3[0-1])$");

               
                if (YYYYMMDD.matcher(data).find())
                        return replaceStr(data, pattern_YYYYMMDD);
                else if(YYYYMM.matcher(data).find())
                        return replaceStr(data,pattern_YYYYMM);
                else if(YYYY.matcher(data).find())
                        return replaceStr(data,pattern_YYYY);
                else if(MMDD.matcher(data).find())
                        return replaceStr(data, pattern_MMDD);
                else if(DD.matcher(data).find())
                        return replaceStr(data, pattern_DD);
                else{
                        return "";
                }
        }


        public static void main(String[] args) {
               
                //sql语句中的使用
                //select dateToFormat(source),getReplace(source) from meta;
                String str = "DB2APP.SDH_AREA_INFO_BASIC_2009";
                System.out.println(dateToFormat(str));
                System.out.println(getReplace(str));
        }
        
        public static String replaceStr(final String sourceString, String[] result) {
                Pattern pattern = Pattern.compile(result[0]);
                Matcher matcher = pattern.matcher(sourceString);
                String temp = NULL_VALUE;
                if(matcher.find()){
                        temp = sourceString.substring(matcher.start(), matcher.end());
                }
                return temp;
        }

        public static String replace(final String sourceString, String[] result) {
                Pattern pattern = Pattern.compile(result[0]);
                Matcher matcher = pattern.matcher(sourceString);
                String temp = matcher.replaceAll(result[1]);
               
                return temp;
        }
}

3.2:创建DB2函数,创建函数的时候可以根据JAVA类中提供的方法进行多个函数的创建

CREATE FUNCTION "DB2INFO"."CONVERTYMDEX"
("DATASTR" VARCHAR(256)
)
  RETURNS VARCHAR(256)
  SPECIFIC "DB2INFO"."CONVERTYMDEX"
  EXTERNAL NAME 'UserFunction!dateToFormat(Ljava/lang/String;)Ljava/lang/String;'
  LANGUAGE JAVA
  PARAMETER STYLE JAVA
  NOT DETERMINISTIC
  FENCED
  THREADSAFE
  RETURNS NULL ON NULL INPUT
  NO SQL
  EXTERNAL ACTION
  NO SCRATCHPAD
  NO FINAL CALL
  ALLOW PARALLEL
  NO DBINFO
  STATIC DISPATCH
  INHERIT SPECIAL REGISTERS;
******************************************************************************************************
总结：
  1.c/C++,Java提供了更为灵活的语法，可以替代SQL不能完成的工作
  2.Java提供的函数执行效率和SQL函数对比，前者执行效率更高
  3.系统环境需要支持JAVA类，尤其是在LINUX/UNIX下
  4.c/C++,Java自定义函数需要DB2 INST1数据库管理员权限去创建，创建后给不同的数据库用户进行赋权操作
******************************************************************************************************