將SRC_CUSTOMER.B 更新至 CUSTOMER的.B
MATCH條件:SRC_CUSTOMER.A = CUSTOMER.A
將以下MERGE語法存成.sql ex:notlogged.sql
*/
MERGE INTO CUSTOMER
USING SRC_CUSTOMER ON SRC_CUSTOMER.A = CUSTOMER.A
WHEN MATCHED THEN
UPDATE SET CUSTOMER.B = SRC_CUSTOMER.B;
--執行並得到以下錯誤訊息
C:\>db2 -tf notlogged.sql
DB21034E 命令被當作 SQL 陳述式處理，因為它不是有效的「命令行處理器」命令。
在SQL 處理程序期間，它已傳回：
SQL0964C 資料庫的交易日誌已滿。 SQLSTATE=57011
--ALTER TABLE 使其不寫LOG
C:\>DB2 ALTER TABLE CUSTOMER ACTIVATE NOT LOGGED INITIALLY
--再執行一次MERGE仍發生SQL0964C錯誤
--解決方法：
--(1)直接修改db環境變數DB2OPTIONS=+c (取消AUTO-COMMIT)
C:\>DB2SET DB2OPTIONS=+c
C:\>DB2 ALTER TABLE CUSTOMER ACTIVATE NOT LOGGED INITIALLY
C:\>db2 -tf notlogged.sql
C:\>DB2 COMMIT
C:\>DB2SET DB2OPTIONS=-c
--(2)只在執行SQL中取消AUTO-COMMIT
--編輯一下notlogged.sql，內容如下:
-- 將AUTO-COMMIT取消
UPDATE COMMAND OPTIONS USING C OFF;
ALTER TABLE CUSTOMER ACTIVATE NOT LOGGED INITIALLY;
MERGE INTO CUSTOMER
USING SRC_CUSTOMER ON SRC_CUSTOMER.A = CUSTOMER.A
WHEN MATCHED THEN
UPDATE SET CUSTOMER.B = SRC_CUSTOMER.B;
COMMIT;
UPDATE COMMAND OPTIONS USING C ON;
執行notlogged.sql
C:\>db2 -tf notlogged.sql
--※ Procedure 的用法
--Procedure: sp_test，內容如下:
CREATE PROCEDURE SP_TEST
SPECIFIC SP_TEST
LANGUAGE SQL
BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE retcode INTEGER DEFAULT 0;
DECLARE szSql VARCHAR(128);
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
SET retcode = SQLCODE;
SET szSql = 'ALTER TABLE CUSTOMER ACTIVATE NOT LOGGED INITIALLY';
EXECUTE IMMEDIATE szSql;
MERGE INTO CUSTOMER
USING SRC_CUSTOMER ON SRC_CUSTOMER.A = CUSTOMER.A
WHEN MATCHED THEN
UPDATE SET CUSTOMER.B = SRC_CUSTOMER.B;
IF (retcode <> 0) AND (retcode <> 100) THEN
ROLLBACK;
RETURN retcode;
END IF;
COMMIT;
RETURN retcode;
END
--執行procedure
C:\>db2 call sp_test
傳回狀態=0
