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;