in0de

-- 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')"

db2 connect to ${dbname}
db2 "select * from sysibm.sysversions"

db2 list history all for database ${dbname}

db2 "select create_time, tabname from syscat.tables where tabname like 'SYSTABLES'"