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


Oracle Common Table Expression (CTE) - Oracle10g

/*
　　DB2 CTE 沒有辦法使用 Set or Select into 將結果設給變數，
　　Oracle CTE 卻可以做到.
　　implement CTE,Recursive SQL in the Scenario:
　　寫一段 PL/SQL, 取得系統日期第三季的起始日及迄日,並將值設給變數
*/

/* PL/SQL 內容如下: */

DECLARE
　　vSTARTDT DATE;
　　vENDDT DATE;
BEGIN
　　WITH TEMP AS
　　(
　　　SELECT　STARTDT + LEVEL COL1
　　　FROM　　(SELECT TO_DATE(EXTRACT(YEAR FROM SYSDATE) ||
　　　　　　　　　　　　　　　　　'0101','YYYYMMDD') STARTDT
　　　　　　　　FROM　　DUAL) A1
　　　CONNECT BY LEVEL <= 365
　　)
　　SELECT　MIN(COL1),MAX(COL1)
　　INTO　　vSTARTDT,vENDDT
　　FROM　　TEMP
　　WHERE　　EXTRACT(YEAR FROM COL1) =
　　　　　　　EXTRACT(YEAR FROM SYSDATE)
　　AND　　　TO_CHAR(COL1,'Q') = 3;

　　DBMS_OUTPUT.PUT_LINE(vSTARTDT);
　　DBMS_OUTPUT.PUT_LINE(vENDDT);
END;
/

/* 執行後傳回結果 */

01-7月 -08
30-9月 -08

已順利完成 PL/SQL 程序.


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


Finding Multi-line Data via DUMP function using Oracle10g

/*
　　從字串型態的欄位找出夾雜換行特殊字元的資料
*/


/* create testing table */

CREATE TABLE TSTRING
(
　　COL0 INTEGER,
　　COL1 VARCHAR2(32)
);

/* create testing data */

INSERT INTO TSTRING VALUES (1,'KX-
123C958');
INSERT INTO TSTRING VALUES (2,'OD-129C' || CHR(10) || '900');
INSERT INTO TSTRING VALUES (3,'OC-125X' || CHR(10) || '9002');
INSERT INTO TSTRING VALUES (4,'OC-127Y' || CHR(10) || '15');
INSERT INTO TSTRING VALUES (5,'LC-124C901');
INSERT INTO TSTRING VALUES (6,'LC-124C902');
INSERT INTO TSTRING VALUES (7,'LC-124C903');
INSERT INTO TSTRING VALUES (8,'
');
INSERT INTO TSTRING VALUES (9,'1
');
INSERT INTO TSTRING VALUES (10,CHR(10));
INSERT INTO TSTRING VALUES (11,'北緯
10.5度');
COMMIT;

/* dump 出來的結果為ascii code，找出夾雜有10的字元 */

SELECT COL0,COL1,COL2,SUBSTR(COL2,INSTR(COL2,':')+1) COL3
FROM
(SELECT COL0,COL1,DUMP(COL1) COL2 FROM TSTRING) T
WHERE SUBSTR(COL2,INSTR(COL2,':')+1) LIKE '%10%';

/* 查詢結果如下: */




/*
　　不過上述語法還是有問題，
　　ex:當 ascii 是 100 及以上的即使沒有換行符號，仍舊會被 select 出來
　　還是直接用 instr 去找，最為保險
　　謝謝熊熊
*/

SELECT * FROM TSTRING WHERE INSTR(COL1,CHR(10)) > 0;


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


Add Materialized Query Table into a Fact table in Olap Center

/*
　　使用Olap Center加入Fact Table時，MQT 是不會出現在選取的 Table 清單之中.
　　但是可以先將MQT alter 成 Regular Table 後加入Cube所使用的Fact　　
*/

/*　Create Testing MQT */

DROP TABLE ORION_MQT
CREATE TABLE ORION_MQT AS
(
　　SELECT * FROM T1
) DATA INITIALLY DEFERRED REFRESH DEFERRED in userspace1;

/* Refresh 資料進去 */

REFRESH TABLE ORION_MQT;

/* Alter 成 regular table */

ALTER TABLE ORION_MQT DROP MATERIALIZED QUERY


/*
　　開啟Olap Center
　　原本已建立一個名為 Orion 的 Cube，現在選取 ORION_MQT 成 FACT TABLE
*/


/* 再將ORION_MQT 還原成Materialized Query Table */

ALTER TABLE ORION_MQT ADD MATERIALIZED QUERY
(
　　SELECT * FROM T1
) DATA INITIALLY DEFERRED REFRESH DEFERRED

SET INTEGRITY FOR ORION_MQT ALL IMMEDIATE UNCHECKED


/*
　　恢復成MQT之後，則可以再進行Refresh。
　　※　 並不會影響使用 Cube 的 Tool。such as QMF 這類交叉分析的 tool。
　　　　只是再開啟 Olap Center 之後，ORION_MQT 被 mark 一個警告。
*/

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


Sorting an alphanumeric column in Oracle10g

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


/* create testing table */

CREATE TABLE TEST
(
　　COL0 CHAR(3)
);

/* 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');
INSERT INTO TEST VALUES ('教');
INSERT INTO TEST VALUES ('教1');
INSERT INTO TEST VALUES ('教2');
INSERT INTO TEST VALUES ('教10');

/* using TRANSLATE Function to separate alphabets and numbers */

SELECT COL0 FROM
(
SELECT COL0,
　　　　TRIM(TRANSLATE(COL0,'1234567890',' ')) COL1,
　　　　TO_NUMBER(COALESCE(TRIM(TRANSLATE(COL0,
　　　　　　　　　　　　　　　　　　　　　　　TRANSLATE(COL0,'1234567890',' '),
　　　　　　　　　　　　　　　　　　　　　　　' ')
　　　　　　　　　　　　　　　),NULL,'0')) COL2
FROM　TEST
) T
ORDER BY COL1,COL2;

/* 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


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


Create Role using DB2 9

/*
　　1) 使用 DB2 9 的新功能建立 ROLE
　　2) 建立測試 user: TestingUser
　　3) grant ROLE to TestingUser
　　4) 由 system table/view 檢視 TestingUser 的權限
*/

/*　1)create role
　　以 db administrator 登錄，將安全管理員角色(secadm)授予User Orion
*/


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

　　資料庫連線資訊

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

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


/* ※ 否則直接以未授予secadm角色的user orion create role 會出現以下錯誤:
DB21034E 指令被當作 SQL 陳述式處理，因為他不是有效的「指令行處理器」指令。 在SQL 處理程序期間，他已傳回：
SQL0552N "ORION" 沒有執行作業 "CREATE ROLE" 的專用權。 SQLSTATE=42502
*/


/* 以orion登錄 */

C:\>db2 connect to sample

　　資料庫連線資訊

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

C:\>db2 create role role_o
DB20000I SQL 指令已順利完成。

C:\>db2 grant all on table department to role role_o
DB20000I SQL 指令已順利完成。


/*　2)建立測試 user: TestingUser
　　　使用「控制台」→「使用者帳戶」建立新的帳戶
　　　db2 不像 oracle
　　　db2 沒有create/drop user、或設定password，完全交由 os 來管理
*/




/*　3) grant ROLE to TestingUser */


C:\>db2 grant role_o to user TestingUser
DB20000I SQL 指令已順利完成。


/*　4) 由 system table/view 檢視 TestingUser 的權限　*/


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

　　資料庫連線資訊

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

C:\>db2 select rolename from syscat.roleauth where grantee = 'TESTINGUSER'

ROLENAME
-----------------
ROLE_O
　　
　　已選取 1 個記錄。

C:\>db2 select privilege,grantable,substr(objectname,1,16), \
db2 (續) => => substr(objectschema,1,16),substr(objecttype,1,10), \
db2 (續) => => substr(grantee,1,16) from sysibmadm.privileges, \
db2 (續) => => syscat.roleauth where authid = 'ROLE_O' \
db2 (續) => => and authid = rolename


PRIVILEGE　　GRANTABLE 3　　　　　 4　　　5　　　　6
----------- --------- ---------- ------ ------- ------------
UPDATE　　　 N　　　　　DEPARTMENT　ORION　TABLE　TESTINGUSER
REFERENCE　 N　　　　　DEPARTMENT　ORION　TABLE　TESTINGUSER
SELECT　　　 N　　　　　DEPARTMENT　ORION　TABLE　TESTINGUSER
INSERT　　　 N　　　　　DEPARTMENT　ORION　TABLE　TESTINGUSER
INDEX　　　　N　　　　　DEPARTMENT　ORION　TABLE　TESTINGUSER
DELETE　　　 N　　　　　DEPARTMENT　ORION　TABLE　TESTINGUSER
ALTER　　　　N　　　　　DEPARTMENT　ORION　TABLE　TESTINGUSER

　　已選取 7 個記錄。


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 方式

Pipe multi records to one column using Oracle10g

/*
　　將多筆資料pipe成一個column
　　使用 Recursive SQL + Self join Table
*/

/* Create testing Table & Data */

CREATE TABLE SAMPLE
(
　　COL1 INTEGER,
　　COL2 CHAR(3)
) IN USERSPACE1

INSERT INTO SAMPLE VALUES (1,'111');
INSERT INTO SAMPLE VALUES (1,'222');
INSERT INTO SAMPLE VALUES (1,'333');
INSERT INTO SAMPLE VALUES (1,'444');

INSERT INTO SAMPLE VALUES (2,'AAA');
INSERT INTO SAMPLE VALUES (2,'BBB');
INSERT INTO SAMPLE VALUES (2,'CCC');

COMMIT;


/*
　　想辦法將資料做成可以 self join 的資料
　　以下面 SQL 為例，self join 時可以用 PRECURSOR 欄位 join 到 RID 欄位
*/


SELECT　COL1,COL2,
　　　　ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) RID,
　　　　ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) - 1 PRECURSOR
FROM　　SAMPLE




/* 使用剛才的 SQL 再搭配 Recursive SQL 將 COL2 PIPE 起來，展開資料如下 */

SELECT COL1,SUBSTR(SYS_CONNECT_BY_PATH(COL2,','),2) HIST
FROM
(
SELECT　COL1,COL2,
　　　　ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) RID,
　　　　ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) - 1 PRECURSOR
FROM　　SAMPLE
) TMP
START WITH RID = 1
CONNECT BY PRIOR RID = PRECURSOR AND PRIOR COL1 = COL1




/*
　　對SQL再做些微修改：
　　1. 增加欄位 CNT 記錄 PIPE 起來的筆數
　　2. 增加WHERE條件，取 RID = CNT 的資料
*/

SELECT COL1,SUBSTR(SYS_CONNECT_BY_PATH(COL2,','),2) HIST
FROM
(
SELECT　COL1,COL2,
　　　　ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) RID,
　　　　ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) - 1 PRECURSOR,
　　　　COUNT(*) OVER(PARTITION BY COL1) CNT
FROM　　SAMPLE
) TMP
WHERE RID = CNT
START WITH RID = 1
CONNECT BY PRIOR RID = PRECURSOR AND PRIOR COL1 = COL1


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 執行效率慢了將近一倍的時間 */

Heterogeneous Services with different characterset using Oracle10g

/*
　　1) connect DB2 using Generic Connectivity
　　　　　- odbc configuration
　　　　　- change parameter HS_FDS_CONNECT_INFO and make .ora file
　　　　　- SQLNet.ora,tnsname.ora,listener.ora
　　　　　- create database link
　　2) query DB2 (big5) testing table
*/

/* 1) connect DB2 using Generic Connectivity */

/*
　　-- 設定 odbc
　　資料庫來源名稱: CDIDB
　　資料庫別名: CDIDB88
*/





/*
　　從 C:\oracle\product\10.2.0\db_1\hs\admin下
　　複製 inithsodbc.ora 命名格式 init + DSN Name + .ora
　　=> initCDIDB88.ora
*/



/*開啟 initCDIDB88.ora　設定參數 HS_FDS_CONNECT_INFO */

HS_FDS_CONNECT_INFO = CDIDB88

/* 開啟 SQLNet.ora 做以下變更 */

SQLNET.AUTHENTICATION_SERVICES= (NTS)

/* 開啟 tnsname.ora 增加以下設定 */

CDIDB88 =
　(DESCRIPTION =
　　(ADDRESS_LIST =
　　　　(ADDRESS = (PROTOCOL = TCP)(HOST = ODDYSEY)(PORT = 1521))
　　　)
　　(CONNECT_DATA =
　　　　(SID = CDIDB88)
　　)
　　(HS = OK)
　)

/* 開啟 listener.ora 增加以下設定 */
　
(SID_DESC =
　　　(PROGRAM = hsodbc)
　　　(ORACLE_HOME = c:\oracle\product\10.2.0\db_1)
　　　(SID_NAME = CDIDB88)
)

/* Listener stop & start */

lsnrctl stop

lsnrctl start

/* connect / as sysdba */

/* create database link */

create database link db2svr
connect to “db2admin”
identified by “db2admin”
using 'CDIDB88';

/* Oracle DB 字元集 */

SQL> select value from nls_database_parameters
2　　where parameter = 'NLS_CHARACTERSET';

VALUE
---------------
AL32UTF8


/* Back to CDIDB88 */

C:\>db2 get db config for cdidb88

　　　　資料庫 cdidb88 的資料庫配置
　
　資料庫配置版次　　　　　　　　　　　= 0x0a00
　資料庫版次　　　　　　　　　　　　　= 0x0a00

　資料庫專屬區　　　　　　　　　　　　= TW
　資料庫字碼頁　　　　　　　　　　　　= 950
　資料庫字碼集　　　　　　　　　　　　= big5
　資料庫國碼/地區碼　　　　　　　　　= 88
　資料庫對照順序　　　　　　　　　　　= UNIQUE
　替代對照順序　　　　　(ALT_COLLATE) =
　‧
　‧
　‧

/* 建立測試 Table & 新增測試資料 */

C:\>db2 create table db2_big5_tab ( col1 varchar(6)) in userspace1
DB20000I SQL 指令已順利完成。

C:\>db2 insert into db2_big5_tab values ('一二三')
DB20000I SQL 指令已順利完成。

/* Back to ORACLE */
/* 透過 Database Link 看 db2 測試table layout */

SQL> desc db2_big5_tab@db2svr

　名稱　　　　　　　　　　空值?　　　　　　　類型
　-------------------- --------------- ----------------
　COL1　　　　　　　　　　　　　　　　　　　VARCHAR2(6)

/* Oracle 在 select 時不受自己 characterset 的影響，資料可完整顯示 */

SQL> select * from db2_big5_tab@db2svr;

COL1
---------
一二三

/* 新增時也可完整新增至 db2 table */

SQL> insert into db2_big5_tab@db2svr values ('四五六');

已建立 1 個資料列.

SQL> commit;

確認完成.

/* Back to DB2 */
/* 從 db2 看測試 table */

C:\>db2 select * from db2_big5_tab

COL1
------
一二三
四五六

　　已選取 2 個記錄。

/* 重建測試 Table */

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

C:\>db2 create table db2_big5_tab (col1 char(2)) in userspace1
DB20000I SQL 指令已順利完成。

C:\>db2 insert into db2_big5_tab values ('一')
DB20000I SQL 指令已順利完成。

/* 重新登錄 Oracle */

SQL> desc db2_big5_tab@db2svr
　
　名稱　　　　　　　　　空值?　　類型
　------------------ ------- ----------------
　COL1　　　　　　　　　　　　　CHAR(2)

/* 看一下內容與長度 */

SQL> select * from db2_big5_tab@db2svr;

COL1
------------
一

SQL> select lengthb(col1) from db2_big5_tab@db2svr;

LENGTHB(COL1)
-------------
　　　　　　　3


Federated UTF-8 and BIG5 databases using DB2 9

/*
　　DATABASES:
　　　　　　　　ORIONDB (codeset UTF-8)
　　　　　　　　BIG5DB (codeset BIG5)
　　TABLES:
　　　　　　　　ORIONDB.UTF8_TAB
　　　　　　　　BIG5DB.BIG5_TAB
　　Create nickname UTF8_TAB on Database BIG5DB for ORIONDB.UTF8_TAB
　　Create nickname BIG5_TAB on Database ORIONDB for BIG5DB.BIG5_TAB
　　Insert multi-byte characters respectively
*/

/* check codeset on ORIONDB */

C:\>db2 get db cfg for oriondb

　　　　資料庫 oriondb 的資料庫配置

　資料庫配置版次　　　　　　　　　　　　　　= 0x0c00
　資料庫版次　　　　　　　　　　　　　　　　= 0x0c00

　資料庫專屬區　　　　　　　　　　　　　　　= TW
　資料庫字碼頁　　　　　　　　　　　　　　　= 1208
　資料庫字碼集　　　　　　　　　　　　　　　= UTF-8
　資料庫國碼/地區碼　　　　　　　　　　　　= 88
　資料庫對照順序　　　　　　　　　　　　　　= IDENTITY
　‧
　‧
　‧

/* Create Database BIG5DB */

create database big5db on 'c:\' using codeset BIG5 TERRITORY TW COLLATE using SYSTEM

C:\>db2 get db cfg for big5db

　　　　資料庫 big5db 的資料庫配置

　資料庫配置版次　　　　　　　　　　　　　　= 0x0c00
　資料庫版次　　　　　　　　　　　　　　　　= 0x0c00

　資料庫專屬區　　　　　　　　　　　　　　　= TW
　資料庫字碼頁　　　　　　　　　　　　　　　= 950
　資料庫字碼集　　　　　　　　　　　　　　　= big5
　資料庫國碼/地區碼　　　　　　　　　　　　= 88
　資料庫對照順序　　　　　　　　　　　　　　= UNIQUE
　‧
　‧
　‧

/* Connect to BIG5DB */

C:\>db2 connect to big5db

　　資料庫連線資訊

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

/* Create Table BIG5_TAB on BIG5DB */

CREATE TABLE BIG5_TAB
(
　　COL1 VARCHAR(10)
) IN USERSPACE1

INSERT INTO BIG5_TAB VALUES ('ＢＩＧ－５')


/*
　　Create Wrapper and Server on database BIG5DB for database ORIONDB
　　to create nickname later.
　　Server name: BIG5DB
*/

/* Connect to ORIONDB */

C:\>db2 connect to oriondb

　　資料庫連線資訊

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

/* Create Table UTF8_TAB on ORIONDB */

CREATE TABLE UTF8_TAB
(
　　COL1 VARCHAR(12)
) IN USERSPACE1

INSERT INTO UTF8_TAB VALUES ('ＵＴＦ８')

/* Create Nickname BIG5_TAB on ORIONDB */

C:\>db2 create nickname orion.big5_tab for big5db.orion.big5_tab
DB20000I SQL 指令已順利完成。


/*
　　Back to BIG5DB
　　Create Nickname UTF8_TAB on BIG5DB
*/


C:\>db2 connect to big5db

　　資料庫連線資訊

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

C:\>db2 create nickname orion.utf8_tab for oriondb.orion.utf8_tab
DB20000I SQL 指令已順利完成。

C:\>db2 select * from utf8_tab

COL1
------------
ＵＴＦ８

　　已選取 1 個記錄。

/* Check Nickname Layout */

C:\>db2 describe table utf8_tab

直欄名稱　綱目　　　資料類型名稱　　長度　　比例　Null
------- --------- ------------ ------ ---- ------
COL1　　SYSIBM　　　VARCHAR　　　　　12　　　0　　是

　　已選取 1 個記錄。

/* Check Data using HEX */

C:\>db2 select hex(col1) from utf8_tab　fetch first 1 row only

1
------------------------
EFBCB5EFBCB4EFBCA6EFBC98

　　已選取 1 個記錄。

/* Check HEX Data which Database BIG5DB use exactly */

C:\>db2 select hex('一') from sysibm.sysdummy1

1
----
A440

　　已選取 1 個記錄。

/* It would be failed as inserting more than four multi-byte characters */

C:\>db2 insert into utf8_tab values ('一二三四五')
DB21034E 指令被當作 SQL 陳述式處理，因為他不是有效的「指令行處理器」指令。 在SQL 處理程序期間，他已傳回：
SQL1822N 從資料來源 "ORIONDB" 接收到的非預期錯誤碼"22001"。相關的文字及記號為 "func="SQLExecute" msg=" SQL0433N 值 "一二三四五"太長。"。 SQLSTATE=560BD

C:\>db2 insert into utf8_tab values ('一二三四')
DB20000I SQL 指令已順利完成。

/* Back to Database ORIONDB */

C:\>db2 connect to oriondb

　　資料庫連線資訊

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


/*
　　two multi-byte characters are failed to select
　　real value is 'ＢＩＧ－５'
*/

C:\>db2 select * from big5_tab

COL1
----------
ＢＩＧ

　　已選取 1 個記錄。

/* Check Nickname Layout */

C:\>db2 describe table big5_tab

直欄名稱　綱目　　　資料類型名稱　　長度　　比例　Null
------- --------- ------------ ------ ---- ------
COL1　　SYSIBM　　　VARCHAR　　　　　10　　　0　　是

　　已選取 1 個記錄。

/* You can insert precise length data, but fail to select completely */

C:\>db2 insert into big5_tab values ('一二三四五')
DB20000I SQL 指令已順利完成。

C:\>db2 select * from big5_tab

COL1
----------
ＢＩＧ
一二三

　　已選取 2 個記錄。

/* Check hex data from big5_tab */

C:\>db2 select hex(col1) from big5_tab fetch first 1 row only

1
------------------------------
A2D0A2D7A2D5A1D0A2B4

已選取 1 個記錄。

/* Check HEX Data which Database ORIONDB use exactly */

C:\>db2 select hex('一') from sysibm.sysdummy1

1
------
E4B880

已選取 1 個記錄。


/*
　　Alter nickname big5_tab (length / 2 * 3)
　　to show whole data
*/

C:\>db2 alter nickname orion.big5_tab alter column col1 local type varchar(15)
DB20000I SQL 指令已順利完成。

C:\>db2 select * from big5_tab

COL1
---------------
ＢＩＧ－５
一二三四五

　　已選取 2 個記錄。


/*
　　after delete record where col1 = '一二三四五'
　　I found something really odd:
　　select with fetch or not will have very different result on hex
*/

C:\>db2 select hex(col1) from big5_tab

1
--------------------
EFBCA2EFBCA9EFBCA720

　　已選取 1 個記錄。

C:\>db2 select hex(col1) from big5_tab fetch first 1 row only

1
--------------------
A2D0A2D7A2D5A1D0A2B4

　　已選取 1 個記錄。

/* And it happened on Multi-Byte Characters */


/*
　　Back to BIG5DB
　　Create Nickname UTF8_TAB2 on BIG5DB
*/


C:\>db2 connect to big5db

　　資料庫連線資訊

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

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

/* insert testing data */
inesrt into big5_tab2 values (1,'odd'),(2,'ＯＤＤ')

/* Connect ORIONDB again */

C:\>db2 connect to oriondb

　　資料庫連線資訊

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

C:\>db2 create nickname orion.big5_tab2 for big5db.orion.big5_tab2
DB20000I SQL 指令已順利完成。

C:\>db2 select col1,col2,hex(col2) from big5_tab2 fetch first 2 row only

COL1　COL2　　3
----- ------ --------------------
　　1　odd　　6F6464
　　2　ＯＤＤ　A2DDA2D2A2D2

　　已選取 2 個記錄。

C:\>db2 select col1,col2,hex(col2) from big5_tab2

COL1　COL2　　3
----　------ --------------------
　　1　odd　　6F6464
　　2　ＯＤＤ　EFBCAFEFBCA4EFBCA4

　　已選取 2 個記錄。


Get numbers from Multi or Single-byte characters using Oracle10g

/*
　　Purpose: eliminate none digital data from strings
　　Using functions: TO_SINGLE_BYTE、ASCII、SYS_CONNECT_BY_PATH、OLAP Function
　　And Recursive SQL: Connect by
*/


/* Marvelous Function: TO_SINGLE_BYTE */

SELECT TO_SINGLE_BYTE('０９７年０１０１＃NO.００１') FROM DUAL

/* Result is */

097年0101#NO.001

/* Chinese characters also turn out to be single-byte */

SELECT SUBSTR(TO_SINGLE_BYTE('０９７年０１０１＃NO.００１'),4,1) FROM DUAL

/* Result is */

年


/*
　　Using Recursive SQL to separate each character
　　Choose number whichever ascii code between 48 and 59
　　Columns (ONO、PRECURSOR) made this inner view a self join-like table
　　Column LENS made of Olap function which counting numbers
*/


SELECT　ROWNUM ONO,DATA,ROWNUM - 1 PRECURSOR,COUNT(*) OVER() LENS
FROM　　(SELECT TXT,
　　　　　　　　(CASE WHEN ASCII(SUBSTR(TXT,LEVEL,1)) BETWEEN 48 AND 59 THEN
　　　　　　　　 　　　SUBSTR(TXT,LEVEL,1) END) DATA
　　　　　FROM　(SELECT TO_SINGLE_BYTE('０９７＃０１－ＮＯ－２９００１３９７６') TXT
　　　　　　　　　FROM DUAL) T
　　　　　CONNECT BY LEVEL <= LENGTH(TXT)
　　　　　) T
WHERE DATA IS NOT NULL


/* Result is */




/*
　　use inner view above to make up numbers
　　SYS_CONNECT_BY_PATH keeps each recursive result of data
*/


SELECT　TXT,REPLACE(SYS_CONNECT_BY_PATH(DATA,' '),' ','') HIST
FROM　　(SELECT TXT,ROWNUM ONO,DATA,ROWNUM - 1 PRECURSOR,
　　　　　　　　　COUNT(*) OVER() LENS
　　　　　FROM　(SELECT TXT,
　　　　　　　　　　　　　(CASE WHEN ASCII(SUBSTR(TXT,LEVEL,1)) BETWEEN 48 AND 59
　　　　　　　　　　　　　　　　　THEN SUBSTR(TXT,LEVEL,1) END) DATA
　　　　　　　　　FROM (SELECT TO_SINGLE_BYTE('０９７＃０１－ＮＯ－２９００１３９７６') TXT
　　　　　　　　　　　　　　FROM DUAL) T
　　　　　　　　　CONNECT BY LEVEL <= LENGTH(TXT)
　　　　　　　　) T WHERE DATA IS NOT NULL
　　　　　)T2
WHERE ONO = LENS
START WITH ONO = 1
CONNECT BY PRIOR ONO = PRECURSOR


/* Result is */

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


"Alter Table" a bit change - DB2 9

/*
　　DB2 8.2 在 ALTER TABLE COLUMN 上的功能很陽春。
　　它只能變更 VARCHAR 欄位的長度，對其他型態的異動是完全無能為力的。

　　DB2 9 雖然有改進，可是卻必須在變更後對 TABLE 做 REORG，
　　(除了 VARCHAR 及 VARGRAPHIC 之外)
　　
　　DB2 9 型態的轉換只要不是型態完全不相關，
　　只要注意長度不要小於原先的長度，大致上都還能做變更
　　SQL Reference Volumne 2 有表列
*/


/* DB2 8.2 只有對 VARCHAR ALTER 時是 okay 的*/

CREATE TABLE TEST
(
　　　COL1 VARCHAR(5),
　　　COL2 CHAR(1)
) IN USERSPACE1;

ALTER TABLE TEST ALTER COLUMN COL1 SET DATA TYPE VARCHAR(10)
DB20000I SQL 指令已順利完成。

ALTER TABLE TEST ALTER COLUMN COL2 SET DATA TYPE CHAR(3)
DB21034E 命令被當作 SQL 陳述式處理， 因為它不是有效的「命令行處理器」命令。在SQL 處理程序期間，它已傳回：
SQL0190N ALTER TABLE "DB2ADMIN.TEST" 為 "COL2" 直欄指定的屬性與現存直欄不相容。 SQLSTATE=42837

/* DB2 9 */
/* create 測試 table */

create table altering_tab
(
　　col1 smallint,
　　col2 char(1),
　　col3 varchar(10),
　　col4 decimal(10,2)
) in userspace1
DB20000I SQL 指令已順利完成。

/* 新增測試資料 */

insert into altering_tab values (1,'A','A123456789',20.6),
　　　　　　　　　　　　　　　　　　(2,'B','B123456789',120.9),
　　　　　　　　　　　　　　　　　　(18,'C','C123456789',99.99),
　　　　　　　　　　　　　　　　　　(200,'G','G123456789',100.65)
DB20000I SQL 指令已順利完成。

/* 將 col1 型態變更為 integer */

alter table altering_tab alter column col1 set data type integer
DB20000I SQL 指令已順利完成。

/* 可以對 table 做 select */

select * from altering_tab

COL1　　　　 COL2 COL3　　　　COL4
----------- ---- ---------- ------------
　　　　　1 A　　A123456789　　　　 20.60
　　　　　2 B　　B123456789　　　　120.90
　　　　 18 C　　C123456789　　　　 99.99
　　　　200 G　　G123456789　　　　100.65

　　已選取 4 個記錄。

/* 但無法做 DML */

insert into altering_tab values (7100,'X','X123456789',999.98)
DB21034E 指令被當作 SQL 陳述式處理，因為他不是有效的「指令行處理器」指令。 在SQL 處理程序期間，他已傳回：
SQL0668N 表格 "ORION.ALTERING_TAB" 上不容許作業，原因碼為 "7"。SQLSTATE=57016

/* 須要做 REORG */

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

/* 之後才能對 TABLE 做 DML */

insert into altering_tab values (7100,'X','X123456789',999.98)
DB20000I SQL 指令已順利完成。

/* 變更 VARCHAR 欄位則無須做 REORG */

alter table altering_tab alter column col3 set data type varchar(16)
DB20000I SQL 指令已順利完成。

insert into altering_tab values (9999,'Y','Y123456789012345',1259.77)
DB20000I SQL 指令已順利完成。

/* 另外須要做 REORG　的還有 DROP 欄位的時候 */

alter table altering_tab drop column col4
DB20000I SQL 指令已順利完成。

insert into altering_tab values (333,'K','K555')
DB21034E 指令被當作 SQL 陳述式處理，因為他不是有效的「指令行處理器」指令。 在SQL 處理程序期間，他已傳回：
SQL0668N 表格 "ORION.ALTERING_TAB" 上不容許作業，原因碼為 "7"。SQLSTATE=57016

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

insert into altering_tab values (333,'K','K555')
DB20000I SQL 指令已順利完成。


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


Partitioned MQT (II) using DB2 9

/*
My scenario:　利用已經做好的 MQT MQT_DAY_LIST，
　　　　　　　　假設現行資料檔 DAY_LIST 有異動
　　　　　　　　MQT_DAY_LIST 應該如何去做 REFRESH
*/

/* 刪除 DAY_LIST 所有大於 2008-09-11 的日期 */

delete from day_list where dt > '2008-09-11'
DB20000I The SQL command completed successfully.

/* MQT_DAY_LIST 還未受影響 */

select count(*) from mqt_day_list

1
-----------
　　　1096

　1 record(s) selected.

/* MQT_DAY_LIST 的 PARTITIONS 如下 */

PARTITIONNAME　　　LOWVALUE　　　　HIGHVALUE
----------------- --------------- -----------------
PART0　　　　　　　'2008-01-01'　　'2008-12-31'
PART2007　　　　　'2007-01-01'　　'2007-12-31'
PART2006　　　　　'2006-01-01'　　'2006-12-31'

　3 record(s) selected.

/* 將 PARTITION PART2007 DETACH 掉 */
alter table mqt_day_list detach partition part2007 into mqt_part2007
SQL3601W The statement caused one or more tables to automatically be placed
in the Set Integrity Pending state. SQLSTATE=01586

/* 檢查完整性同時做REFRESH */

set integrity for mqt_day_list immediate checked
DB20000I The SQL command completed successfully.

/* 將 PARTITION PART2006 DETACH 掉 */

alter table mqt_day_list detach partition part2006 into mqt_part2006
SQL3601W The statement caused one or more tables to automatically be placed
in the Set Integrity Pending state. SQLSTATE=01586

/* 檢查完整性同時做REFRESH */

set integrity for mqt_day_list immediate checked
DB20000I The SQL command completed successfully.

/* MQT_DAY_LIST 還有多少筆數 */

select count(*) from mqt_day_list

1
-----------
　　　　　255

　1 record(s) selected.

/* 把 MQT_DAY_LIST 變更為一般的 Table */

alter table mqt_day_list drop materialized query
DB20000I The SQL command completed successfully.

/* 把才 DETACH 的PARTITION 分別再 ATTACH 回來 */

alter table mqt_day_list attach partition part2007 starting '2007-01-01' ending '2007-12-31' from mqt_part2007
SQL3601W The statement caused one or more tables to automatically be placed in the Set Integrity Pending state. SQLSTATE=01586

alter table mqt_day_list attach partition part2006 starting '2006-01-01' ending '2006-12-31' from mqt_part2006
DB20000I The SQL command completed successfully.

/* 檢查完整性同時做REFRESH */

set integrity for mqt_day_list immediate checked
DB20000I The SQL command completed successfully.

/* 新的DAY_LIST資料進來+2007年度+2006年度資料 */

select count(*) from mqt_day_list

1
-----------
　　　　　985

　1 record(s) selected.

/* 把 MQT_DAY_LIST 變更為MQT Table */

alter table mqt_day_list add materialized query
(select dt from day_list)
data initially deferred refresh deferred
DB20000I The SQL command completed successfully.

/* 由於還未經過 SET INTEGRITY，因此沒辦法做 SELECT */

select count(*) from mqt_day_list

1
-----------
SQL0668N Operation not allowed for reason code "1" on table
"DB2ADMIN.MQT_DAY_LIST". SQLSTATE=57016


/*
　這裡一樣不能做checked，否則 MQT 重新 REFRESH 後，就只剩 DAY_LIST 的資料
　這也是為什麼需要將 MQT 變更為一般的 TABLE 再來做 ATTACH
*/


set integrity for mqt_day_list all immediate unchecked
DB20000I The SQL command completed successfully.

/* 接著就可以檢查筆數是否正確 */

select count(*) from mqt_day_list

1
-----------
　　　　　985

　1 record(s) selected.


Partitioned MQT (I) using DB2 9

/*
My scenario : 先完成一個有歷史資料的 Materialized Query Table，
　　　　　　　　但 MQT create 在只有現行資料的 Table 上，
　　　　　　　　歷史資料用 attach 方式進去。

1) 前置處理(create table & insert data):
　　　　day_list存2008資料,day_2007存2007資料,day_2006存2006資料。
　　　　create partitioned mqt table
　　　　　　　　mqt建立在day_list上。
　　　　how to attach
　　　　　　　　使mqt_day_list有2008資料，也有歷史記錄2007及2006。
*/

/* 1) initiation - create table */
/*　　　使用之前用的day_list */

create table day_list
(
　　dt date
) partition by range(dt)
(starting '2008-01-01' ending '2008-12-31' every 3 months)

/*　　　新增2008日期資料 */

INSERT　INTO DAY_LIST
SELECT　DATE(RTRIM(CHAR(YEAR(CURRENT DATE)))||'-01-01') + N DAY
FROM　(SELECT (ROW_NUMBER() OVER() - 1) N
　　　　FROM SYSIBM.SYSTABLES FETCH FIRST 366 ROW ONLY) D
WHERE　YEAR(DATE(RTRIM(CHAR(YEAR(CURRENT DATE)))||'-01-01')+N DAY) = YEAR(CURRENT DATE);

/*　　　建立 day_2007 */

create table day_2007
(
　　dt date
) in userspace1;

/*　　　新增2007日期資料 */

INSERT INTO DAY_2007
SELECT　DATE(RTRIM(CHAR(2007))||'-01-01') + N DAY
FROM　(SELECT (ROW_NUMBER() OVER() - 1) N
　　　　FROM SYSIBM.SYSTABLES FETCH FIRST 366 ROW ONLY) D
WHERE　YEAR(DATE(RTRIM(CHAR(2007))||'-01-01')+N DAY) = 2007;

/*　　　建立 day_2006 */

create table day_2006
(
　　dt date
) in userspace1;

/*　　　新增2006日期資料 */

INSERT INTO DAY_2006
SELECT　DATE(RTRIM(CHAR(2006))||'-01-01') + N DAY
FROM　(SELECT (ROW_NUMBER() OVER() - 1) N
　　　　FROM SYSIBM.SYSTABLES FETCH FIRST 366 ROW ONLY) D
WHERE　YEAR(DATE(RTRIM(CHAR(2006))||'-01-01')+N DAY) = 2006;


/*　　　CREATE MQT TABLE */

create table mqt_day_list as
(
　　select　dt
　　from　　day_list
)DATA INITIALLY DEFERRED REFRESH deferred
PARTITION BY RANGE(dt)
(STARTING '2008-01-01' ENDING '2008-12-31')
in userspace1;

/*　　　將資料滾進 MQT */

set integrity for mqt_day_list immediate checked

/*　　　或者使用REFRESH TABLE都可以 */

refresh table mqt_day_list

/*　　　檢查一下mqt的partition */

select　datapartitionname,datapartitionid,lowvalue,highvalue
from　sysibm.sysdatapartitions
where　tabname = 'MQT_DAY_LIST'

PARTITIONNAME　　　　　　LOWVALUE　　　　　　HIGHVALUE
---------------------　------------------ ----------------
PART0　　　　　　　　　　'2008-01-01'　　　　'2008-12-31'

　1 record(s) selected.

/*　　　確定資料滾進來 */

select count(*) from mqt_day_list

1
-----------
　　　　　366

　1 record(s) selected.

/*　　　讓 mqt 有 2007 及 2006 歷史資料 */
/*　　　先使 MQT Table 變成普通的 table */

alter table mqt_day_list drop materialized query
DB20000I The SQL command completed successfully.

/*　　　將先前做好的day_2007 attach上來 */

alter table mqt_day_list attach partition part2007 starting '2007-01-01' ending '2007-12-31' from day_2007
SQL3601W The statement caused one or more tables to automatically be placed in the Set Integrity Pending state. SQLSTATE=01586

/*　　　檢查完整性 */

set integrity for mqt_day_list immediate checked
DB20000I The SQL command completed successfully.

/*　　　將先前做好的day_2006 也attach上來 */

alter table mqt_day_list attach partition part2006 starting '2006-01-01' ending '2006-12-31' from day_2006
SQL3601W The statement caused one or more tables to automatically be placed in the Set Integrity Pending state. SQLSTATE=01586

/*　　　檢查完整性 */

set integrity for mqt_day_list immediate checked
DB20000I The SQL command completed successfully.

/*　　　把變成普通 TABLE 的 MQT 再 ALTER 回 MQT Table */

alter table mqt_day_list add materialized query
(select dt from day_list) data initially deferred refresh deferred


/*
　　　這時一定要unchecked
　　　因為一旦checked，mqt 又會從day_list refresh資料進來
*/

C:\>db2 set integrity for mqt_day_list all immediate unchecked
DB20000I The SQL command completed successfully.


/*　　　檢查mqt筆數 */

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

1
-----------
　　　　1096

　1 record(s) selected.