XMLTable transform xml data to structured data using Oracle10g

/*
　　XML型態的資料可借XMLTable轉換成像實體Table一樣的結構
*/


--　利用之前存到AREA 這個XML型態的資料

SQL> select * from area;

SYS_NC_ROWINFO$
-------------------------------------------------------------------------------------
< ?xml version="1.0" encoding="BIG5"? >
< ROWSET >
　< THISROW >
　　< AREAID >1< /AREAID>
　　< AREA_ENAME >Africa< /AREA_ENAME>
　　< AREA_CNAME >非洲< /AREA_CNAME>
　< /THISROW >
　< THISROW >
　　< AREAID >2< /AREAID >
　　< AREA_ENAME >America< /AREA_ENAME >
　　< AREA_CNAME >美洲< /AREA_CNAME >
　< /THISROW >
　< THISROW >
　　< AREAID >3< /AREAID >
　　< AREA_ENAME >Asia< /AREA_ENAME >
　　< AREA_CNAME >亞洲< /AREA_CNAME >
　< /THISROW >
　< THISROW >
　　< AREAID >4< /AREAID >
　　< AREA_ENAME >Europe< /AREA_ENAME >
　　< AREA_CNAME >歐洲< /AREA_CNAME >
　< /THISROW >
　< THISROW >
　　< AREAID >5< /AREAID >
　　< AREA_ENAME >Middle East< /AREA_ENAME >
　　< AREA_CNAME >中東< /AREA_CNAME >
　< /THISROW >
　< THISROW >
　　< AREAID >6< /AREAID >
　　< AREA_ENAME >Oceania< /AREA_ENAME >
　　< AREA_CNAME >大洋洲< /AREA_CNAME >
　< /THISROW >
< /ROWSET >


-- 運用XMLTable的語法如下:
-- 先存成 xml2structure.sql


select　area2.ID,area2.AREA_ENAME,area2.AREA_CNAME
from　　area,
　　　　xmltable('for $i in /ROWSET/THISROW
　　　　　　　　　　return $i'
　　　　PASSING OBJECT_VALUE
　　　　COLUMNS ID　　　　　　VARCHAR(2)　PATH 'AREAID',
　　　　　　　　　AREA_ENAME VARCHAR2(32) PATH 'AREA_ENAME',
　　　　　　　　　AREA_CNAME VARCHAR2(32) PATH 'AREA_CNAME'
)　area2
/

-- 執行 xml2structure.sql

SQL> column id format a2
SQL> column area_ename format a20
SQL> column area_cname format a20

SQL> @c:\xml2structure.sql;

ID　AREA_ENAME　　　　　　　AREA_CNAME
--　--------------------　--------------------
1　　Africa 　　　　　　　　非洲
2　　America 　　　　　　　美洲
3　　Asia 　　　　　　　　　亞洲
4　　Europe 　　　　 　　　歐洲
5　　Middle East　　 　　　中東
6　　Oceania 　　　　　　　大洋洲

已選取 6 個資料列.


Rmdir whatever you like! Damn DB2 UDB8.2

接近中午的時候，我正在用 Remote Console 連到客戶的 Production，因為凌晨的倉儲 ETL 排程由於 Production 機器遇到意外關機所以沒有執行完成。

結果發生一件事，害我心臟差點停掉。

不曉得是哪個不肖的傢伙把一個非常危險的 batch 放在桌面上。

一千多個日子裡也許根本都沒人去點它，好死不死的，今天網路速度很龜、游標也很慢，

本來要點選 Services 的，結果居然就點中了它。

那該死的 batch 是古早前，當這台 Production 還是 Testing 的時候，把當時 Production 的幾個 DB 備過來這台機器用的。

所以裡面不乏幾個 rmdir ......

當然是來不及阻止，DB2\NODE0000\ 下分別有兩個 DB 在使用的路徑，裡面的檔案就這麼被清空了。唉 ～

好在昨天的xcopy排程有成功執行完，不然事情就更麻煩了。

只好把 DB2 所有的 services 全部停掉，再拿昨晚 xcopy 好 DB2\NODE0000\ 下那兩個路徑所有檔案給蓋回來，接著把 DB2 的 services 再起起來。

不是我要說，這 DB2 也未免太隨便了點。

愛怎麼刪‧就怎麼刪！刪‧刪‧刪‧刪‧刪‧刪！

愛怎麼蓋‧就怎麼蓋！蓋‧蓋‧蓋‧蓋‧蓋‧蓋！

建議下次 IBM 就請小S來代言好了。

討厭這種感覺，真是恍如隔世一般。害我都不知道今天是星期幾了。

Create XML from Real Table using Oracle10g

--建立一個實體table

SQL> CREATE TABLE TBLAREA
　2　(
　3　　AREAID INTEGER,
　4　　AREA_ENAME VARCHAR2(32),
　5　　AREA_CNAME VARCHAR2(32)
　6　);

已建立表格.

--借用上一篇diary使用的XML Table及語法新增資料

SQL> INSERT INTO TBLAREA
　2　SELECT extractvalue(xseq.column_value,'/THISROW/AREAID') as AREAID,
　3　　　　　extractvalue(xseq.column_value,'/THISROW/AREA_ENAME') as AREA_ENAME,
　4　　　　　extractvalue(xseq.column_value,'/THISROW/AREA_CNAME') as AREA_CNAME
　5　from AREA d,TABLE(XMLSEQUENCE(extract(value(d),'/ROWSET/THISROW'))) xseq;

已建立 6 個資料列.

SQL> commit;

確認完成.

SQL> SELECT * FROM TBLAREA;

　AREAID　AREA_ENAME　　　　　AREA_CNAME
　------- ----------------- ------------------------------
　　　　1　Africa　　　　　　　非洲
　　　　2　America　　　　　　美洲
　　　　3　Asia　　　　　　　　亞洲
　　　　4　Europe　　　　　　　歐洲
　　　　5　Middle Eas　　　　 中東
　　　　6　Oceania　　　　　　 大洋洲

已選取 6 個資料列.


--運用XMLQuery將TBLAREA實體Table資料讀出來組成xml格式
--再使用dbms_xdb.createresource將xml存入XML DB的repository去


DECLARE
　　　　XMLdoc XMLType;
　　　　res boolean;
BEGIN
　SELECT XMLQuery(
　　'for $j in 1 return (
　　< AREAS > {
　　　for $i in ora:view("TBLAREA")/ROW
　　　return (< ROW >
　　　　　　　　< AREAID >{xs:integer($i/AREAID)}< /AREAID >
　　　　　　　　< AREA_ENAME >{xs:string($i/AREA_ENAME)}< /AREA_ENAME >
　　　　　　　　< AREA_CNAME >{xs:string($i/AREA_CNAME)}< /AREA_CNAME >
　　　　　　　< /ROW >)} < /AREAS >)'
returning content) into XMLdoc
from dual;

res:= DBMS_XDB.CREATERESOURCE('/public/orion/xml/Table2XML.XML',XMLdoc);

commit;

END;
/

--將上述sql存成.sql: table2xml.sql

SQL> @c:\table2xml.sql

已順利完成 PL/SQL 程序.

--開啟Browser看看xml是否進到repository去



--點選Table2XML.XML看內容


SQL> select xmlquery('for $i in fn:doc("/public/orion/xml/Table2XML.XML")
　2　return $i' RETURNING CONTENT) AS RESULT FROM DUAL;

RESULT
-------------------------------------------------------------------------------------
< AREAS >< ROW > < AREAID >1< /AREAID >< AREA_ENAME >Africa< /AREA_ENAME >< AREA_CNAME >非洲< /AREA_CNAME >< /ROW >< ROW >< AREAID >2< /AREAID >< AREA_ENAME >America< /AREA_ENAME >< AREA_CNAME >美洲< /AREA_CNAME >< /ROW >< ROW >< AREAID >3
< /AREAID >< AREA_ENAME >Asia< /AREA_ENAME >< AREA_CNAME >亞洲< /AREA_CNAME >< /ROW >< ROW >< AREAID >4< /AREAID >< AREA_ENAME >Europe< /AREA_ENAME >< AREA_CNAME >歐洲< /AREA_CNAME >< /ROW >< ROW >< AREAID >5< /AREAID >< AREA_ENAME >Middle East< /AREA_ENAME >< AREA_CNAME >中東< /AREA_CNAME >< /ROW >< ROW >< AREAID >6< /AREAID >< AREA_ENAME >Oceania< /AREA_ENAME >< AREA_CNAME >大洋洲 < /AREA_CNAME >< /ROW >< /AREAS >


Oracle XML DB (II) using Oracle10g

/*
　　現在就是要將XML（.xml及.xsd）放到XML DB的repository去.
　　會用到DBMS_XDB及DBMS_XMLSCHEMA這兩個Package
*/

-- 檢查一下我的port number有沒有設定

SQL> SELECT dbms_xdb.gethttpport FROM dual;

GETHTTPPORT
-----------
0

-- 設定為8080

SQL> EXEC dbms_xdb.sethttpport(8080);

PL/SQL procedure successfully completed.

-- Listener叫起來

C:\>lsnrctl start

-- browser 輸入網址http://localhost:8080/
--之前已經使用DBMS_XDB.CREATEFOLDER去建立我自己希望在repository裡所呈現的路徑


/* 大致上是這樣
　　public　
　　　└　orion
　　　　　　├　xml
　　　　　　└　xsd
*/


/*
　※　加進去的檔案名稱是有區分大小寫的
　ex:
　　/public/orion/xml/Area.xml和
　　/public/orion/xml/area.xml是會被認為兩個完全不一樣的檔案
*/


--先加入xsd

SQL> declare
　2　res boolean;
　3　begin
　4　res:=dbms_xdb.createresource('/public/orion/xsd/area.xsd',getdocument('area.xsd'));
　5　end;
　6　/

已順利完成 PL/SQL 程序.

--加進來後，area.xsd出現在browser裡



--註冊xsd前須要權限上的異動
--以dba身份登入


SQL>connect / as sysdba
已連線.
SQL>grant alter session to orion;
順利授權.

--換回原來的USER後，接著去註冊xsd

SQL> begin
　2　dbms_xmlschema.registerschema('http://localhost:8080/public/orion/xsd/area.xsd',getDocument('area.xsd'));
　3　end;
　4　/

已順利完成 PL/SQL 程序.

--註冊後，area.xsd出現在sys/schema下


--這時候在user_objects裡就已經看得到AREA了


SQL> select object_name,object_type from user_objects
　2　where object_name like '%AREA%';

OBJECT_NAME　　　　OBJECT_TYPE
----------------　-------------------------------------------------------------
AREA　　　　　　　　TABLE
AREA$xd　　　　　　TRIGGER
AREA_OT　　　　　　TYPE
AREA_ROW_NTT　　　TYPE
AREA_ROW_OT　　　　TYPE


--加入xml

SQL> declare
　2　res boolean;
　3　begin
　4　res:=dbms_xdb.createresource('/public/orion/xml/area.xml',getDocument('area.xml'));
　5　end;
　6　/

已順利完成 PL/SQL 程序.

--加進來後，area.xml出現在browser裡


--看一下AREA的變化

SQL> select count(*) from area;
　　COUNT(*)
　----------
　　　　　　1

--對AREA做XML的QUERY

SQL> select extractvalue(xseq.column_value,'/THISROW/AREAID') as AREAID,
　2　extractvalue(xseq.column_value,'/THISROW/AREA_ENAME') as AREA_ENAME,
　3　extractvalue(xseq.column_value,'/THISROW/AREA_CNAME') as AREA_CNAME
　4　from AREA d,TABLE(XMLSEQUENCE(EXTRACT(VALUE(d),'/ROWSET/THISROW'))) xseq;

AREAID　　AREA_ENAME　　　　　　　　　AREA_CNAME
　--------　------------------------ -----------------------------
　　　　　1　Africa　　　　　　　　　　　非洲
　　　　　2　America　　　　　　　　　　 美洲
　　　　　3　Asia　　　　　　　　　　　　亞洲
　　　　　4　Europe　　　　　　　　　　 歐洲
　　　　　5　Middle East　　　　　　　　中東
　　　　　6　Oceania　　　　　　　　　　 大洋洲

已選取 6 個資料列.


-- 測試一下是否可以下條件

SQL> select extractvalue(xseq.column_value,'/THISROW/AREA_CNAME')
　2　from AREA d,TABLE(XMLSEQUENCE(EXTRACT(VALUE(d),'/ROWSET/THISROW'))) xseq
　3　where extractvalue(xseq.column_value,'/THISROW/AREAID') = 5;

EXTRACTVALUE(XSEQ.COLUMN_VALUE,'/THISROW/AREA_CNAME')
-----------------------------------------------------
中東


-- 未完，因為還有許多不了解的

Oracle XML DB (I) using Oracle10g

/*
　IBM Database Magazine DB2用「A Surprising Ace in the Hole」來形容XML的performance。
　到底真這麼強嗎? 我不知道。因為我並沒有 DB2 9 可以做測試。
　而Oracle 10g我是有的。Oracle在External Table的效能向來都是很好的，
　所以拿Oracle來操作一次。
　※　IBM的小氣程度才是「A Surprising Ace in the Hole」咧!
*/


-- 前置處理
-- Create Directory

SQL>CREATE OR REPLACE DIRECTORY XMLDIR
　2　AS C:\oracle\product\10.2.0\ora_xml';
SQL>GRANT READ,WRITE ON DIRECTORY XMLDIR TO orion;

--AL32UTF8的NLS_CHARSET_ID是873

SQL>select nls_charset_id('AL32UTF8') from dual;

873

--按照Oracle XML DB (Database Developer's Guide)說的
-- 也建一個GetDocument Function好將指定的文件開啟成為clob讀出來
-- 不過這裡用的是loadclobfromfile


create or replace function getDocument(filename varchar2) return clob
　　　authid current_user is
xbfile bfile;
xclob clob:=' ';
lang_ctx number := DBMS_LOB.default_lang_ctx;
src_offset number := 1;
dst_offset number := 1;
warning number;
begin
　　　　xbfile := bfilename('XMLDIR',filename);
　　　　dbms_lob.fileopen(xbfile,dbms_lob.file_readonly);
　　　　dbms_lob.loadclobfromfile(xclob,xbfile,dbms_lob.getlength(xbfile),src_offset,dst_offset,873,lang_ctx,warning);
　　　　dbms_lob.fileclose(xbfile);
　　　　return xclob;
end;


--準備一個area.xsd file，內容如下:
--※文件裡已將table名稱設定為AREA


< xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xdb="http://xmlns.oracle.com/xdb" xdb:storeVarrayAsTable="true" >
　< xs:element name="ROWSET" type="ROWSET_TYPE" xdb:defaultTable="AREA" / >
　　< xs:complexType name="ROWSET_TYPE" xdb:SQLType="AREA_OT" xdb:maintainDOM="false" >
　　　< xs:sequence >
　　　　< xs:element name="THISROW" type="ROW_TYPE" xdb:SQLCollType="AREA_ROW_NTT" minOccurs="0" maxOccurs="unbounded" xdb:maintainOrder="false"/ >
　　　< /xs:sequence >
　　　< /xs:complexType >
　　　< xs:complexType name="ROW_TYPE" xdb:SQLType="AREA_ROW_OT" xdb:maintainDOM="false" >
　　　　< xs:sequence >
　　　　　< xs:element name="AREAID" type="xs:int" xdb:SQLType="NUMBER"/ >
　　　　　< xs:element name="AREA_ENAME" type="xs:string"/ >
　　　　　< xs:element name="AREA_CNAME" type="xs:string"/ >
　　　　< /xs:sequence >
　　　< /xs:complexType >
< /xs:schema >

--準備一個area.xml file，內容如下:
/*
　　※ area.xml除了encoding要設為UTF-8之外，儲存時須將格式設為UTF-8，
　　　否則 中文字 在browser以及使用loadclobfromfile時會出現亂碼
　　※ http://localhost:8080/public/orion/xsd/area.xsd是
　　　area.xsd將註冊及存放的地方，大小寫是有區分的
*/

< ?xml version="1.0" encoding="UTF-8" ? >
< ROWSET xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://localhost:8080/public/orion/xsd/area.xsd" >
< THISROW >< AREAID >1< /AREAID>< AREA_ENAME >Africa< /AREA_ENAME >< AREA_CNAME >非洲< /AREA_CNAME >< /THISROW >
< THISROW >< AREAID >2< /AREAID >< AREA_ENAME >America< /AREA_ENAME >< AREA_CNAME >美洲< /AREA_CNAME >< /THISROW >
< THISROW >< AREAID >3< /AREAID >< AREA_ENAME >Asia< /AREA_ENAME >< AREA_CNAME >亞洲< /AREA_CNAME >< /THISROW >
< THISROW >< AREAID >4< /AREAID >< AREA_ENAME >Europe< /AREA_ENAME >< AREA_CNAME >歐洲< /AREA_CNAME >< /THISROW >
< THISROW >< AREAID >5< /AREAID >< AREA_ENAME >Middle East< /AREA_ENAME >< AREA_CNAME >中東< /AREA_CNAME >< /THISROW >
< THISROW >< AREAID >6< /AREAID >< AREA_ENAME >Oceania< /AREA_ENAME >< AREA_CNAME >大洋洲< /AREA_CNAME >< /THISROW >
< /ROWSET >


--試用看看

SQL> select XMLTYPE(getDocument('area.xml')) from dual;

XMLTYPE(GETDOCUMENT('AREA1.XML'))
-----------------------------------------------------------------------
< ?xml version="1.0" encoding="BIG5"? >
< ROWSET xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://
localhost:8080/public/orion/xsd/area.xsd" >
< THISROW >
　　< AREAID >1< /AREAID >
　　< AREA_ENAME >Africa< /AREA_ENAME >
　　< AREA_CNAME >非洲< /AREA_CNAME >
< /THISROW >
< THISROW >
　　< AREAID >2< /AREAID >
　　< AREA_ENAME >America< /AREA_ENAME >
　　< AREA_CNAME >美洲< /AREA_CNAME >
< /THISROW >
< THISROW >
　　< AREAID >3< /AREAID >
　　< AREA_ENAME >Asia< /AREA_ENAME >
　　< AREA_CNAME >亞洲< /AREA_CNAME >
< /THISROW >
< THISROW >
　　< AREAID >4< /AREAID >
　　< AREA_ENAME >Europe< /AREA_ENAME >
　　< AREA_CNAME >歐洲< /AREA_CNAME >
< /THISROW >
< THISROW >
　　< AREAID >5< /AREAID >
　　< AREA_ENAME >Middle East< /AREA_ENAME >
　　< AREA_CNAME >中東< /AREA_CNAME >
< /THISROW >
< THISROW >
　　< AREAID >6< /AREAID >
　　< AREA_ENAME >Oceania< /AREA_ENAME >
　　< AREA_CNAME >大洋洲< /AREA_CNAME >
< /THISROW >
< /ROWSET >


--　待續 Oracle XML DB(II)

Recursive SQL and Row_number() to pipe email address using DB2 UDB8.2

-- 選取 TABLE:EBANK_MAIL 裡所有 SUBJ 為 FIN 的 E-Mail address
-- 五個 E-Mails 為一組寄送報表，以下為例:(將 MAILLIST 以五個為一組 pipe起來)


CREATE TABLE EBANK_MAIL
(
　　SUBJ VARCHAR(5),
　　MAILLIST VARCHAR(64)
) IN USERSPACE1;

INSERT INTO EBANK_MAIL VALUES ('FIN','AndyC@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','Chan@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','CLY@EBANK.COM');
INSERT INTO EBANK_MAIL VALUES ('FIN','CPY@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','CFC@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','CLH@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','chengs@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','Chenjl@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','chenlk@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','Chenmj@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','chihc@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','chousc@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','chuangsy@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','chucw@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','DAYCC@EBANK.COM');
INSERT INTO EBANK_MAIL VALUES ('FIN','FUMS@EBANK.COM');
INSERT INTO EBANK_MAIL VALUES ('FIN','GEORGE@EBANK.COM');
INSERT INTO EBANK_MAIL VALUES ('FIN','HOKT@EBANK.COM');
INSERT INTO EBANK_MAIL VALUES ('FIN','Hotc@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','HOWH@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','KKR@EBANK.COM');
INSERT INTO EBANK_MAIL VALUES ('FIN','LDC@ebank.com');
INSERT INTO EBANK_MAIL VALUES ('FIN','MEI@ebank.com');

--使用Recursive Sql與Row_number()

WITH T(COL1,CNT,X,ML) AS
(
SELECT　0 COL1,COUNT(*) CNT,X,CAST('' AS VARCHAR(1000)) ML
FROM　　(SELECT MAILLIST,ROW_NUMBER() OVER() / 5 X
　　　　　FROM EBANK_MAIL
　　　　　WHERE SUBJ = 'FIN') TMP
GROUP　　BY X
UNION　　ALL
SELECT　T.COL1 + 1,T.CNT,PLUS.X,ML|| ',' || RTRIM(PLUS.MAILLIST)
FROM　　(SELECT ROW_NUMBER() OVER(PARTITION BY X) COL1,MAILLIST,X
　　　　　FROM　　(SELECT　MAILLIST,ROW_NUMBER() OVER() / 5 X
　　　　　　　　　　FROM　　EBANK_MAIL
　　　　　　　　　　WHERE　SUBJ = 'FIN') TMP1
　　　　　　　　　　) PLUS,T
WHERE　　PLUS.X = T.X
AND　　　PLUS.COL1 <= T.CNT
AND　　　T.COL1 + 1 = PLUS.COL1
)
SELECT　SUBSTR(ML,2) ML
FROM　　T
WHERE　COL1 = CNT;

--結果如下

ML
----------------------------------------------------------------------------------
AndyC@ebank.com,Chan@ebank.com,CLY@EBANK.COM,CPY@ebank.com
HOWH@ebank.com,KKR@EBANK.COM,LDC@ebank.com,MEI@ebank.com
CFC@ebank.com,CLH@ebank.com,chengs@ebank.com,Chenjl@ebank.com,chenlk@ebank.com
Chenmj@ebank.com,chihc@ebank.com,chousc@ebank.com,chuangsy@ebank.com,chucw@ebank.com
DAYCC@EBANK.COM,FUMS@EBANK.COM,GEORGE@EBANK.COM,HOKT@EBANK.COM,Hotc@ebank.com


SQL0964C Error using DB2 UDB8.2

/*
　　將SRC_CUSTOMER.B　更新至　CUSTOMER的.B
　　MATCH條件:SRC_CUSTOMER.A = CUSTOMER.A

　　將以下MERGE語法存成.sql ex:notlogged.sql
*/

MERGE　　INTO CUSTOMER
USING　　SRC_CUSTOMER ON SRC_CUSTOMER.A = CUSTOMER.A
WHEN　　　MATCHED THEN
UPDATE　　SET CUSTOMER.B = SRC_CUSTOMER.B;

--執行並得到以下錯誤訊息

C:\>db2 -tf notlogged.sql

DB21034E 命令被當作 SQL 陳述式處理，因為它不是有效的「命令行處理器」命令。
在SQL 處理程序期間，它已傳回：
SQL0964C 資料庫的交易日誌已滿。 SQLSTATE=57011

--ALTER TABLE 使其不寫LOG

C:\>DB2 ALTER TABLE CUSTOMER ACTIVATE NOT LOGGED INITIALLY

--再執行一次MERGE仍發生SQL0964C錯誤

--解決方法：
--(1)直接修改db環境變數DB2OPTIONS=+c (取消AUTO-COMMIT)

C:\>DB2SET DB2OPTIONS=+c
C:\>DB2 ALTER TABLE CUSTOMER ACTIVATE NOT LOGGED INITIALLY
C:\>db2 -tf notlogged.sql
C:\>DB2 COMMIT
C:\>DB2SET DB2OPTIONS=-c

--(2)只在執行SQL中取消AUTO-COMMIT
--編輯一下notlogged.sql，內容如下:
-- 將AUTO-COMMIT取消

UPDATE COMMAND OPTIONS USING C OFF;

ALTER TABLE CUSTOMER ACTIVATE NOT LOGGED INITIALLY;

MERGE　　INTO CUSTOMER
USING　　SRC_CUSTOMER ON SRC_CUSTOMER.A = CUSTOMER.A
WHEN　　　MATCHED THEN
UPDATE　　SET CUSTOMER.B = SRC_CUSTOMER.B;

COMMIT;

UPDATE COMMAND OPTIONS USING C ON;

執行notlogged.sql

C:\>db2 -tf notlogged.sql

--※ Procedure 的用法
--Procedure: sp_test，內容如下:

CREATE PROCEDURE SP_TEST
SPECIFIC SP_TEST
LANGUAGE SQL
BEGIN

　　　DECLARE SQLCODE INTEGER DEFAULT 0;
　　　DECLARE retcode INTEGER DEFAULT 0;
　　　DECLARE szSql VARCHAR(128);

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

　　　SET szSql = 'ALTER TABLE CUSTOMER ACTIVATE NOT LOGGED INITIALLY';
　　　EXECUTE IMMEDIATE szSql;

　　　MERGE　　INTO CUSTOMER
　　　USING　　SRC_CUSTOMER ON SRC_CUSTOMER.A = CUSTOMER.A
　　　WHEN　　　MATCHED THEN
　　　UPDATE　　SET CUSTOMER.B = SRC_CUSTOMER.B;

　　　IF (retcode <> 0) AND (retcode <> 100) THEN
　　　　　ROLLBACK;
　　　　　RETURN retcode;
　　　END IF;
　　　COMMIT;
　　　RETURN retcode;
END

--執行procedure

C:\>db2 call sp_test

　　傳回狀態=0


Merge vs Update performance test using DB2 UDB8.2

--建立測試TABLE DS1,DS2

CREATE TABLE DS1
(
　　X　　INTEGER NOT NULL PRIMARY KEY,
　　CNT　INTEGER
) IN USERSPACE1;

CREATE TABLE DS2
(
　　X　INTEGER NOT NULL,
　　Y　INTEGER NOT NULL,
　　PRIMARY KEY (X,Y)
) IN USERSPACE1;

--新增測試資料
--DS1 X:1~100，共100筆資料

INSERT INTO DS1(X)
WITH N(X) AS (
　SELECT　1 X
　FROM　　SYSIBM.SYSDUMMY1
　UNION　　ALL
　SELECT　N.X + 1
　FROM　　N
　WHERE　　N.X + 1 <= 100
)
SELECT N.X FROM N;

--DS2 X:1~100 Y:1~100 CROSS JOIN，共10000筆資料

INSERT INTO DS2
WITH N(X) AS (
　SELECT　1 X
　FROM　　SYSIBM.SYSDUMMY1
　UNION　　ALL
　SELECT　N.X + 1
　FROM　　N
　WHERE　　N.X + 1 <= 100
)
SELECT N.X , N1.X
FROM 　N,N N1;

--將以下SQL Statement存成.sql file ex:1.sql
--(不要斷行，也不要加結束符號ex:「;」)

MERGE INTO DS1
USING (SELECT X,COUNT(*) CNT
FROM DS2
GROUP BY X) DS2 ON DS2.X = DS1.X
WHEN MATCHED THEN
UPDATE SET DS1.CNT = DS2.CNT

--使用Explain SQL Tool

C:\>db2expln -database CDIDB2 -g -f c:\1.sql -o c:\1.log

--開啟1.log

IBM DB2 Universal Database SQL Explain Tool
******************** DYNAMIC ***************************************
==================== STATEMENT ==========================================

　　Isolation Level　　　　　　= Cursor Stability
　　Blocking　　　　　　　　　　= Block Unambiguous Cursors
　　Query Optimization Class = 5

　　Partition Parallel　　　　= No
　　Intra-Partition Parallel = No

　　SQL Path　　　　　　　　　　= "SYSIBM", "SYSFUN", "SYSPROC", "ORION"

SQL Statement:
　MERGE INTO DS1 USING (
　　SELECT X, COUNT(*)CNT
　　FROM DS2
　　GROUP BY X)DS2 ON DS2.X =DS1.X
　WHEN MATCHED
　THEN
　UPDATE SET DS1.CNT =DS2.CNT

Section Code Page = 1208

Estimated Cost = 89.078125
Estimated Cardinality = 1.000000


Access Table Name = ORION.DS2 ID = 2,143
|　#Columns = 1
|　Relation Scan
|　|　Prefetch: Eligible
|　Lock Intents
|　|　Table: Intent Share
|　|　Row　: Next Key Share
|　Sargable Predicate(s)
|　|　Insert Into Sorted Temp Table ID = t1
|　|　|　#Columns = 2
|　|　|　#Sort Key Columns = 1
|　|　|　|　Key 1: X (Ascending)
|　|　|　Sortheap Allocation Parameters:
|　|　|　|　#Rows　　　= 25
|　|　|　|　Row Width = 12
|　|　|　Piped
|　|　|　Buffered Partial Aggregation
Sorted Temp Table Completion ID = t1
Access Temp Table ID = t1
|　#Columns = 2
|　Relation Scan
|　|　Prefetch: Eligible
Final Aggregation
|　Group By
|　Column Function(s)
Right Outer Hash Join
|　Early Out: Single Match Per Inner Row
|　Estimated Build Size: 4705
|　Estimated Probe Size: 7843
|　Access Table Name = ORION.DS1 ID = 2,142
|　|　#Columns = 1
|　|　Relation Scan
|　|　|　Prefetch: Eligible
|　|　Isolation Level: Read Stability
|　|　Lock Intents
|　|　|　Table: Intent Exclusive
|　|　|　Row　: Exclusive
|　|　Sargable Predicate(s)
|　|　|　Process Probe Table for Hash Join
Residual Predicate(s)
|　#Predicates = 2
Establish Row Position
|　Access Table Name = ORION.DS1 ID = 2,142
Update: Table Name = ORION.DS1 ID = 2,142
|　Update Predicate(s)
|　|　#Predicates = 1
End of section

Optimizer Plan:
　　　　　　　　　UPDATE
　　　　　　　　　(　　2)
　　　　　　　　/　　　　\
　　　　　　FETCH　　　Table:
　　　　　　(　　3)　　ORION
　　　　　/　　　　\　　DS1
　　　FILTER　　Table:
　　　(　　4)　　ORION
　　　　　|　　　DS1
　　　HSJOIN
　　　(　　5)
　　　/　　　\
　TBSCAN　　GRPBY
　(　　6)　　(　　7)
　　　|　　　　|
　Table:　　TBSCAN
　ORION　　　(　　8)
　DS1　　　　　　|
　　　　　　　　SORT
　　　　　　　(　　9)
　　　　　　　　　|
　　　　　　　TBSCAN
　　　　　　　(　　10)
　　　　　　　　　|
　　　　　　　　Table:
　　　　　　　　ORION
　　　　　　　　DS2

--將以下SQL Statement存成.sql file ex:2.sql
--(不要斷行，也不要加結束符號ex:「;」)

UPDATE DS1 SET CNT = (SELECT COUNT(*)
　　　　　　　　　　　　　FROM DS2
　　　　　　　　　　　　　WHERE DS2.X = DS1.X
　　　　　　　　　　　　　GROUP BY DS2.X)
WHERE DS1.X IN (SELECT X FROM DS2 GROUP BY X)

--使用Explain SQL Tool

C:\>db2expln -database CDIDB2 -g -f c:\2.sql -o c:\2.log

--開啟2.log

IBM DB2 Universal Database SQL Explain Tool
******************** DYNAMIC ***************************************
==================== STATEMENT ==========================================

　　　Isolation Level　　　= Cursor Stability
　　　Blocking　　　　　　　= Block Unambiguous Cursors
　　　Query Optimization Class = 5
　
　　　Partition Parallel　　　　= No
　　　Intra-Partition Parallel = No
　
　　　SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "ORION"

SQL Statement:
　UPDATE DS1 SET CNT =(
　　　SELECT COUNT(*)
　　　FROM DS2
　　　WHERE DS2.X =DS1.X
　　　GROUP BY DS2.X)
　WHERE DS1.X IN (
　　　SELECT X
　　　FROM DS2
　　　GROUP BY X)

Section Code Page = 1208


Estimated Cost = 463.691986
Estimated Cardinality = 25.000000


Access Table Name = ORION.DS1 ID = 2,142
|　Index Scan: Name = SYSIBM.SQL080703112552650 ID = 1
|　|　Regular Index (Not Clustered)
|　|　Index Columns:
|　|　|　1: X (Ascending)
|　#Columns = 1
|　#Key Columns = 0
|　|　Start Key: Beginning of Index
|　|　Stop Key: End of Index
|　Data Prefetch: Eligible 0
|　Index Prefetch: None
|　Lock Intents
|　|　Table: Intent Exclusive
|　|　Row　: Exclusive
Merge Join
|　Early Out: Single Match Per Outer Row
|　Access Table Name = ORION.DS2 ID = 2,143
|　|　#Columns = 1
|　|　Relation Scan
|　|　|　Prefetch: Eligible
|　|　Lock Intents
|　|　|　Table: Intent Share
|　|　|　Row　: Next Key Share
|　|　Sargable Predicate(s)
|　|　|　Insert Into Sorted Temp Table ID = t1
|　|　|　|　#Columns = 1
|　|　|　|　#Sort Key Columns = 1
|　|　|　|　|　Key 1: X (Ascending)
|　|　|　|　Sortheap Allocation Parameters:
|　|　|　|　|　#Rows　　　= 167
|　|　|　|　|　Row Width = 8
|　|　|　|　Piped
|　Sorted Temp Table Completion ID = t1
|　Access Temp Table ID = t1
|　|　#Columns = 1
|　|　Relation Scan
|　|　|　Prefetch: Eligible
|　|　Sargable Predicate(s)
|　|　|　Predicate Aggregation
|　|　|　|　Group By
|　Aggregation Completion
|　|　Group By
Nested Loop Join
|　Piped Inner
|　Access Table Name = ORION.DS2 ID = 2,143
|　|　Index Scan: Name = SYSIBM.SQL080703112635470 ID = 1
|　|　|　Regular Index (Not Clustered)
|　|　|　Index Columns:
|　|　|　|　1: X (Ascending)
|　|　|　|　2: Y (Ascending)
|　|　#Columns = 1
|　|　#Key Columns = 1
|　|　|　Start Key: Inclusive Value
|　|　|　|　|　1: ?
|　|　|　Stop Key: Inclusive Value
|　|　|　|　|　1: ?
|　|　Index-Only Access
|　|　Index Prefetch: None
|　|　Lock Intents
|　|　|　Table: Intent Share
|　|　|　Row　: Next Key Share
|　|　Sargable Index Predicate(s)
|　|　|　Predicate Aggregation
|　|　|　|Group By
|　|　|　|　Column Function(s)
|　Aggregation Completion
|　|　Group By
|　|　Column Function(s)
Update: Table Name = ORION.DS1 ID = 2,142
End of section

Optimizer Plan:
　　　　　　　　　　　　　　　　　　　　　　　　　　UPDATE
　　　　　　　　　　　　　　　　　　　　　　　　　　(　　2)
　　　　　　　　　　　　　　　　　　　　　　　/---/　　　　\--\
　　　　　　　　　　　　　　　　　　　　NLJOIN　　　　　　　　Table:
　　　　　　　　　　　　　　　　　　　　(　　3)　　　　　　　　ORION
　　　　　　　　　　　　　　　　/-----/　　\-----\　　　　　　DS1
　　　　　　　　　　　　MSJOIN　　　　　　　　　　　GRPBY
　　　　　　　　　　　　(　　4)　　　　　　　　　　　(　12)
　　　　　　　　　　　　/　　　\---\　　　　　　　　　　|
　　　　　　　　FETCH　　　　　　　　*　　　　　　　　IXSCAN
　　　　　　　　(　　5)　　　　　　　*　　　　　　　　(　13)
　　　　　　　　/　　　\　　　　　　|　　　　　　　　/　　　\--\
　　　　IXSCAN　　　　Table:　　GRPBY　　Index:　　　　　　　Table:
　　　　(　　5)　　　　ORION　　(　　8)　SYSIBM　　　　　　　　ORION
　　　　　|　　　　　　DS1　　　　　|　　SQL080703112635470　　DS2
　Index:　　　　　　　　　　　　TBSCAN
　SYSIBM　　　　　　　　　　　　(　　9)
　SQL080703112552650　　　　　　　|
　　　　　　　　　　　　　　　　　SORT
　　　　　　　　　　　　　　　　(　　10)
　　　　　　　　　　　　　　　　　　|
　　　　　　　　　　　　　　　　　TBSCAN
　　　　　　　　　　　　　　　　(　　11)
　　　　　　　　　　　　　　　　　　|
　　　　　　　　　　　　　　　　　Table:
　　　　　　　　　　　　　　　　　ORION
　　　　　　　　　　　　　　　　　DS2

※ so as Merge vs Delete

Data movement on Materialized Views using Oracle10g

/*
　　測試Oracle Materialized View的使用方式
　　測試Oracle Materialized View是否可使用SQLLDR
　　測試Oracle Materialized View重建時是否會影響引用MV的Procedures及Views
*/

--建立測試Table

CREATE TABLE DATASET
(
　　X　　INTEGER NOT NULL,
　　Y　　INTEGER NOT NULL,
　　XY　INTEGER,
　　PRIMARY KEY (X,Y)
);
CREATE TABLE DATASUM
(
　　X　　　INTEGER,
　　TOTAL　INTEGER
);

--新增測試資料

INSERT　INTO DATASET
SELECT　X,Y,X*Y
FROM　　(SELECT LEVEL X FROM DUAL CONNECT BY LEVEL <= 100),
　　　　(SELECT LEVEL Y FROM DUAL CONNECT BY LEVEL <= 100);　

COMMIT;

--建立一個Materialized View

SQL>CREATE MATERIALIZED VIEW MV_DATASET AS SELECT * FROM DATASET;

--跟DB2的Materialized Query Table不同的是：建立時資料就已進入MV_DATASET。DB2還必須做REFRESH

SQL>SELECT COUNT(*) FROM MV_DATASET;

COUNT(*)
-----------
10000

--測試REFRESH功能

SQL>INSERT INTO DATASET VALUES (101,101,101*101);
SQL>COMMIT;
SQL>ALTER TABLE MV_DATASET NOLOGGING;
SQL>EXEC DBMS_MVIEW.REFRESH('MV_DATASET','cf');

已順利完成PL/SQL程序.

SQL>SELECT COUNT(*) FROM MV_DATASET;
COUNT(*)
-----------
10001

--測試是否可在MV_DTASET上使用SQL LOADER
--準備一個CONTROL FILE dataset.ctl ，內容如下:

LOAD DATA
INFILE *
APPEND
INTO TABLE MV_DATASET
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(　
　　X,
　　Y,
　　XY
)
BEGINDATA
101|1|101
101|2|202

--執行SQLLDR

C:\>sqlldr userid=orion/orion control=c:\dataset.ctl log=c:\errlog.log

--開啟errlog.log，部份訊息如下:
SQL*Loader-601: 對 INSERT 選項而言, 表格必須是空的. 表格 MV_DATASET 上有錯

--清空MV_DATASET後再試

SQL>truncate table MV_DATASET;
C:\>sqlldr userid=orion/orion control=c:\dataset.ctl log=c:\errlog.log

--開啟errlog.log，部份訊息如下:

記錄 1: 被拒 - 表格 MV_DATASET 發生錯誤
ORA-01732: 不能在此視觀表進行資料操作作業

記錄 2: 被拒 - 表格 MV_DATASET 發生錯誤
ORA-01732: 不能在此視觀表進行資料操作作業

表格 MV_DATASET:
　已順利載入 0 資料列.
　由於資料錯誤, 2 資料列 未被載入
　因為所有的 WHEN 子句均不成立, 0 資料列 未被載入
　因為所有的欄位均為 NULL, 0 資料列 未被載入

※結論是DB2的Materialized Query Table是可以被load資料進去，Oracle的則不行

--測試Oracle的Materialized View重建時是否影響view或者procedure which are using MV
--建立view base on mv_dataset

SQL>DROP VIEW V_DATASET
　　CREATE VIEW V_DATASET
　　AS
　　SELECT * FROM MV_DATASET;

SQL>DROP MATERIALIZED VIEW MV_DATASET;
SQL>CREATE MATERIALIZED VIEW MV_DATASET AS SELECT * FROM DATASET;
SQL>SELECT COUNT(*) FROM V_DATASET;

COUNT(*)
-----------
10001

--建立procedure using mv_dataset，內容如下:

CREATE OR REPLACE PROCEDURE SP_DATASUM(nErr OUT NUMBER)
IS
BEGIN
　　nErr := 0;
　　BEGIN
　　　　INSERT INTO DATASUM
　　　　SELECT X,SUM(XY)
　　　　FROM MV_DATASET
　　　　GROUP BY X;

　　EXCEPTION
　　　　WHEN OTHERS THEN
　　　　nErr := SQLCODE;
　　　　ROLLBACK;
　　　　RETURN;
　　END
　　COMMIT;
END;

--重建Materialized View

SQL>DROP MATERIALIZED VIEW MV_DATASET;
SQL>CREATE MATERIALIZED VIEW MV_DATASET AS SELECT * FROM DATASET;
SQL>var nErr number
SQL>exec sp_datasum(:nErr)

已順利完成PL/SQL程序.

SQL>print nErr

NERR
-----
　　0

SQL>SELECT COUNT(*) FROM DATASUM;
COUNT(*)
--------
　　101


Bulk Binds to enhance cursor performance using Oracle10g

～ FORALL and BULK COLLECT INTO ～


-- 比較使用BULK COLLECT INTO以及既有Cursor的執行效能
-- 建立測試Tables

SQL>CREATE TABLE MY_ALL_OBJECTS
　　AS SELECT * FROM ALL_OBJECTS;

SQL>CREATE TABLE RESULT_OBJECTS
　　AS SELECT * FROM MY_ALL_OBJECTS WHERE 1 = 0;

SQL>SELECT COUNT(*) FROM MY_ALL_OBJECTS;

COUNT(*)
------------
40997

-- 建立測試Procedures BULK_TEST

CREATE OR REPLACE PROCEDURE BULK_TEST (p_array_size IN PLS_INTEGER DEFAULT 100)
IS
　　TYPE ARRAY IS TABLE OF my_all_objects%ROWTYPE;
　　l_data ARRAY;
　　CURSOR OBJ_CUR IS SELECT * FROM my_all_objects;
BEGIN

　　OPEN OBJ_CUR;
　　LOOP
　　FETCH OBJ_CUR BULK COLLECT INTO l_data LIMIT p_array_size;
　　
　　FORALL i IN 1..l_data.COUNT
　　　　INSERT INTO RESULT_OBJECTS VALUES l_data(i);
　　　　EXIT WHEN l_data.COUNT = 0;
　　END LOOP;
　　CLOSE OBJ_CUR;
　　COMMIT;
END BULK_TEST;


-- 建立測試Procedures BULK_TEST2用既有CURSOR語法

CREATE OR REPLACE PROCEDURE BULK_TEST2
IS
　　vOWNER　　　　　　my_all_objects.OWNER%TYPE;
　　vOBJECT_NAME　　　my_all_objects.OBJECT_NAME%TYPE;
　　vSUBOBJECT_NAME　my_all_objects.SUBOBJECT_NAME%TYPE;
　　vOBJECT_ID　　　　my_all_objects.OBJECT_ID%TYPE;
　　vDATA_OBJECT_ID　my_all_objects.DATA_OBJECT_ID%TYPE;
　　vOBJECT_TYPE　　　my_all_objects.OBJECT_TYPE%TYPE;
　　vCREATED　　　　　my_all_objects.CREATED%TYPE;
　　vLAST_DDL_TIME 　my_all_objects.LAST_DDL_TIME%TYPE;
　　vTIMESTAMP 　　　my_all_objects.TIMESTAMP%TYPE;
　　vSTATUS　　　　　my_all_objects.STATUS%TYPE;
　　vTEMPORARY　　　my_all_objects.TEMPORARY%TYPE;
　　vGENERATED　　　my_all_objects.GENERATED%TYPE;
　　vSECONDARY　　　my_all_objects.SECONDARY%TYPE;

　　CURSOR OBJ_CUR IS SELECT * FROM my_all_objects;
BEGIN
　　OPEN OBJ_CUR;
　　LOOP
　　FETCH OBJ_CUR INTO vOwner,vObject_name,vSubObject_name,
　　　　　　　　　　　　　vOBJECT_ID,vDATA_OBJECT_ID,vOBJECT_TYPE,
　　　　　　　　　　　　　vCREATED,vLAST_DDL_TIME,vTIMESTAMP,
　　　　　　　　　　　　　vSTATUS,vTEMPORARY,vGENERATED,vSECONDARY;
　　EXIT WHEN OBJ_CUR%NOTFOUND;

　　INSERT INTO RESULT_OBJECTS
　　VALUES (vOwner,vObject_name,vSubObject_name,vOBJECT_ID,vDATA_OBJECT_ID,
　　　　　　　vOBJECT_TYPE,vCREATED,vLAST_DDL_TIME,vTIMESTAMP,vSTATUS,
　　　　　　　vTEMPORARY,vGENERATED,vSECONDARY);

　　END LOOP;
　　CLOSE OBJ_CUR;
　　COMMIT;
END BULK_TEST2;


--將測試程式包在bulk.sql，內容如下

select current_timestamp from dual;
exec BULK_TEST;
select current_timestamp from dual;
select count(*) from RESULT_OBJECTS;


--執行

SQL>@c:\bulk.sql;


--測試執行時間結果:

CURRENT_TIMESTAMP
-------------------------------------
02-7月 -08 12.29.43.612000 下午 +08:00

已順利完成 PL/SQL 程序.

CURRENT_TIMESTAMP
-------------------------------------
02-7月 -08 12.29.44.112000 下午 +08:00

COUNT(*)
----------
　　40997

--將測試程式同樣包在bulk.sql，內容如下

select current_timestamp from dual;
exec BULK_TEST2;
select current_timestamp from dual;
select count(*) from RESULT_OBJECTS;

--執行

SQL>@c:\bulk.sql;

--測試執行時間結果:

CURRENT_TIMESTAMP
-------------------------------------
02-7月 -08 12.29.44.453000 下午 +08:00

已順利完成 PL/SQL 程序.

CURRENT_TIMESTAMP
-------------------------------------
02-7月 -08 12.29.47.056000 下午 +08:00

COUNT(*)
----------
　　40997

--使用舊有的cursor語法執行時間明顯慢了幾秒
※ Bulk binds reduce the number of context switches between the PL/SQL engine and the database engine

※ The tradeoff with BULK COLLECT, like so many other performance-enhancing features, is "run faster but consume more memory." - By Steven Feuerstein.
http://www.oracle.com/technology/oramag/oracle/08-mar/o28plsql.html