DB2总结(常用命令、存储过程)

目录

一、常用命令

二、从数据库提取数据表以及字段信息

三、编写SQL脚本（创建数据表）

四、编写存储过程

五、DB2存储过程实例练习

---

一、常用命令

执行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