CREATE PROCEDURE SP_REMOVE_PARTITION ( IN V_VIEW_NAME VARCHAR(128),
IN V_VIEW_SCHEMA VARCHAR(128),
IN V_RANGE_START VARCHAR(2000),
IN V_RANGE_END VARCHAR(2000),
IN V_RECS_TO_DEL INTEGER,
IN V_PARTS_TO_KEEP INTEGER,
IN V_DELETE_TABLES INTEGER,
OUT V_RC INTEGER,
OUT V_ERROR_TEXT VARCHAR(32672) )
SPECIFIC SP_REMOVE_PARTITION
------------------------------------------------------------------------
-- SP_REMOVE_PARTITION
--
-- This procedure removes a partition from the partitioned view.
-- The procedure can work in two modes, either remove a specific partition, or
-- remove the oldest partitions until X records are deleted.
------------------------------------------------------------------------
P1: BEGIN
-- Declare variables
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE V_SQLCODE INTEGER;
DECLARE V_SEQ INT;
DECLARE V_MAX_SEQ INT;
DECLARE V_ROWS_REMOVED INTEGER;
DECLARE V_COUNTINUE_DELETE SMALLINT;
DECLARE V_PART_ROWS INT;
DECLARE V_MIN_DELETED_SEQ INT;
DECLARE V_MAX_DELETED_SEQ INT;
DECLARE V_TARGET_TABLE_SCHEMA VARCHAR(128);
DECLARE V_TARGET_TABLE_NAME VARCHAR(128);
DECLARE V_SQL_DROP VARCHAR(32000);
DECLARE TABLES_DELETE_CURSOR CURSOR FOR
SELECT 'DROP TABLE '||STRIP(TARGET_TABLE_SCHEMA)||'.'||STRIP(TARGET_TABLE_NAME)||'_'||
DIGITS(SEQ)
FROM T_CONTROL_PARTITIONS T1
JOIN T_CONTROL_PARTITIONED_TABLES T2
ON T1.VIEW_SCHEMA = T2.VIEW_SCHEMA
AND T1.VIEW_NAME = T2.VIEW_NAME
WHERE T1.VIEW_SCHEMA = V_VIEW_SCHEMA
AND T1.VIEW_NAME = V_VIEW_NAME
AND T1.SEQ BETWEEN V_MIN_DELETED_SEQ AND V_MAX_DELETED_SEQ
AND T1.IS_ACTIVE = 0;
-- HANDLERS
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS EXCEPTION 1 V_ERROR_TEXT = MESSAGE_TEXT;
SET V_RC = SQLCODE;
ROLLBACK;
END;
INSERT INTO T_CONTROL_LOG (MSG_TEXT)
VALUES ('SP_REMOVE_PARTITION STARTING WITH V_VIEW_NAME='||V_VIEW_NAME||', V_VIEW_SCHEMA='||V_VIEW_SCHEMA||
', V_RANGE_START='||coalesce(V_RANGE_START,'null')||', V_RANGE_END='||coalesce(V_RANGE_END,'null')
||', V_RECS_TO_DEL='|| coalesce(VARCHAR(V_RECS_TO_DEL),'null')
|| ', V_PARTS_TO_KEEP='||coalesce(VARCHAR(V_PARTS_TO_KEEP),'null'));
--COMMIT;
SET V_PARTS_TO_KEEP = COALESCE(V_PARTS_TO_KEEP,1);
SET V_RC = 0;
SET V_ERROR_TEXT = 'NO ERROR';
-- IF WE NEED TO REMOVE A SPECIFIC PARTITION, REMOVE IT
IF (V_RANGE_START IS NOT NULL AND V_RANGE_END IS NOT NULL) THEN
INSERT INTO T_CONTROL_LOG (MSG_TEXT)
VALUES ('SP_REMOVE_PARTITION - DELETE BY RANGE OF PARTITIONS');
-- DELETE THE RANGE OF PARTITIONS BETWEEN THESE TWO VALUES
SELECT MIN(SEQ), MAX(SEQ) INTO V_MIN_DELETED_SEQ, V_MAX_DELETED_SEQ
FROM FINAL TABLE (
UPDATE T_CONTROL_PARTITIONS
SET IS_ACTIVE = 0
WHERE VIEW_NAME = V_VIEW_NAME
AND VIEW_SCHEMA = V_VIEW_SCHEMA
AND RANGE_START >= V_RANGE_START
AND RANGE_END <= V_RANGE_END
AND IS_ACTIVE = 1
AND SEQ <= (SELECT MAX(SEQ) - V_PARTS_TO_KEEP
FROM T_CONTROL_PARTITIONS
WHERE VIEW_NAME = V_VIEW_NAME
AND VIEW_SCHEMA = V_VIEW_SCHEMA
));
-- CHECK THAT SOME PARTITIONS WERE SELECTED
IF (SQLCODE = 100) THEN
SET V_RC = -4;
SET V_ERROR_TEXT = 'SP_REMOVE_PARTITION => NO PARTITIONS WERE FOUND IN THE SPECIFIED RANGE';
ELSE
INSERT INTO T_CONTROL_LOG (MSG_TEXT)
VALUES ('SP_REMOVE_PARTITION - DELETED PARTITIONS '||
VARCHAR(V_MIN_DELETED_SEQ)||' TO '||VARCHAR(V_MAX_DELETED_SEQ));
END IF;
-- WE NEED TO DELETE BY RANGE OF RECORDS
ELSEIF (V_RECS_TO_DEL IS NOT NULL) THEN
INSERT INTO T_CONTROL_LOG (MSG_TEXT)
VALUES ('SP_REMOVE_PARTITION - DELETE BY RECORD COUNT');
-- AT THIS POINT WE SCAN THE PARTITIONS FROM FIRST TO LAST AND DECIDE WHICH PARTITIONS TO
-- REMOVE ACCORDING TO THE STATISTICS IN THE CATALOG
SELECT MIN(T2.TARGET_TABLE_NAME), MIN(T2.TARGET_TABLE_SCHEMA),
MIN(T1.SEQ), MAX(T1.SEQ) - V_PARTS_TO_KEEP , 1, 0
INTO V_TARGET_TABLE_NAME, V_TARGET_TABLE_SCHEMA,
V_SEQ, V_MAX_SEQ, V_COUNTINUE_DELETE, V_ROWS_REMOVED
FROM T_CONTROL_PARTITIONS T1
JOIN T_CONTROL_PARTITIONED_TABLES T2
ON T1.VIEW_NAME = T2.VIEW_NAME
AND T1.VIEW_SCHEMA = T2.VIEW_SCHEMA
WHERE T1.VIEW_NAME = V_VIEW_NAME
AND T1.VIEW_SCHEMA = V_VIEW_SCHEMA
AND IS_ACTIVE = 1;
IF (SQLCODE = 100) THEN
SET V_RC = -8;
SET V_ERROR_TEXT = 'SP_REMOVE_PARTITION => VIEW DOES NOT EXISTS OR HAS 0 PARTITIONS';
END IF;
WHILE (V_SEQ <= V_MAX_SEQ AND V_COUNTINUE_DELETE = 1) DO
-- GET NUM OF ROWS FROM SYSCAT.TABLES FOR THIS PARTITION
SELECT CARD
INTO V_PART_ROWS
FROM SYSCAT.TABLES
WHERE TABSCHEMA = V_TARGET_TABLE_SCHEMA
AND TABNAME = V_TARGET_TABLE_NAME || '_' || DIGITS(V_SEQ)
AND STATS_TIME IS NOT NULL;
SET V_SQLCODE = SQLCODE;
if (V_SQLCODE = 100) then
INSERT INTO T_CONTROL_LOG (MSG_TEXT) VALUES(V_TARGET_TABLE_NAME || '_' || DIGITS(V_SEQ));
END IF;
INSERT INTO T_CONTROL_LOG (MSG_TEXT)
VALUES ('SP_REMOVE_PARTITION - SEQ '||VARCHAR(V_SEQ)||' HAS '||VARCHAR(V_PART_ROWS)||' ROWS');
-- CHECK IF WE WANT TO REMOVE THIS PARTITION
IF ((V_SQLCODE <> 100) AND (V_ROWS_REMOVED + V_PART_ROWS <= V_RECS_TO_DEL)) THEN
-- ADVANCE THE SEQUENCE
SET V_SEQ = V_SEQ + 1;
SET V_ROWS_REMOVED = V_ROWS_REMOVED + V_PART_ROWS;
INSERT INTO T_CONTROL_LOG (MSG_TEXT)
VALUES ('SP_REMOVE_PARTITION - REMOVE SEQ '||VARCHAR(V_SEQ));
ELSE
-- STOP
SET V_COUNTINUE_DELETE = 0;
END IF;
END WHILE;
-- DELETE THE PARTIITONS WE MARKED FOR DELETION (ALL THE PARTITIONS WITH SEQ > V_SEQ
INSERT INTO T_CONTROL_LOG (MSG_TEXT)
VALUES ('SP_REMOVE_PARTITION - REMOVING UP TO SEQ '||VARCHAR(V_SEQ-1));
SELECT MIN(SEQ), MAX(SEQ) INTO V_MIN_DELETED_SEQ, V_MAX_DELETED_SEQ
FROM FINAL TABLE (
UPDATE T_CONTROL_PARTITIONS
SET IS_ACTIVE = 0
WHERE VIEW_NAME = V_VIEW_NAME
AND VIEW_SCHEMA = V_VIEW_SCHEMA
AND IS_ACTIVE = 1
AND SEQ < V_SEQ);
INSERT INTO T_CONTROL_LOG (MSG_TEXT)
VALUES ('SP_REMOVE_PARTITION - DELETED PARTITIONS '||
VARCHAR(V_MIN_DELETED_SEQ)||' TO '||VARCHAR(V_MAX_DELETED_SEQ));
ELSEIF (V_PARTS_TO_KEEP > 1) THEN
INSERT INTO T_CONTROL_LOG (MSG_TEXT)
VALUES ('SP_REMOVE_PARTITION - DELETE ALL PARTITIONS EXCEPT MOST RECENT '||VARCHAR(V_PARTS_TO_KEEP));
-- DELETE ALL BUT THE LAST V_PARTS_TO_KEEP PARTITIONS
SELECT MIN(SEQ), MAX(SEQ) INTO V_MIN_DELETED_SEQ, V_MAX_DELETED_SEQ
FROM FINAL TABLE (
UPDATE T_CONTROL_PARTITIONS
SET IS_ACTIVE = 0
WHERE VIEW_NAME = V_VIEW_NAME
AND VIEW_SCHEMA = V_VIEW_SCHEMA
AND IS_ACTIVE = 1
AND SEQ <= (SELECT MAX(SEQ) - V_PARTS_TO_KEEP
FROM T_CONTROL_PARTITIONS
WHERE VIEW_NAME = V_VIEW_NAME
AND VIEW_SCHEMA = V_VIEW_SCHEMA
));
-- CHECK THAT SOME PARTITIONS WERE SELECTED
IF (SQLCODE = 100) THEN
SET V_RC = -4;
SET V_ERROR_TEXT = 'SP_REMOVE_PARTITION => NO PARTITIONS WERE FOUND IN THE SPECIFIED RANGE';
ELSE
INSERT INTO T_CONTROL_LOG (MSG_TEXT)
VALUES ('SP_REMOVE_PARTITION - DELETED PARTITIONS '||
VARCHAR(V_MIN_DELETED_SEQ)||' TO '||VARCHAR(V_MAX_DELETED_SEQ));
END IF;
ELSE
SET V_RC = -8;
SET V_ERROR_TEXT = 'THE RANGE PROVIDED TO SP_REMOVE_PARTITION IS INVALID';
INSERT INTO T_CONTROL_LOG (MSG_TEXT)
VALUES (V_ERROR_TEXT);
END IF;
-- RECREATE THE PARTITIONED VIEW
IF (V_RC = 0) THEN
CALL SP_RECREATE_PVIEW (V_VIEW_NAME, V_VIEW_SCHEMA, V_RC, V_ERROR_TEXT);
END IF;
-- LOOP THROUGH THE TABLES WE NEED TO DELETE AND DELETE THEM
IF (V_DELETE_TABLES = 1) THEN
INSERT INTO T_CONTROL_LOG (MSG_TEXT)
VALUES ('SP_REMOVE_PARTITION - USER CHOSE PHYSICAL DELETE');
OPEN TABLES_DELETE_CURSOR;
FETCH TABLES_DELETE_CURSOR INTO V_SQL_DROP;
WHILE (SQLCODE = 0) DO
INSERT INTO T_CONTROL_LOG (MSG_TEXT)
VALUES (V_SQL_DROP);
PREPARE S1 FROM V_SQL_DROP;
EXECUTE S1;
FETCH TABLES_DELETE_CURSOR INTO V_SQL_DROP;
END WHILE;
CLOSE TABLES_DELETE_CURSOR;
-- DELETE THE REOCRDS FROM THE CONTROL TABLE
DELETE FROM T_CONTROL_PARTITIONS
WHERE VIEW_SCHEMA = V_VIEW_SCHEMA
AND VIEW_NAME = V_VIEW_NAME
AND SEQ BETWEEN V_MIN_DELETED_SEQ AND V_MAX_DELETED_SEQ
AND IS_ACTIVE = 0;
END IF;
END P1