Autonomous transactions - DB2 9.7 Express-C

嗯?...這個在 Oracle 不是很早就有的嗎?
既然你出了，就意思意思玩一下

註：DB2 9.7還加了幾個 FUNCTIONs 像LAST_DAY，NEXT_DAY等，
　　另外，終於可以用 CREATE OR REPLACE語法、
　　也可以執行 Oracle PL/SQL。但Express-C版本沒辦法測...


/* 建立測試 table */

CREATE TABLE TEST
(
　　COL1 INTEGER,
　　COL2 VARCHAR(32)
) IN USERSPACE1;

CREATE TABLE AUTONOMOUS_TAB
(
　　SYSDT TIMESTAMP,
　　STEP_DESC VARCHAR(32)
) IN USERSPACE1;



/*
　建立測試 procedure sp_inslog 記錄 log
　採Autonomous方式
　在LANGUAGE SQL下方寫上 AUTONOMOUS，告訴 DB2 這支程式用自己的 Transaction
*/

create or replace procedure sp_inslog(in insz varchar(64))
specific sp_inslog
language sql
autonomous
begin
　　　insert into autonomous_tab
　　　values (current timestamp,insz);
　　　commit;
end


/*
　建立測試 procedure sp_proc1
　為一般procedure，裡面呼叫 sp_inslog執行寫log工作
*/

create or replace procedure sp_proc1
specific sp_proc1
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 test values (0,'ETL開始日期'||char(current date));
　　if (retcode <> 0) and (retcode <> 100) then
　　　　rollback;
　　　　call sp_inslog(char(retcode) || ' 流程0執行失敗');
　　else
　　　　call sp_inslog('流程0執行成功');
　　end if;

　　insert into test values (1,'ETL開始時間'||char(current date));

　　IF (retcode <> 0) AND (retcode <> 100) THEN
　　　　rollback;
　　　　call sp_inslog(char(retcode) || ' 流程1執行失敗');
　　else
　　　　call sp_inslog('流程1執行成功');
　　end if;
　　commit;
end

/* 測試成功的狀況 */
C:\>db2 call sp_proc1

　傳回狀態 = 0

/* 看結果 */

C:\>db2 select * from test

COL1　　　　　COL2
----------- --------------------------------
　　　　　　0 ETL開始日期2009-09-21
　　　　　　1 ETL開始時間2009-09-21

　　已選取 2 個記錄。

C:\>db2 select * from autonomous_tab

SYSDT　　　　　　　　　　　　　STEP_DESC
-------------------------- -------------
2009-09-21-17.02.38.141000 流程0執行成功
2009-09-21-17.02.38.201000 流程1執行成功

　　已選取 2 個記錄。


/*
　直接改一下 procedure sp_proc1 讓它在執行新增第二次test時失敗
*/

create or replace procedure sp_proc1
specific sp_proc1
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 test values (0,'ETL開始日期'||char(current date));
　　　if (retcode <> 0) and (retcode <> 100) then
　　　　　　rollback;
　　　　　　call sp_inslog(char(retcode) || ' 流程0執行失敗');
　　　else
　　　　　　call sp_inslog('流程0執行成功');
　　　end if;

　　　insert into test values　(1,'倉儲系統ETL開始時間'||
　　　　　　　　　　　　　　　　　　char(current timestamp));

　　　IF (retcode <> 0) AND (retcode <> 100) THEN
　　　　　　rollback;
　　　　　　call sp_inslog(char(retcode) || ' 流程1執行失敗');
　　　else
　　　　　　call sp_inslog('流程1執行成功');
　　　end if;
　　　commit;
end

/* 測試失敗的狀況 */
C:\>db2 call sp_proc1

　傳回狀態 = 0


C:\>db2 select * from test

COL1　　　　　COL2
----------- --------------------------------

　　已選取 0 個記錄。


/* autonomous_tab第一筆資料未隨著外面那層 procedure的rollback而跟著rollback */

C:\>db2 select * from autonomous_tab

SYSDT　　　　　　　　　　　　　STEP_DESC
-------------------------- --------------------------
2009-09-21-17.39.45.593000 流程0執行成功
2009-09-21-17.39.45.614000 -433 流程1執行失敗

已選取 2 個記錄。