db2工作笔记

// 生成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" 

//环比 
values (to_date(substr('20120531',1,6)||'01','yyyymmdd')-1 day) 

2012-4-30 上午12:00:00

//同比



//比年初
values (to_date(substr('20120531',1,4)||'0101','yyyymmdd'))
2012-1-1 上午12:00:00