清空表数据
SET CURRENT SCHEMA = "bsdcmacw";
SET CURRENT PATH = SYSIBM,SYSFUN,SYSPROC,SYSIBMADM,BSDCMACW;
CREATE OR REPLACE PROCEDURE BSDCMACW.P_TRUNC_TABLE (
IN P_OWNER VARCHAR(32),
IN P_TABLE VARCHAR(32) )
SPECIFIC P_TRUNC_TABLE
LANGUAGE SQL
NOT DETERMINISTIC
EXTERNAL ACTION
MODIFIES SQL DATA
CALLED ON NULL INPUT
INHERIT SPECIAL REGISTERS
OLD SAVEPOINT LEVEL
BEGIN
DECLARE v_sql_str VARCHAR(1024);
SET v_sql_str = 'ALTER TABLE ' || p_owner || '.' || p_table || ' ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE';
--SET v_sql_str = 'TRUNCATE TABLE ' || p_owner || '.' || p_table || ' IGNORE DELETE TRIGGERS DROP STORAGE IMMEDIATE';
--执行报错 (SQLSTATE 25001)-该语句只允许作为工作单元的第一条语句
EXECUTE IMMEDIATE v_sql_str;
COMMIT;
END;
SET CURRENT SCHEMA = BSDCMACW;
最后调用
call BSDCMACW.P_TRUNC_TABLE('BSDCMACW','T_ADS_FACT_CWMO_0010');