DB2存储过程命令解析

1.Handler 学习 异常
http://blog.csdn.net/byxdaz/article/details/1377554
1)语法：
DECLARE handler-type Handler for Condition SQL-procedure-statement;
如：DECLARE EXIT HANDLER FOR SQLEXCEPTION ...或 DECLARE CONTINUE HANDLER FOR NOT FOUND ...
2)解释：
当DB2触发一个满足定义的条件时(发生异常情况时)，就会将控制权交给条件处理器(Condition Handler),
条件处理器根据指定的处理类型(HANDLER Type) 来执行指定的SQL-procedure-statement
3)处理类型(handler-type)：CUNTINUE、EXIT、UNDO
CUNTINUE：表示，当抛出异常后，由对应的异常处理器解决异常，工作流会继续执行抛出异常语
句的下一个语句
EXIT：表示，当抛出异常后，相应的异常处理器解决该异常，工作流会直接到程序的末尾。
UNDO：表示,当抛出异常后，对应的异常处理器解决此异常情况，工作流直接到达程序的末尾并且
撤销所有已实现的操作，或者回滚所有已执行的语句。
4) Condition 异常处理器可以处理基于特定SQLSTATE值的自定义异常，或者处理系统预定义异常
系统预定义的3种异常：
(1)NOT FOUND SQLCODE为100或SQLSTATE以'02'开头的所有错误。这个异常通常在SELECT没有返回行的时候出现。
(2)SQLEXCEPTION SQLCODE为负数情况
(3)SQLWARNING 导致警告异常或者导致正100以上的SQLCODE值的异常。
如果产生了NOT FOUND 或者SQLWARNING异常，并且没有为这个异常定义异常处理器，系统就会忽略这个异常，
并且将控制流转向下一个语句。
如果产生了SQLEXCEPTION异常，并且没有为这个异常定义异常处理器，那么存储过程就会失败，系统会将控制
流返回调用者。
5)条件处理器 SQL-procedure-statement
     (1)可以是单纯SQL     (2)可以是用BEGIN...END来定义语句块
如果是语句块(2),需要定义2个本地变量或参数接收 SQLCODE 和 SQLSTATE,不接收直接丢失
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
        SET R_CODE=SQLCODE;--
        SET O_ERR_NO=1;--
        ROLLBACK;--
        INSERT INTO TST_PROC_LOG(SEQ,LOGID,PROC_NAME,PROC_STATE,TIME,BUG_SQLCODE) VALUES(nextval
        for SEQ_TST_PROC_LOG,LOGID,PROC_NAME,'0',CURRENT TIMESTAMP,CHAR(R_CODE));
        COMMIT;--
END;--
2.DB2 Merge 可以将一个表中的数据合并到另一个表中，在合并的同时可以进行插入、删除、更新等操作
----------******----------
MERGE INTO TST_SEC_APP_COUNT T1 USING TST_SALE_CODE T2 ON T1.SALE_PK = T2.SALE_PK
WHEN MATCHED (and ...) THEN UPDATE SET T1.ORGID = T2.ORGID
--(1)说明有问题抛异常
--WHEN MATCHED AND EM.SALARY>MA.SALARY THEN SIGNAL SQLSTATE '70001' SET
--MESSAGE_TEXT = 'EM.SALARY>MA.SALARY'
--(2)删除满足条件的数据 WHEN MATCHED AND EM.SALARY>MA.SALARY THEN DELETE
WHEN NOT MATCHED THEN INSERT VALUES (T2.MANAGERID,T2.NAME,T2.SALARY);
----------******----------
解释：合并表 TST_SEC_APP_COUNT 和表 TST_SALE_CODE 关联字段ON T1.SALE_PK = T2.SALE_PK
如果两表字段关联存在数据，修改表T1字段 T1.ORGID = T2.ORGID
如果两表字段关联不存在数据，则添加T1表 T2的数据
3.数据表删除(删除大量数据)
ALTER TABLE <TABLE_NAME> ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
如：ALTER TABLE TEMP_SEC_APP_COUNT_F ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
--不加 WITH EMPTY TABLE 执行删除操作时不记录日志，提交后约束解除。
SET STMT = 'ALTER TABLE TST_SEC_APP_COUNT activate not logged initially ';
EXECUTE IMMEDIATE STMT;--;
delete from TST_SEC_APP_COUNT A where exists (select 1 from TMP_COME_DATE1 where COME_DATE = A.COME_DATE);
COMMIT;--
4.DB2 Load：数据的装入(Load)
SET LOAD_COMMAND='LOAD FROM D:\FCC\biz_input\MIS_CAD_MPMY_'||CURRENT_DAY||'.txt OF DEL REPLACE INTO TEMP_SEC_APP_COUNT NONRECOVERABLE';
CALL SYSPROC.DB2LOAD(1,'',LOAD_COMMAND,SQLCODE,'','','','','','','','','','','');COMMIT;
1)LOAD FROM * OF *：固定关键字
2)filename：要装载到数据库的文件名,如果大于1个,可以使用逗号分隔开如："/tmp/1.dat","/tmp/2.dat"
3)filetype：要装载数据文件的类型，大家用到比较多的应该是DEL，一般有4种，IXF,DEL,ASC,CURSOR
ASC表示不分界的ASCII数据，数据的划分由位置决定。DEL表示分界的ASCII数据，每行的数据长度可变。
分界的数据可以使用多种修饰符，主要的两种是 COLDEL 和 CHARDEL;COLDEL 决定列和列之间如何分界，
CHARDEL 决定字符串数据如何分界
4)INSERT / REPLACE INTO PROD.TABLE
5)NONRECOVERABLE = NOLOGGING
如果使用这个选项,在装载操作之后表空间并不处于备份未完成状态,在装载操作期间不必复制装载的数据。
参考文档：http://blog.csdn.net/dlodj/article/details/7023093
☆☆☆ 导出数据样例(以 | 分割): ☆☆☆
EXPORT TO D:\FCC\biz_input\TESTSSSS_20170906.txt OF DEL MODIFIED BY CODEPAGE=1208(UTF-8编码格式) COLDEL0X7C(以|进行数据分割) SELECT * FROM TBL_STAFF_LOGIN_AUTHORITY WITH UR
☆☆☆ 导入数据样例 ☆☆☆
DB2 LOAD CLIENT(如果是远程导入，需要加) FROM D:\gy\20170923\TEMP_ABOUT_RECIPIENT.TXT OF DEL REPLACE INTO TEMP_ABOUT_RECIPIENT NONRECOVERABLE
5.数据库导出指令 DB2LOOK
db2cmd
db2 connect to SMIS user db2admin using Pass@word
DB2LOOK -d [database] -u [user] -e -o [url]
DB2LOOK -d tfmrpt2 -u WALID -e -o db2look.sql
--数据库 tfmrpt2 由用户 WALID 创建的所有表和联合对象的DDL语句db2look输出被发送到名为
--db2look.sql 的文件中
-- -e 提取数据库对象的DDL语句，如表、索引、触发器、函数过程等
-- -o 将输出结果写入一个文件，如果没有指定选项，将输出结果写入标准输出设备
--db2look -d SMIS -e -l -o db2look_smis_20170911.ddl
DB2LOOK -d SMIS -e -a -o db2look_smis_20170910.ddl
-- -l 为用户定义的表空间、数据库分区组合缓冲池生成DDL语句。
参考文档：http://www.jb51.net/article/31909.htm
--ftp导出 ddl 文件步骤
1).在20服务器对应的数据上，先创建用户，然后从20服务器上拉到本地
->smisuser/2wsx3edc
->ls
->mkdir wjb
->ls
->cd wjb
->db2 list db directory
->db2 connect to SMIS_DB
->db2look -d SMIS_DB -a -e -o db2look_smisdb_20170523.ddl
2).使用ftp服务，把文件从20服务器上拉倒本地
cmd 然后
->ftp 182.180.125.20
//连接 182.180.125.20 用户名:smisuser 密码:2wsx3edc
->ls -lrt
->cd wjb
->ls -lr
->bin
->get db2look_smisdb_20170522.ddl
6.reorg runstats
CALL SYSPROC.ADMIN_CMD('REORG TABLE DB2ADMIN.TEMP_SEC_APP_COUNT');--
CALL SYSPROC.ADMIN_CMD('RUNSTATS ON TABLE DB2ADMIN.TEMP_SEC_APP_COUNT and indexes all');
reorg table 通过重构行来消除"碎片"数据并压缩信息，对表进行重组。
runstats on table 收集表的统计信息。
reorgchk on table all 确定是否需要对表进行重组，对于对所有表自动执行 runstats 很有用
reorg 和 runstats 都是单个表优化，初始化的命令,处于Reorg Pending状态的表有可能会阻碍后续操作。
在对表数据进行许多更改之后，逻辑上连续的数据可能会位于不连续的物理数据页上，在许多插入操作创建
了溢出记录时尤其如此。按这种方式组织数据时，数据库管理器必须执行其他读操作才能访问顺序数据。
另外，在删除大量行后，也需要执行其他的读操作。表重组操作会整理数据碎片来减少浪费的空间，并对行
进行重新排序以合并溢出记录，从而加快数据访问速度并最终提高查询性能。还可以指定根据特定索引来
重新排序数据以便查询时通过最少次数据读取操作就可以访问数据。
对表增删改字段语句执行完修改后都要执行上面两个语句：
--REORG TABLE SMISUSER.TBL_EMG_EQMINF;
--RUNSTATS ON TABLE SMISUSER.TBL_EMG_EQMINF WITH DISTRIBUTION AND DETAILED INDEXES ALL;
--增加字段
ALTER TABLE TBL_EMG_EQMINF ADD COLUMN USER_ID CHARACTER(10);
--修改字段
1)修改字段类型,限制有：
varchar 可以修改为char 也可以改变长度，但能长不能短。 integer可以修改为varchar ,但varchar 的长度
要大于integer
ALTER TABLE TEMP_RPT_FH_INTO_PIECES ALTER IS_PIN_MARK SET DATA TYPE VARCHAR(128);
2)字段修改为不为空
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET NOT NULL;
3)将已有的字段修改为自增长，但前提是该字段是整形且字段不能为空。
Alter table <table name> alter column <column name> set generated always as identity(
 start with 1,increment by 1);
4)对已有字段重命名
ALTER TABLE <table name> RENAME  column <column name> TO <new column name>;
5)修改已有字段的默认值
ALTER TABLE <table name> ALTER  column <column name>  { SET DEFAULT value | DROP DEFAULT };
--删除字段
ALTER table <table name> DROP column <column name>;
对表添加注释：COMMENT ON
COMMENT ON TABLE "DB2ADMIN"."TEMP_STAFF_NUM_ORG" IS '主任组内人数';
COMMENT ON "DB2ADMIN"."TEMP_STAFF_NUM_ORG"(
    "HEAD_ID" IS '主任id', "PERSON_STAFF_NUM" IS '组内人数' );
7.调用存储过程
call DB2ADMIN.PROC_GET_REPORT_AFTER_MONTH_TEST006(?,?);
--out类型用'?',in直接赋值