Two phase commit using DB2 9

/*
　　DB2 的 Two phase commit 是很久的東西了。但之前一直沒去測，後來才知道其實很簡單。
　　
　　My Scenario:
　　　　DB1:SAMPLE　　 TABLE: N_CONFIG
　　　　DB2:ORIONDB　　TABLE: ORION_CFG、TRC_LOG
　　　　PROCEDURE: SP_2PHASECOMMIT 建在ORIONDB。
　　　　　　　　　　先新增N_CONFIG再新增ORION_CFG。
　　　　　　　　　　新增失敗則ROLLBACK。
　　　　　　　　　　留LOG: TRC_LOG。
　　　　FEDERATION 的設定參考
　　　　Global variable and federated stored procedure using DB2 9
　　　　http://orionsdatabase.blogspot.com/2008/09/global-variable-and-federated-stored.html
*/


/*
　　DB:ORIONDB
　　Federation: Server 參數 DB2_TWO_PHASE_COMMIT 的設定
*/


/*　不使用 DB2CC 則可直接下 SQL */

ALTER SERVER SAMPLE OPTIONS (ADD DB2_TWO_PHASE_COMMIT 'Y')


※　不設定為 Y 。未來執行PROCEDURE會失敗。錯誤訊息為 -30090，表示不支援這種TWO PHASE COMMIT


/* 在 DB SAMPLE 上建立 N_CONFIG */

CREATE TABLE N_CONFIG
(
　　　KIND　　　VARCHAR(32),
　　　VALUE　　　VARCHAR(32),
　　　DESC　　　VARCHAR(128)
) IN USERSPACE1;

/* 在 DB ORIONDB 上建立 ORION_CFG 及 TRC_LOG */

CREATE TABLE ORION_CFG
(
　　　KIND　　　VARCHAR(32) NOT NULL PRIMARY KEY,
　　　VALUE　　　VARCHAR(32),
　　　DESC　　　VARCHAR(128)
) IN USERSPACE1;

CREATE TABLE TRC_LOG
(
　　　SYSDT　　　　　TIMESTAMP,
　　　ERROR_CODE　　INTEGER,
　　　INPUT_VALUE　　VARCHAR(1024)
) IN USERSPACE1;

/* 在 DB ORIONDB 上為 DB SAMPLE 的 N_CONFIG 建立 NICKNAME */

CREATE NICKNAME ORION.N_CONFIG FOR SAMPLE.ORION.N_CONFIG;

/* 在 DB ORIONDB 建立 stored procedure */

CREATE PROCEDURE SP_2PHASECOMMIT(IN iC1 VARCHAR(32),
　　　　　　　　　　　　　　　　　　　IN iC2 VARCHAR(32),
　　　　　　　　　　　　　　　　　　　IN iC3 VARCHAR(128))
SPECIFIC SP_2PHASECOMMIT
LANGUAGE SQL
BEGIN
　　　
　　　DECLARE SQLCODE　　INTEGER DEFAULT 0;
　　　DECLARE RETCODE　　INTEGER DEFAULT 0;

　　　DECLARE CONTINUE HANDLER
　　　FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
　　　SET RETCODE = SQLCODE;

　　　INSERT INTO N_CONFIG(KIND,VALUE,DESC)
　　　VALUES (iC1,iC2,iC3);

　　　IF (retcode <> 0) AND (retcode <> 100) THEN
　　　　　　GOTO LABEL1;
　　　END IF;

　　　INSERT INTO ORION_CFG(KIND,VALUE,DESC)
　　　VALUES (iC1,iC2,iC3);

　　　IF (retcode <> 0) AND (retcode <> 100) THEN
　　　　　　GOTO LABEL1;
　　　END IF;

　　　INSERT INTO TRC_LOG
　　　VALUES (CURRENT TIMESTAMP,retcode,RTRIM(iC1) || '-' ||
　　　　　　　　　　　　　　　　　　　　　　　RTRIM(iC2) || '-' ||
　　　　　　　　　　　　　　　　　　　　　　　RTRIM(iC3));
　　　COMMIT;
　　　RETURN retcode;
LABEL1:
　　　ROLLBACK;
　　　INSERT INTO TRC_LOG
　　　VALUES (CURRENT TIMESTAMP,retcode,RTRIM(iC1) || '-' ||
　　　　　　　　　　　　　　　　　　　　　　　RTRIM(iC2) || '-' ||
　　　　　　　　　　　　　　　　　　　　　　　RTRIM(iC3));
　　　COMMIT;
　　　RETURN retcode;
END



※　建立時會出現以下 warning :
　　　SQL1179W 稱為 "ORION.P6301784" 的 "PACKAGE" 可能要求呼叫者對資料來源物件具有必要的專用權。 SQLSTATE=01639
※　不用理會這個 warning


/* 測試 */

C:\>db2 call sp_2phasecommit('SERVER','10.7.72.88','SERVER IP')

傳回狀態 = 0

C:\>db2 select * from n_config

KIND　　　　　　　　VALUE　　　　　　　DESC
----------------- ---------------- ----------------
SERVER　　　　　　　10.7.72.88　　　　SERVER IP

　　已選取 1 個記錄。

C:\>db2 select * from orion_cfg

KIND　　　　　　　　VALUE　　　　　　　DESC
----------------- ---------------- ----------------
SERVER　　　　　　　10.7.72.88　　　　SERVER IP

　　已選取 1 個記錄。

C:\>DB2 SELECT * FROM TRC_LOG

SYSDT　　　　　　　　　　　　　ERROR_CODE　INPUT_VALUE
-------------------------- ----------- -----------------------------------------
2008-09-17-16.31.19.299000　　　　　 0 　SERVER-10.7.72.88-SERVER IP

　　已選取 1 個記錄。

/* 測試duplicate，做rollback */

C:\>db2 call sp_2phasecommit('SERVER','10.7.72.88','SERVER IP')

傳回狀態 = -803

C:\>db2 select * from n_config

KIND　　　　　　　　VALUE　　　　　　　DESC
----------------- ---------------- ----------------
SERVER　　　　　　　10.7.72.88　　　　SERVER IP

　　已選取 1 個記錄。


C:\>db2 select * from orion_cfg

KIND　　　　　　　　VALUE　　　　　　　DESC
----------------- ---------------- ----------------
SERVER　　　　　　　10.7.72.88　　　　SERVER IP

　　已選取 1 個記錄。


C:\>db2 select * from trc_log

SYSDT　　　　　　　　　　　　　ERROR_CODE　INPUT_VALUE
-------------------------- ----------- -----------------------------------------
2008-09-17-16.31.19.299000　　　　　 0 SERVER-10.7.72.88-SERVER IP
2008-09-17-16.31.44.655000　　　　-803 SERVER-10.7.72.88-SERVER IP

已選取 2 個記錄。