目录
一、常用命令
执行shell脚本命令:sh 文件名.sh
执行SQL脚本命令:db2 -txvf 文件名.sql
连接数据库命令:db2 connect to 数据库名
关闭数据库命令:db2 connect reset
给文件赋权限命名:chmod -R 777 [文件]
编译存储过程命令:db2 -td@ -vf 存储过程名称.sql
运行存储过程命令: db2 "call 存储过程名称('1')"
二、从数据库提取数据表以及字段信息
SELECT
'' 系统简称,
T.TABNAME 表英文名,
T.REMARKS 表中文名称,
C.COLNO + 1 字段序号,
C.REMARKS 字段名称,
C.COLNAME 字段代码,
C.TYPENAME 字段数据类型,
C.LENGTH 字段长度,
C.SCALE 字段精度,
CASE
WHEN C.NULLS = 'Y' THEN 'N'
WHEN C.NULLS = 'N' THEN 'Y'
ELSE ''
END 是否主键,
C.NULLS 是否允许为NULL
FROM
SYSCAT.TABLES t
LEFT JOIN SYSCAT.COLUMNS c
ON
T.TABNAME = C.TABNAME
AND T.TABSCHEMA = C.TABSCHEMA
WHERE
T.TABSCHEMA = 'DB2RRS'
AND
T.TABNAME IN
('CC_ASSCONTCORRES', '数据表名','数据表名'......)
ORDER BY
T.TABNAME,
C.COLNO + 1
注意:
' ' 系统简称,
T.TABNAME 表英文名,
T.REMARKS 表中文名称,
C.COLNO+1 字段序号,
C.REMARKS 字段名称,
C.COLNAME 字段代码
,C.TYPENAME 字段数据类型
,C.LENGTH 字段长度
,C.SCALE 字段精度`
以上字段是根据需求选取的
三、编写SQL脚本(创建数据表)
-- DB2RRS.SOURCE_TABLE definition
CREATE TABLE "DB2RRS "."SOURCE_TABLE" (
"TAB_NAME" VARCHAR(200) ,
"TAB_DESCRI" VARCHAR(4000) )
IN "WHC_BUS" ;
GRANT CONTROL ON TABLE "DB2RRS "."SOURCE_TABLE" TO USER "DB2RRS " ;
-- DB2RRS.GET_ESCAPESPACE_DELSQL definition
CREATE TABLE "DB2RRS "."GET_ESCAPESPACE_DELSQL" (
"TAB_NAME" VARCHAR(200) ,
"EXPDELSQL" VARCHAR(20000) )
IN "WHSC_BUS" ;
GRANT CONTROL ON TABLE "DB2RRS "."GET_ESCAPESPACE_DELSQL" TO USER "DB2RRS " ;
注意: "WHC_BUS"代表该数据表所属的表空间;
"DB2RRS"代表数据库的标识名
执行SQL脚本,成功后表会自动创建
创建表以后要导入数据,否则会查询不到数据
四、编写存储过程
CREATE OR REPLACE PROCEDURE GET_ESCAPESPACE_DELSQL(PAR_TYPE VARCHAR (1))
BEGIN
DECLARE V_TABNAME VARCHAR(200);
DECLARE V_COLNAME VARCHAR(200);
DECLARE V_TYPENAME VARCHAR(200);
DECLARE V_INFOLOG VARCHAR(200);
DECLARE V_DBSH VARCHAR(200);
DECLARE V_TABDESC VARCHAR(200);
DECLARE V_EERRLOG VARCHAR(300);
DECLARE V_COLSQL VARCHAR(20000);
DECLARE V_SQLEXPAL VARCHAR(20000);
DECLARE V_LENGTHS int;
DECLARE V_SKIP_FLAG INT DEFAULT 0;
DECLARE V_SKIP_FLAGCOL INT DEFAULT 0;
DECLARE V_TABLE_CURSOR CURSOR FOR
SELECT TAB_NAME,TAB_DESCRI FROM SOURCE_TABLE ORDER BY TAB_NAME;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_SKIP_FLAG = 1;
--清理sql表
--delete from GET_ESCAPESPACE_DELSQL;
--commit;
SET V_DBSH='db2 "export to ${EXPORTDATA_DIR}/GRT_P_DEPOSIT_REC.del of del modified by nochardel coldel0x1b codepage = 1208 ';
OPEN V_TABLE_CURSOR;
read_loop: LOOP
FETCH V_TABLE_CURSOR INTO V_TABNAME,V_TABDESC;
IF V_SKIP_FLAG = 1 THEN
LEAVE read_loop;
END IF;
SELECT
listagg(CAST(case when TYPENAME in ('CLOB','LONG VARCHAR') or (TYPENAME='VARCHAR' and length>50) then 'trim(replace(replace(coalesce('||COLNAME||',''''),chr(13),''''),chr(10),''''))' else COLNAME end ||'
' AS varchar(20000)),
',') WITHIN GROUP
(ORDER BY
COLNO) INTO V_COLSQL
FROM
syscat.COLUMNS c
WHERE
TABSCHEMA = 'DB2RRS'
AND TABNAME = V_TABNAME
GROUP BY c.TABNAME;
SET V_INFOLOG='echo `date` 开始导出'||V_TABDESC||' '||'
';
SET V_EERRLOG='
'||'if [ $? != "0" ]
then
echo "导出'||V_TABDESC||'失败"|tee -a ${BATCH_LOGLK}
exit 1
fi
';
SET V_COLSQL=V_DBSH||'SELECT '||V_COLSQL||' FROM '||V_TABNAME||'"';
SET V_SQLEXPAL=V_INFOLOG||V_COLSQL||V_EERRLOG ;
INSERT INTO GET_ESCAPESPACE_DELSQL(tab_name,expdelsql) VALUES(V_TABNAME,V_SQLEXPAL);
END LOOP;
CLOSE V_TABLE_CURSOR;
COMMIT;
END
@
编译存储过程:
db2 -td@ -vf expshujuhusqlFLS.sql
执行结果:
DB20000I The SQL command completed successfully.
执行存储过程:
db2 "call GET_ESCAPESPACE_DELSQL('1')"
执行结果:
Return Status = 0
连接数据库查询数据
SELECT * FROM GET_ESCAPESPACE_DELSQL
五、DB2存储过程实例练习
1、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 '更新记录数';
2、存储过程举例(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@
3、编译命令(proc_test_proc.sql为例):
对文件赋权:
chmod 777 proc_test_proc.sql
编译:
db2 -td@ -vf proc_test_proc.sql
4、存储过程运行:
db2 "call db2inst1.proc_test_proc('$input',?,?)" --$input为日期参数
运行成功结果举例:
输出参数的值
--------------------------
参数名: O_RETURN_CODE
参数值: 0
参数名: O_RETURN_MSG
参数值: 存储过程db2inst1.proc_test_proc运行成功!
返回状态 = 0