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