DB2 detch分区

谁借莪１个温暖的怀抱￠ 2022-08-18 03:22 119阅读 0赞

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;

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;