动态卸载分区表数据

此存储过程实现动态卸载分区表数据。

SET SCHEMA TMISUSR ;


SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TMISUSR";


CREATE PROCEDURE "TMISUSR"."PRC_BATCH_DETACH_DATA"
 (IN "PI_D_ACCT" DATE, 
  IN "PI_S_SCHEMA" VARCHAR(20), 
  OUT "PO_I_RETCODE" INTEGER, 
  OUT "PO_S_RETTEXT" VARCHAR(2048), 
  OUT "PO_S_STMT" VARCHAR(4096)
 ) 
  SPECIFIC "TMISUSR"."SQL100810165810100"
  DYNAMIC RESULT SETS 1
  LANGUAGE SQL
  NOT DETERMINISTIC
  CALLED ON NULL INPUT
  EXTERNAL ACTION
  OLD SAVEPOINT LEVEL
  MODIFIES SQL DATA
  INHERIT SPECIAL REGISTERS
  ------------------------------------------------------------------------
-- SQL 存储过程 
-- 功能 : 动态卸载分区表数据 
-- 调用时机：月末日切前
-- db2 "call  PROC_BATCH_DETACH_DATA('2006-08-01','tmisusr',?,?,?)"
------------------------------------------------------------------------
P1:BEGIN
DECLARE V_s_Stmt VARCHAR(8192) DEFAULT '';
DECLARE V_s_AllStmt VARCHAR(8192) DEFAULT '';
declare v_s_ErrMsg varchar(1024);--错误原因描述
declare SQLCODE INT;
declare SQLSTATE CHAR(5);

-- 声明异常处理
--declare continue handler for SQLSTATE '02000'
-- begin
-- end;
declare exit handler for SQLEXCEPTION
begin
GET DIAGNOSTICS EXCEPTION 1 v_s_ErrMsg = MESSAGE_TEXT;
   values(SQLCODE,'动态卸载分区表数据错误，错误状态：'||SQLSTATE||'说明:'||v_s_ErrMsg,v_s_Stmt) into po_i_RetCode,po_s_RetText,po_s_Stmt;
end;


IF DAY(pi_d_Acct + 1 days) <> 1 THEN
SIGNAL SQLSTATE '22007' SET MESSAGE_TEXT = '调用参数：清理日期必须为月末 ！';
END IF;
   
    FOR v_Par AS SELECT TABNAME,DATAPARTITIONNAME 
    FROM TABLE
    (
    select TABNAME,DATAPARTITIONNAME 
from syscat.datapartitions a , td_multitbl_design b 
where 
   a.TABSCHEMA = UPPER(pi_s_Schema) 
--Before Pef:   
--AND POSITION(b.S_TBLNAME,UPPER(a.TABNAME),OCTETS) <> 0
--After Per：
AND a.TABNAME >= UPPER(b.S_TBLNAME) AND a.TABNAME < UPPER(b.S_TBLNAME)||'99'
AND 
(
( a.LOWVALUE = ''''||CHAR((pi_d_Acct + 1 days - b.I_SAVETERM years),ISO )||''''  AND b.S_SWITCHFREQ = '1' )
OR 
( a.LOWVALUE = CAST(((YEAR(pi_d_Acct + 1 days) - b.I_SAVETERM )*100 + MONTH(pi_d_Acct + 1 days)) AS CHAR(6)) AND b.S_SWITCHFREQ = '2' )
OR
( a.LOWVALUE = CAST((YEAR(pi_d_Acct + 1 days) - b.I_SAVETERM ) AS CHAR(4))   AND b.S_SWITCHFREQ = '3' )  
)
    ) AS TMP DO
    SET V_s_Stmt = 'ALTER TABLE '||v_Par.TABNAME||' DETACH PARTITION '||v_Par.DATAPARTITIONNAME||' INTO PAR_TMP ' ;
   SET V_s_AllStmt = V_s_AllStmt||V_s_Stmt||' ; ' ; 
PREPARE s1 FROM V_s_Stmt;
   execute s1;
   DROP TABLE PAR_TMP;   
    END FOR;
    IF V_s_AllStmt = '' THEN
    values(SQLCODE,'目前没有需要卸载分区表数据',V_s_AllStmt ) into po_i_RetCode,po_s_RetText,po_s_Stmt;    
    ELSE
   values(SQLCODE,'动态卸载分区表数据成功',V_s_AllStmt) into po_i_RetCode,po_s_RetText,po_s_Stmt;        
    END IF;
END P1;