一、DB2存储过程中日志表建表语句:
create table db2inst1.dim_proc_run_log
(
proc_name varchar(200) ,
proc_para varchar(20) ,
step_no integer ,
step_desc varchar(100) ,
step_over_time timestamp ,
step_records integer
)distribute by hash(proc_name,proc_para,step_no) in userspace1 not logged initially ;
--注释
comment on table db2inst1.dim_proc_run_log is '存储过程运行日志表';
comment on column db2inst1.dim_proc_run_log.proc_name is '存储过程名称';
comment on column db2inst1.dim_proc_run_log.proc_para is '数据账期';
comment on column db2inst1.dim_proc_run_log.step_desc is '步骤说明';
comment on column db2inst1.dim_proc_run_log.step_no is '步骤';
comment on column db2inst1.dim_proc_run_log.step_over_time is '运行结束时间';
comment on column db2inst1.dim_proc_run_log.step_records is '更新记录数';
二、存储过程举例(SQL文件):proc_test_proc.sql,脚本文件建议使用utf8编码
create or replace procedure db2inst1.proc_test_proc (in i_date varchar(20),out o_return_code integer, out o_return_msg varchar(300) )
p1: begin
--*****************************************************************************************
-- sql 存储过程
-- 名称 : proc_test_proc
-- 频率 : 日
-- 参数 : i_date: 统计日期
-- o_return_code: 返回值
-- o_return_msg: 返回信息
-- 功能描述 : 测试
-- 返回值 : 0 正确; <0 错误
-- 输入表 : db2inst1.dual
-- 输出表 : test_proc
-- 创建日期 : 19991231
-- 创建人 : test
-- 修改历史 : 修改人 修改时间 主要改动说明
-- test 19991231 新增
-- test1 29991231 修改
--*****************************************************************************************
--声明
declare sqlcode integer default 0;
declare retcode integer default 0;
declare vs_bcyc_id varchar(20); --统计日期(yyyy-mm-dd)
declare vi_bcyc_id varchar(8); --统计日期(yyyymmdd)
declare vs_dynstr1 varchar(200); --存放动态sql执行语句
declare vs_proc_name varchar(50); --存放日志表存储过程名
declare vi_proc_para varchar(20); --存放日志表存储过程参数
declare vi_step_no integer; --存放日志表步骤序号
declare vs_step_desc varchar(100); --存放日志表步骤描述
declare vi_step_records integer default 0; --更新记录数
--声明异常
declare continue handler for sqlexception set retcode = sqlcode;
declare continue handler for sqlwarning set retcode = sqlcode;
declare continue handler for not found set retcode = sqlcode;
--赋值
set o_return_code = 0;
set o_return_msg = '存储过程未运行!';
set vs_proc_name = 'db2inst1.proc_test_proc';
set vi_proc_para = i_date;
set vs_bcyc_id = i_date; --传入日期参数
set vi_bcyc_id = to_char(date(vs_bcyc_id),'yyyymmdd');
-----------------------------------------------------------------------------------------------
--向日志表插入数据
-----------------------------------------------------------------------------------------------
set vi_step_no = 0;
set vs_step_desc = '存储过程运行开始!';
--log
delete from db2inst1.dim_proc_run_log where (proc_name=vs_proc_name and proc_para=vi_proc_para) or proc_para is null;
insert into db2inst1.dim_proc_run_log(proc_name,proc_para,step_no,step_desc,step_over_time,step_records) values (vs_proc_name,vi_proc_para,vi_step_no,vs_step_desc,current timestamp,0);
commit;
-----------------------------------------------------------------------------------------------
--清空当前周期数据
-----------------------------------------------------------------------------------------------
set vi_step_no = 1;
set vs_step_desc = '清空结果表当前周期数据';
--[sql]
delete from db2inst1.test_proc where dw_date=vi_bcyc_id;
commit;
--[sql]
--判断sql返回值是否正常
if retcode < 0 then
set o_return_code = retcode;
set o_return_msg = 'sql'||char(vi_step_no)||':'||vs_step_desc||'出错!';
rollback;
--err_log
insert into db2inst1.dim_proc_run_log(proc_name,proc_para,step_no,step_desc,step_over_time,step_records)
values (vs_proc_name,vi_proc_para,-1,o_return_msg||'sql_code:'||char(o_return_code),current timestamp,0);
--err_log
return o_return_code;
else
set retcode = 0;
commit;
end if;
--log
insert into db2inst1.dim_proc_run_log(proc_name,proc_para,step_no,step_desc,step_over_time,step_records) values (vs_proc_name,vi_proc_para,vi_step_no,vs_step_desc,current timestamp,0);
commit;
-----------------------------------------------------------------------------------------------
--生成结果表
-----------------------------------------------------------------------------------------------
set vi_step_no = 2;
set vs_step_desc = '生成结果表';
--[sql]
insert
into
db2inst1.test_proc
select
vi_bcyc_id as dw_date ,
a.userid
from
db2inst1.dual a ;
--[sql]
--获取更新条数
get diagnostics vi_step_records = row_count;
if retcode < 0 then
set o_return_code = retcode;
set o_return_msg = 'sql'||char(vi_step_no)||':'||vs_step_desc||'出错!';
rollback;
--err_log
insert into db2inst1.dim_proc_run_log(proc_name,proc_para,step_no,step_desc,step_over_time,step_records)
values (vs_proc_name,vi_proc_para,-1,o_return_msg||'sql_code:'||char(o_return_code),current timestamp,0);
--err_log
return o_return_code;
else
set retcode = 0;
commit;
end if;
--log
insert into db2inst1.dim_proc_run_log(proc_name,proc_para,step_no,step_desc,step_over_time,step_records) values (vs_proc_name,vi_proc_para,vi_step_no,vs_step_desc,current timestamp,vi_step_records);
commit;
-----------------------------------------------------------------------------------------------
--存储过程执行成功
-----------------------------------------------------------------------------------------------
set vi_step_no = 999;
set vs_step_desc = '存储过程运行结束!';
--log
insert into db2inst1.dim_proc_run_log(proc_name,proc_para,step_no,step_desc,step_over_time,step_records) values (vs_proc_name,vi_proc_para,vi_step_no,vs_step_desc,current timestamp,0);
commit ;
set o_return_code = 0 ;
set o_return_msg = '存储过程'||vs_proc_name||'运行成功!';
end p1@
三、编译命令(proc_test_proc.sql为例):
对文件赋权:
chmod 777 proc_test_proc.sql
编译:
db2 -td@ -vf proc_test_proc.sql
四、存储过程运行:
db2 "call db2inst1.proc_test_proc('$input',?,?)" --$input为日期参数
运行成功结果举例:
输出参数的值
--------------------------
参数名: O_RETURN_CODE
参数值: 0
参数名: O_RETURN_MSG
参数值: 存储过程db2inst1.proc_test_proc运行成功!
返回状态 = 0