// 生成32位随机数
CREATE OR REPLACE FUNCTION "RSPDB"."F_GETROUNDOF32" () 
specific F_GETROUNDOF32
  LANGUAGE SQL
  RETURNS varchar(40)
  BEGIN atomic
  declare v_branch_id_ods varchar(40);
  declare v_count int;
  declare v_branch_id_rsp  varchar(40);
      set   v_branch_id_ods =    (SELECT  substr(max
(branch_id),1,24) || to_char(floor(RAND() * 89999999 + 10000000))  from SYS_branch_info);  
      set   v_count =    (select  count(*) from sys_branch_info where branch_id = v_branch_id_ods);
      set   v_branch_id_rsp = (select  branch_id  from sys_branch_info where branch_id = v_branch_id_ods);
       if v_count =  0  then
         return v_branch_id_ods;
       end if;
       return v_branch_id_rsp;
END
select substr(A.ACCDATE,1,4) || '-' || substr(A.ACCDATE,5,2)|| '-' || substr(A.ACCDATE,7,2)  as ACCDATE  from F_INN_GEN_GL_BRIAC A



db2  取月所在的天数
select (current date + 1 month - day(current date + 1 month) days)
from  sysibm.sysdummy1


计算月数
select   timestampdiff(64,timestamp(to_date('2013-11-01','YYYY-MM-DD')) - timestamp(to_date('2012-11-01','YYYY-MM-DD')))  from sysibm.SYSDUMMY1

timestampdiff(64,timestamp(to_date(enddate,'YYYY-MM-DD')) - timestamp(to_date(startdate,'YYYY-MM-DD')))

求月数
db2函数 定义
CREATE FUNCTION "DB2INST"."F_GETMONTHS" (startdate varchar(40),enddate varchar(40))
  RETURNS int
  specific F_GETMONTHS
  LANGUAGE SQL
BEGIN atomic
  declare v_time int;
  set v_time = (SELECT (year(to_date(enddate,'YYYY-MM-DD')) - year(to_date(startdate,'YYYY-MM-DD')))* 12 + (month(to_date(enddate,'YYYY-MM-DD')) - month(to_date(startdate,'YYYY-MM-DD'))) FROM  sysibm.SYSDUMMY1);
  return v_time;
END

验证 values(f_getmonths('2011-01-01','2012-03-03'))









CREATE OR REPLACE FUNCTION "DB2INST"."F_ISNUMBER"   
(v_str varchar(100))
specific F_ISNUMBER
returns int
begin atomic
    declare v_len int;
    set v_len=length(v_str);
    if v_len=0 or v_str is null then
        return 0;
    end if;
    while v_len >0 do
        if substr(v_str,v_len,1) not in ('0','1','2','3','4','5','6','7','8','9') then
            return 0;
        end if;
        set v_len=v_len-1;
    end while;
    return 1;
end

values(F_ISNUMBER(123123))  return 1;

//去掉回车换行符号和 导入导出 带分隔符
select replace(OBJ_VAL,chr(10),'') from APP_FAST_RPT_DS_INFO where DATASET_ID = '48e4a83134664df10134665e7b25000b'
select  replace(replace(OBJ_VAL,chr(13),''),chr(10),'') from APP_FAST_RPT_DS_INFO where DATASET_ID = '48e4a83134664df10134665e7b25000b'
db2 "export to f_com_com_sp_brctl.txt of del modified by codepage=1208 COLDEL| select * from f_com_com_sp_brctl"
db2 "import from f_com_com_sp_brctl.txt of del modified by codepage=1208 COLDEL| insert into f_com_com_sp_brctl_t"