Orion's DB Lab Diary - since 2008: 12月 2008

2008年12月28日星期日

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

2008年12月15日星期一

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

2008年12月8日星期一

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;

2008年12月3日星期三

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

2008年12月1日星期一

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 一個警告。
*/

訂閱：文章 (Atom)

Orion's DB Lab Diary - since 2008

2008年12月28日星期日

Few notes of using identity column and identity_val_local function - DB2

2008年12月15日星期一

Oracle Common Table Expression (CTE) - Oracle10g

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

2008年12月8日星期一

Finding Multi-line Data via DUMP function using Oracle10g

2008年12月3日星期三

My misconception about inner view - DB2 9

2008年12月1日星期一

Add Materialized Query Table into a Fact table in Olap Center

About Myself

網誌存檔

分類

References

Orion's DB Lab Diary - since 2008

2008年12月28日 星期日

Few notes of using identity column and identity_val_local function - DB2

2008年12月15日 星期一

Oracle Common Table Expression (CTE) - Oracle10g

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

2008年12月8日 星期一

Finding Multi-line Data via DUMP function using Oracle10g

2008年12月3日 星期三

My misconception about inner view - DB2 9

2008年12月1日 星期一

Add Materialized Query Table into a Fact table in Olap Center

About Myself

網誌存檔

分類

References

2008年12月28日星期日

2008年12月15日星期一

2008年12月8日星期一

2008年12月3日星期三

2008年12月1日星期一