DROP PROCEDURE DB2USER.TOOLS_PARTITION_TABLE_SHOW (VARCHAR ());
CREATE OR REPLACE PROCEDURE Tools_partition_table_show(IN ETL_DATE VARCHAR(8))
/******************************************************************************
NAME:
PURPOSE:
REVISIONS:
Ver Date Author Description
--------- ---------- ------------ ------------------------------------
1.0 2015-07-22 Zen 1. 作为分区表添加和快速删除分区的一个示例
供有相同需求的脚本参考。
******************************************************************************/
LANGUAGE SQL
BEGIN
DECLARE V_LOCATION VARCHAR(100);
DECLARE V_START_TIME TIMESTAMP;
DECLARE V_SQLMSG VARCHAR(255);
DECLARE V_CNT INT;
DECLARE V_PARTITION_NAME VARCHAR(50);
DECLARE V_PARTITION_END VARCHAR(50);
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS EXCEPTION 1 V_SQLMSG = MESSAGE_TEXT;
CALL DB2USER.PRO_LOG(ETL_DATE,'Tools_partition_table_show','测试分区表作业方式',V_START_TIME,current timestamp,'ERROR',V_LOCATION,V_SQLMSG);
END;
/*清空目标表*/
SET V_START_TIME = current timestamp;
SET V_LOCATION = '清空数据';
/*
DELETE FROM DB2USER.S_CLM_RATE_POL_AAA WHERE BBQ = SUBSTR(ETL_DATE,1,6);
COMMIT;*/
SET V_LOCATION = '测试保单赔率表的抽取开始';
SET V_PARTITION_NAME = 'P'||SUBSTR(ETL_DATE,1,6);
SET V_PARTITION_END = TO_CHAR(add_months(TO_DATE(ETL_DATE,'YYYYMMDD'),1),'YYYYMM');
--SELECT TO_CHAR(add_months(TO_DATE(ETL_DATE,'YYYYMMDD'),1),'YYYYMM') INTO V_PARTITION_END FROM sysibm.dual;
--判断分区是否存在,如果存在,数据转出删除
SELECT COUNT(*) INTO V_CNT FROM syscat.datapartitions t
WHERE tabschema='DB2USER'
AND tabname='S_CLM_RATE_POL_AAA_TEST'
AND datapartitionname=V_PARTITION_NAME ;
IF V_CNT=1 THEN
-- 分区数据转出
EXECUTE IMMEDIATE 'ALTER TABLE S_CLM_RATE_POL_AAA_test DETACH PARTITION '||V_PARTITION_NAME||' INTO DB2USER.TEMP_S_CLM_RATE_POL_AAA';
COMMIT;
EXECUTE IMMEDIATE 'DROP TABLE DB2USER.TEMP_S_CLM_RATE_POL_AAA';
END IF ;
SET V_LOCATION = 'after 分区数据转出';
--非正常DML或DDL 需要调用 sysproc.admin_cmd();
--收集统计信息
CALL SYSPROC.ADMIN_CMD('RUNSTATS ON TABLE db2user.S_CLM_RATE_POL_AAA_test');
-- 添加新分区
EXECUTE IMMEDIATE 'ALTER TABLE DB2USER.S_CLM_RATE_POL_AAA_test ADD PARTITION '||V_PARTITION_NAME||' STARTING '||SUBSTR(ETL_DATE,1,6)||' INCLUSIVE ENDING '||V_PARTITION_END||' exclusive';
SET V_LOCATION = 'after 添加新分区';
COMMIT;
EXECUTE IMMEDIATE 'ALTER TABLE DB2USER.S_CLM_RATE_POL_AAA_test ACTIVATE NOT LOGGED INITIALLY';
FOR REC AS WITH TMP(TYPE) AS (SELECT 'AAA1' AS TYPE FROM SYSIBM.DUAL
UNION ALL
SELECT 'AAA2' AS TYPE FROM SYSIBM.DUAL
UNION ALL
SELECT 'AAA4' AS TYPE FROM SYSIBM.DUAL)
SELECT * FROM TMP
DO
SET V_LOCATION = 'test'||REC.TYPE;
INSERT INTO DB2USER.S_CLM_RATE_POL_AAA_test
SELECT
......END FOR;
CALL DB2USER.PRO_LOG(ETL_DATE,'Tools_partition_table_show','测试分区表作业方式',V_START_TIME,current timestamp,'SUCCESS','','');
END;
3.总结:
a.db2 中表分区目前只支持range分区,没有oracle的丰富。
b.分区不能直接删除必须先 DETACH PARTITION ,detach之后必须commit不然会报结构不完善的错误。
c.需要添加新的分区只需要直接 ADD partition。
d.DDL语句用在procedure中需要显示commit。