ADMIN_COPY_SCHEMA using DB2 9.7 Express-C

/*
　　DB2 9 提供一組還蠻好用的 Procedures,　
　　可以將指定的 source schema 下所有物件(還是有限制),複製到指定的 target schema 下
　　也可以刪除指定 schema 下所有物件(我相信還是有限制的,　這部份待測)　
*/

-- 前置作業(create users、create table、view、mqt 及 insert 資料)

「電腦管理」-> 「本機使用者和群組」-> 「使用者」 點選 「新使用者」
分別建立使用者 db2admin、dba。

-- 以使用者 db2admin 登進 db2

C:\>db2 create table t1 ( col1 integer,col2 varchar(32) ) in userspace1
DB20000I SQL 指令已順利完成。

C:\>db2 insert into t1 values (1,'abc'),(2,'xxx')
DB20000I SQL 指令已順利完成。

C:\>db2 create view v_t1 as select * from t1
DB20000I SQL 指令已順利完成。

-- 測試包含其它 schema 物件的 view 是否亦可被複製
C:\>db2 create view v_ap as select * from administrator.ap
DB20000I SQL 指令已順利完成。

C:\>db2 create table mqt_t1 as ( select * from t1 ) data initially deferred refresh deferred in userspace1
DB20000I SQL 指令已順利完成。

C:\>db2 refresh table mqt_t1
DB20000I SQL 指令已順利完成。

C:\>db2 terminate
DB20000I TERMINATE 指令已順利完成。


-- 檢查 db2admin 權限(必須要有 DBADM 權限)

C:\>db2 get authorizations

　現行使用者的管理權限

　直接 SYSADM 權限　　　　　　= NO
　直接 SYSCTRL 權限　　　　　　= NO
　直接 SYSMAINT 權限　　　　　= NO
　直接 DBADM 權限　　　　　　　= NO
　‧
　‧
　‧


-- 以最高權限登入, 將 DBADM 權限授與 db2admin (若不授與，則以下複製schema的部份改以擁有 DBADM 權限的 USER 來執行亦可)

C:\>db2 grant dbadm on database to user db2admin
DB20000I SQL 指令已順利完成。


-- 再回到 db2admin user

C:\>db2 get authorizations

　現行使用者的管理權限

　直接 SYSADM 權限　　　　　　= NO
　直接 SYSCTRL 權限　　　　　　= NO
　直接 SYSMAINT 權限　　　　　= NO
　直接 DBADM 權限　　　　　　　= YES
　‧
　‧
　‧

-- 執行 SYSPROC.ADMIN_COPY_SCHEMA 將物件複製至 USER DBA
-- 第三個參數是複製模式,可選 DLL(只有Layout)或 COPY(含資料) 或 COPYNO(CREATE & LOAD)
-- 第四個參數是OWNER,　給定為NULL的話,　只有ADMINISTRATOR有專用權.
--　　 以DBA登入做SELECT　會因為有專用權而發生以下錯誤
--　　　　　SQL0551N "DBA" 沒有在物件 "DBA.T1" 上執行作業 "SELECT" 的必要授權或專用權。
--　　　　　SQLSTATE=42501
-- 最後兩個則為產生錯誤時,　自動會建於TABLESPACE:SYSTOOLSPACE下的 ERRSCHEMA.ERRTAB

C:\>db2 CALL sysproc.admin_copy_schema('DB2ADMIN','DBA','COPY','DBA','USERSPACE1','USERSPACE1','ERRSCHEMA','ERRTAB')

　輸出參數的值
　--------------------------
　參數名稱：ERRORTABSCHEMA
　參數值： -

　參數名稱：ERRORTABNAME
　參數值： -

　傳回狀態 = 0




-- 以 USER DBA 登入檢查建立的物件

C:\>db2 select name,type,creator from sysibm.systables where creator = 'DBA'

NAME　　　　TYPE　　CREATOR
----------　------　-----------
MQT_T1　　　S　　　　DBA
T1　　　　　T　　　　DBA
V_AP　　　　V　　　　DBA
V_T1　　　　V　　　　DBA

　　已選取 4 個記錄。


-- 若必須將整個SCHEMA下的物件做刪除則可執行

C:\>db2 call sysproc.admin_drop_schema('DBA',NULL,'ERRSCHEMA','ERRTAB')

輸出參數的值
--------------------------
參數名稱：ERRORTABSCHEMA
參數值： -

參數名稱：ERRORTAB
參數值： -

傳回狀態 = 0


Equivalent of Oracle's Ref Cursor in DB2

/*
以 DB2 的語法來實現像 Oracle Ref cursor的做法:
　宣告：　　DECLARE cursorname CURSOR [WITH HOLD] WITH RETURN FOR CUR1;
　程式主體：PREPARE CUR1 FROM dynamic string;
　開啟：　　cursor　OPEN cursorname;

My scenario:
1.　建立一個測試 table, 存放日期,年,月,日
2.　運用Session global variable存放年份,做為dynamic string裡的年度條件
3.　DB2 procedure 裡傳回單一resultset及cursor的語法
4.　測試結果
*/
/*　　建立測試table及運用CTE與Recursive SQL新增日期資料　　*/

CREATE TABLE T3
(
　　DT　　　INTEGER,
　　YEAR_N　INTEGER,
　　MONTH_N　INTEGER,
　　DAY_N　　INTEGER
) IN USERSPACE1;

INSERT INTO T3
WITH N(COL1)
AS
(SELECT DATE('2000-01-01') FROM SYSIBM.DUAL
UNION ALL
SELECT COL1 + 1 DAY FROM N
WHERE YEAR(COL1 + 1 DAY) <= 2010
)
SELECT INT(COL1),YEAR(COL1),MONTH(COL1),DAY(COL1) FROM N

/*　　建立session global variable　　*/

CREATE VARIABLE ADMINISTRATROR.YEAR_SET INTEGER

-- 給定值為2009
SET ADMINISTRATROR.YEAR_SET = 2009


/*　　附帶說明：只有相同 session 才看得到 session global variable 設定值　　
　　　測試再另使用db2cmd去看這個year_set
*/

C:\>db2 connect to orion

　　資料庫連線資訊

　資料庫伺服器　　　　　= DB2/NT 9.7.0
　SQL 授權 ID　　　　　　= ADMINIST...
　本端資料庫別名　　　　= ORION

C:\>db2 values administrator.year_set

1
-----------
　　　　　-

　　已選取 1 個記錄。

/*　　建立 procedure sp_test 傳回一個 result　　*/

CREATE PROCEDURE SP_TEST
RESULT SETS 1
LANGUAGE SQL
BEGIN
　　DECLARE SQLCODE INTEGER DEFAULT 0;
　　DECLARE retcode INTEGER DEFAULT 0;
　　DECLARE szSql VARCHAR(128);

　　DECLARE TIMECUR CURSOR WITH HOLD WITH RETURN FOR CUR1;

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

　　SET szSql = 'SELECT DT FROM T3 WHERE YEAR_N = YEAR_SET';

　　PREPARE CUR1 FROM szSql;
　　OPEN TIMECUR;
END

/*　　測試(需傳回所有2009年的日期)　　*/

C:\>db2 call sp_test

　結果集 1
　--------------

　DT
　-----------
　　　20090101
　　　20090102
　　　20090103
　　　20090104
　　　20090105
　　　20090106
　　　20090107
　　　20090108
　　　20090109
　　　20090110
　　　20090111
　　　.
　　　.
　　　.


Show Part of Depenencies with SYSIBM.SYSDEPENDENCIES using DB2 Express-C 9.7

/*
SYSIBM.SYSDEPENDENCIES 可以來找出 Stored Procedure 使用了哪些 Tables.
但遇到 dynamic sql 當然就沒辦法這樣找了.
*/

-- 建立測試 Tables & 資料

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

CREATE TABLE F1
(
　　COL1 INTEGER NOT NULL PRIMARY KEY,
　　COL2 VARCHAR(32)
) IN USERSPACE1;

INSERT INTO A1 VALUES　(10,'測試1'),(10,'測試2'),
　　　　　　　　　　　　　(10,'測試3'),(20,'測試4'),
　　　　　　　　　　　　　(20,'測試5'),(20,'測試6'),
　　　　　　　　　　　　　(30,'測試7'),(30,'測試8'),
　　　　　　　　　　　　　(30,'測試9'),(30,'測試10'),
　　　　　　　　　　　　　(30,'測試11');

INSERT INTO F1 VALUES　(10,'代碼10'),
　　　　　　　　　　　　　(20,'代碼20'),
　　　　　　　　　　　　　(30,'代碼30');


-- 建立測試 Procedure

CREATE OR REPLACE PROCEDURE SP_TEST
SPECIFIC SP_TEST
LANGUAGE SQL
BEGIN
　　　　IF EXISTS (SELECT 1 FROM A1
　　　　　　　　　　WHERE COL1 NOT IN (SELECT COL1 FROM F1)) THEN
　　　　　　　INSERT INTO F1
　　　　　　　SELECT COL1,'代碼' || RTRIM(CHAR(COL1))
　　　　　　　FROM A1
　　　　　　　WHERE COL1 NOT IN (SELECT COL1 FROM F1);
　　　　END IF;
END


-- 檢查關聯性

SELECT T.DNAME,A.BNAME
FROM SYSCAT.PACKAGEDEP A,
　　　(SELECT BNAME,DNAME FROM SYSIBM.SYSDEPENDENCIES
　　　　WHERE DSCHEMA = 'ADMINISTRATOR'
　　　　AND DNAME = 'SP_TEST') T
WHERE BTYPE = 'T' AND PKGNAME = T.BNAME;

DNAME　　　　　　　　　　　　　　　BNAME
------------------------------- -----------------
SP_TEST　　　　　　　　　　　　　　F1
SP_TEST　　　　　　　　　　　　　　A1

　　已選取 2 個記錄。


-- 再建立另一測試 Procedure

CREATE OR REPLACE PROCEDURE SP_TEST2
SPECIFIC SP_TEST2
LANGUAGE SQL
BEGIN
　　DECLARE SZSTR VARCHAR(1024);
　　SET SZSTR = 'INSERT INTO A1 ' ||
　　　　　　　　　'SELECT COL1,''代碼'' ||
　　　　　　　　　RTRIM(CHAR(COL1)) ' ||
　　　　　　　　　'FROM (SELECT MAX(COL1) + 10 COL1 FROM A1) T';

　　EXECUTE IMMEDIATE SZSTR;
END


-- 檢查關聯性，SP_TEST2 並未被 SELECT 出來

SELECT T.DNAME,A.BNAME
FROM SYSCAT.PACKAGEDEP A,
　　　(SELECT BNAME,DNAME FROM SYSIBM.SYSDEPENDENCIES
　　　　WHERE DSCHEMA = 'ADMINISTRATOR'
　　　) T
WHERE BTYPE = 'T' AND PKGNAME = T.BNAME;


DNAME　　　　　　　　　　　　　　　BNAME
------------------------------- -----------------
SP_TEST　　　　　　　　　　　　　　F1
SP_TEST　　　　　　　　　　　　　　A1

　　已選取 2 個記錄。



-- 只好先用 Procedure 的程式內容來查

SELECT ROUTINENAME FROM SYSIBM.SYSROUTINES
WHERE ROUTINESCHEMA = 'ADMINISTRATOR'
AND TEXT LIKE '%A1%'

ROUTINENAME
------------------------------------------------
SP_TEST
SP_TEST2

已選取 2 個記錄。


-- 案子小也就算了，大案子就沒辦法這樣查了....

Avoid from SQL20165N using DB2 9.7 Express-C

/*
想對刪除的資料同時做搬移，但是以下語法一直有錯

INSERT INTO TEST2
SELECT * FROM OLD TABLE
(DELETE FROM TEST WHERE COL1 = 'twn-2');

Server Msg: -20165, State: 428FL, [IBM][CLI Driver][DB2/NT]
SQL20165N 不容許 FROM 子句內的 SQL 資料變更陳述式位在指定它的環境定義中。
SQLSTATE=428FL

但是山不轉，路轉。如果我用 EXPORT/LOAD 呢?
*/
/* 建立測試 Tables & Data */

Create Table TEST
(
　　COL1 CHAR(5),
　　COL2 CHAR(2),
　　COL3 CHAR(12)
) IN USERSPACE1

INSERT INTO TEST VALUES ('kor-1','dv','200706231040');
INSERT INTO TEST VALUES ('kor-1','dv','200706231045');
INSERT INTO TEST VALUES ('kor-1','dv','200706231050');
INSERT INTO TEST VALUES ('kor-1','cu','200706231055');
INSERT INTO TEST VALUES ('kor-1','cu','200706231055');
INSERT INTO TEST VALUES ('kor-1','rv','200706220450');
INSERT INTO TEST VALUES ('kor-1','rv','200706220450');
INSERT INTO TEST VALUES ('kor-1','rv','200706220450');
INSERT INTO TEST VALUES ('kor-1','rv','200706220453');
INSERT INTO TEST VALUES ('twn-2','dv','200706220454');

-- TEST2 存放從 TEST 刪掉的資料
CREATE TABLE TEST2 LIKE TEST IN USERSPACE1;


/* DML都失敗，改測 EXPORT/LOAD */


C:\>db2 declare cur cursor for select * from old table (delete from test where c
ol1 = 'twn-2')
DB20000I SQL 指令已順利完成。

C:\>db2 load from cur of cursor insert into test2
SQL3501W 由於禁止資料庫向前回復， 所以表格常駐的表格空間將不放入備份懸置狀態。

SQL1193I 公用程式正在開始從 SQL 陳述式 " select * from old table (delete from
test where col1 = 'twn-2')" 載入資料。

SQL3500W 公用程式在 "2009-09-30 15:52:19.926580" 時開始 "LOAD" 階段。

SQL3519W 開始載入「一致點」。輸入記錄數 = "0"。

SQL3520W 成功載入「一致點」。

SQL3110N 公用程式已完成處理。自輸入檔讀取第 "1" 列。

SQL3519W 開始載入「一致點」。輸入記錄數 = "1"。

SQL3520W 成功載入「一致點」。

SQL3515W 公用程式已在 "2009-09-30 15:52:20.295444" 時完成 "LOAD" 階段。


已讀取的列數　　　　　　　　= 1
已略過的列數　　　　　　　　= 0
已載入的列數　　　　　　　　= 1
已拒絕的列數　　　　　　　　= 0
已拒絕的列數　　　　　　　　= 0
已確定的列數　　　　　　　　= 1

/* 看一下資料是否 LOAD 到 TEST2 去 */

C:\>db2 select * from test2

COL1　COL2　COL3
----- ---- ------------
twn-2　dv　200706220454

　　已選取 1 個記錄。


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 個記錄。


Performance of Merge statement using DB2 Express-C 9.7

/*
Update 的語法在 Performance tuning 上被我奉為萬靈丹的 Merge
原來也有不能勝出的時候。

My Scenario:
Table t: col1 以日期帶序號為單號。 Ex:20090903001
Table t1: col1 以單號帶項次。　　　Ex:2009090300101
　　　　　　　　　　　　　　　　　　　　2009090300102
　　　　　　　　　　　　　　　　　　　　2009090300103
　　　　　　　　　　　　　　　　　　　　2009090300104
　　　　　　　　　　　　　　　　　　　　2009090300105
join 條件: t.col1 = substr(t1.col1,1,11)，將 t 的其它欄位值更新至 t1
Merge 語法的效能就差到不行，反而是 Update 的效能超快
*/

/*　建立測試 table t 並新增資料　*/

create table t
(
　　col1 char(11),
　　col2 char(8)
) in userspace1;

INSERT INTO T
WITH N (COL0,COL1,COL2) AS (
SELECT 1 COL0,SUBSTR(CHAR(INT(DATE(SYSDATE))),1,8) ||
　　　　　　　　SUBSTR(CHAR_OLD(DECIMAL(1,3,0)),1,3),
　　　　　　　　CHAR(INT(DATE(SYSDATE)))
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT N.COL0 + 1,SUBSTR(CHAR(INT(DATE(SYSDATE))),1,8) ||
　　　　　　　　　　SUBSTR(CHAR_OLD(DECIMAL(N.COL0 + 1,3,0)),1,3),
　　　　　　　　　　N.COL2
FROM N
WHERE N.COL0 + 1 <= 100)
SELECT COL1,COL2 FROM N;


/*　建立測試 table t1 並新增資料　*/

create table t1
(
　　col1 char(13),
　　col2 char(8)
) in userspace1;

INSERT INTO T1(COL1)
WITH N (COL0,COL1,OCOL1) AS (
SELECT 1 COL0,COL1 || SUBSTR(CHAR_OLD(DECIMAL(1,2,0)),1,2),
　　　　　COL1 OCOL1
FROM　　T
WHERE　MOD(INT(RIGHT(COL1,2)),2) = 0
UNION ALL
SELECT N.COL0 + 1,RTRIM(N.OCOL1) ||
　　　　　　　　　　SUBSTR(CHAR_OLD(DECIMAL(N.COL0 + 1,2,0)),1,2),
　　　　　　　　　　N.OCOL1
FROM N
WHERE N.COL0 + 1 <= 5)
SELECT COL1 FROM N ORDER BY COL1;

/*　將 merge 語法存入 merge.sql　*/

merge into t1 using t on t.col1 = substr(t1.col1,1,11)
when matched then
update set t1.col2 = t.col2

/*　將 update 語法存入 update.sql　*/

update t1 set col2 = (select t.col2
　　　　　　　　　　　　　from t where t.col1 = substr(t1.col1,1,11))

/*　執行 db2expln 看 merge 的表現　*/
C:\>db2expln -database ORION -g -stmtfile merge.sql -o merge.log

/*　開啟 merge.log　*/
C:\>notepad merge.log


Statement:
　merge into t1 using t on t.col1 =substr(t1.col1, 1, 11)
　when matched
　then
　update set t1.col2 =t.col2

Section Code Page = 1208
Estimated Cost = 3692.811523
Estimated Cardinality = 240.000000

Access Table Name = ADMINISTRATOR.T ID = 2,9479
|　#Columns = 2
|　Skip Inserted Rows
|　Avoid Locking Committed Data
|　Currently Committed for Cursor Stability
|　May participate in Scan Sharing structures
|　Scan may start anywhere and wrap, for completion
|　Scan can be throttled in scan sharing management
|　Relation Scan
|　|　Prefetch: Eligible
|　Lock Intents
|　|　Table: Intent Share
|　|　Row　: Next Key Share
Left Outer Nested Loop Join
|　Access Table Name = ADMINISTRATOR.T1 ID = 2,9480
|　|　#Columns = 0
|　|　Skip Inserted Rows
|　|　Evaluate Block/Data Predicates Before Locking Committed Row
|　|　May participate in Scan Sharing structures
|　|　Fast scan, for purposes of scan sharing management
|　|　Relation Scan
|　|　|　Prefetch: Eligible
|　|　Isolation Level: Read Stability
|　|　Lock Intents
|　|　|　Table: Intent Exclusive
|　|　|　Row　: Update
|　|　Sargable Predicate(s)
|　|　|　#Predicates = 1

Insert Into Sorted Temp Table ID = t1
|　#Columns = 3
|　#Sort Key Columns = 1
|　|　Key 1: (Ascending)
|　Sortheap Allocation Parameters:
|　|　#Rows　　　= 250.000000
|　|　Row Width = 28
|　Piped
Access Temp Table ID = t1
|　#Columns = 3
|　Relation Scan
|　|　Prefetch: Eligible
Residual Predicate(s)
|　#Predicates = 4
Residual Predicate(s)
|　#Predicates = 1
Establish Row Position
|　Access Table Name = ADMINISTRATOR.T1 ID = 2,9480
Update:　Table Name = ADMINISTRATOR.T1 ID = 2,9480
|　Update Predicate(s)
|　|　#Predicates = 1
End of section

Optimizer Plan:
　　　　　　　　　　　　　　　　　　Rows
　　　　　　　　　　　　　　　　　Operator
　　　　　　　　　　　　　　　　　　(ID)
　　　　　　　　　　　　　　　　　　Cost
　　　　　　　　　　　　　　　　　　240
　　　　　　　　　　　　　　　　　UPDATE
　　　　　　　　　　　　　　　　　　( 2)
　　　　　　　　　　　　　　　　　3692.81
　　　　　　　　　　　　　　　/--/　　　　　\
　　　　　　　　　　　　240　　　　　　　　　250
　　　　　　　　　　　FETCH　　　　　　　　Table:
　　　　　　　　　　　( 3)　　　　　　　　ADMINISTRATOR
　　　　　　　　　　1877.31　　　　　　　　T1
　　　　　　　　　　/　　　\
　　　　　　　　240　　　　250
　　　　　　　FILTER　　　Table:
　　　　　　　( 4)　　　　ADMINISTRATOR
　　　　　　61.6852　　　　T1
　　　　　　　　|
　　　　　　　250
　　　　　　FILTER
　　　　　　( 5)
　　　　　　61.6458
　　　　　　　　|
　　　　　　　250
　　　　　　TBSCAN
　　　　　　( 6)
　　　　　　61.2642
　　　　　　　　|
　　　　　　　250
　　　　　　SORT
　　　　　　( 7)
　　　　　　61.2416
　　　　　　　　|
　　　　　　　250
　　　　　　NLJOIN
　　　　　　( 8)
　　　　　　61.1053
　　　　　/　　　　\---\
　　　100　　　　　　　*
　　　TBSCAN　　　　　　|
　　　( 9)　　　　　　250
　　　15.2041　　　　Table:
　　　　|　　　　　　　ADMINISTRATOR
　　　　100　　　　　　T1
　Table:
　ADMINISTRATOR
　T

/*　執行 db2expln 看 update 的表現　*/
C:\>db2expln -database ORION -g -stmtfile update.sql -o update.log

/*　開啟 update.log　*/
C:\>notepad update.log


Statement:
　update t1 set col2 =
　　　(select t.col2
　　　from t
　　　where t.col1 =substr(t1.col1, 1, 11))

Section Code Page = 1208
Estimated Cost = 1952.518555
Estimated Cardinality = 250.000000

Access Table Name = ADMINISTRATOR.T1 ID = 2,9480
|　#Columns = 1
|　Skip Inserted Rows
|　May participate in Scan Sharing structures
|　Relation Scan
|　|　Prefetch: Eligible
|　Lock Intents
|　|　Table: Intent Exclusive
|　|　Row　: Exclusive

Nested Loop Join
|　Piped Inner
|　Access Table Name = ADMINISTRATOR.T ID = 2,9479
|　|　#Columns = 1
|　|　Skip Inserted Rows
|　|　Avoid Locking Committed Data
|　|　Currently Committed for Cursor Stability
|　|　May participate in Scan Sharing structures
|　|　Scan may start anywhere and wrap, for completion
|　|　Fast scan, for purposes of scan sharing management
|　|　Scan can be throttled in scan sharing management
|　|　Relation Scan
|　|　|　Prefetch: Eligible
|　|　Lock Intents
|　|　|　Table: Intent Share
|　|　|　Row　: Next Key Share
|　|　Sargable Predicate(s)
|　|　|　#Predicates = 1
Update:　Table Name = ADMINISTRATOR.T1 ID = 2,9480
End of section

Optimizer Plan:
　　　　　　　　　　　　　　Rows
　　　　　　　　　　　　　Operator
　　　　　　　　　　　　　　(ID)
　　　　　　　　　　　　　　Cost
　　　　　　　　　　　　　　250
　　　　　　　　　　　　　UPDATE
　　　　　　　　　　　　　　( 2)
　　　　　　　　　　　　　1952.52
　　　　　　　　　　　/--/　　　　\
　　　　　　　　250　　　　　　　　250
　　　　　　　NLJOIN　　　　　　Table:
　　　　　　　　( 3)　　　　　　ADMINISTRATOR
　　　　　　　61.3672　　　　　T1
　　　　　　/　　　　\
　　　　250　　　　　　1
　　　TBSCAN　　　　　TBSCAN
　　　( 4)　　　　　　( 5)
　　　22.8603　　　　15.2357
　　　　|　　　　　　　　|
　　　250　　　　　　　100
　Table:　　　　　　　Table:
　ADMINISTRATOR　　　ADMINISTRATOR
　T1　　　　　　　　　T



Merge　的 Estimated Cost = 3692.811523
Update 的 Estimated Cost = 1952.518555

Limitation of Using Merge in DB2 using DB2 Express-C 9.7

Merge Update 有一定的限制，例如想更新的 Table 是個 Nickname 就不行
My Scenario:
Database1: Taurus 建立 Table b1
Database2: Orion　建立 Table a1
　　　　　　　　　建立 Nickname b1 federated from Taurus

在 Database Orion 對 b1 以 Merge 方式將 a1 的值更新到 b1，則會產生錯誤
SQL0270N 不支援函數 (原因碼 = "67")。 SQLSTATE=42997

67　　請勿在 MERGE 陳述式中，指定暱稱或暱稱的視圖作為目標。


My Solutions:
1. 能以傳統Update的語法做更新
2. 在 Database Taurus 上建立 nickname a1，在 Taurus 對 b1 做 merge 語法更新之。

※ 無法藉由 Wrapper Server 增加參數 DB2_TWO_PHASE_COMMIT = 'Y' 來達到目的。


/* 建立測試環境 */

C:\>db2 connect to orion

資料庫連線資訊

資料庫伺服器 = DB2/NT 9.7.0
SQL 授權 ID = ADMINIST...
本端資料庫別名 = ORION

C:\>db2 create table a1 ( col1 integer,col2 varchar(16)) in userspace1
DB20000I SQL 指令已順利完成。

C:\>db2 insert into a1 values (1,'TESTING'),(2,'PRODUCTION')
DB20000I SQL 指令已順利完成。

C:\>db2 terminate
DB20000I TERMINATE 指令已順利完成。

C:\>db2 connect to taurus

　　資料庫連線資訊

　資料庫伺服器　　　　　　= DB2/NT 9.7.0
　SQL 授權 ID　　　　　　= ADMINIST...
　本端資料庫別名　　　　　= TAURUS

C:\>db2 create table b1 ( col1 integer,col2 varchar(16)) in userspace1
DB20000I SQL 指令已順利完成。

C:\>db2 insert into b1(col1) values (1),(2)
DB20000I SQL 指令已順利完成。

C:\>db2 terminate
DB20000I TERMINATE 指令已順利完成。


/* 建立對應Nickname */

C:\>db2 connect to orion

　　資料庫連線資訊

　資料庫伺服器　　　　　　= DB2/NT 9.7.0
　SQL 授權 ID　　　　　　= ADMINIST...
　本端資料庫別名　　　　　= ORION

C:\>db2 create nickname administrator.b1 for w2taurus.administrator.b1
DB20000I SQL 指令已順利完成。

 
/* 在 Orion Database 上對 Nickname 做 Merge Update */

C:\>db2 merge into b1 using a1 on a1.col1 = b1.col1 when matched then update set
b1.col2 = a1.col2

 
/* 產生錯誤訊息 */

DB21034E 指令被當作 SQL 陳述式處理，因為它不是有效的「指令行處理器」指令。 在 SQL 處理程序期間，它已傳回：
SQL0270N 不支援函數 (原因碼 = "67")。 SQLSTATE=42997

/* 不信邪，想試試 Two phase commit */

C:\>db2 connect to orion

　　資料庫連線資訊

　資料庫伺服器　　　　　　= DB2/NT 9.7.0
　SQL 授權 ID　　　　　　= ADMINIST...
　本端資料庫別名　　　　　= ORION

C:\>db2 alter server w2taurus options (add DB2_TWO_PHASE_COMMIT 'Y')
DB20000I SQL 指令已順利完成。

 
/* 依舊失敗 */

C:\>db2 merge into b1 using a1 on a1.col1 = b1.col1 when matched then update set
b1.col2 = a1.col2

DB21034E 指令被當作 SQL 陳述式處理，因為它不是有效的「指令行處理器」指令。 在SQL 處理程序期間，它已傳回：
SQL0270N 不支援函數 (原因碼 = "67")。 SQLSTATE=42997

 
/* 再建在 Taurus 上試試 */

C:\>db2 connect to taurus

　　資料庫連線資訊

　資料庫伺服器　　　　　　= DB2/NT 9.7.0
　SQL 授權 ID　　　　　　= ADMINIST...
　本端資料庫別名　　　　　= TAURUS

C:\>db2 alter server w2orion options(add DB2_TWO_PHASE_COMMIT 'Y')
DB20000I SQL 指令已順利完成。


/* 回到 Database Orion 再試 */

C:\>db2 connect to orion

　　資料庫連線資訊

　資料庫伺服器　　　　　　= DB2/NT 9.7.0
　SQL 授權 ID　　　　　　= ADMINIST...
　本端資料庫別名　　　　　= ORION

C:\>db2 merge into b1 using a1 on a1.col1 = b1.col1 when matched then update set
b1.col2 = a1.col2
DB21034E 指令被當作 SQL 陳述式處理，因為它不是有效的「指令行處理器」指令。 在SQL 處理程序期間，它已傳回：
SQL0270N 不支援函數 (原因碼 = "67")。 SQLSTATE=42997


Char_Old function (Leading zeroes and a trailing decimal characters) - DB2 Express-C 9.7

/*
　　Char function 是我在 DB2 8.2 算是很喜歡的 function
　　一直到今天，我用 DB2 Express-C 9.7 來做資料分析時，
　　才發現它已經沒有原來我想用來補零的功能...
　　要改用 char_old function
*/



C:\>db2 select decimal(200,5,0) from sysibm.sysdummy1

1
-------
　　200.

　　已選取 1 個記錄。

 

/* 掐頭去尾的 char function */

C:\>db2 select char(decimal(200,5,0)) from sysibm.sysdummy1

1
-------
200

　　已選取 1 個記錄。


/* 使用 char_old */

C:\>db2 select char_old(decimal(200,5,0)) from sysibm.sysdummy1

1
-------
00200.

　　已選取 1 個記錄。



Meaningful numeric expression using DB2 9

/*
　　在 db2 forum 看到一個問題：
　　提問的人想要將 2.00000000000000E+010 轉成 readable、
　　meaningful 2x10^10。
　　And here is my recursive sql
　　※用 DB2 9 的 New Datatype Decfloat，
　　　在 decimal 型態轉 char 很方便
*/

/* using Recursive SQL，一直除10，除到 >= 1 */

WITH N(ORGVAL,RESULTVAL,DIVVAL,PLUS)
AS
(SELECT ORGVAL,ORGVAL / DIVVAL RESULTVAL,DIVVAL,1 PLUS
　FROM　(SELECT　DECFLOAT(2.00000000000000E+010) ORGVAL,
　　　　　　　　　10 DIVVAL
　　　　　FROM　　SYSIBM.SYSDUMMY1) T
　UNION　ALL
　SELECT　N.ORGVAL,N.RESULTVAL / 10,DIVVAL,N.PLUS + 1
　FROM　　N
　WHERE　N.RESULTVAL / 10 >= 1
)
SELECT　RTRIM(CHAR(RESULTVAL))||'x'||
　　　　　RTRIM(CHAR(DIVVAL))||'^'||
　　　　　RTRIM(CHAR(PLUS))
FROM　　(SELECT RESULTVAL,DIVVAL,PLUS,
　　　　　　　　　ROW_NUMBER() OVER(ORDER BY PLUS DESC) OID
　　　　　FROM N) T1
WHERE　OID = 1;

/* 2.00000000000000E+010的結果 */
2x10^10

/* 測試 0.125040000000000E+010 */

WITH N(ORGVAL,RESULTVAL,DIVVAL,PLUS)
AS
(SELECT ORGVAL,ORGVAL / DIVVAL RESULTVAL,DIVVAL,1 PLUS
　FROM　(SELECT　DECFLOAT(0.125040000000000E+010) ORGVAL,
　　　　　　　　　10 DIVVAL
　　　　　FROM　SYSIBM.SYSDUMMY1) T
　UNION　ALL
　SELECT　N.ORGVAL,N.RESULTVAL / 10,DIVVAL,N.PLUS + 1
　FROM　　N
　WHERE　N.RESULTVAL / 10 >= 1
)
SELECT　RTRIM(CHAR(RESULTVAL))||'x'||
　　　　　RTRIM(CHAR(DIVVAL))||'^'||
　　　　　RTRIM(CHAR(PLUS))
FROM　　(SELECT RESULTVAL,DIVVAL,PLUS,
　　　　　　　　　ROW_NUMBER() OVER(ORDER BY PLUS DESC) OID
　　　　　FROM N) T1
WHERE　　OID = 1;

/* 0.125040000000000E+010的結果 */
1.2504x10^9

/* 測試 1.39080000000000E+010 */

WITH N(ORGVAL,RESULTVAL,DIVVAL,PLUS)
AS
(SELECT ORGVAL,ORGVAL / DIVVAL RESULTVAL,DIVVAL,1 PLUS
　FROM　(SELECT　DECFLOAT(1.39080000000000E+010) ORGVAL,
　　　　　　　　　10 DIVVAL
　　　　　FROM　SYSIBM.SYSDUMMY1) T
　UNION　ALL
　SELECT N.ORGVAL,N.RESULTVAL / 10,DIVVAL,N.PLUS + 1
　FROM　　N
　WHERE　N.RESULTVAL / 10 >= 1
)
SELECT　RTRIM(CHAR(RESULTVAL))||'x'||
　　　　　RTRIM(CHAR(DIVVAL))||'^'||
　　　　　RTRIM(CHAR(PLUS))
FROM　　(SELECT RESULTVAL,DIVVAL,PLUS,
　　　　　　　　　ROW_NUMBER() OVER(ORDER BY PLUS DESC) OID
　　　　　FROM N) T1
WHERE　　OID = 1;

/* 1.39080000000000E+010的結果 */
1.3908x10^10

Few notes of using identity column and identity_val_local function - DB2

/*
　　Note: 最好還是不要使用 Identity column，免得找自己麻煩
　　1) Create Table with identity column
　　2) Return column value via identity_val_local function
　　3) Different between insert statements
　　4) Data movement
　　5) Reset identity value
*/

/*　1) Create Table　*/

CREATE TABLE IDENTITY_LOG
(
　SEQID　INTEGER NOT NULL
　　　　　GENERATED ALWAYS AS IDENTITY
　　　　　(START WITH 1 INCREMENT BY 1, NO CACHE),
　SYSDT　TIMESTAMP
) IN USERSPACE1;

/*　Insert data　*/

C:\>db2 insert into identity_log(sysdt) values (current timestamp)
DB20000I SQL 指令已順利完成。

/*　自動產生 seqid　*/

C:\>db2 select * from identity_log

SEQID　　　　SYSDT
----------- --------------------------
　　　　　　1 2008-12-29-10.52.54.665000

　　已選取 1 個記錄。

/*　2) 使用Function Identity_val_local()取值　*/

C:\>db2 values identity_val_local()

1
---------------------------------
　　　　　　　　　　　　　　　　　　1.

　　已選取 1 個記錄。

/*　Function Identity_val_local()只能在同一個connect下取值　*/

C:\>db2 connect to sample

　　資料庫連線資訊

　資料庫伺服器　　　　　= DB2/NT 9.5.0
　SQL 授權 ID　　　　　= ORION
　本地資料庫別名　　　　= SAMPLE

C:\>db2 values identity_val_local()

1
---------------------------------
　　　　　　　　　　　　　　　　　　-

　　已選取 1 個記錄。

/*　3) insert values 才能搭配 identity_val_local()　*/

C:\>db2 insert into identity_log(sysdt) select current timestamp from sysibm.sys
dummy1
DB20000I SQL 指令已順利完成。


/*　使用 insert select 時identity_val_local()取不到任何值，只保留最後一次產生的值　*/

C:\>db2 values identity_val_local()

1
---------------------------------
　　　　　　　　　　　　　　　　　　1.

　　已選取 1 個記錄。

/*　4) 使用Load時的方法　*/

C:\>db2 select * from identity_log

SEQID　　　　SYSDT
----------- --------------------------
　　　　　　4 2008-12-29-11.01.18.560000
　　　　　　5 2008-12-29-11.01.27.603000

　　已選取 2 個記錄。

C:\>db2 export to identity_log.del of del select * from identity_log
SQL3104N 「匯出」公用程式正在開始將資料匯出至檔案 "identity_log.del"。

SQL3105N 「匯出」公用程式已完成，共匯出 "2" 列。

已匯出的列數：2

/*　清空 Table　*/

C:\>db2 alter table identity_log activate not logged initially with empty table
DB20000I SQL 指令已順利完成。

/*　Modified by identityoverride　*/

C:\>db2 load from identity_log.del of del modified by identityoverride insert in
to identity_log

C:\>db2 select * from identity_log

SEQID　　　　SYSDT
----------- --------------------------
　　　　　　4 2008-12-29-11.01.18.560000
　　　　　　5 2008-12-29-11.01.27.603000

　　已選取 2 個記錄。

/*　5) Reset Identity　再由1開始　*/

C:\>db2 alter table identity_log alter column seqid restart with 1
DB20000I SQL 指令已順利完成。

C:\>db2 insert into identity_log(sysdt) values (current timestamp)
DB20000I SQL 指令已順利完成。

C:\>db2 select * from identity_log

SEQID　　　　SYSDT
----------- --------------------------
　　　　　　1 2008-12-29-11.04.35.062000
　　　　　　4 2008-12-29-11.01.18.560000
　　　　　　5 2008-12-29-11.01.27.603000

　　已選取 1 個記錄。


DB2 UDF using Common Table Expression (CTE) - DB2 9

/*
　　implement CTE,UDF and Recursive SQL in the Scenario:
　　giving the parameters year and quarter and
　　returning the start and end date of the assigned parameters.
　　運用 Recursive SQL 產生指定日曆天組一個 CTE
　　包成一個User Define Function.
　　input paramters: 年,季
　　output table: two columns of 季的起始日,迄日
*/


/* create function */

CREATE FUNCTION UDF_QUARTER2DATE(nYear　　VARCHAR(4),
　　　　　　　　　　　　　　　　　　　　nQuarter INTEGER)
RETURNS TABLE (MINDT　　INTEGER,
　　　　　　　　　MAXDT　　INTEGER)
LANGUAGE SQL
SPECIFIC UDF_QUARTER2DATE
BEGIN ATOMIC
RETURN
WITH N(DT) AS
(
SELECT　DATE(SUBSTR(nYear,1,4) || '-01-01') DT
FROM 　SYSIBM.SYSDUMMY1
UNION　ALL
SELECT　N.DT + 1 DAY
FROM　　N
WHERE　YEAR(N.DT + 1 DAY) <= INT(nYear)
)
SELECT　MIN(INT(DT)),MAX(INT(DT))
FROM　　N
WHERE　QUARTER(DT) = nQuarter;
END

/* call function & return result */

SELECT * FROM TABLE(UDF_QUARTER2DATE('2008',3))

MINDT　　　　MAXDT
----------- ------------
20080701　　20080930


My misconception about inner view - DB2 9

/*
　　常久以來我一直認為將資料先以條件挑出，再包在 inner view 裡，執行效能會比較好。
　　但是用 SQL Explain Tool，發現結果並不是這樣。
*/


/* 建立測試 Tables，relations below:
　　A1
　　└ A2 join on COL1
　　　└ A3 join on COL1,COL3,COL4　　　
*/

CREATE TABLE A1
(
　　COL1　　VARCHAR(64)
) IN USERSPACE1;

CREATE TABLE A2
(
　　COL1　　VARCHAR(64),
　　COL3　　VARCHAR(64),
　　COL4　　INTEGER
) IN USERSPACE1;

CREATE TABLE A3
(
　　COL1　　VARCHAR(64),
　　COL3　　VARCHAR(64),
　　COL4　　INTEGER,
　　COL5　　INTEGER
) IN USERSPACE1;

/* insert 測試資料 */

INSERT INTO A1 SELECT NAME FROM SYSIBM.SYSTABLES;


/* random 產生0或1，來新增測試資料 */

INSERT　INTO A2
SELECT　TBNAME,NAME,INT(RAND()* 2)
FROM　　SYSIBM.SYSCOLUMNS;


/* random 產生 negative number，來新增測試資料 */

INSERT INTO A3
SELECT COL1,COL3,COL4,
　　　　CASE WHEN COL5 < 0 THEN NULL ELSE COL5 END
FROM　(SELECT A2.*,
　　　　　　　　INT(RAND()*10000)-INT(RAND()*10000) COL5
　　　　FROM 　A2) T;

/* 將 inner view 方式所下的 sql 存成 strsql.sql */

SELECT *
FROM A1
INNER JOIN (SELECT *
　　　　　　　FROM A2
　　　　　　　WHERE COL4 = 1) A2 ON A2.COL1 = A1.COL1
INNER JOIN (SELECT *
　　　　　　　FROM A3
　　　　　　　WHERE COL5 IS NULL) A3 ON A3.COL1 = A2.COL1
　　　　　　　　　　　　　　　　　　　　AND A3.COL3 = A2.COL3
　　　　　　　　　　　　　　　　　　　　AND A3.COL4 = A2.COL4;

/* 以 db2expln 測試使用 inner view 的結果 */

C:\>db2expln -database sample -stmtfile strsql.sql -terminator ; -output strsql.
exp -g


/* 截取結果如下 */

Section Code Page = 1208

Estimated Cost = 104.724731
Estimated Cardinality = 6.353128

Access Table Name = ORION.A2 ID = 2,47
|　#Columns = 3
|　Avoid Locking Committed Data
|　Evaluate Block/Data Predicates Before Locking Committed Row
|　Relation Scan
|　|　Prefetch: Eligible
|　Lock Intents
|　|　Table:　Intent Share
|　|　Row　:　Next Key Share
| Sargable Predicate(s)
|　|　#Predicates = 1
|　|　Process Build Table for Hash Join
Hash Join
|　Estimated Build Size: 224000
|　Estimated Probe Size: 104000
|　Access Table Name = ORION.A3 ID = 2,48
|　|　#Columns = 4
|　|　Avoid Locking Committed Data
|　|　Evaluate Block/Data Predicates Before Locking Committed Row
|　|　Relation Scan
|　|　|　Prefetch: Eligible
|　|　Lock Intents
|　|　|　Table:　Intent Share
|　|　|　Row　:　Next Key Share
|　|　Sargable Predicate(s)
|　|　|　#Predicates = 2
|　|　|　Process Probe Table for Hash Join
Hash Join
|　Estimated Build Size: 4000
|　Estimated Probe Size: 20000
|　Bit Filter Size: 800
|　Access Table Name = ORION.A1 ID = 2,36
|　|　#Columns = 1
|　|　Avoid Locking Committed Data
|　|　Relation Scan
|　|　|　Prefetch: Eligible
|　|　Lock Intents
|　|　|　Table: Intent Share
|　|　|　Row : Next Key Share
|　|　Sargable Predicate(s)
|　|　|　Process Probe Table for Hash Join
Return Data to Application
|　#Columns = 8
End of section

Optimizer Plan:

　　　　　　Rows
　　　　　Operator
　　　　　　(ID)
　　　　　　Cost

　　　　　6.35313
　　　　　RETURN
　　　　　(　1)
　　　　　104.725
　　　　　　　|
　　　　　6.35313
　　　　　HSJOIN
　　　　　(　2)
　　　　　104.725
　　　　　/　　　\
　　　466　　　　　5.5624
　　TBSCAN　　　　HSJOIN
　　(　3)　　　　　(　4)
　15.4103　　　　89.2723
　　　|　　　　　/　　　　\
　　466　　　1423.23　　2883
　Table:　　TBSCAN　　　TBSCAN
　ORION　　　(　5)　　　(　6)
　　A1　　　44.9003　　43.5846
　　　　　　　　|　　　　　|
　　　　　　　5544　　　5544
　　　　　　　Table:　　Table:
　　　　　　　ORION　　　ORION
　　　　　　　　A3　　　　A2

/* 將非 inner view 方式所下的 sql 存成 strsql1.sql */

SELECT　*
FROM　　A1,A2,A3
WHERE　　A2.COL1 = A1.COL1
AND　　　A3.COL1 = A2.COL1
AND　　　A3.COL3 = A2.COL3
AND　　　A3.COL4 = A2.COL4
AND　　　A2.COL4 = 1
AND　　　A3.COL5 IS NULL;

/* 以 db2expln 測試非使用 inner view 的結果 */

C:\>db2expln -database sample -stmtfile strsql1.sql -terminator ; -output strsql
1.exp -g

/* 截取結果如下(與使用 inner view 的結果一樣) */

Section Code Page = 1208

Estimated Cost = 104.724731
Estimated Cardinality = 6.353128

Access Table Name = ORION.A2 ID = 2,47
|　#Columns = 3
|　Avoid Locking Committed Data
|　Evaluate Block/Data Predicates Before Locking Committed Row
|　Relation Scan
|　|　Prefetch: Eligible
|　Lock Intents
|　|　Table: Intent Share
|　|　Row : Next Key Share
|　Sargable Predicate(s)
|　|　#Predicates = 1
|　|　Process Build Table for Hash Join
Hash Join
|　Estimated Build Size: 224000
|　Estimated Probe Size: 104000
|　Access Table Name = ORION.A3 ID = 2,48
|　|　#Columns = 4
|　|　Avoid Locking Committed Data
|　|　Evaluate Block/Data Predicates Before Locking Committed Row
|　|　Relation Scan
|　|　|　Prefetch: Eligible
|　|　Lock Intents
|　|　|　Table: Intent Share
|　|　|　Row : Next Key Share
|　| Sargable Predicate(s)
|　|　|　#Predicates = 2
|　|　|　Process Probe Table for Hash Join
Hash Join
|　Estimated Build Size: 4000
|　Estimated Probe Size: 20000
|　Bit Filter Size: 800
|　Access Table Name = ORION.A1 ID = 2,36
|　|　#Columns = 1
|　|　Avoid Locking Committed Data
|　|　Relation Scan
|　|　|　Prefetch: Eligible
|　|　Lock Intents
|　|　|　Table: Intent Share
|　|　|　Row : Next Key Share
|　|　Sargable Predicate(s)
|　|　|　Process Probe Table for Hash Join
Return Data to Application
|　#Columns = 8
End of section

Optimizer Plan:

　　　　　　Rows
　　　　　Operator
　　　　　　(ID)
　　　　　　Cost

　　　　　6.35313
　　　　　RETURN
　　　　　(　1)
　　　　　104.725
　　　　　　　|
　　　　　6.35313
　　　　　HSJOIN
　　　　　(　2)
　　　　　104.725
　　　　/　　　　　\
　　　466　　　　5.5624
　　TBSCAN　　　　HSJOIN
　　(　3)　　　　　(　4)
　　15.4103　　　　89.2723
　　　　|　　　　　/　　　　\
　　　466　　　1423.23　　2883
　　Table:　　TBSCAN　　　TBSCAN
　　ORION　　　(　5)　　　　(　6)
　　　A1　　　44.9003　　　43.5846
　　　　　　　　　|　　　　　　|
　　　　　　　　5544　　　　　5544
　　　　　　　　Table:　　　Table:
　　　　　　　　ORION　　　　ORION
　　　　　　　　　A3　　　　　　A2


LOAD From and using ADMIN_CMD in DB2 9

/*
　　0) create testing table
　　1) Load from
　　　　- 一般 Load and 指定欄位 Load
　　　　- Load from cursor
　　2) Load from and ADMIN_CMD procedure
　　3) get Result using ADMIN_CMD
　　4) Note: load fail
*/

/* 0) create testing table & insert testing data */

CREATE TABLE LOAD_RESULT
(
　　TABLENAME　　　　VARCHAR(32),
　　ROWS_READ　　　　INTEGER,
　　ROWS_SKIPPED　　INTEGER,
　　ROWS_LOADED　　　INTEGER,
　　ROWS_REJECTED　　INTEGER,
　　ROWS_DELETED　　INTEGER,
　　ROWS_COMMITTED　INTEGER
) IN USERSPACE1;

create table ldr
(
　　col1 integer,
　　col2 varchar(32),
　　col3 decimal(19,2)
) in userspace1;

create table ldr2
(
　　col4 varchar(64),
　　col5 bigint,
　　col6 integer
) in userspace1;

create table source_ldr
(
　　col1 integer,
　　col2 varchar(32),
　　col3 decimal(19,2),
　　col4 varchar(64),
　　col5 bigint,
　　col6 integer
) in userspace1

insert into source_ldr values (1,'Red Wine',95,'Taipei City,R.O.C',124500,100);
insert into source_ldr values (2,'Coffee',35,'Columbia',13500,109);
insert into source_ldr values (3,'Apple Juice',80,'NY, U.S.A',19500,150);
insert into source_ldr values (4,'Beer',31,'Paris, France',12900,130);
insert into source_ldr values (5,'Bottle of Water',18,'Barcelona, Spain'19500,15);


/* export */

export to c:\ldr.del of del select * from source_ldr

/* 1) Load from */
/* 一般Load */

Load from c:\ldr.del of del insert into ldr

/* 指定欄位Load */

Load from c:\ldr.del of del method p (4,6) insert into ldr2(col4,col6)

/* Load from cursor */

C:\>db2 declare cur cursor for select * from source_ldr
DB20000I SQL 指令已順利完成。

C:\>db2 load from cur of cursor replace into ldr
SQL3501W 由於禁止資料庫向前回復， 所以表格常駐的表格空間將不放入備份懸置狀態。

SQL1193I　公用程式正在開始從 SQL 陳述式 " select * from source_ldr" 載入資料。
SQL3500W　公用程式在 "2008-11-21 13:28:24.050661" 時開始 "LOAD" 階段。
SQL3519W　開始載入「一致點」。輸入記錄數 = "0"。
SQL3520W　成功載入「一致點」。
SQL3110N　公用程式已完成處理。自輸入檔讀取第 "5" 列。
SQL3519W　開始載入「一致點」。輸入記錄數 = "5"。
SQL3520W　成功載入「一致點」。
SQL3515W　公用程式已在 "2008-11-21 13:28:24.464409" 時完成 "LOAD" 階段。

已讀取的列數　　　　　　　　= 5
已略過的列數　　　　　　　　= 0
已載入的列數　　　　　　　　= 5
已拒絕的列數　　　　　　　　= 0
已拒絕的列數　　　　　　　　= 0
已確定的列數　　　　　　　　= 5

/* 2) Load from and ADMIN_CMD procedure */

C:\>db2 call sysproc.admin_cmd('load from c:\ldr.del of del insert into ldr')

　結果集 1
　--------------
　ROWS_READ　ROWS_SKIPPED　ROWS_LOADED　ROWS_REJECTED　ROWS_DELETED　ROWS_COMMITTED...
　------------　---------------　--------------　----------------　---------------　-----------------
　　　　　5　　　　　　　0　　　　　　　5　　　　　　　　0　　　　　　　0　　　　　　　　　5

　　已選取 1 個記錄。

　傳回狀態 = 0

/* 或者load from cursor(結果同上) */

C:\>db2 call sysproc.admin_cmd('load from (select * from source_ldr) of cursor
insert into ldr')

/* 3) get Result using ADMIN_CMD */

DROP PROCEDURE SP_LOADCURSOR
GO
CREATE PROCEDURE SP_LOADCURSOR
SPECIFIC SP_LOADCURSOR
LANGUAGE SQL
BEGIN
　DECLARE SQLCODE INTEGER DEFAULT 0;
　DECLARE retcode INTEGER DEFAULT 0;
　DECLARE LOC1 RESULT_SET_LOCATOR VARYING;
　DECLARE nRead INT;
　DECLARE nSkip INT;
　DECLARE nLoad INT;
　DECLARE nReject INT;
　DECLARE nDel INT;
　DECLARE nCommit INT;

　BEGIN
　　DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
　　SET RETCODE = SQLCODE;
　　CALL SYSPROC.ADMIN_CMD('LOAD FROM (SELECT * FROM SOURCE_LDR)' ||
　　　　　　　　　　　　　　　'OF CURSOR INSERT INTO LDR');

　　ASSOCIATE RESULT SET LOCATOR (LOC1) WITH PROCEDURE SYSPROC.ADMIN_CMD;
　　ALLOCATE cur1 CURSOR FOR RESULT SET LOC1;

　　OPEN CUR1;
　　FETCH CUR1 INTO nRead,nSkip,nLoad,nReject,nDel,nCommit;
　　　　INSERT INTO LOAD_RESULT
　　　　VALUES ('LDR',nRead,nSkip,nLoad,nReject,nDel,nCommit);
　　CLOSE CUR1;
　　COMMIT;
　END;
　SET RETCODE = 0;
END

/* 執行 */

call SP_LOADCURSOR

/* SELECT LOAD_RESULT 結果 */


/* 4) Load Fail */
/* issue command and press Ctrl-C */

C:\>db2 call sysproc.admin_cmd('load from c:\ldr.del of del replace into ldr')

C:\>db2 select * from ldr

COL1　　　　　COL2　　　　　　　　　　　　　　　　　　　　　COL3
----------------- ----------------------------------------------------------- ---------------------
SQL0668N　　表格 "ORION.LDR" 上不容許作業，原因碼為 "3"。　SQLSTATE=57016


/* 終止load，使table可被使用 */

C:\>db2 call sysproc.admin_cmd('load from c:\ldr.del of del terminate into ldr')


Sorting an alphanumeric column in DB2 9

/*
　　sorting alphanumeric column.
　　say, column COL0 values with below:
　　　　C1
　　　　C2
　　　　C3
　　　　C10
　　after order by COL0,it turns out to be
　　　　C1
　　　　C10
　　　　C2
　　　　C3
　　but it should be
　　　　C1
　　　　C2
　　　　C3
　　　　C10

　　1) sorting alphanumeric column support Multi-Byte character
　　2) sorting alphanumeric column support Single-Byte character
*/

/* Create testing table */

CREATE TABLE TEST
(
　　COL0 VARCHAR(6)
) IN USERSPACE1;

/* insert testing table */

INSERT INTO TEST VALUES ('C1');
INSERT INTO TEST VALUES ('C2');
INSERT INTO TEST VALUES ('C3');
INSERT INTO TEST VALUES ('C4');
INSERT INTO TEST VALUES ('C5');
INSERT INTO TEST VALUES ('C6');
INSERT INTO TEST VALUES ('C7');
INSERT INTO TEST VALUES ('C8');
INSERT INTO TEST VALUES ('C9');
INSERT INTO TEST VALUES ('C10');
INSERT INTO TEST VALUES ('C11');
INSERT INTO TEST VALUES ('C12');
INSERT INTO TEST VALUES ('C13');
INSERT INTO TEST VALUES ('CE1');
INSERT INTO TEST VALUES ('CE2');
INSERT INTO TEST VALUES ('E1');
INSERT INTO TEST VALUES ('B8');
INSERT INTO TEST VALUES ('BE1');
INSERT INTO TEST VALUES ('BE2');
INSERT INTO TEST VALUES ('CE');
INSERT INTO TEST VALUES ('教1');
INSERT INTO TEST VALUES ('教2');
INSERT INTO TEST VALUES ('教10');

/*　1) Multi-Byte character sorting */

WITH N(CID,RID,COL0,LENS,COL1,COL2) AS
(
SELECT　1 CID,RID,COL0,LENS,
　　　　CASE WHEN ASCII(SUBSTRING(COL0,1,1,CODEUNITS32)) NOT BETWEEN 48 AND 57 THEN
　　　　　　　　　　SUBSTRING(COL0,1,1,CODEUNITS32) ELSE '' END COL1,
　　　　CASE WHEN ASCII(SUBSTRING(COL0,1,1,CODEUNITS32)) BETWEEN 48 AND 57 THEN
　　　　　　　　　　SUBSTRING(COL0,1,1,CODEUNITS32) ELSE '' END COL2
FROM
(
SELECT　ROW_NUMBER() OVER() RID,COL0,LENGTH(RTRIM(COL0)) LENS
FROM　　TEST
) T
UNION ALL
SELECT　N.CID + 1,N.RID,N.COL0,N.LENS,
　　　　N.COL1||
　　　　CASE WHEN ASCII(SUBSTRING(COL0,N.CID + 1,1,CODEUNITS32))
　　　　　　　　　　　　NOT BETWEEN 48 AND 57 THEN
　　　　　　　　　　SUBSTRING(COL0,N.CID + 1,1,CODEUNITS32) ELSE '' END COL1,
　　　　N.COL2||
　　　　CASE WHEN ASCII(SUBSTRING(COL0,N.CID + 1,1,CODEUNITS32))
　　　　　　　　　　　　BETWEEN 48 AND 57 THEN
　　　　　　　　　　SUBSTRING(COL0,N.CID + 1,1,CODEUNITS32) ELSE '' END COL2
FROM　　N
WHERE　　N.CID + 1 <= N.LENS
)
SELECT COL0 FROM N
WHERE CID = LENS
ORDER BY COL1,INT(CASE WHEN COL2='' THEN '0' ELSE COL2 END);

/* And the Result */

COL0
----
B8
BE1
BE2
C1
C2
C3
C4
C5
C6
C7
C8
C9
C10
C11
C12
C13
CE
CE1
CE2
E1
教1
教2
教10

/* Truncate testing table */

ALTER TABLE TEST ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE

/* insert testing data */

INSERT INTO TEST VALUES ('C1');
INSERT INTO TEST VALUES ('C2');
INSERT INTO TEST VALUES ('C3');
INSERT INTO TEST VALUES ('C4');
INSERT INTO TEST VALUES ('C5');
INSERT INTO TEST VALUES ('C6');
INSERT INTO TEST VALUES ('C7');
INSERT INTO TEST VALUES ('C8');
INSERT INTO TEST VALUES ('C9');
INSERT INTO TEST VALUES ('C10');
INSERT INTO TEST VALUES ('C11');
INSERT INTO TEST VALUES ('C12');
INSERT INTO TEST VALUES ('C13');
INSERT INTO TEST VALUES ('CE1');
INSERT INTO TEST VALUES ('CE2');
INSERT INTO TEST VALUES ('E1');
INSERT INTO TEST VALUES ('B8');
INSERT INTO TEST VALUES ('BE1');
INSERT INTO TEST VALUES ('BE2');
INSERT INTO TEST VALUES ('CE');

/* 2) Single-Byte character sorting */

SELECT COL0
FROM
(
SELECT　COL0,
　　　　LTRIM(RTRIM(TRANSLATE(COL0,'','0123456789'))) COL1,
　　　　LTRIM(RTRIM(TRANSLATE(UPPER(COL0),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'))) COL2
FROM　　TEST
) TMP
ORDER BY COL1,INT(CASE WHEN COL2='' THEN '0' ELSE COL2 END);


/* And the Result */

COL0
-----
B8
BE1
BE2
C1
C10
C11
C12
C13
C2
C3
C4
C5
C6
C7
C8
C9
CE
CE1
CE2
E1


Grant privileges to Role and Test Dynamic SQL using DB2 9

/*
　　Privilege Grant to Role，再以 Dynamic SQL create objects
　　在 Oracle 上測試的結果：
　　會產生 Oracle insufficient privileges error, ‘ORA-01031.’
　　解決方法是，必須直接 grant 相關的 privileges 給 User
　　而在 DB2 上測試並不會發生這樣的問題
*/

/* 接上篇，給定createin權限給TestingUser */

C:\>db2 connect to sample user db2admin using db2admin

　　資料庫連線資訊

　資料庫伺服器　　　　= DB2/NT 9.5.0
　SQL 授權 ID　　　　= DB2ADMIN
　本地資料庫別名　　　= SAMPLE

/* 讓 TestingUser 可以對 Orion 這個 schema 做 alter,create,drop */

C:\>db2 grant alterin,createin,dropin on schema orion to role role_o
DB20000I SQL 指令已順利完成。

C:\>db2 terminate
DB20000I TERMINATE 指令已順利完成。

C:\>db2 connect to sample user testinguser using testinguser

　　資料庫連線資訊

　資料庫伺服器　　　　= DB2/NT 9.5.0
　SQL 授權 ID　　　　= TESTINGU...
　本地資料庫別名　　　= SAMPLE


/*
　　用TestingUser create procedure ，內容是 dynamic recreate sequence
　　傳入參數 Sequence name
*/

CREATE PROCEDURE SP_CRTSEQ(in SeqName varchar(32))
SPECIFIC SP_CRTSEQ
LANGUAGE SQL
BEGIN
　　DECLARE SQLCODE INTEGER DEFAULT 0;
　　DECLARE RETCODE INTEGER DEFAULT 0;
　　declare szStr varchar(128);
　　DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,
　　　　　　　　　　　　　　　　　　　SQLWARNING,
　　　　　　　　　　　　　　　　　　　NOT FOUND
　　SET RETCODE = SQLCODE;

　　set szStr = 'DROP SEQUENCE ORION.' || Seqname;
　　EXECUTE IMMEDIATE szStr;
　　set szstr = 'create SEQUENCE ORION.' || Seqname ||
　　　　　　　　　' start with 1 increment by 1';
　　EXECUTE IMMEDIATE szstr;
　　COMMIT;
END


/* 執行 */

C:\>db2 call sp_crtseq('seq2')

　　傳回狀態 = 0

C:\>db2 select orion.seq2.nextval from sysibm.sysdummy1

1
-----------
1

　　已選取 1 個記錄。


Get Error Message using Function SYSPROC.SQLERRM in DB2 9.5

/*
　　DB2 9.5 FUNCTION　SYSPROC.SQLERRM
　　傳入 SQLCODE
　　傳回 Error Message
　　ex:
　　　　SELECT SYSPROC.SQLERRM(-402) FROM SYSIBM.SYSDUMMY1
　　傳回
　　　　SQL0402N　The data type of an operand of an arithmetic function or
　　　　operation "" is not numeric.
*/

/* create table for testing */

CREATE TABLE EXCEPTION_TEST
(
　　COL1　INTEGER NOT NULL PRIMARY KEY
) IN USERSPACE1

/* create table for storing log */

CREATE TABLE EXCEPTION_LOG
(
　　SYSDT TIMESTAMP,
　　INPUT_VALUE VARCHAR(50),
　　ERRCODE INTEGER,
　　ERRMSG VARCHAR(1024)
) IN USERSPACE1;

/* create procedure */

CREATE PROCEDURE SP_EXCEPTION(IN iNum INTEGER)
SPECIFIC SP_EXCEPTION
LANGUAGE SQL
BEGIN

　　DECLARE SQLCODE INTEGER DEFAULT 0;
　　DECLARE RETCODE INTEGER DEFAULT 0;

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

　　　　INSERT INTO EXCEPTION_TEST VALUES (iNum);

　　　　IF (RETCODE <> 0) AND (RETCODE <> 100) THEN
　　　　　　INSERT INTO EXCEPTION_LOG
　　　　　　VALUES (CURRENT TIMESTAMP,CHAR(iNum),RETCODE,
　　　　　　　　　　　SYSPROC.SQLERRM(RETCODE));
　　　　END IF;
　　　　COMMIT;
　　END;
END

/* call procedure twice for testing duplicate */

C:\>db2 call sp_exception(1)

　　傳回狀態 = 0

C:\>db2 call sp_exception(1)

　　傳回狀態 = 0

/* check exception_log table */

SELECT * FROM EXCEPTION_LOG


Get exported rows using DB2 9

/*
　　記錄匯出 table 的筆數
　　1) 使用 system stored procedure ADMIN_CMD 來執行 export
　　2) 將 call ADMIN_CMD 包在另一支 procedure 中，
　　　 使用 ASSOCIATE RESULT 去接傳回的 result set 即 ROWS_EXPORTED
*/

/* call ADMIN_CMD to export table orion */

C:\>db2 call sysproc.admin_cmd('export to c:\orion.del of del select * from orion')

　結果集　1
　--------------
　ROWS_EXPORTED　MSG_RETRIEVAL　　　　　MSG_REMOVAL
　-------------- --------------------- -------------------------
　　　　　　　4959 -　　　　　　　　　　　　-

　　已選取 1 個記錄。

　傳回狀態 = 0


/* Create testing table to store ROWS_EXPORTED */

CREATE TABLE EXP_RESULT
(
　　TBNAME VARCHAR(32),
　　EXP_ROWS BIGINT
);


/*
　　Create testing procedure
　　使用 ASSOCIATE LOCATORS 接 call procedure 傳回的 result set(s)
*/

CREATE PROCEDURE SP_TEST
SPECIFIC SP_TEST
LANGUAGE SQL
BEGIN

　　DECLARE SQLCODE INTEGER DEFAULT 0;
　　DECLARE RETCODE INTEGER DEFAULT 0;
　　DECLARE LOC1 RESULT_SET_LOCATOR VARYING;
　　DECLARE nRow INT;

　　BEGIN
　　　　DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
　　　　SET RETCODE = SQLCODE;
　　　　
　　　　CALL SYSPROC.ADMIN_CMD('EXPORT TO C:\ORION.DEL OF DEL SELECT * FROM ORION');

　　　　ASSOCIATE RESULT SET LOCATOR (LOC1) WITH PROCEDURE SYSPROC.ADMIN_CMD;
　　　　ALLOCATE cur1 CURSOR FOR RESULT SET LOC1;

　　　　OPEN CUR1;
　　　　FETCH CUR1 INTO nRow;
　　　　INSERT INTO EXP_RESULT VALUES ('ORION',nRow);
　　　　CLOSE CUR1;
　　　　COMMIT;
　　END;
　　SET RETCODE = 0;
END

/* execute stored procedure */

C:\>db2 call sp_test

　傳回狀態 = 0

/* check target table EXP_RESULT */

C:\>db2 select * from exp_result

TBNAME　　　EXP_ROWS
---------- ---------------
ORION 　　　　　　　　　4959

　　已選取 1 個記錄。

※ 最好 stored procedure: SP_TEST 改寫成 Dynamic SQL 方式

Dynamic SQL and Global Variable using DB2 9

/*
　　Purpose: 使用 Global Variable 提升 Dynamic SQL 的 performance
　　建立兩個不同的 procedures 比較使用或不使用 Global Variable 的效能差異

　　參考: Expert One-on-One　(Thomas Kyte)　
　　　　　topic〔Use Bind Variables〕
*/


/* Connect to ORIONDB */

C:\>db2 connect to oriondb

　　資料庫連線資訊

　資料庫伺服器　　　　　= DB2/NT 9.5.0
　SQL 授權 ID　　　　　= ORION
　本地資料庫別名　　　　= ORIONDB

/* Create Globa Variable */

C:\>db2 create variable orion.keyvalue integer
DB20000I SQL 指令已順利完成。

/* 建立測試 Table Target_tab */

create table target_tab
(
　　col1 integer,
　　col2 varchar(64),
　　col3 varchar(64)
) in userspace1

/* 建立測試 Table Source_tab */

create table source_tab
(
　　col1 integer,
　　col2 varchar(64),
　　col3 varchar(64)
) in userspace1

/* 新增測試資料 */

insert into source_tab
select row_number() over(),tbname,name
from sysibm.syscolumns;

insert into source_tab
select (select max(col1) + 1 from source_tab),
　　　　tbname,name
from 　sysibm.syscolumns;

insert into source_tab
select (select max(col1) + 1 from source_tab),
　　　　tbname,name
from 　sysibm.syscolumns;

insert into source_tab
select (select max(col1) + 1 from source_tab),
　　　　tbname,name
from 　sysibm.syscolumns;

select count(*) from source_tab

1
-----------
　　　19804

　　已選取 1 個記錄。


/*
　　Create Procedure sp_testvariable (使用 Global Variable:KeyValue)
*/


CREATE PROCEDURE SP_TESTVARIABLE
SPECIFIC SP_TESTVARIABLE
LANGUAGE SQL
BEGIN
　　　　DECLARE n INTEGER;
　　　　DECLARE i INTEGER;
　　　　DECLARE strSql VARCHAR(128);

　　　　SELECT　COUNT(*) INTO n
　　　　FROM　　SOURCE_TAB;

　　　　SET i = 1;
　　　　WHILE i <= n DO
　　　　　　　　-- 使用剛才建立的 Global Variable
　　　　　　　　SET KeyValue = i;
　　　　　　　　SET strSql = 'INSERT INTO TARGET_TAB ' ||
　　　　　　　　　　　　　　　'SELECT * FROM SOURCE_TAB ' ||
　　　　　　　　　　　　　　　'WHERE COL1 = KeyValue';
　　　　　　　　EXECUTE IMMEDIATE strSqL;
　　　　　　　　SET i = i + 1;
　　　　END WHILE;
END


/*
　　Create Procedure sp_testnovariable (不使用 Global Variable)
*/


CREATE PROCEDURE SP_TESTNOVARIABLE
SPECIFIC SP_TESTNOVARIABLE
LANGUAGE SQL
BEGIN
　　　　DECLARE n INTEGER;
　　　　DECLARE i INTEGER;
　　　　DECLARE strSql VARCHAR(128);

　　　　SELECT　COUNT(*) INTO n
　　　　FROM　　SOURCE_TAB;

　　　　SET i = 1;
　　　　WHILE i <= n DO
　　　　　　　　SET strSql = 'INSERT INTO TARGET_TAB ' ||
　　　　　　　　　　　　　　　'SELECT * FROM SOURCE_TAB ' ||
　　　　　　　　　　　　　　　'WHERE COL1 = ' || CHAR(i);
　　　　　　　　EXECUTE IMMEDIATE strSqL;
　　　　　　　　SET i = i + 1;
　　　　END WHILE;
END

/* 編寫 CLP Script: test.sql 內容如下 */

alter table target_tab activate not logged initially with empty table;

values current timestamp;

call sp_testvariable;

values current timestamp;

/* 編寫 CLP Script: testno.sql 內容如下 */

alter table target_tab activate not logged initially with empty table;

values current timestamp;

call sp_testnovariable;

values current timestamp;

/* 執行 test.sql (使用Global Variable) */

C:\>db2 -tf test.sql
DB20000I SQL 指令已順利完成。

1
--------------------------
2008-10-01-14.12.39.334000

　　已選取 1 個記錄。

　傳回狀態 = 0

1
--------------------------
2008-10-01-14.13.55.324000

　　已選取 1 個記錄。

C:\>db2 select count(*) from target_tab

1
-----------
　　　19804

　　已選取 1 個記錄。

/* 計算執行時間(秒) */

SELECT TIMESTAMPDIFF(2,CHAR(TIMESTAMP('2008-10-01-14.13.55.324000')-
　　　　　　　　　　　　　　　　　TIMESTAMP('2008-10-01-14.12.39.334000'))) SEC
FROM SYSIBM.SYSDUMMY1

SEC
-------------
75
　　已選取 1 個記錄。

/* 執行 testno.sql(不使用Global Variable) */

C:\>db2 -tf testno.sql
DB20000I SQL 指令已順利完成。

1
--------------------------
2008-10-01-14.14.29.964000

　　已選取 1 個記錄。

　傳回狀態 = 0

1
--------------------------
2008-10-01-14.16.50.446000

　　已選取 1 個記錄。

/* 計算執行時間(秒) */

SELECT TIMESTAMPDIFF(2,CHAR(TIMESTAMP('2008-10-01-14.16.50.446000')-
　　　　　　　　　　　　　　　　　TIMESTAMP('2008-10-01-14.14.29.964000'))) SEC
FROM SYSIBM.SYSDUMMY1

SEC
--------------
140

已選取 1 個記錄。

/* 不使用Global Variable 的 Dynamic SQL 執行效率慢了將近一倍的時間 */

Get numbers from Multi or Single-byte characters using DB2 9

/*
　　Purpose:　將數字(全形或半行)從字串中篩選出來。
　　Using DB2 functions:　HEX、ASCII、SUBSTRING、CHAR_LENGTH
　　And Recursive SQL
*/

/*
　　利用 Recursive SQL 的特性將字元一個一個拆開
　　再運用 HEX 及 ASCII 判斷字元是否為數字，是則取出，否則轉空白
　　同樣利用 Recursive SQL 的特性將取出的數字 pipe 起來
*/


WITH N(X,TXT,T1,LENS) AS
(
SELECT 1 X,TXT,
　　　　　CASE WHEN HEX(SUBSTRING(TXT,1,1,CODEUNITS32)) BETWEEN 'EFBC90'
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　AND 'EFBC99' THEN
　　　　　　　　　　　RIGHT(HEX(SUBSTRING(TXT,1,1,CODEUNITS32)),1)
　　　　　　　　WHEN ASCII(SUBSTRING(TXT,1,1,CODEUNITS32)) BETWEEN 48
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　AND 57 THEN
　　　　　　　　　　　SUBSTRING(TXT,1,1,CODEUNITS32)
　　　　　ELSE '' END T1,CHAR_LENGTH(TXT,CODEUNITS32) LENS
FROM　　(SELECT '文０９７字０１０１－123' TXT FROM SYSIBM.SYSDUMMY1) T
UNION　　ALL
SELECT　N.X + 1,TXT,T1 ||
　　　　CASE WHEN HEX(SUBSTRING(TXT,N.X + 1,1,CODEUNITS32)) BETWEEN 'EFBC90'
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　AND 'EFBC99' THEN
　　　　　　　　　　RIGHT(HEX(SUBSTRING(TXT,N.X + 1,1,CODEUNITS32)),1)
　　　　　　　WHEN ASCII(SUBSTRING(TXT,N.X + 1,1,CODEUNITS32)) BETWEEN 48
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　AND 57 THEN
　　　　　　　　　　SUBSTRING(TXT,N.X + 1,1,CODEUNITS32)
　　　　ELSE '' END T1,N.LENS
FROM　　N
WHERE　N.X + 1 <= CHAR_LENGTH(TXT,CODEUNITS32)
)
SELECT * FROM N
ORDER BY TXT,X

/* 選取結果如下 */


※ 如果只要選取結果，則在加上 where 條件，Recursive 的次數 = 字串長度即可
/* 建立測試 Table，測試多筆資料 */

CREATE TABLE ORION_TEXT
(
　　　TXT VARCHAR(60)
) IN USERSPACE1

/* 建立測試資料 */

INSERT INTO ORION_TEXT VALUES ('文０９７字號３８０９２　７')
INSERT INTO ORION_TEXT VALUES ('作廢文０９6字號0083216')
INSERT INTO ORION_TEXT VALUES ('作廢文０９６字號0083216')
INSERT INTO ORION_TEXT VALUES ('')
INSERT INTO ORION_TEXT VALUES ('NO9701－０913８８')


/*
　　將使用 dummy table 的地方改為 ORION_TEXT
　　加入選取條件為 X = LENS
*/

WITH N(X,TXT,T1,LENS) AS
(
SELECT 1 X,TXT,
　　　　　CASE WHEN HEX(SUBSTRING(TXT,1,1,CODEUNITS32)) BETWEEN 'EFBC90'
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　AND 'EFBC99' THEN
　　　　　　　　　　　RIGHT(HEX(SUBSTRING(TXT,1,1,CODEUNITS32)),1)
　　　　　　　　WHEN ASCII(SUBSTRING(TXT,1,1,CODEUNITS32)) BETWEEN 48
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　AND 57 THEN
　　　　　　　　　　　SUBSTRING(TXT,1,1,CODEUNITS32)
　　　　　ELSE '' END T1,CHAR_LENGTH(TXT,CODEUNITS32) LENS
FROM　　ORION_TEXT
UNION　　ALL
SELECT　N.X + 1,TXT,T1 ||
　　　　CASE WHEN HEX(SUBSTRING(TXT,N.X + 1,1,CODEUNITS32)) BETWEEN 'EFBC90'
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　AND 'EFBC99' THEN
　　　　　　　　　　RIGHT(HEX(SUBSTRING(TXT,N.X + 1,1,CODEUNITS32)),1)
　　　　　　　WHEN ASCII(SUBSTRING(TXT,N.X + 1,1,CODEUNITS32)) BETWEEN 48
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　AND 57 THEN
　　　　　　　　　　SUBSTRING(TXT,N.X + 1,1,CODEUNITS32)
　　　　ELSE '' END T1,N.LENS
FROM　　N
WHERE　N.X + 1 <= CHAR_LENGTH(TXT,CODEUNITS32)
)
SELECT TXT,T1 FROM N
WHERE X = LENS
ORDER BY TXT,X

/* 結果如下圖 */

New decimal floating-point data type - DB2 9

/*
　　DB2 9 增加了一個提升 decimal 的資料型態，叫做 decfloat
　　除了增加數值資料運算時的精確性之外，運算的效能據說非常好
　　但是，必須搭配 IBM 的處理器 POWER6，才能感受它的效能
　　由於 decfloat 有遵照 IEEE 754 浮點表示法，
　　在一般處理器上進行運算，資料並不會有問題，
　　只是效能跟 decimal 沒什麼差別
*/


/* 建立測試 table */

create table datatype_test
(
　　tbcreator varchar(16),
　　amt1 decimal(19,2),
　　amt2 decfloat(16),
　　amt3 decfloat(34)
) in userspace1
DB20000I SQL 指令已順利完成。


/*
　　看一下在 system table 裡 table layout
　　decfloat 的欄位跟 float(doulbe) 在 SCALE 上都是給0
　　※　decfloat 的 LENGTH 是 IN BYTES
*/

select name,coltype,length,scale
from sysibm.syscolumns
where tbname = 'DATATYPE_TEST'
order by colno

NAME　　　　　　　COLTYPE　LENGTH SCALE
---------------- -------- ------ ------
TBCREATOR　　　　VARCHAR　　　16　　　0
AMT1　　　　　　　DECIMAL　　　19　　　2
AMT2　　　　　　　DECFLOAT　　　8　　　0
AMT3　　　　　　　DECFLOAT　　16　　　0

　　已選取 4 個記錄。


/* 新增測試資料，金額欄位取亂數 */

insert into datatype_test
select tbcreator,decimal(rand() * 10000000,19,2),
　　　　cast(rand() * 10000000.00 as decfloat(16)),
　　　　cast(rand() * 10000000.00 as decfloat(34))
from sysibm.syscolumns
DB20000I SQL 指令已順利完成。

insert into datatype_test
select tbcreator,decimal(rand() * 10000000,19,2),
　　　　cast(rand() * 10000000.00 as decfloat(16)),
　　　　cast(rand() * 10000000.00 as decfloat(34))
from sysibm.syscolumns
DB20000I SQL 指令已順利完成。

insert into datatype_test
select tbcreator,decimal(rand() * 10000000,19,2),
　　　　cast(rand() * 10000000.00 as decfloat(16)),
　　　　cast(rand() * 10000000.00 as decfloat(34))
from sysibm.syscolumns
DB20000I SQL 指令已順利完成。

/* 檢查總筆數 */

select count(*) from datatype_test

1
-----------
　　　16272

　　已選取 1 個記錄。


/*
　　選兩筆來看看
　　decfloat 雖然在 SCALE 上都是給0，小數點還是有
*/


select * from datatype_test fetch first 2 row only

TBCREATOR　　　　AMT1　　　　　　AMT2　　　　　　　AMT3
--------- 　------------- ------------------　-----------------
DB2EXT　　　　1933042.39　　5635853.144932401　12512.58888515885
DB2EXT　　　　4798730.43　　5850093.081453902　8087405.0111392559

　　已選取 2 個記錄。

/* 做一下reorgchk */

reorgchk update statistics on table orion.datatype_test



/* 使用 sql explain 分別對不同金額欄位做加總 */

C:\>db2expln -database SAMPLE -g -statement "select sum(amt1) from datatype_test" -o c:\stmtfile1.log

C:\>db2expln -database SAMPLE -g -statement "select sum(amt2) from datatype_test" -o c:\stmtfile2.log

C:\>db2expln -database SAMPLE -g -statement "select sum(amt3) from datatype_test" -o c:\stmtfile3.log


/* 發現無論對哪一個欄位運算效能都一樣，所以要搭配IBM POWER6處理器才看得出來 */

Estimated Cost = 138.325012
Estimated Cardinality = 1.000000

Access Table Name = ORION.DATATYPE_TEST ID = 2,33
|　#Columns = 1
|　Avoid Locking Committed Data
|　Relation Scan
|　|　Prefetch: Eligible
|　Lock Intents
|　|　Table: Intent Share
|　|　Row　: Next Key Share
|　Sargable Predicate(s)
|　|　Predicate Aggregation
|　|　|　Column Function(s)
Aggregation Completion
|　Column Function(s)
Return Data to Application
|　#Columns = 1
End of section

Optimizer Plan:
　　　Rows
　　Operator
　　　(ID)
　　　Cost
　　　　
　　　　1
　　RETURN
　　　(　1)
　　138.325
　　　　|
　　　　1
　　　GRPBY
　　　(　2)
　　138.325
　　　　|
　　　16272
　　　TBSCAN
　　　(　3)
　　　136.964
　　　　|
　　　16272
　Table:
　ORION
　DATATYPE_TEST

/* 測試數值欄位 ALTER 成 DECFLOAT */

create table test
(
　　col1 smallint,
　　col2 integer,
　　col3 bigint,
　　col4 decimal(19,2),
　　col5 float,
　　col6 decfloat(16)
) in userspace1
DB20000I SQL 指令已順利完成。

/* SYSTEM TABLE 裡的 TABLE LAYOUT */

select name,coltype,length,scale
from sysibm.syscolumns
where tbname = 'TEST'
order by colno

NAME　　　　　　　COLTYPE　LENGTH SCALE
---------------- -------- ------ ------
COL1　　　　　　　SMALLINT　　　2　　　　0
COL2　　　　　　　INTEGER　　　　4　　　　0
COL3　　　　　　　BIGINT　　　　8　　　　0
COL4　　　　　　　DECIMAL　　　19　　　　2
COL5　　　　　　　DOUBLE　　　　8　　　　0
COL6　　　　　　　DECFLOAT　　　8　　　　0

　　已選取 6 個記錄。

/* ALTER SMALLINT 欄位成 DECFLOAT */

alter table test alter column col1 set data type decfloat(16)
DB20000I SQL 指令已順利完成。

reorg table test
DB20000I REORG 指令已順利完成。

/* ALTER INTEGER 欄位成 DECFLOAT */

alter table test alter column col2 set data type decfloat(16)
DB20000I SQL 指令已順利完成。

reorg table test
DB20000I REORG 指令已順利完成。

/* ALTER BIGINT 欄位成 DECFLOAT(注意長度) */

alter table test alter column col3 set data type decfloat(34)
DB20000I SQL 指令已順利完成。

reorg table test
DB20000I REORG 指令已順利完成。

/* ALTER DECIMAL 欄位成 DECFLOAT(注意長度) */

alter table test alter column col4 set data type decfloat(34)
DB20000I SQL 指令已順利完成。

reorg table test
DB20000I REORG 指令已順利完成。

/* ALTER FLOAT 欄位成 DECFLOAT(注意長度) */

alter table test alter column col5 set data type decfloat(34)
DB20000I SQL 指令已順利完成。

reorg table test
DB20000I REORG 指令已順利完成。

/* ALTER DECFLOAT 長度 */

alter table test alter column col6 set data type decfloat(34)
DB20000I SQL 指令已順利完成。

reorg table test
DB20000I REORG 指令已順利完成。

/* 但是已經ALTER成DECFLOAT的欄位，似乎無法再變更為其它數值欄位 */

alter table test alter column col1 set data type decimal(31)
DB21034E 指令被當作 SQL 陳述式處理，因為他不是有效的「指令行處理器」指令。 在SQL 處理程序期間，他已傳回：
SQL0190N ALTER TABLE "ORION.TEST" 為直欄 "COL1"所指定的屬性與現存直欄不相容。 SQLSTATE=42837

alter table test alter column col1 set data type bigint
DB21034E 指令被當作 SQL 陳述式處理，因為他不是有效的「指令行處理器」指令。 在SQL 處理程序期間，他已傳回：
SQL0190N ALTER TABLE "ORION.TEST" 為直欄 "COL1"所指定的屬性與現存直欄不相容。 SQLSTATE=42837

alter table test alter column col1 set data type float
DB21034E 指令被當作 SQL 陳述式處理，因為他不是有效的「指令行處理器」指令。 在SQL 處理程序期間，他已傳回：
SQL0190N ALTER TABLE "ORION.TEST" 為直欄 "COL1"所指定的屬性與現存直欄不相容。 SQLSTATE=42837