CREATE OR REPLACE PROCEDURE SET_INTEGRITY(
IN table_name VARCHAR(128),OUT counts INTEGER,OUT err_code INTEGER, OUT err_msg VARCHAR(2048))
SPECIFIC SET_INTEGRITY
MODIFIES SQL DATA
LANGUAGE SQL
BEGIN
--...
显示全部CREATE OR REPLACE PROCEDURE SET_INTEGRITY(
IN table_name VARCHAR(128),OUT counts INTEGER,OUT err_code INTEGER, OUT err_msg VARCHAR(2048))
SPECIFIC SET_INTEGRITY
MODIFIES SQL DATA
LANGUAGE SQL
BEGIN
--変数の宣言
DECLARE pending_num INTEGER DEFAULT 0;
DECLARE load_count INTEGER DEFAULT 0;
DECLARE stmt1 VARCHAR(1000);
DECLARE s2 STATEMENT;
DECLARE table_num CURSOR FOR s2;
DECLARE check_pending CURSOR FOR
SELECT count(1)
FROM SYSCAT.TABLES
WHERE STATUS <>'N'
AND ACCESS_MODE <>'F'
AND tabname = table_name
AND OWNER = current USER ;
OPEN check_pending ;
FETCH check_pending INTO pending_num ;
CLOSE check_pending ;
SET err_code = 0;
SET err_msg = '';
IF(pending_num >0) THEN
SET stmt1 = 'set integrity for ' || table_name || ' immediate checked' ;
prepare s1 from stmt1;
COMMIT ;
EXECUTE s1;
OPEN check_pending ;
FETCH check_pending INTO pending_num ;
CLOSE check_pending ;
IF(pending_num =0) THEN
SET err_msg = table_name || 'の制約が復活しました。';
SET err_code = 0;
ELSE
SET err_msg = table_name || 'の制約が復活失敗しました。';
SET err_code = 1;
SET stmt1 = 'load from empty.txt of del terminate into ' || table_name || ' NONRECOVERABLE';
CALL SYSPROC.ADMIN_CMD(stmt1) ;
END IF ;
ELSE
SET err_msg = table_name || 'の制約がありません。';
SET err_code = 0;
END IF;
SET stmt1 = 'SELECT count(1) FROM ' || table_name;
prepare s2 FROM stmt1;
OPEN table_num;
FETCH table_num INTO counts;
CLOSE table_num;
END
;
整体逻辑是这样。在load的完以后写了一个SET_INTEGRITY的prucedure,在这个prucedure要实现的是。先做set integrity操作,操作完以后在check一下表状态,如果表还是属于挂起状态的话,就要把这个表rollback。
收起