db2에는 아직 ALTER TABLE tblName DROP PARTITION partName 구문이 없기 때문에
1. 파티션을 detach
2. 파티션테이블로부터 detach 된 single table을 drop
의 단계로 진행해야 한다.
이 때, detach 동작은 background에서 asynchronous하게 동작하므로
실제로 detach가 되어 single table로 떨어져나간 시점에 DROP TABLE을 수행하여야 하는데
partition detach가 금방 수행완료될 지 아닐 지는 알 수가 없다.
이러한 고려사항을 포함하여 간편하게 수행할 수 있는 프로시저의 소스는 아래와 같다.
-- db2 -td@ -vf dropPartition.sql
-- in dropPartition.sql
CREATE OR REPLACE PROCEDURE DBA.DROP_PARTITION (IN tblSchema VARCHAR(40), IN tblName VARCHAR(60), IN partName VARCHAR(120))
SPECIFIC DROP_PARTITION
LANGUAGE SQL
BEGIN
DECLARE tblSchema VARCHAR(40) DEFAULT '___________';
DECLARE detachSQL VARCHAR(2000);
DECLARE dropSQL VARCHAR(2000);
DECLARE isTblAbsent INT;
DECLARE dpID INT;
DECLARE dpState CHAR;
DECLARE isNormalState INT DEFAULT 0;
DECLARE curDPLock CURSOR FOR
SELECT DATAPARTITIONID, STATUS
FROM SYSIBM.SYSDATAPARTITIONS DP
WHERE DP.TABSCHEMA = tblSchema
AND DP.TABNAME = tblName
AND (STATUS = 'L' OR STATUS = 'D' OR STATUS = 'I');
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET isNormalState = 1;
CALL DBA.chkTblExist (tblSchema, tblName, isTblAbsent);
IF isTblAbsent = 1
THEN
RETURN -1;
END IF;
SET detachSQL = 'ALTER TABLE ' || tblSchema || '.' || tblName || ' DETACH PARTITION '|| partName || ' INTO ' || tblSchema || '.' || tblName || '_' || partName || '_DETACHED';
--CALL DBMS_OUTPUT.PUT_LINE(detachSQL);
EXECUTE IMMEDIATE detachSQL;
EXECUTE IMMEDIATE 'COMMIT WORK';
WAIT_LOOP:
LOOP
OPEN curDPLock;
FETCH curDPLock INTO dpID, dpState;
CLOSE curDPLock;
IF isNormalState = 1
THEN
LEAVE WAIT_LOOP;
ELSE
ITERATE WAIT_LOOP;
END IF;
END LOOP;
SET dropSQL = 'DROP TABLE ' || tblSchema || '.' || tblName || '_' || partName || '_DETACHED';
-- CALL DBMS_OUTPUT.PUT_LINE(dropSQL);
EXECUTE IMMEDIATE dropSQL;
EXECUTE IMMEDIATE 'COMMIT WORK';
RETURN 0;
END@
--
/* Sample Table DDL
DROP TABLE INODE.TMP;
CREATE TABLE INODE.TMP (
OPRN_DT VARCHAR(8) NOT NULL ,
RIDE_TROP_ID VARCHAR(9) NOT NULL ,
RIDE_STN_NO VARCHAR(4) NOT NULL ,
ALGH_TROP_ID VARCHAR(9) NOT NULL ,
ALGH_STN_NO VARCHAR(4) NOT NULL ,
MTAR_AREA_CD VARCHAR(2) NOT NULL ,
RIDE_NCNT INTEGER NOT NULL WITH DEFAULT 0 ,
ALGH_NCNT INTEGER NOT NULL WITH DEFAULT 0 ,
RIDE_TTPS_NUM BIGINT NOT NULL WITH DEFAULT 0 ,
ALGH_TTPS_NUM BIGINT NOT NULL WITH DEFAULT 0 ,
RIDE_AMT BIGINT NOT NULL WITH DEFAULT 0 ,
ALGH_AMT BIGINT NOT NULL WITH DEFAULT 0 ,
RIDE_VLT_AMT BIGINT NOT NULL WITH DEFAULT 0 ,
DC_AMT BIGINT WITH DEFAULT 0 ,
GNPS_NUM BIGINT NOT NULL WITH DEFAULT 0 ,
TNPS_NUM BIGINT NOT NULL WITH DEFAULT 0 ,
CHPS_NUM BIGINT NOT NULL WITH DEFAULT 0 ,
SNCN_PSNG_NUM BIGINT NOT NULL WITH DEFAULT 0 ,
DSPR_PSNG_NUM BIGINT NOT NULL WITH DEFAULT 0 ,
NTMP_NUM BIGINT NOT NULL WITH DEFAULT 0 ,
FRGN_SNCN_PSNG_NUM BIGINT WITH DEFAULT 0 ,
FRAP_PSNG_NUM BIGINT WITH DEFAULT 0 ,
DW_LST_UPD_DTM VARCHAR(14) NOT NULL )
COMPRESS YES ADAPTIVE
DISTRIBUTE BY HASH(RIDE_STN_NO)
INDEX IN TR_MT16_I01 PARTITION BY RANGE(OPRN_DT)
(PART TMP_P201600 STARTING(MINVALUE) ENDING('20151299') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201601 STARTING('20160101') ENDING('20160199') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201602 STARTING('20160201') ENDING('20160299') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201603 STARTING('20160301') ENDING('20160399') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201604 STARTING('20160401') ENDING('20160499') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201605 STARTING('20160501') ENDING('20160599') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201606 STARTING('20160601') ENDING('20160699') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201607 STARTING('20160701') ENDING('20160799') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201608 STARTING('20160801') ENDING('20160899') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201609 STARTING('20160901') ENDING('20160999') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201610 STARTING('20161001') ENDING('20161099') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201611 STARTING('20161101') ENDING('20161199') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201612 STARTING('20161201') ENDING('20161299') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201701 STARTING('20170101') ENDING('20170199') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201702 STARTING('20170201') ENDING('20170299') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201703 STARTING('20170301') ENDING('20170399') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201704 STARTING('20170401') ENDING('20170499') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201705 STARTING('20170501') ENDING('20170599') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201706 STARTING('20170601') ENDING('20170699') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201707 STARTING('20170701') ENDING('20170799') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201708 STARTING('20170801') ENDING('20170899') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201709 STARTING('20170901') ENDING('20170999') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201710 STARTING('20171001') ENDING('20171099') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201711 STARTING('20171101') ENDING('20171199') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201712 STARTING('20171201') ENDING('20171299') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201801 STARTING('20180101') ENDING('20180199') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201802 STARTING('20180201') ENDING('20180299') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201803 STARTING('20180301') ENDING('20180399') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201804 STARTING('20180401') ENDING('20180499') IN TR_MT16_D01 INDEX IN TR_MT16_I01,
PART TMP_P201805 STARTING('20180501') ENDING('20180599') IN TR_MT16_D01 INDEX IN TR_MT16_I01);
CREATE UNIQUE INDEX INODE.PK_TMP ON KSCCDW.TMP (OPRN_DT ASC, RIDE_TROP_ID ASC, RIDE_STN_NO ASC, ALGH_TROP_ID ASC, ALGH_STN_NO ASC, MTAR_AREA_CD ASC) PARTITIONED;
ALTER TABLE INODE.TMP ADD CONSTRAINT PK_TMP PRIMARY KEY (OPRN_DT,RIDE_TROP_ID,RIDE_STN_NO,ALGH_TROP_ID,ALGH_STN_NO,MTAR_AREA_CD);
*/
-- Sample detach partition SQL
-- db2 "CALL DBA.DROP_PARTITION('INODE','TMP','TMP_P201601')"
