ADMIN_COPY_SCHEMA using DB2 9.7 Express-C

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

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

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

-- 以使用者 db2admin 登進 db2

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

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

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

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

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

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

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


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

C:\>db2 get authorizations

　現行使用者的管理權限

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


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

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


-- 再回到 db2admin user

C:\>db2 get authorizations

　現行使用者的管理權限

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

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

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

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

　參數名稱：ERRORTABNAME
　參數值： -

　傳回狀態 = 0




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

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

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

　　已選取 4 個記錄。


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

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

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

參數名稱：ERRORTAB
參數值： -

傳回狀態 = 0


PureXML using DB2 Express-C 9.7

/*
　　1.測試 DB2 Express-C 9.7 pureXML
　　2.兩個 xml 資料之間的 join
　　3.一個 xml document 裡有多筆資料時的資料呈現
　　p.s　程式中＜＞及縮排的空白為全形，使用前須先轉為single-byte
*/

-- 建立地區xsd,有地區代碼與地區英文名稱

＜?xml version="1.0" encoding="UTF-8"?＞
＜xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"＞
＜xs:element name="region"＞
　＜xs:complexType＞
　　＜xs:sequence＞
　　　＜xs:element name="regno" type="xs:string"/＞
　　　＜xs:element name="regename" type="xs:string"/＞
　　＜/xs:sequence＞
　＜/xs:complexType＞
＜/xs:element＞
＜/xs:schema＞

-- 註冊地區xsd

register xmlschema 'http://localhost/region/region.xsd' from c:\region.xsd as xsd_region complete
DB20000I REGISTER XMLSCHEMA 指令已順利完成。

-- p.s 從register中移除xsd的語法如下

drop xsrobject xsd_region


-- 由xsrobjects看註冊資料是否完整
select substr(targetnamespace,1,30),substr(schemalocation,1,30)
from syscat.xsrobjects where objectname = 'XSD_REGION'

1　　　　　　　　　　　　　　　　　2
------------------------------ ------------------------------
http://localhost/region　　　　　http://localhost/region/region

　　已選取 1 個記錄。

-- 建立地區table,　regioninfo為xml格式

create table region
(
　id integer,
　regioninfo xml
)
DB20000I SQL 指令已順利完成。

-- 將地區xml資料寫成.sql檔,名稱為region.sql.格式必須符合xsd的定義

insert into region values
(1,xmlvalidate(xmlparse(document '＜region＞
　　　　　　　　　　　　　　　　　　　＜regno＞1＜/regno＞
　　　　　　　　　　　　　　　　　　　＜regename＞Northern＜/regename＞
　　　　　　　　　　　　　　　　　　　＜/region＞'
　　　　　　　　　　　　) according to xmlschema ID XSD_REGION));

insert into region values
(2,xmlvalidate(xmlparse(document '＜region＞
　　　　　　　　　　　　　　　　　　　＜regno＞2＜/regno＞
　　　　　　　　　　　　　　　　　　　＜regename＞Central＜/regename＞
　　　　　　　　　　　　　　　　　　　＜/region＞'
　　　　　　　　　　　　) according to xmlschema ID XSD_REGION));

insert into region values
(3,xmlvalidate(xmlparse(document '＜region＞
　　　　　　　　　　　　　　　　　　　＜regno＞3＜/regno＞
　　　　　　　　　　　　　　　　　　　＜regename＞Eastern＜/regename＞
　　　　　　　　　　　　　　　　　　　＜/region＞'
　　　　　　　　　　　　) according to xmlschema ID XSD_REGION));

insert into region values
(4,xmlvalidate(xmlparse(document '＜region＞
　　　　　　　　　　　　　　　　　　　＜regno＞4＜/regno＞
　　　　　　　　　　　　　　　　　　　＜regename＞Western＜/regename＞
　　　　　　　　　　　　　　　　　　　＜/region＞'
　　　　　　　　　　　　) according to xmlschema ID XSD_REGION));

insert into region values
(5,xmlvalidate(xmlparse(document '＜region＞
　　　　　　　　　　　　　　　　　　　＜regno＞5＜/regno＞
　　　　　　　　　　　　　　　　　　　＜regename＞Southern＜/regename＞
　　　　　　　　　　　　　　　　　　　＜/region＞'
　　　　　　　　　　　　) according to xmlschema ID XSD_REGION));


-- 執行region.sql將資料新增進table region

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

DB20000I SQL 指令已順利完成。

DB20000I SQL 指令已順利完成。

DB20000I SQL 指令已順利完成。

DB20000I SQL 指令已順利完成。

-- 使用xml function查看結果,並以table方式呈現

select　regno,regename
from　　region,
　　　　xmltable('for $reg in $d/region return $reg'
　　　　　　　　　passing region.regioninfo as "d"
　　　　　　　　　COLUMNS regno varchar(5) path 'regno/text()',
　　　　　　　　　　　　　regename varchar(20) path 'regename/text()') t

REGNO　REGENAME
-----　--------------------
1　　　Northern
2　　　Central
3　　　Eastern
4　　　Western
5　　　Southern

　　已選取 5 個記錄。

-- 建立銷售記錄xsd,有地區代碼、銷售日期、產品名稱、價格與數量

＜?xml version="1.0" encoding="UTF-8"?＞
＜xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"＞
＜xs:element name="sales"＞
　＜xs:complexType＞
　　＜xs:sequence＞
　　　＜xs:element name="regno" type="xs:string"/＞
　　　＜xs:element name="dt" type="xs:integer"/＞
　　　＜xs:element name="product" type="xs:string"/＞
　　　＜xs:element name="prize" type="xs:decimal"/＞
　　　＜xs:element name="quantity" type="xs:integer"/＞
　　＜/xs:sequence＞
　＜/xs:complexType＞
＜/xs:element＞
＜/xs:schema＞


-- 註冊銷售記錄xsd

register xmlschema 'http://localhost/sales/sales.xsd' from c:\sales.xsd as xsd_sales complete
DB20000I REGISTER XMLSCHEMA 指令已順利完成。

-- 建立銷售記錄table,　salesrpt為xml格式

create table sales
(
　　sno integer,
　　salesrpt xml
)
DB20000I SQL 指令已順利完成。

-- 將銷售記錄xml資料寫成.sql檔,名稱為sales.sql

insert into sales values
(1,xmlvalidate(xmlparse(document '＜sales＞
　　　　　　　　　　　　　　　　　　　＜regno＞1＜/regno＞
　　　　　　　　　　　　　　　　　　　＜dt＞20091011＜/dt＞
　　　　　　　　　　　　　　　　　　　＜product＞ring99＜/product＞
　　　　　　　　　　　　　　　　　　　＜prize＞12.55＜/prize＞
　　　　　　　　　　　　　　　　　　　＜quantity＞10＜/quantity＞
　　　　　　　　　　　　　　　　　　　＜/sales＞'
　　　　　　　　　　　　) according to xmlschema ID XSD_SALES));

insert into sales values
(2,xmlvalidate(xmlparse(document '＜sales＞
　　　　　　　　　　　　　　　　　　　＜regno＞3＜/regno＞
　　　　　　　　　　　　　　　　　　　＜dt＞20091011＜/dt＞
　　　　　　　　　　　　　　　　　　　＜product＞bangles＜/product＞
　　　　　　　　　　　　　　　　　　　＜prize＞1000＜/prize＞
　　　　　　　　　　　　　　　　　　　＜quantity＞1＜/quantity＞
　　　　　　　　　　　　　　　　　　　＜/sales＞'
　　　　　　　　　　　　) according to xmlschema ID XSD_SALES));

insert into sales values
(3,xmlvalidate(xmlparse(document '＜sales＞
　　　　　　　　　　　　　　　　　　　＜regno＞2＜/regno＞
　　　　　　　　　　　　　　　　　　　＜dt＞20091015＜/dt＞
　　　　　　　　　　　　　　　　　　　＜product＞ring33＜/product＞
　　　　　　　　　　　　　　　　　　　＜prize＞50＜/prize＞
　　　　　　　　　　　　　　　　　　　＜quantity＞6＜/quantity＞
　　　　　　　　　　　　　　　　　　　＜/sales＞'
　　　　　　　　　　　　) according to xmlschema ID XSD_SALES));

insert into sales values
(4,xmlvalidate(xmlparse(document '＜sales＞
　　　　　　　　　　　　　　　　　　　＜regno＞1＜/regno＞
　　　　　　　　　　　　　　　　　　　＜dt＞20091105＜/dt＞
　　　　　　　　　　　　　　　　　　　＜product＞ring99＜/product＞
　　　　　　　　　　　　　　　　　　　＜prize＞13＜/prize＞
　　　　　　　　　　　　　　　　　　　＜quantity＞10＜/quantity＞
　　　　　　　　　　　　　　　　　　　＜/sales＞'
　　　　　　　　　　　　) according to xmlschema ID XSD_SALES));

insert into sales values
(5,xmlvalidate(xmlparse(document '＜sales＞
　　　　　　　　　　　　　　　　　　　＜regno＞1＜/regno＞
　　　　　　　　　　　　　　　　　　　＜dt＞20091109＜/dt＞
　　　　　　　　　　　　　　　　　　　＜product＞ring99＜/product＞
　　　　　　　　　　　　　　　　　　　＜prize＞13.52＜/prize＞
　　　　　　　　　　　　　　　　　　　＜quantity＞10＜/quantity＞
　　　　　　　　　　　　　　　　　　　＜/sales＞'
　　　　　　　　　　　　) according to xmlschema ID XSD_SALES));

insert into sales values
(6,xmlvalidate(xmlparse(document '＜sales＞
　　　　　　　　　　　　　　　　　　　＜regno＞3＜/regno＞
　　　　　　　　　　　　　　　　　　　＜dt＞20091110＜/dt＞
　　　　　　　　　　　　　　　　　　　＜product＞ring99＜/product＞
　　　　　　　　　　　　　　　　　　　＜prize＞13.5＜/prize＞
　　　　　　　　　　　　　　　　　　　＜quantity＞5＜/quantity＞
　　　　　　　　　　　　　　　　　　　＜/sales＞'
　　　　　　　　　　　　) according to xmlschema ID XSD_SALES));


-- 執行sales.sql將資料新增進table sales

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

DB20000I SQL 指令已順利完成。

DB20000I SQL 指令已順利完成。

DB20000I SQL 指令已順利完成。

DB20000I SQL 指令已順利完成。

DB20000I SQL 指令已順利完成。

-- 使用xml function查看結果,並以table方式呈現

select　regno,dt,product,prize,quantity
from　　sales,
　　　　xmltable('for $sal in $d/sales return $sal'
　　　　passing sales.salesrpt as "d"
　　　　COLUMNS regno varchar(5) path 'regno/text()',
　　　　dt integer path 'dt/text()',
　　　　product varchar(10) path 'product/text()',
　　　　prize decimal(10,2) path 'prize/text()',
　　　　quantity integer path 'quantity/text()') t2

REGNO DT　　　　　PRODUCT　　　　PRIZE　　　　QUANTITY
----- ----------- ---------- ------------ -----------
1　　　　20091011 ring99　　　　　　12.55　　　　　　　10
3　　　　20091011 bangles　　　　 1000.00　　　　　　　 1
2　　　　20091015 ring33　　　　　　50.00　　　　　 　　6
1　　　　20091105 ring99　　　　　　13.00　　　　　　　10
1　　　　20091109 ring99　　　　　　13.52　　　　　　　10
3　　　　20091110 ring99　　　　　　13.50　　　　　 　　5

　　已選取 6 個記錄。


-- 地區xml資料與銷售記錄xml資料相互join，混用sql語法

select t3.regno,regename,dt,product,prize,quantity
from
(select　regno,regename
from　　region,
　　　　xmltable('for $reg in $d/region return $reg'
　　　　　　　　　passing region.regioninfo as "d"
　　　　　　　　　COLUMNS regno varchar(5) path 'regno/text()',
　　　　　　　　　　　　　regename varchar(20) path 'regename/text()') t) t1,
(select　regno,dt,product,prize,quantity
from　　sales,
　　　　xmltable('for $sal in $d/sales return $sal'
　　　　　　　　　passing sales.salesrpt as "d"
　　　　　　　　　COLUMNS regno varchar(5) path 'regno/text()',
　　　　　　　　　　　　　dt integer path 'dt/text()',
　　　　　　　　　　　　　product varchar(10) path 'product/text()',
　　　　　　　　　　　　　prize decimal(10,2) path 'prize/text()',
　　　　　　　　　　　　　quantity integer path 'quantity/text()') t2) t3
where t1.regno = t3.regno

REGNO　REGENAME　　　　　　DT　　　　　PRODUCT　　　　PRIZE　　　　　QUANTITY
-----　------------------- ----------- ---------- ------------ -----------
1　　　Northern　　　　　　　　20091011 ring99　　　　　　12.55　　　　　　　10
3　　　Eastern　　　　　　　　 20091011 bangles　　　　 1000.00　　　　　　　 1
2　　　Central　　　　　　　　 20091015 ring33　　　　　　50.00　　　　　 　　6
1　　　Northern　　　　　　　　20091105 ring99　　　　　　13.00　　　　　　　10
1　　　Northern　　　　　　　　20091109 ring99　　　　　　13.52　　　　　　　10
3　　　Eastern　　　　　　　　 20091110 ring99　　　　　　13.50　　　　　　　 5

　　已選取 6 個記錄。

-- 另外追加測試xml含有多筆資料時的狀況
-- 建立table City,欄位為城市代碼,城市名稱,行政區資料

create table City
(
　cityid　　char(2),
　cityname　varchar(32),
　cityinfo　xml
) in userspace1;

insert into city values
('01','台北市',
'＜city＞
　＜county＞
　　＜id＞0101＜/id＞
　　＜name＞松山區＜/name＞
　＜/county＞
　＜county＞
　　＜id＞0102＜/id＞
　　＜name＞大安區＜/name＞
　＜/county＞
　＜county＞
　　＜id＞0109＜/id＞
　　＜name＞大同區＜/name＞
　＜/county＞
　＜county＞
　　＜id＞0110＜/id＞
　　＜name＞中山區＜/name＞
　＜/county＞
　＜county＞
　　＜id＞0111＜/id＞
　　＜name＞內湖區＜/name＞
　＜/county＞
　＜county＞
　　＜id＞0112＜/id＞
　　＜name＞南港區＜/name＞
　＜/county＞
　＜county＞
　　＜id＞0113＜/id＞
　　＜name＞木柵區＜/name＞
　＜/county＞
　＜county＞
　　＜id＞0114＜/id＞
　　＜name＞景美區＜/name＞
　＜/county＞
　＜county＞
　　＜id＞0115＜/id＞
　　＜name＞士林區＜/name＞
　＜/county＞
　＜county＞
　　＜id＞0116＜/id＞
　　＜name＞北投區＜/name＞
　＜/county＞
　＜county＞
　　＜id＞0117＜/id＞
　　＜name＞信義區＜/name＞
　＜/county＞
　＜county＞
　　＜id＞0118＜/id＞
　　＜name＞中正區＜/name＞
　＜/county＞
　＜county＞
　　＜id＞0119＜/id＞
　　＜name＞萬華區＜/name＞
　＜/county＞
　＜county＞
　　＜id＞0120＜/id＞
　　＜name＞文山區＜/name＞
　＜/county＞
＜/city＞');

-- 行政區xml欄位原為多筆資料時的資料呈現

select　cityid,cityname,id,name
from　　city,xmltable('for $cty in $d/city/county return $cty'
　　　　　　　　　　　passing city.cityinfo as "d"
　　　　　　　　　　　columns id varchar(4) path 'id/text()',
　　　　　　　　　　　name varchar(16) path 'name/text()') t

CITYID　CITYNAME　　　　　　　　　　　　ID　　NAME
------　--------------------------- ----　----------------
01　　　台北市　　　　　　　　　　　　　0101　松山區
01　　　台北市　　　　　　　　　　　　　0102　大安區
01　　　台北市　　　　　　　　　　　　　0109　大同區
01　　　台北市　　　　　　　　　　　　　0110　中山區
01　　　台北市　　　　　　　　　　　　　0111　內湖區
01　　　台北市　　　　　　　　　　　　　0112　南港區
01　　　台北市　　　　　　　　　　　　　0113　木柵區
01　　　台北市　　　　　　　　　　　　　0114　景美區
01　　　台北市　　　　　　　　　　　　　0115　士林區
01　　　台北市　　　　　　　　　　　　　0116　北投區
01　　　台北市　　　　　　　　　　　　　0117　信義區
01　　　台北市　　　　　　　　　　　　　0118　中正區
01　　　台北市　　　　　　　　　　　　　0119　萬華區
01　　　台北市　　　　　　　　　　　　　0120　文山區

　　已選取 14 個記錄。


Equivalent of Oracle's Ref Cursor in DB2

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

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

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

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

/*　　建立session global variable　　*/

CREATE VARIABLE ADMINISTRATROR.YEAR_SET INTEGER

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


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

C:\>db2 connect to orion

　　資料庫連線資訊

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

C:\>db2 values administrator.year_set

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

　　已選取 1 個記錄。

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

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

　　DECLARE TIMECUR CURSOR WITH HOLD WITH RETURN FOR CUR1;

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

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

　　PREPARE CUR1 FROM szSql;
　　OPEN TIMECUR;
END

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

C:\>db2 call sp_test

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

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


Bug has Fixed when using Merge Statement with Database Link - Oracle 10g

/*
先前已經測過 Merge 的語法透過 DB2 Federation 做更新是不可行的.
Oracle 的話要上patch，聽說11g已經沒有這個bug了

My Scenario:建兩個 Database 與 兩個 Table 分別如下:
　　DB1: Orion　　UserName:donkey 　　Table: Oriontb
　　DB2: Odyssey　UserName:voyager　　Table: Odysseytb
測試使用傳統update語法與Merge Update
*/


/*　用User:voyager登進odyssey建立測試table與資料　*/

create table odysseytb
(
　　col1 integer not null primary key,
　　col2 varchar2(32)
);
insert into odysseytb values (1,'1 from odysseytb');
insert into odysseytb values (2,'2 from odysseytb');
commit;

 
/*　用User:donkey登進orion建立database link及測試table與資料　*/

create table oriontb
(
　　col1 integer not null primary key,
　　col2 varchar2(32)
);
insert into oriontb values (1,'1 from oriontb');
commit;

 

1. user donkey　已經由 grant create database link to donkey 而有權限 create database link
2. 使用 odyssey 資料庫 user voyager的 權限


create database link odyssey
connect to voyager
identified by voyager
using 'odyssey'

 
/*　測試連線是否成功　*/

select * from odysseytb@odyssey;

　　　COL1　COL2
---------- --------------------------------
　　　　　1　1 from odysseytb
　　　　　2　2 from odysseytb

 
/*　測試傳統update語法,用oriontb去update odysseytb@odyssey 　*/

update (select oriontb.col1,oriontb.col2,
　　　　　　　　　t.col1 link_col1,t.col2 link_col2
　　　　from oriontb,odysseytb@odyssey t
　　　　where oriontb.col1 = t.col1) set link_col2 = col2

已更新 1 個資料列.

select * from odysseytb@odyssey;

　　　COL1　COL2
---------- ---------------------------------
　　　　　1　1 from oriontb
　　　　　2　2 from odysseytb

 
/*　Rollback。範例資料還需再使用　*/

/*　測試傳統update語法,用odysseytb@odyssey來update oriontb　*/

update (select oriontb.col1,oriontb.col2,
　　　　　　　　　t.col1 link_col1,t.col2 link_col2
　　　　from oriontb,odysseytb@odyssey t
　　　　where oriontb.col1 = t.col1) set col2 = link_col2;

已更新 1 個資料列.

select * from oriontb;

　　　COL1　COL2
---------- ----------------------------------
　　　　　1　1 from odysseytb


 
/*　測試 Merge Update 語法，結果得到以下錯誤　*/


merge into oriontb using odysseytb@odyssey t on (oriontb.col1 = t.col1)
when matched then
update set oriontb.col2 = t.col2;


merge into oriontb using odysseytb@odyssey t on (oriontb.col1 = t.col1)
*
ERROR 在行 1:
ORA-02064: 不支援分散式作業

 
/*　測試 Merge Insert 語法，結果得到以下錯誤　*/

merge into oriontb using odysseytb@odyssey t on (oriontb.col1 = t.col1)
when not matched then insert (oriontb.col1,oriontb.col2)
values (t.col1,t.col2);

merge into oriontb using odysseytb@odyssey t on (oriontb.col1 = t.col1)
*
ERROR 在行 1:
ORA-02064: 不支援分散式作業


CTE and RECURSIVE SQL Using SQL SERVER 2005 EXPRESS

/*
大概將近十年沒使用 SQL Server 開發系統了。
一直到最近有個新案子，它的後端需要使用SQL Server 2005 Express資料庫，我才有機會和動力去摸摸它。
‧SQL Server 2005 Express 與過去幾個版本一樣，非常容易上手，
‧它的oSQL(sqlcmd)功能還算強，但還是沒有像 DB2 與 Oracle 功能來得強大。
‧它的幾個extended stored procedures一樣好用(雖然有幾個因為安全性問題被移除，迂迴一下也可以做到)
‧OPENROWSET 等這類功能做得真好
‧終於有了 CTE 與 Recursive SQL 的語法，不過語法有追隨 DB2 的嫌疑。
　而且有多此一舉的感覺...LOG下來以免被我遺忘

Scenario: 使用 Recursive 產生日曆資料
*/

/*　建立測試 Table　*/

CREATE TABLE D_LIST
(
　DT VARCHAR(8)
)


/*
　使用CTE搭配Recursive SQL產生2009年日曆資料
　1.將MAXRECURSION 0表示不設限，否則會有以下錯誤
　　　Msg 530, Level 16, State 1, Line 1
　　　陳述式已結束。最大遞迴 100 已在陳述式完成之前用盡。
　2.INSERT 語法擺放的位置與 DB2 CTE 不同
*/

WITH N(DT)
AS
(SELECT '20090101'
UNION ALL
SELECT CONVERT(VARCHAR(8),DATEADD(D,1,CONVERT(DATETIME,DT,112)),112)
FROM N
WHERE CONVERT(VARCHAR(4),DATEADD(D,1,CONVERT(DATETIME,DT,112)),112) = '2009'
)
INSERT INTO D_LIST
SELECT * FROM N
OPTION (MAXRECURSION 0);


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

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

-- 建立測試 Tables & 資料

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

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

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

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


-- 建立測試 Procedure

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


-- 檢查關聯性

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

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

　　已選取 2 個記錄。


-- 再建立另一測試 Procedure

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

　　EXECUTE IMMEDIATE SZSTR;
END


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

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


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

　　已選取 2 個記錄。



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

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

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

已選取 2 個記錄。


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

Test Thesaurus Expend Search using NSE9.7

/*
1. Net Search Extender 9.7 由於DB2 9.7在權限上更加的嚴謹，所以必須給定權限才能使用
2. 中文用同義詞辭典做 extend search 是 workable 的.　建立時的編碼要注意
*/


/*　grant 權限　*/
grant dbadm on database to db2admns

grant dataaccess on database to db2admns

/*　讓 NSE 對 Database ORION 做 text search　*/
db2text enable database for text connect to orion

/* 建立測試 Table 及資料　*/


CREATE TABLE HEADLINE
(
　　SNO　INTEGER NOT NULL PRIMARY KEY,
　　TXT　VARCHAR(128)
);

INSERT INTO HEADLINE VALUES
(1,'首支西班牙F1車隊成立'),
(2,'西班牙車手Pedro de la Rosa很有可能加入Campos車隊'),
(3,'西班牙車手Fernando Alonso加入Ferrari車隊'),
(4,'西班牙將減少鬥牛場次'),
(5,'F1明年將禁用輪圈整流罩'),
(6,'薪資仲裁 小小郭籌碼大增'),
(7,'一級方程式賽車世界冠軍將可能於巴西站產生'),
(8,'兩屆世界冠軍阿隆索將加入法拉利車隊'),
(9,'Kalou signs new Chelsea contract'),
(10,'Spanish olive farmers hit by price falls')


/* 建立 index 在 headline 的 txt 欄位上 */
db2text create index headline_idx for text on headline(txt)

/* refresh index */
db2text update index headline_idx for text



/*
　建立同義詞辭典，存放在sqllib\db2ext\下，名稱給定為 nsesamplethes.def
　編碼須為UTF-8 否則在編譯時給定 -ccsid 1208之後中文查仍是有問題
*/


:WORDS
　　阿隆索
　.RELATED_TO F1
　.RELATED_TO 一級方程式賽車
　.RELATED_TO 西班牙
　.SYNONYM_OF Fernando Alonso
　.SYNONYM_OF Alonso

:WORDS
　　西班牙
　.SYNONYM_OF Spain
　.RELATED_TO Spanish


/*　編譯同義詞辭典　*/
C:\Program Files\IBM\SQLLIB\db2ext>db2extth -f nsesamplethes.def -ccsid 1208 -t thes\

同義詞辭典編譯器
CTE0001 作業順利完成。

/*　以"阿隆索"做關聯字搜尋　*/

db2 => select sno,txt from headline \
db2 (續) => => where contains(txt,'THESAURUS "nsesamplethes" \
db2 (續) => => expand related term of "阿隆索"') > 0

SNO　　　　　TXT
----------- --------------------------------------------------
　　　　　　1 首支西班牙F1車隊成立
　　　　　　2 西班牙車手Pedro de la Rosa很有可能加入Campos車隊
　　　　　　3 西班牙車手Fernando Alonso加入Ferrari車隊
　　　　　　4 西班牙將減少鬥牛場次
　　　　　　5 F1明年將禁用輪圈整流罩
　　　　　　7 一級方程式賽車世界冠軍將可能於巴西站產生
　　　　　　8 兩屆世界冠軍阿隆索將加入法拉利車隊

　　已選取 7 個記錄。


/*　以"阿隆索"做同義字搜尋　*/

db2 => select sno,txt from headline \
db2 (續) => => where contains(txt,'THESAURUS "nsesamplethes" \
db2 (續) => => expand synonym term of "阿隆索"') > 0

SNO　　　　　TXT
----------- ---------------------------------------------------
　　　　　　3 西班牙車手Fernando Alonso加入Ferrari車隊
　　　　　　8 兩屆世界冠軍阿隆索將加入法拉利車隊

　　已選取 2 個記錄。


/*　以"西班牙"做關聯字搜尋　*/

db2 => select sno,txt from headline \
db2 (續) => => where contains(txt,'THESAURUS "nsesamplethes" \
db2 (續) => => expand related term of "西班牙"') > 0

SNO　　　　　TXT
----------- ----------------------------------------------------
　　　　　　1 首支西班牙F1車隊成立
　　　　　　2 西班牙車手Pedro de la Rosa很有可能加入Campos車隊
　　　　　　3 西班牙車手Fernando Alonso加入Ferrari車隊
　　　　　　4 西班牙將減少鬥牛場次
　　　　　　8 兩屆世界冠軍阿隆索將加入法拉利車隊
　　　　　 10 Spanish olive farmers hit by price falls

　　已選取 6 個記錄。


/*　以"西班牙"做同義字搜尋　*/

db2 => select sno,txt from headline \
db2 (續) => => where contains(txt,'THESAURUS "nsesamplethes" \
db2 (續) => => expand synonym term of "西班牙"') > 0

SNO　　　　　TXT
----------- ----------------------------------------------------
　　　　　　1 首支西班牙F1車隊成立
　　　　　　2 西班牙車手Pedro de la Rosa很有可能加入Campos車隊
　　　　　　3 西班牙車手Fernando Alonso加入Ferrari車隊
　　　　　　4 西班牙將減少鬥牛場次

已選取 4 個記錄。


Avoid from SQL20165N using DB2 9.7 Express-C

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

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

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

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

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

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

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


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


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

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

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

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

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

SQL3520W 成功載入「一致點」。

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

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

SQL3520W 成功載入「一致點」。

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


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

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

C:\>db2 select * from test2

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

　　已選取 1 個記錄。


Autonomous transactions - DB2 9.7 Express-C

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

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


/* 建立測試 table */

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

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



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

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


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

create or replace procedure sp_proc1
specific sp_proc1
language sql
begin
　　DECLARE SQLCODE INTEGER DEFAULT 0;
　　DECLARE retcode INTEGER DEFAULT 0;

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

　　insert into test values (0,'ETL開始日期'||char(current date));
　　if (retcode <> 0) and (retcode <> 100) then
　　　　rollback;
　　　　call sp_inslog(char(retcode) || ' 流程0執行失敗');
　　else
　　　　call sp_inslog('流程0執行成功');
　　end if;

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

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

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

　傳回狀態 = 0

/* 看結果 */

C:\>db2 select * from test

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

　　已選取 2 個記錄。

C:\>db2 select * from autonomous_tab

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

　　已選取 2 個記錄。


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

create or replace procedure sp_proc1
specific sp_proc1
language sql
begin
　　　DECLARE SQLCODE INTEGER DEFAULT 0;
　　　DECLARE retcode INTEGER DEFAULT 0;

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

　　　insert into test values (0,'ETL開始日期'||char(current date));
　　　if (retcode <> 0) and (retcode <> 100) then
　　　　　　rollback;
　　　　　　call sp_inslog(char(retcode) || ' 流程0執行失敗');
　　　else
　　　　　　call sp_inslog('流程0執行成功');
　　　end if;

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

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

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

　傳回狀態 = 0


C:\>db2 select * from test

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

　　已選取 0 個記錄。


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

C:\>db2 select * from autonomous_tab

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

已選取 2 個記錄。


Performance of Merge statement using DB2 Express-C 9.7

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

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

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

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

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


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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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


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

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

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

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

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



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

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

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

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

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


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

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


/* 建立測試環境 */

C:\>db2 connect to orion

資料庫連線資訊

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

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

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

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

C:\>db2 connect to taurus

　　資料庫連線資訊

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

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

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

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


/* 建立對應Nickname */

C:\>db2 connect to orion

　　資料庫連線資訊

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

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

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

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

 
/* 產生錯誤訊息 */

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

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

C:\>db2 connect to orion

　　資料庫連線資訊

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

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

 
/* 依舊失敗 */

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

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

 
/* 再建在 Taurus 上試試 */

C:\>db2 connect to taurus

　　資料庫連線資訊

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

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


/* 回到 Database Orion 再試 */

C:\>db2 connect to orion

　　資料庫連線資訊

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

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


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

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



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

1
-------
　　200.

　　已選取 1 個記錄。

 

/* 掐頭去尾的 char function */

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

1
-------
200

　　已選取 1 個記錄。


/* 使用 char_old */

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

1
-------
00200.

　　已選取 1 個記錄。



DB2 Express-C 9.7 GUI Chinese characters does not show properly

/*
　　DB2 Express-C 9.7安裝後 GUI 的 menu 字體成了亂碼
　　本來以為會是\IBM\SQLLIB\java\jdk\jre\lib下的fontconfig.properties.src有關
　　結果，內容被我改得面目全非卻還是不見效...
　　沒想到原來解法是這麼簡單
*/


/*　
　控制中心等GUI字體設定

　Reference: IBM DB2 控制中心等圖形工具在 Windows 下的字體設置
　http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0703caojx/


　點選「工具設定」後，選擇「字型」，接著
　將「功能表(M)」的字型下拉式選單設為對話，
　關閉控制中心，接著再開起即可
*/




/*　
　配置輔助程式GUI中文字體無法正常顯示

　Reference:IZ01055: DB2 MESSAGE WINDOW DOES NOT SHOW SOME OF THE
　　　　　　CHINESE CHARACTERS PROPERLY
　　　　　　WHEN CONFIGURATION ASSISTANT IS OPENED AS 'DB2CA'.
　http://www-01.ibm.com/support/docview.wss?uid=swg1IZ01055

　把\IBM\SQLLIB\bin\下的db2ca.bat先做個備份，接著再用以下內容取代即可

*/


set RUNTIME_FLAGS=-Xmx128m -Xms8m -Xquickstart -Xgcpolicy:optavgpause

IF "%1" == "wait" GOTO WAIT
IF "%1" == "-ic" GOTO WSWB
　db2javit -j:CC -s: -i: -l: -o:"%RUNTIME_FLAGS%" -a:"-ca %2 %3 %4 %5 %6 %7 %8"
　GOTO END

:WAIT
　db2javit -j:CC -s: -w: -i: -l: -o:"%RUNTIME_FLAGS%" -a:"-ca %3 %4 %5 %6 %7 %8 %9"
　GOTO END

:WSWB
　db2icdocs.exe
　GOTO END

:end


Grant Load authority using DB2 Express-C 9.7

/*
　　好不容易終於拿到 DB2 Express-C 9.7
　　灌好之後才發現原來它是這麼令人陌生...(是我假放太久了嗎?)
　　一是控制中心的選單字體全成了亂碼，二是權限異動頗大
　　到現在還是搞不清楚。不過先把弄出來的做一下紀錄...

　　在什麼都沒設定之下執行 load，就會有以下錯誤:
　　SQL0552N "ADMINISTRATOR" 沒有執行作業 "LOAD" 的專用權。 SQLSTATE=42502

　　原來 LOAD 權限已經不再是預設就有的，因此權限設定上還真麻煩，
　　特別是我只是單純用本機的 Administrator 去做測試
*/


/*　檢查本機使用者和群組設定　*/

「開始」→「程式集」→「系統管理工具」→「電腦管理」
展開「系統工具」接著再展開「本機使用者和群組」
點選「群組」。檢查是否存在名稱為 DB2ADMNS 的群組。
再點選「使用者」。將 Administrator 「成員隸屬」的選單新增DB2ADMNS群組。


/*　建自己的 DB　*/

不使用「DB2安裝」所建立的DB:SAMPLE，原因是，
我還沒Try出來到底怎麼使Administrator有執行grant的專用權。
另外我也還沒辦法用localsystem account去logon。

如果我要Administrator有SECADM (Manages security within a database)的權限，
目前所能做的就是使 Administrator 成為 db 的 creator。


C:\>db2start
SQL1063N DB2START 處理成功。

C:\>db2 create database orion
DB20000I CREATE DATABASE 指令已順利完成。


C:\>db2 connect to orion

　　資料庫連線資訊

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


/*　檢查有哪些權限　*/
C:\>db2 get authorizations



　　現行使用者的管理權限

　直接 SYSADM 權限　　　　　　　= NO
　直接 SYSCTRL 權限　　　　　　　= NO
　直接 SYSMAINT 權限　　　　　　= NO
　直接 DBADM 權限　　　　　　　　= YES
　直接 CREATETAB 權限　　　　　　= NO
　直接 BINDADD 權限　　　　　　　= NO
　直接 CONNECT 權限　　　　　　　= NO
　直接 CREATE_NOT_FENC 權限　　= NO
　直接 IMPLICIT_SCHEMA 權限　　 = NO
　直接 LOAD 權限　　　　　　　　　= NO
　直接 QUIESCE_CONNECT 權限　　　　= NO
　直接 CREATE_EXTERNAL_ROUTINE 權限 = NO
　直接 SYSMON 權限　　　　　　　　　　= NO

　間接 SYSADM 權限　　　　　　　　= NO
　間接 SYSCTRL 權限　　　　　　　　= YES
　間接 SYSMAINT 權限　　　　　　　= YES
　間接 DBADM 權限　　　　　　　　= NO
　間接 CREATETAB 權限　　　　　　= YES
　間接 BINDADD 權限　　　　　　　= YES
　間接 CONNECT 權限　　　　　　　= YES
　間接 CREATE_NOT_FENC 權限　　　= NO
　間接 IMPLICIT_SCHEMA 權限　　　= YES
　間接 LOAD 權限　　　　　　　　　= NO
　間接 QUIESCE_CONNECT 權限　　　= NO
　間接 CREATE_EXTERNAL_ROUTINE 權限　= NO
　間接 SYSMON 權限　　　　　　　　　　= YES


/*　自己不能grant自己　*/
C:\>db2 grant load on database to administrator
DB21034E 指令被當作 SQL 陳述式處理，因為它不是有效的「指令行處理器」指令。
在SQL 處理程序期間，它已傳回：
SQL0554N 授權 ID 無法授與自身專用權或權限。 SQLSTATE=42502

/*　grant權限給群組　*/

C:\>db2 grant load on database to db2admns
DB20000I SQL 指令已順利完成。

C:\>db2 create table t1 (c1 integer)
DB20000I SQL 指令已順利完成。

/*　再檢查一下權限　*/

C:\>db2 get authorizations

　　現行使用者的管理權限

　直接 SYSADM 權限　　　　　　　= NO
　直接 SYSCTRL 權限　　　　　　　= NO
　直接 SYSMAINT 權限　　　　　　= NO
　直接 DBADM 權限　　　　　　　　= YES
　直接 CREATETAB 權限　　　　　　= NO
　直接 BINDADD 權限　　　　　　　= NO
　直接 CONNECT 權限　　　　　　　= NO
　直接 CREATE_NOT_FENC 權限　　= NO
　直接 IMPLICIT_SCHEMA 權限　　 = NO
　直接 LOAD 權限　　　　　　　　　= NO
　直接 QUIESCE_CONNECT 權限　　　　= NO
　直接 CREATE_EXTERNAL_ROUTINE 權限 = NO
　直接 SYSMON 權限　　　　　　　　　　= NO

　間接 SYSADM 權限　　　　　　　　= NO
　間接 SYSCTRL 權限　　　　　　　= YES
　間接 SYSMAINT 權限　　　　　　= YES
　間接 DBADM 權限　　　　　　　　= NO
　間接 CREATETAB 權限　　　　　= YES
　間接 BINDADD 權限　　　　　　= YES
　間接 CONNECT 權限　　　　　　　= YES
　間接 CREATE_NOT_FENC 權限　　　= NO
　間接 IMPLICIT_SCHEMA 權限　　　= YES

　間接 LOAD 權限　　　　　　　　　　= YES

　間接 QUIESCE_CONNECT 權限　　　　　= NO
　間接 CREATE_EXTERNAL_ROUTINE 權限 = NO
　間接 SYSMON 權限　　　　　　　　　　= YES


/*　測試load　*/

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

SQL3109N 公用程式開始自檔案 "C:\t1.del" 載入資料。

SQL3500W 公用程式在 "2009-08-04 20:27:16.489660" 時開始 "LOAD" 階段。

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

SQL3520W 成功載入「一致點」。

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

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

SQL3520W 成功載入「一致點」。

SQL3515W 公用程式已在 "2009-08-04 20:27:16.700221" 時完成 "LOAD" 階段。

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


Use Least function - Oracle 10g

/*
　　Scenario:
　　User 說，正常的情況下Column A 的值要>= Column B 且
　　　　　　　　　　　　　Column B 的值要>= Column C 且
　　　　　　　　　　　　　Column C 的值要>= Column D
　　User 要找出不正常的資料。

　　My solution: 使用Least Function
　　Least((A-B),(B-C),(C-D)) < 0
*/

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


create table test
(
　　col_id varchar2(5),
　　col_a integer,
　　col_b integer,
　　col_c integer,
　　col_d integer
);

SQL> insert into test values ('00001',4,3,2,1);
已建立 1 個資料列.
SQL> insert into test values ('00002',9,1,2,5);
已建立 1 個資料列.
SQL> insert into test values ('00003',7,5,5,5);
已建立 1 個資料列.
SQL> insert into test values ('00004',3,4,9,5);
已建立 1 個資料列.
SQL> commit;

 
/*　找非正常的資料　*/


select * from test
where least((col_a-col_b),(col_b - col_c),(col_c - col_d)) < 0;

COL_ID　　　COL_A　　　COL_B　　　　COL_C　　　COL_D
---------- ---------- ---------- ---------- ----------
00002　　　　　　　　　9　　　　　1　　　　　　2　　　　　　5
00004　　　　　　　　　3　　　　　4　　　　　　9　　　　　　5


Related Hints using Oracle 10g

/*
　　過去，我幾乎沒有使用過 Oracle Related Hints 去改善 sql 的 performance，
　　一直到我們家熊熊說，在他開發的專案裡，測試效果可以從十幾分鐘降到不到一分鐘...
*/


/* 試試 Set Autotrace On 是否可使用 */
SQL> set autotrace on

/* 假使出現以下錯誤訊息
　　1.改以 dba 身份登入
　　2.執行 plustrce.sql
　　3.grant PLUSTRACE role
*/

SP2-0618: 找不到階段作業 ID. 請檢查是否啟用 PLUSTRACE 角色
SP2-0611: 啟動 STATISTICS 報表時發生錯誤

SQL> connect / as sysdba
已連線.

SQL> @C:\oracle\product\10.2.0\db_1\sqlplus\admin\plustrce.sql
SQL> disconnect
已切斷與 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 的連線

SQL> grant plustrace to donkey;
順利授權.
SQL> connect donkey/donkey
已連線.
SQL> set autotrace on


/*　建立測試 table 與資料 */


create table countings
(
　　col1 integer not null,
　　col2 integer not null,
　　col3 varchar2(32)
);

insert into countings
select x,y,to_char(x) || ' x ' || to_char(y) || ' = ' || to_char(x*y)
from
(select level x from dual connect by level <= 1000) t,
(select level y from dual connect by level <= 1000) t1;

alter table countings add constraint countings_pk primary key (col1,col2);


/*　使用 traceonly 不列出 query 資料 */
SQL> set autotrace traceonly

/*　加 Related Hints 告訴 Oracle 不要使用 index */

SQL> select /*+ full(countings) */ *
　2　from countings
　3　where col1 = 555;

已選取 1000 個資料列.

執行計畫
----------------------------------------------------------
Plan hash value: 2354059736

-------------------------------------------------------------------------------
|　Id　|　Operation　　　　　| Name　　　| Rows　| Bytes | Cost (%CPU)| Time　　　|
-------------------------------------------------------------------------------
|　　0 | SELECT STATEMENT |　　　　　　 |　1000 | 44000 |　991　　(5)| 00:00:12 |
|*　 1 | TABLE ACCESS FULL | COUNTINGS |　1000 | 44000 |　991　　(5)| 00:00:12 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
　　　　　　　　　　　　　　　　　1 - filter("COL1"=555)

Note
-----
　- dynamic sampling used for this statement

統計值
----------------------------------------------------------
　　　　　　7　recursive calls
　　　　　　0　db block gets
　　　　 4489　consistent gets
　　　　　　0　physical reads
　　　　　　0　redo size
　　　　36769　bytes sent via SQL*Net to client
　　　　 1111　bytes received via SQL*Net from client
　　　　　 68　SQL*Net roundtrips to/from client
　　　　　　0　sorts (memory)
　　　　　　0　sorts (disk)
　　　　 1000　rows processed


/*　加 Related Hints 告訴 Oracle 使用 index */

SQL> select /*+ index(countings) */ *
　2　from countings
　3　where col1 = 555;

已選取 1000 個資料列.

執行計畫
----------------------------------------------------------
Plan hash value: 3805481017

--------------------------------------------------------------------------------------------
|　Id　|Operation　　　　　　　　　　| Name 　　　　| Rows | Bytes | Cost (%CPU)| Time　　　　　|
--------------------------------------------------------------------------------------------
|　0　|SELECT STATEMENT　　　　　　　|　　　　　　　|　1000 | 44000 |　　10　　(0)| 00:00:01 |
|　1　|TABLE ACCESS BY INDEX ROWID |COUNTINGS　　|　1000 | 44000 |　　10　　(0)| 00:00:01 |
|*　2|INDEX RANGE SCAN　　　　　　　|COUNTINGS_PK　|　1000 |　　　　|　　5　　(0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
　　2 - access("COL1"=555)

Note
-----
　　- dynamic sampling used for this statement

統計值
----------------------------------------------------------
　　　　　　7 recursive calls
　　　　　　0 db block gets
　　　　　221 consistent gets
　　　　　　0 physical reads
　　　　　　0 redo size
　　　　40712 bytes sent via SQL*Net to client
　　　　 1111 bytes received via SQL*Net from client
　　　　　 68 SQL*Net roundtrips to/from client
　　　　　　0 sorts (memory)
　　　　　　0 sorts (disk)
　　　　 1000 rows processed


Create a thesaurus to expand search terms with NSE 9.5

/*
　　Net Search Extender 也可以對 Varchar 型態做 Search。
　　建立詞典 Thesaurus 後，也可以藉此查出相關聯或者存在同義詞的資料出來
　　※ 中文 workable
*/

/* 建立測試 Table*/

CREATE TABLE HEADLINE
(
　　SNO　INTEGER NOT NULL PRIMARY KEY,
　　TXT　VARCHAR(128)
);

/* 新增與 Alonso,西班牙相關的資料 */

INSERT INTO HEADLINE VALUES
(1,'Focused Alonso sets sights on title')
,(2,'Renault expect to challenge again')
,(3,'Cost-cutting plan agreed for F1')
,(4,'Discovery of a Sunken Armada from the Golden Age of Piracy')
,(5,'Pasties challenge paella')
,(6,'Matador aged 11 kills six bulls')
,(7,'Fernando Alonso wins the Singapore Grand Prix');

/* 啟動 NSE Service */

db2text start

/* 建立 index 在 headline 的 txt 欄位上 */

db2text create index headline_idx for text on headline(txt)

/* refresh index */

db2text update index headline_idx for text


/* 建立詞典，存放在sqllib\db2ext\下，名稱給定為 nsesamplethes.def */

:WORDS
　　Alonso
　.RELATED_TO F1
　.RELATED_TO Renault
　.RELATED_TO Spain
　.SYNONYM_OF Fernando Alonso

:WORDS
　　Spain
　.RELATED_TO Matador
　.RELATED_TO paella
　.RELATED_TO Armada



/*
　　Compile nsesamplethes.def
　　1. 用db2extth compile
　　2. 1208 為 UTF-8
　　3. -t compile　產生的檔案所放置的路徑
*/

C:\Program Files\IBM\SQLLIB\db2ext>db2extth -f nsesamplethes.def -ccsid 1208 -t thes\

/* compile 詞典後產生檔案，如圖 */


/* 查詢與Alonso有關的資料 */

db2 => select sno,txt from headline \
db2 (續) => => where contains(txt,'THESAURUS "nsesamplethes" \
db2 (續) => => EXPAND RELATED TERM OF "alonso"') > 0

SNO TXT
--- ---------------------------------------------
　1 Focused Alonso sets sights on title
　2 Renault expect to challenge again
　3 Cost-cutting plan agreed for F1
　7 Fernando Alonso wins the Singapore Grand Prix

　　已選取 4 個記錄。

/* 查詢與Alonso同義的資料 */

db2 => select sno,txt from headline \
db2 (續) => => where contains(txt,'THESAURUS "nsesamplethes"
db2 (續) => => EXPAND SYNONYM TERM OF "alonso"') > 0

SNO TXT
--- ---------------------------------------------
　1 Focused Alonso sets sights on title
　7 Fernando Alonso wins the Singapore Grand Prix

　　已選取 2 個記錄。


/* 查詢與spain相關的資料 */

db2 => select sno,txt from headline \
db2 (續) => => where contains(txt,'THESAURUS "nsesamplethes"
db2 (續) => => EXPAND RELATED TERM OF "spain"') > 0

SNO TXT
--- ---------------------------------------------
　1 Focused Alonso sets sights on title
　4 Discovery of a Sunken Armada from the Golden Age of Piracy
　5 Pasties challenge paella
　6 Matador aged 11 kills six bulls
　7 Fernando Alonso wins the Singapore Grand Prix

　　已選取 5 個記錄。


/* 查詢與spain同義的資料 */

db2 => select sno,txt from headline \
db2 (續) => => where contains(txt,'THESAURUS "nsesamplethes"
db2 (續) => => EXPAND SYNONYM TERM OF "spain"') > 0

SNO TXT
--- ---------------------------------------------

　　已選取 0 個記錄。


Meaningful numeric expression using DB2 9

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

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

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

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

/* 測試 0.125040000000000E+010 */

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

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

/* 測試 1.39080000000000E+010 */

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

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

Few notes of using identity column and identity_val_local function - DB2

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

/*　1) Create Table　*/

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

/*　Insert data　*/

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

/*　自動產生 seqid　*/

C:\>db2 select * from identity_log

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

　　已選取 1 個記錄。

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

C:\>db2 values identity_val_local()

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

　　已選取 1 個記錄。

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

C:\>db2 connect to sample

　　資料庫連線資訊

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

C:\>db2 values identity_val_local()

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

　　已選取 1 個記錄。

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

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


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

C:\>db2 values identity_val_local()

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

　　已選取 1 個記錄。

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

C:\>db2 select * from identity_log

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

　　已選取 2 個記錄。

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

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

已匯出的列數：2

/*　清空 Table　*/

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

/*　Modified by identityoverride　*/

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

C:\>db2 select * from identity_log

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

　　已選取 2 個記錄。

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

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

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

C:\>db2 select * from identity_log

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

　　已選取 1 個記錄。


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 程序.