CREATE PROCEDURE SP_ADD_PARTITION ( IN V_VIEW_NAME VARCHAR(128),
IN V_VIEW_SCHEMA VARCHAR(128),
IN V_NEW_RANGE_START VARCHAR(2000),
IN V_NEW_RANGE_END VARCHAR(2000),
OUT V_RC INTEGER,
OUT V_ERROR_TEXT VARCHAR(32672) )
SPECIFIC SP_ADD_PARTITION
------------------------------------------------------------------------
-- SP_ADD_PARTITION
--
-- This procedure adds a partition to a partitioned view.
-- It gets as parameters the view name and the ranges of the new partition
-- It then creates a new table that will hold that range of values and add
-- it to the partitioned view
------------------------------------------------------------------------
P1: BEGIN
-- Declare variables
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE V_MODEL_TABLE_NAME VARCHAR(128);
DECLARE V_MODEL_TABLE_SCHEMA VARCHAR(128);
DECLARE V_TARGET_TABLE_NAME VARCHAR(128);
DECLARE V_NEW_TABLE_NAME VARCHAR(128);
DECLARE V_TARGET_TABLE_SCHEMA VARCHAR(128);
DECLARE V_PARTITION_COLUMN VARCHAR(128);
DECLARE V_MAX_SEQ INT;
DECLARE V_TBSPACE_CLAUSE VARCHAR(500);
DECLARE SQL_STMT VARCHAR(32000) DEFAULT '';
DECLARE SQL_STMT2 VARCHAR(32000) DEFAULT '';
DECLARE EXEC_IND INT;
DECLARE TXT VARCHAR(2000);
DECLARE INDEX_CURSOR CURSOR WITH HOLD FOR
select CASE WHEN COLSEQ = 1 THEN ' CREATE '||
CASE WHEN T1.UNIQUERULE IN ('P','U') THEN ' UNIQUE ' ELSE '' END ||
' INDEX '||T2.INDNAME||'_'
||DIGITS(V_MAX_SEQ)||' ON ' || STRIP(V_TARGET_TABLE_SCHEMA) || '.' ||
V_NEW_TABLE_NAME || ' ( ' ELSE '' END ||
CASE WHEN T2.COLSEQ BETWEEN 2 AND T1.COLCOUNT THEN ',' ELSE '' END || T2.COLNAME
|| ' ' || CASE T2.COLORDER WHEN 'A' THEN ' ASC ' ELSE ' DESC ' END ||
CASE WHEN T2.COLSEQ = T1.COLCOUNT THEN ') '|| --IN ' || t3.tbspace ||
CASE WHEN T1.REVERSE_SCANS = 'Y' THEN ' ALLOW REVERSE SCANS '
ELSE ' DISALLOW REVERSE SCANS ' END
ELSE ''
END TXT,
CASE WHEN T1.COLCOUNT = T2.COLSEQ THEN 1 ELSE 0 END AS EXEC_IND
--t2.INDSCHEMA, t2.indname, t2.colname, t2.COLSEQ, t2.colorder,
--t1.uniquerule, t1.colcount, t1.reverse_scans
from syscat.indexes t1
join syscat.indexcoluse t2
on t1.indschema = t2.indschema
and t1.indname = t2.indname
--join syscat.TABLESPACES t3
-- on t1.tbspaceid = t3.tbspaceid
WHERE T1.TABNAME = V_MODEL_TABLE_NAME
AND T1.TABSCHEMA = V_MODEL_TABLE_SCHEMA
order by t2.indschema, t2.indname, t2.colseq
WITH UR;
/*
DECLARE VIEW_CURSOR CURSOR WITH HOLD FOR
select CASE WHEN SEQ = 1 THEN ' CREATE VIEW '|| STRIP(V_VIEW_SCHEMA) || '.' || STRIP(V_VIEW_NAME) || ' AS '
WHEN SEQ <= TOTAL_SEQS THEN ' UNION ALL '
ELSE '' END ||
' SELECT * FROM ' || STRIP(V_TARGET_TABLE_SCHEMA) || '.' || V_TARGET_TABLE_NAME ||
'_' || DIGITS(SEQ) || ' WHERE ' || V_PARTITION_COLUMN || ' >= ' ||
V_NEW_RANGE_START || ' AND ' || V_PARTITION_COLUMN || ' < ' ||
V_NEW_RANGE_END
TXT,
CASE WHEN SEQ = TOTAL_SEQS THEN 1 ELSE 0 END AS EXEC_IND
from (SELECT COUNT(*) OVER() TOTAL_SEQS, SEQ,
RANGE_START, RANGE_END
FROM T_CONTROL_PARTITIONS
WHERE VIEW_NAME = V_VIEW_NAME
AND VIEW_SCHEMA = V_VIEW_SCHEMA
AND IS_ACTIVE = 1
) T
order by SEQ
WITH UR;
*/
-- HANDLERS
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
--SET V_ERROR_TEXT = 'ERROR HAPPENED, SQLCODE='||CHAR(SQLCODE)||', SQLSTATE='||SQLSTATE;
GET DIAGNOSTICS EXCEPTION 1 V_ERROR_TEXT = MESSAGE_TEXT;
SET V_RC = SQLCODE;
SET V_ERROR_TEXT = 'EXECUTING ' || SQL_STMT || ' RETURNED '|| V_ERROR_TEXT;
--insert into ddl_log (stmt_text) values(v_error_text);
----COMMIT;
ROLLBACK;
END;
INSERT INTO T_CONTROL_LOG (MSG_TEXT)
VALUES ('SP_ADD_PARTITION STARTING WITH V_VIEW_NAME='||V_VIEW_NAME||', V_VIEW_SCHEMA='||V_VIEW_SCHEMA||
', V_NEW_RANGE_START='||V_NEW_RANGE_START||', V_NEW_RANGE_END='||V_NEW_RANGE_END);
----COMMIT;
-- STEP 01 - GET THE NAME OF THE MODEL TABLE AND PARITITON_COLUMN
SELECT MODEL_TABLE_NAME, MODEL_TABLE_SCHEMA, TARGET_TABLE_NAME, TARGET_TABLE_SCHEMA, PARTITION_COLUMN
INTO V_MODEL_TABLE_NAME, V_MODEL_TABLE_SCHEMA, V_TARGET_TABLE_NAME, V_TARGET_TABLE_SCHEMA, V_PARTITION_COLUMN
FROM T_CONTROL_PARTITIONED_TABLES
WHERE VIEW_NAME = V_VIEW_NAME
AND VIEW_SCHEMA = V_VIEW_SCHEMA;
-- IF NO PARTITIONS EXISTS, EXIT
IF (SQLCODE = 100) THEN
SET V_RC = -8;
SET V_ERROR_TEXT = 'NO ROWS FOUND IN T_CONTROL_PARTITIONED_TABLES FOR VIEW_NAME ' ||
STRIP(V_VIEW_SCHEMA)||'.'|| V_VIEW_NAME;
INSERT INTO T_CONTROL_LOG (MSG_TEXT)
VALUES (V_ERROR_TEXT);
--COMMIT;
RETURN;
END IF;
-- GET THE NUMBER OF PARTITIONS IN THE CURRENT VIEW
SELECT MAX(SEQ) + 1
INTO V_MAX_SEQ
FROM T_CONTROL_PARTITIONS
WHERE VIEW_NAME = V_VIEW_NAME
AND VIEW_SCHEMA = V_VIEW_SCHEMA;
IF (SQLCODE = 100 OR V_MAX_SEQ is NULL) THEN
SET V_MAX_SEQ = 1;
END IF;
INSERT INTO T_CONTROL_LOG (MSG_TEXT)
VALUES ('NEXT PARTITION HAS ID = '|| VARCHAR(V_MAX_SEQ));
--COMMIT;
-- CREATE A NEW TABLE FOR THIS PARTITION
SET V_NEW_TABLE_NAME = V_TARGET_TABLE_NAME || '_' || DIGITS(V_MAX_SEQ);
SELECT ' IN '||TBSPACE || CASE WHEN INDEX_TBSPACE IS NOT NULL THEN ' INDEX IN ' || INDEX_TBSPACE ELSE '' END
INTO V_TBSPACE_CLAUSE
FROM SYSCAT.TABLES
WHERE TABSCHEMA = V_MODEL_TABLE_SCHEMA
AND TABNAME = V_MODEL_TABLE_NAME
AND TYPE = 'T';
INSERT INTO T_CONTROL_LOG (MSG_TEXT)
VALUES('V_TARGET_TABLE_SCHEMA='||V_TARGET_TABLE_SCHEMA||', V_TARGET_TABLE_NAME='||V_NEW_TABLE_NAME||
', V_MODEL_TABLE_SCHEMA='||V_MODEL_TABLE_SCHEMA || ', V_MODEL_TABLE_NAME='||V_MODEL_TABLE_NAME);
--COMMIT;
SET SQL_STMT = 'CREATE TABLE ' || STRIP(V_TARGET_TABLE_SCHEMA) || '.' || STRIP(V_NEW_TABLE_NAME) ||
' LIKE ' || STRIP(V_MODEL_TABLE_SCHEMA) || '.' || STRIP(V_MODEL_TABLE_NAME) ||
' INCLUDING COLUMN DEFAULTS '||V_TBSPACE_CLAUSE;
INSERT INTO T_CONTROL_LOG (MSG_TEXT)
VALUES(SQL_STMT);
--COMMIT;
PREPARE S1 FROM SQL_STMT;
EXECUTE S1;
-- ADD A CHECK CONSTRAINT FOR THAT TABLE
SET SQL_STMT = 'ALTER TABLE ' || STRIP(V_TARGET_TABLE_SCHEMA) || '.' || STRIP(V_NEW_TABLE_NAME) ||
' ADD CONSTRAINT ' || V_TARGET_TABLE_NAME || '_CHECK CHECK (' || V_PARTITION_COLUMN || ' >= ' ||
V_NEW_RANGE_START || ' AND ' || V_PARTITION_COLUMN || ' < ' ||
V_NEW_RANGE_END ||')';
INSERT INTO T_CONTROL_LOG (MSG_TEXT)
VALUES(SQL_STMT);
--COMMIT;
PREPARE S1 FROM SQL_STMT;
EXECUTE S1;
/*
-- GET THE UPPER BOUND OF THE LAST PARTITION IN THE PREVIOUS PARTITION
SELECT SEQ, RANGE_END INTO MAX_SEQ, NEW_RANGE_START
FROM (SELECT SEQ, RANGE_START, RANGE_END,
ROW_NUMBER() OVER(PARTITION BY VNAME,TNAME,CNAME ORDER BY SEQ DESC) ROWN
FROM T_PART_CONTROL
WHERE VNAME = VIEW_NAME
--AND TNAME = MODEL_TABLE_NAME
--AND CNMAE = KEY_COLUMN_NAME
) T
WHERE ROWN = 1;
-- IF NO PARTITIONS EXISTS, WE INITIALIZE THE VARIABLES
IF (SQLCODE = 100) THEN
SET NEW_RANGE_START = trunc_timestamp(CURRENT TIMESTAMP, 'HH24');
SET MAX_SEQ = 0;
END IF;
SET NEW_RANGE_END = NEW_RANGE_START + 4 HOURS;
SET NEW_TABLE_NAME = SUBSTR(MODEL_TABLE_NAME, 1, LOCATE('_MODEL',MODEL_TABLE_NAME))
|| CHAR(MAX_SEQ+1);
-- INSERT A NEW PARTITION TO THE CONTROL TABLE
INSERT INTO T_PART_CONTROL (VNAME, TNAME, CNAME, SEQ, RANGE_SIZE)
VALUES (VIEW_NAME, NEW_TABLE_NAME, KEY_COLUMN_NAME, MAX_SEQ + 1,
NEW_RANGE_START,
NEW_RANGE_END);
*/
--PREPARE S1 FROM SQL_STMT;
--EXECUTE S1;
-- CREATE ALL INDEXES FOR THAT TABLE
OPEN INDEX_CURSOR ; --USING MODEL_TABLE_NAME ;
SET SQL_STMT = '';
--INSERT INTO DDL_LOG(STMT_TEXT) VALUES ('CURSOR IS OPENED WITH SQLCODE='||VARCHAR(V_RC));
----COMMIT;
FETCH FROM INDEX_CURSOR INTO TXT, EXEC_IND ;
--INSERT INTO DDL_LOG(STMT_TEXT) VALUES ('FIRST FETCH OK');
----COMMIT;
WHILE (SQLCODE = 0) DO
SET SQL_STMT = SQL_STMT || TXT;
IF EXEC_IND = 1 THEN
INSERT INTO T_CONTROL_LOG (MSG_TEXT)
VALUES(SQL_STMT);
--COMMIT;
PREPARE s1 FROM SQL_STMT;
EXECUTE S1;
SET SQL_STMT = '';
END IF;
FETCH INDEX_CURSOR INTO TXT, EXEC_IND ;
END WHILE;
CLOSE INDEX_CURSOR;
-- NOW WE NEED TO ADD THIS PARTITION TO THE PARTITIONS TABLE
INSERT INTO T_CONTROL_PARTITIONS (VIEW_NAME, VIEW_SCHEMA, SEQ, RANGE_START, RANGE_END, IS_ACTIVE)
VALUES (V_VIEW_NAME, V_VIEW_SCHEMA, V_MAX_SEQ, V_NEW_RANGE_START, V_NEW_RANGE_END, 1);
-- RECREATE THE PARTITIONED VIEW
CALL SP_RECREATE_PVIEW (V_VIEW_NAME, V_VIEW_SCHEMA, V_RC, V_ERROR_TEXT);
/*
OPEN VIEW_CURSOR ;
SET SQL_STMT = '';
FETCH FROM VIEW_CURSOR INTO TXT, EXEC_IND ;
WHILE (SQLCODE = 0) DO
SET SQL_STMT = SQL_STMT || TXT;
FETCH VIEW_CURSOR INTO TXT, EXEC_IND ;
END WHILE;
CLOSE VIEW_CURSOR;
-- RECREATE THE VIEW
SET SQL_STMT2 = 'DROP VIEW ' || STRIP(V_VIEW_SCHEMA) || '.' || V_VIEW_NAME;
INSERT INTO T_CONTROL_LOG (MSG_TEXT)
VALUES(SQL_STMT2);
INSERT INTO T_CONTROL_LOG (MSG_TEXT)
VALUES(SQL_STMT);
--COMMIT;
PREPARE S2 FROM SQL_STMT2;
PREPARE S1 FROM SQL_STMT;
-- CHECK IF THE VIEW ALREADY EXISTS, IF NOT, NO NEED TO DROP IT
SELECT TABNAME INTO V_VIEW_NAME
FROM SYSCAT.TABLES
WHERE TABNAME = V_VIEW_NAME
AND TABSCHEMA = V_VIEW_SCHEMA
AND TYPE = 'V';
IF (SQLCODE = 0) THEN
EXECUTE S2; -- DO THE DROP
END IF;
EXECUTE S1; -- DO THE CREATE
*/
SET V_RC = 0;
SET V_ERROR_TEXT = 'NO ERROR';
END P1