DB2 detch分区
来源:互联网 发布:有趣的名字知乎 编辑:程序博客网 时间:2024/06/05 07:51
1.SP_ADD_PARTITION (调用2.WAITFORDETACH 过程)
CREATE OR REPLACE PROCEDURE DW.SP_ADD_PARTITION (
IN V_TABLE VARCHAR(100),
IN V_PARTITION_NAME VARCHAR(20),
IN V_START VARCHAR(10),
IN V_END VARCHAR(10) )
BEGIN
--声明变量
DECLARE D_TIME_START TIMESTAMP;
DECLARE V_CNT INTEGER;
DECLARE N_ROWCOUNT INTEGER; --变更记录数
DECLARE V_SQL1 VARCHAR(400);
DECLARE V_TMP_NAME VARCHAR(100);
DECLARE V_TMP_NAME1 VARCHAR(100);
DECLARE SQLCODE INTEGER;
DECLARE V_SQLERRM VARCHAR(400);
DECLARE V_SQLCODE INTEGER;
DECLARE V_RET INTEGER;
DECLARE V_MSG VARCHAR(200);
DECLARE V_PART_ID INTEGER;
--异常声明
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS EXCEPTION 1 V_SQLERRM = MESSAGE_TEXT;
SET V_SQLCODE=SQLCODE;
ROLLBACK;
RETURN;
END;
--变量赋值
SET D_TIME_START=CURRENT TIMESTAMP;
--
SELECT COUNT(*) INTO V_CNT FROM SYSCAT.DATAPARTITIONS
WHERE TABSCHEMA ='DW'
AND TABNAME = UPPER(V_TABLE)
AND DATAPARTITIONNAME = UPPER(V_PARTITION_NAME)
WITH UR;
SELECT DATAPARTITIONID INTO V_PART_ID FROM SYSCAT.DATAPARTITIONS
WHERE TABSCHEMA ='DW'
AND TABNAME = UPPER(V_TABLE)
AND DATAPARTITIONNAME = UPPER(V_PARTITION_NAME)
WITH UR;
IF V_CNT>0 THEN
SELECT UPPER(V_TABLE||'_ADD_PART') INTO V_TMP_NAME FROM SYSIBM.DUAL;
CALL DROP_TMP_TABLE(V_TMP_NAME);
SET V_SQL1='ALTER TABLE '||V_TABLE||' DETACH PARTITION '||UPPER(V_PARTITION_NAME)||' INTO '||V_TMP_NAME;
PREPARE S1 FROM V_SQL1;
EXECUTE S1;
COMMIT;
CALL WAITFORDETACH(V_RET,V_MSG,'DW',V_TABLE,V_PART_ID);
COMMIT;
CALL DROP_TMP_TABLE(V_TMP_NAME);
COMMIT;
END IF;
SET V_SQL1='ALTER TABLE DW.'||V_TABLE||' ADD PARTITION '||UPPER(V_PARTITION_NAME)||' STARTING '||V_START||' INCLUSIVE ENDING '||V_END||' EXCLUSIVE';
PREPARE S1 FROM V_SQL1;
EXECUTE S1;
END;
IN V_TABLE VARCHAR(100),
IN V_PARTITION_NAME VARCHAR(20),
IN V_START VARCHAR(10),
IN V_END VARCHAR(10) )
BEGIN
--声明变量
DECLARE D_TIME_START TIMESTAMP;
DECLARE V_CNT INTEGER;
DECLARE N_ROWCOUNT INTEGER; --变更记录数
DECLARE V_SQL1 VARCHAR(400);
DECLARE V_TMP_NAME VARCHAR(100);
DECLARE V_TMP_NAME1 VARCHAR(100);
DECLARE SQLCODE INTEGER;
DECLARE V_SQLERRM VARCHAR(400);
DECLARE V_SQLCODE INTEGER;
DECLARE V_RET INTEGER;
DECLARE V_MSG VARCHAR(200);
DECLARE V_PART_ID INTEGER;
--异常声明
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS EXCEPTION 1 V_SQLERRM = MESSAGE_TEXT;
SET V_SQLCODE=SQLCODE;
ROLLBACK;
RETURN;
END;
--变量赋值
SET D_TIME_START=CURRENT TIMESTAMP;
--
SELECT COUNT(*) INTO V_CNT FROM SYSCAT.DATAPARTITIONS
WHERE TABSCHEMA ='DW'
AND TABNAME = UPPER(V_TABLE)
AND DATAPARTITIONNAME = UPPER(V_PARTITION_NAME)
WITH UR;
SELECT DATAPARTITIONID INTO V_PART_ID FROM SYSCAT.DATAPARTITIONS
WHERE TABSCHEMA ='DW'
AND TABNAME = UPPER(V_TABLE)
AND DATAPARTITIONNAME = UPPER(V_PARTITION_NAME)
WITH UR;
IF V_CNT>0 THEN
SELECT UPPER(V_TABLE||'_ADD_PART') INTO V_TMP_NAME FROM SYSIBM.DUAL;
CALL DROP_TMP_TABLE(V_TMP_NAME);
SET V_SQL1='ALTER TABLE '||V_TABLE||' DETACH PARTITION '||UPPER(V_PARTITION_NAME)||' INTO '||V_TMP_NAME;
PREPARE S1 FROM V_SQL1;
EXECUTE S1;
COMMIT;
CALL WAITFORDETACH(V_RET,V_MSG,'DW',V_TABLE,V_PART_ID);
COMMIT;
CALL DROP_TMP_TABLE(V_TMP_NAME);
COMMIT;
END IF;
SET V_SQL1='ALTER TABLE DW.'||V_TABLE||' ADD PARTITION '||UPPER(V_PARTITION_NAME)||' STARTING '||V_START||' INCLUSIVE ENDING '||V_END||' EXCLUSIVE';
PREPARE S1 FROM V_SQL1;
EXECUTE S1;
END;
2.WAITFORDETACH
CREATE OR REPLACE PROCEDURE DW.WAITFORDETACH (
OUT RETURNCODE INTEGER,
OUT MSG VARCHAR(128),
IN SCHEMANAME VARCHAR(128),
IN TABLENAME VARCHAR(128),
IN DATAPARTID INTEGER DEFAULT -1 )
BEGIN
DECLARE DPID INT;
DECLARE DPSTATE CHAR;
DECLARE DONE BOOLEAN DEFAULT FALSE;
DECLARE TABNOTFOUND BOOLEAN DEFAULT FALSE;
DECLARE CURCLOSED BOOLEAN DEFAULT FALSE;
DECLARE OLDLOCKTIMEOUT INTEGER;
DECLARE ALLDETACHCHECK CURSOR FOR
SELECT DATAPARTITIONID, STATUS FROM SYSCAT.DATAPARTITIONS
WHERE TABSCHEMA = SCHEMANAME
AND TABNAME = TABLENAME
AND (STATUS = 'L' OR STATUS = 'D')
WITH CS;
DECLARE ONEDETACHCHECK CURSOR FOR
SELECT DATAPARTITIONID, STATUS FROM SYSCAT.DATAPARTITIONS
WHERE TABSCHEMA = SCHEMANAME
AND TABNAME = TABLENAME
AND DATAPARTITIONID = DATAPARTID
WITH CS;
-- ADD SIGNAL HANDLERS FOR ERRORS THAT YOU CONSIDER NON-FATAL HERE
-- EXAMPLE FOR NO ROW FOUND AND LOCK TIMEOUT SHOWN BELOW
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
-- IF NO RECORD WAS FOUND THERE IS NOTHING TO WAIT FOR, SO WE ARE DONE
SET DONE = TRUE;
END;
DECLARE CONTINUE HANDLER FOR SQLSTATE '40001'
BEGIN
-- ON A LOCK TIMEOUT WE ARE NOT DONE, WE WANT TO LOOP AGAIN
SET DONE = FALSE;
-- THE CURSOR WILL BE CLOSED AS PART OF THE ROLLBACK DURING THE LOCK TIMEOUT
SET CURCLOSED = TRUE;
END;
-- INITIALIZE RETURN CODE TO 0
SET RETURNCODE = 0;
-- SAVE CURRENT LOCK TIMEOUT VALUE
VALUES CURRENT LOCK TIMEOUT INTO OLDLOCKTIMEOUT;
SET CURRENT LOCK TIMEOUT -1;
WHILE DONE = FALSE DO
SET CURCLOSED = FALSE;
IF DATAPARTID <> -1
THEN
OPEN ONEDETACHCHECK;
FETCH ONEDETACHCHECK INTO DPID, DPSTATE;
-- TWO CASES HERE:
-- (I) DETACH HAS ALREADY COMPLETED HENCE PARTITION ENTRY NOT
-- FOUND IN CATALOGS (INDICATED BY DONE = TRUE, HANDLED LATER)
-- (II) DETACH IN PROGRESS, PARTITION STATE SHOULD NOT BE VISIBLE
IF DONE <> TRUE AND (DPSTATE = '' OR DPSTATE = 'A') THEN
SET MSG = 'CANNOT WAITFORDETACH IF DETACH WAS NOT ISSUED ON ' ||
'DATAPARTITIONID ' || DATAPARTID;
SET RETURNCODE = -1;
GOTO EXIT;
END IF;
IF CURCLOSED <> TRUE THEN
CLOSE ONEDETACHCHECK;
END IF;
ELSE
OPEN ALLDETACHCHECK;
FETCH ALLDETACHCHECK INTO DPID, DPSTATE;
IF CURCLOSED <> TRUE THEN
CLOSE ALLDETACHCHECK;
END IF;
END IF;
END WHILE;
IF DATAPARTID <> -1
THEN
SET MSG = 'DETACH COMPLETED ON DATAPARTITIONID ' || DATAPARTID;
ELSE
SET MSG = 'ALL DETACH OPERATIONS COMPLETED ON TABLE ' ||
SCHEMANAME || '.' || TABLENAME;
END IF;
EXIT:
-- RESTORE LOCK TIMEOUT VALUE
SET CURRENT LOCK TIMEOUT OLDLOCKTIMEOUT;
RETURN RETURNCODE;
END;
OUT RETURNCODE INTEGER,
OUT MSG VARCHAR(128),
IN SCHEMANAME VARCHAR(128),
IN TABLENAME VARCHAR(128),
IN DATAPARTID INTEGER DEFAULT -1 )
BEGIN
DECLARE DPID INT;
DECLARE DPSTATE CHAR;
DECLARE DONE BOOLEAN DEFAULT FALSE;
DECLARE TABNOTFOUND BOOLEAN DEFAULT FALSE;
DECLARE CURCLOSED BOOLEAN DEFAULT FALSE;
DECLARE OLDLOCKTIMEOUT INTEGER;
DECLARE ALLDETACHCHECK CURSOR FOR
SELECT DATAPARTITIONID, STATUS FROM SYSCAT.DATAPARTITIONS
WHERE TABSCHEMA = SCHEMANAME
AND TABNAME = TABLENAME
AND (STATUS = 'L' OR STATUS = 'D')
WITH CS;
DECLARE ONEDETACHCHECK CURSOR FOR
SELECT DATAPARTITIONID, STATUS FROM SYSCAT.DATAPARTITIONS
WHERE TABSCHEMA = SCHEMANAME
AND TABNAME = TABLENAME
AND DATAPARTITIONID = DATAPARTID
WITH CS;
-- ADD SIGNAL HANDLERS FOR ERRORS THAT YOU CONSIDER NON-FATAL HERE
-- EXAMPLE FOR NO ROW FOUND AND LOCK TIMEOUT SHOWN BELOW
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
-- IF NO RECORD WAS FOUND THERE IS NOTHING TO WAIT FOR, SO WE ARE DONE
SET DONE = TRUE;
END;
DECLARE CONTINUE HANDLER FOR SQLSTATE '40001'
BEGIN
-- ON A LOCK TIMEOUT WE ARE NOT DONE, WE WANT TO LOOP AGAIN
SET DONE = FALSE;
-- THE CURSOR WILL BE CLOSED AS PART OF THE ROLLBACK DURING THE LOCK TIMEOUT
SET CURCLOSED = TRUE;
END;
-- INITIALIZE RETURN CODE TO 0
SET RETURNCODE = 0;
-- SAVE CURRENT LOCK TIMEOUT VALUE
VALUES CURRENT LOCK TIMEOUT INTO OLDLOCKTIMEOUT;
SET CURRENT LOCK TIMEOUT -1;
WHILE DONE = FALSE DO
SET CURCLOSED = FALSE;
IF DATAPARTID <> -1
THEN
OPEN ONEDETACHCHECK;
FETCH ONEDETACHCHECK INTO DPID, DPSTATE;
-- TWO CASES HERE:
-- (I) DETACH HAS ALREADY COMPLETED HENCE PARTITION ENTRY NOT
-- FOUND IN CATALOGS (INDICATED BY DONE = TRUE, HANDLED LATER)
-- (II) DETACH IN PROGRESS, PARTITION STATE SHOULD NOT BE VISIBLE
IF DONE <> TRUE AND (DPSTATE = '' OR DPSTATE = 'A') THEN
SET MSG = 'CANNOT WAITFORDETACH IF DETACH WAS NOT ISSUED ON ' ||
'DATAPARTITIONID ' || DATAPARTID;
SET RETURNCODE = -1;
GOTO EXIT;
END IF;
IF CURCLOSED <> TRUE THEN
CLOSE ONEDETACHCHECK;
END IF;
ELSE
OPEN ALLDETACHCHECK;
FETCH ALLDETACHCHECK INTO DPID, DPSTATE;
IF CURCLOSED <> TRUE THEN
CLOSE ALLDETACHCHECK;
END IF;
END IF;
END WHILE;
IF DATAPARTID <> -1
THEN
SET MSG = 'DETACH COMPLETED ON DATAPARTITIONID ' || DATAPARTID;
ELSE
SET MSG = 'ALL DETACH OPERATIONS COMPLETED ON TABLE ' ||
SCHEMANAME || '.' || TABLENAME;
END IF;
EXIT:
-- RESTORE LOCK TIMEOUT VALUE
SET CURRENT LOCK TIMEOUT OLDLOCKTIMEOUT;
RETURN RETURNCODE;
END;
0 0
- DB2 detch分区
- DB2 分区
- [转贴]DB2 分区特性
- DB2数据库分区(1)
- DB2数据库分区(2)
- db2 增加删除分区
- db2删除分区
- DB2分区概念
- DB2 分区表增加分区
- DB2 V9表分区
- DB2建立分区数据库
- DB2 表分区
- db2 分区数据库详解
- db2表分区
- db2 增加删除分区
- db2 数据库分区
- 如何建立DB2分区数据库?
- 搭建db2数据库多分区
- Android :JNI基本原理
- 【Java编程】使用增强for循环和迭代器遍历Map集合
- js学习总结
- 前端知乎:关于阮一峰博客《学习Javascript闭包》章节中最后两个思考题
- 【Android】Facebook 登陆APP ID获取 android hash key获取,development/ release
- DB2 detch分区
- Android 反射实战 - 更换APP皮肤<1>
- VS2010为所有的工程配置lib和include路径
- 倒计时自动关闭弹出窗口
- <<UNIX环境高级编程>>随书代码的打开方式
- 认识DOM的三大节点:元素节点,文本节点,属性节点以及nodeName,nodeType,nodeValue的区别
- iOS - 断言处理与调试
- VC++中的通知消息
- 结构(公有类)的运算符重载