DB2清空表数据存储过程

清空表数据
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');