1. DB2 編程
1.1 建存儲過程時 Create? 后一定不要用 TAB 鍵
create procedure
的 create 后只能用空格 , 而不可用 tab 健，否則編譯會通不過。
切記，切記。

1.2 使用臨時表

?? 要注意，臨時表只能建在 user?tempory?tables?space? 上，如果 database 只有 system?tempory?table?space 是不能建臨時表的。
?? 另外， DB2 的臨時表和 sybase 及 oracle 的臨時表不太一樣， DB2 的臨時表是在一個 session 內有效的。所以，如果程序有多線程，最好不要用臨時表，很難控制。
??? 建臨時表時最好加上 ??with??replace 選項，這樣就可以不顯示的 drop? 臨時表，建臨時表時如果不加該選項而該臨時表在該 session 內已創建且沒有 drop, 這時會發生錯誤。
1.3 從數據表中取指定前幾條記錄
select??*??from?tb_market_code?fetch?first?1?rows?only

但下面這種方式不允許
select?market_code?into?v_market_code?
????????from?tb_market_code?fetch?first?1?rows?only;?????
????
選第一條記錄的字段到一個變量以以下方式代替
????declare?v_market_code?char(1);
????declare?cursor1?cursor?for?select?market_code?from?tb_market_code?
fetch?first?1?rows?only?for?update;
????open?cursor1;
????fetch?cursor1?into?v_market_code;
????close?cursor1;

1.4 游標的使用
注意 commit 和 rollback
使用游標時要特別注意如果沒有加 with?hold? 選項 , 在 Commit 和 Rollback 時 , 該游標將被關閉。 Commit? 和 Rollback 有很多東西要注意。特別小心

游標的兩種定義方式
一種為
declare?continue?handler?for?not?found
???begin
?????set?v_notfound?=?1;
???end;

declare?cursor1?cursor?with?hold?for?select?market_code?from?tb_market_code? for?update;
open?cursor1;
set?v_notfound=0;
fetch?cursor1?into?v_market_code;
while?v_notfound=0?Do
--work
set?v_notfound=0;
fetch?cursor1?into?v_market_code;
end?while;
close?cursor1;
這種方式使用起來比較復雜，但也比較靈活。特別是可以使用 with?hold? 選項。如果循環內有 commit 或 rollback? 而要保持該 cursor 不被關閉，只能使用這種方式。

另一種為
????? pcursor1:?for?loopcs1?as??cousor1??cursor??as
select??market_code??as?market_code
???????????from?tb_market_code
???????????for?update
????????do
????????end?for;
??????? 這種方式的優點是比較簡單，不用（也不允許）使用 open,fetch,close 。
?? 但不能使用 with??hold? 選項。如果在游標循環內要使用 commit,rollback 則不能使用這種方式。如果沒有 commit 或 rollback 的要求，推薦使用這種方式 ( 看來 For 這種方式有問題 ) 。

修改游標的當前記錄的方法
update?tb_market_code?set?market_code='0'?where?current?of?cursor1;
不過要注意將 cursor1 定義為可修改的游標
??declare?cursor1?cursor?for?select?market_code?from?tb_market_code?
for?update;

for?update? 不能和 GROUP?BY 、 ?DISTINCT 、 ?ORDER?BY 、 ?FOR?READ?ONLY 及 UNION,?EXCEPT,?or?INTERSECT 但 ?UNION?ALL 除外）一起使用。



1.5 類似 decode 的轉碼操作
oracle 中有一個函數 ?select?decode(a1,'1','n1','2','n2','n3')?aa1?from
db2 沒有該函數，但可以用變通的方法
select?case?a1?
when?'1'?then?'n1'?
when?'2'?then?'n2'?
else?'n3'
? ??end?as?aa1?from

1.6 類似 charindex 查找字符在字串中的位置
Locate(‘y’,’dfdasfay’)
查找 ’y’? 在 ’dfdasfay’ 中的位置。

1.7 類似 datedif 計算兩個日期的相差天數
days(date(‘2001-06-05’))?–?days(date(‘2001-04-01’))
days? 返回的是從 ??0001-01-01? 開始計算的天數
1.8 寫 UDF 的例子
C 寫見 sqllib\samples\cli\udfsrv.c

1.9 創建含 identity 值 ( 即自動生成的 ID) 的表
建這樣的表的寫法
CREATE?TABLE?test
?????(t1?SMALLINT?NOT?NULL
????????GENERATED?ALWAYS?AS?IDENTITY
????????(START?WITH?500,?INCREMENT?BY?1),
??????t2?CHAR(1));
在一個表中只允許有一個 identity 的 column.

?

1.10 預防字段空值的處理
SELECT?DEPTNO?,DEPTNAME?,COALESCE(MGRNO?,'ABSENT'),ADMRDEPT
FROM?DEPARTMENT
???COALESCE 函數返回 () 中表達式列表中第一個不為空的表達式，可以帶多個表達式。
??? 和 sqlserver 的 isnull 類似，但 isnull 好象只能兩個表達式； oracle 為 NVL 。
?????

1.11 取得處理的記錄數
declare?v_count?int;
update tb_test set t1=’0’
where t2=’2’;
-- 檢查修改的行數 , 判斷指定的記錄是否存在
get?diagnostics v_?count=ROW_COUNT;?????
只對 update,insert,delete 起作用 .
不對 select?into? 有效


1.12 從存儲過程返回結果集(游標)的用法
1.12.1 建一 sp 返回結果集
CREATE?PROCEDURE?DB2INST1.Proc1?(??)
????LANGUAGE?SQL
????result?sets?2( 返回兩個結果集 )
------------------------------------------------------------------------
--?SQL? 存儲過程 ?
------------------------------------------------------------------------
P1:?BEGIN
????????declare?c1?cursor??with?return?to?caller?for?
????????????select??market_code
????????????from????tb_market_code;
????????-- 指定該結果集用于返回給調用者
????????declare?c2?cursor??with?return?to?caller?for?
????????????select??market_code
????????????from????tb_market_code;
?????????open?c1;
?????????open?c2;
END?P1???????????????????????????????????????


1.12.2 建一 SP 調該 sp 且使用它的結果集

CREATE?PROCEDURE?DB2INST1.Proc2?(
out?out_market_code?char(1))
????LANGUAGE?SQL
------------------------------------------------------------------------
--?SQL? 存儲過程 ?
------------------------------------------------------------------------
P1:?BEGIN

?declare?loc1,loc2?result_set_locator?varying;?
-- 建立一個結果集數組
call?proc1;
-- 調用該 SP 返回結果集。
associate?result?set?locator(loc1,loc2)?with?procedure?proc1;
-- 將返回結果集和結果集數組關聯
?allocate?cursor1?cursor?for?result?set?loc1;
?allocate?cursor2?cursor?for?result?set?loc2;
-- 將結果集數組分配給 cursor
fetch??cursor1?into?out_market_code;
-- 直接從結果集中賦值
close?cursor1;?????????

END?P1

1.12.3 動態 SQL 寫法
?????DECLARE?CURSOR?C1?FOR?STMT1;?
?????PREPARE?STMT1?FROM
????????'ALLOCATE?C2?CURSOR?FOR?RESULT?SET??';
1.12.4 注意：
(1) 如果一個 sp 調用好幾次，只能取到最近一次調用的結果集。 ?
(2) allocate 的 cursor 不能再次 open ，但可以 close ，是 close?sp 中的對應 cursor 。

1.13 類型轉換函數
select?cast?(?current?time?as?char(8))?from?tb_market_code

1.14 存儲過程的互相調用
目前 ,c?sp 可以互相調用。
Sql?sp? 可以互相調用，
Sql?sp? 可以調用 C?sp ，
但 C?sp? 不可以調用 Sql?sp( 最新的說法是可以 )

1.15 C 存儲過程參數注意
create?procedure?pr_clear_task_ctrl(
IN?IN_BRANCH_CODE?char(4),
??????????????IN?IN_TRADEDATE???char(8),
?????????? IN?IN_TASK_ID?????char(2),
???????IN?IN_SUB_TASK_ID?char(4),
???????OUT?OUT_SUCCESS_FLAG?INTEGER?)
?
DYNAMIC?RESULT?SETS?0
LANGUAGE?C?
PARAMETER?STYLE?GENERAL?WITH?NULLS( 如果不是這樣， sql? 的 sp 將不能調用該用 c 寫的存儲過程，產生保護性錯誤 )
NO?DBINFO
FENCED
MODIFIES?SQL?DATA
EXTERNAL?NAME?'pr_clear_task_ctrl!pr_clear_task_ctrl'@

?

1.16 存儲過程 fence 及 unfence
fence 的存儲過程單獨啟用一個新的地址空間 , 而 unfence 的存儲過程和調用它的進程使用同一個地址空間。
一般而言， fence 的存儲過程比較安全。
但有時一些特殊的要求，如要取調用者的 pid ，則 fence 的存儲過程會取不到，而只有 unfence 的能取到。

1.17 SP 錯誤處理用法
如果在 SP 中調用其它的有返回值的，包括結果集、臨時表和輸出參數類型的 SP ，
DB2 會自動發出一個 SQLWarning 。而在我們原來的處理中對于 SQLWarning 都
會插入到日志，這樣子最后會出現多條 SQLCODE=0 的警告信息。
處理辦法：
定義一個標志變量，比如 DECLARE?V_STATUS?INTEGER?DEFAULT?0,
在 CALL?SPNAME 之后 ,?SET?V_STATUS?=?1,
DECLARE?CONTINUE?HANDLER?FOR?SQLWARNING
BEGIN
IF?V_STATUS?<>?1?THEN
-- 警告處理，插入日志
SET?V_STATUS?=?0;
END?IF;
END;
1.18 import 用法
db2?import??from??gh1.out???of??DEL?messages?err.txt?insert?into??db2inst1.tb_dbf_match_ha

注意要加 schma

1.19 values 的使用
如果有多個 ?set?? 語句給變量付值，最好使用 values 語句，改寫為一句。這樣可以提高效率。
?
但要注意， values 不能將 null 值付給一個變量。
values(null)?into?out_return_code;
這個語句會報錯的。


1.20 給 select? 語句指定隔離級別
select?*?from?tb_head_stock_balance?with?ur
?
1.21 atomic 及 not?atomic 區別
atomic 是將該部分程序塊指定為一個整體 , 其中任何一個語句失敗 , 則整個程序塊都相當于沒做 , 包括包含在 atomic 塊內的已經執行成功的語句也相當于沒做，有點類似于 transaction 。

1.22 日期和時間的使用
要使用 SQL 獲得當前的日期、時間及時間戳記，請參考適當的 DB2 寄存器：

SELECT current date FROM sysibm.sysdummy1

SELECT current time FROM sysibm.sysdummy1

SELECT current timestamp FROM sysibm.sysdummy1

sysibm.sysdummy1 表是一個特殊的內存中的表，用它可以發現如上面演示的 DB2 寄存器的值。您也可以使用關鍵字 VALUES 來對寄存器或表達式求值。例如，在 DB2 命令行處理器（ Command Line Processor ， CLP ）上，以下 SQL 語句揭示了類似信息：

VALUES current date

VALUES current time

VALUES current timestamp

在余下的示例中，我將只提供函數或表達式，而不再重復 SELECT ... FROM sysibm.sysdummy1 或使用 VALUES 子句。

要使當前時間或當前時間戳記調整到 GMT/CUT ，則把當前的時間或時間戳記減去當前時區寄存器：

current time - current timezone

current timestamp - current timezone

給定了日期、時間或時間戳記，則使用適當的函數可以單獨抽取出（如果適用的話）年、月、日、時、分、秒及微秒各部分：

YEAR (current timestamp)

MONTH (current timestamp)

DAY (current timestamp)

HOUR (current timestamp)

MINUTE (current timestamp)

SECOND (current timestamp)

MICROSECOND (current timestamp)

從時間戳記單獨抽取出日期和時間也非常簡單：

DATE (current timestamp)

TIME (current timestamp)

因為沒有更好的術語，所以您還可以使用英語來執行日期和時間計算：

current date + 1 YEAR

current date + 3 YEARS + 2 MONTHS + 15 DAYS

current time + 5 HOURS - 3 MINUTES + 10 SECONDS

要計算兩個日期之間的天數，您可以對日期作減法，如下所示：

days (current date) - days (date('1999-10-22'))

而以下示例描述了如何獲得微秒部分歸零的當前時間戳記：

CURRENT TIMESTAMP - MICROSECOND (current timestamp) MICROSECONDS

如果想將日期或時間值與其它文本相銜接，那么需要先將該值轉換成字符串。為此，只要使用 CHAR() 函數：

char(current date)

char(current time)

char(current date + 12 hours)

要將字符串轉換成日期或時間值，可以使用：

TIMESTAMP ('2002-10-20-12.00.00.000000')

TIMESTAMP ('2002-10-20 12:00:00')

????????? DATE ('2002-10-20')

????????? DATE ('10/20/2002')

????????? TIME ('12:00:00')

????????? TIME ('12.00.00')

TIMESTAMP() 、 DATE() 和 TIME() 函數接受更多種格式。上面幾種格式只是示例，我將把它作為一個練習，讓讀者自己去發現其它格式。

?

?

?

?

?

?

?

?

?

?

?

?

?

日期函數

有時，您需要知道兩個時間戳記之間的時差。為此， DB2 提供了一個名為 TIMESTAMPDIFF() 的內置函數。但該函數返回的是近似值，因為它不考慮閏年，而且假設每個月只有 30 天。以下示例描述了如何得到兩個日期的近似時差：

timestampdiff (<n>, char(

????????? timestamp('2002-11-30-00.00.00')-

????????? timestamp('2002-11-08-00.00.00')))

對于 <n> ，可以使用以下各值來替代，以指出結果的時間單位：

• 1 = 秒的小數部分
• 2 = 秒
• 4 = 分
• 8 = 時
• 16 = 天
• 32 = 周
• 64 = 月
• 128 = 季度
• 256 = 年

當日期很接近時使用 timestampdiff() 比日期相差很大時精確。如果需要進行更精確的計算，可以使用以下方法來確定時差（按秒計）：

(DAYS(t1) - DAYS(t2)) * 86400 + ?

(MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2))

為方便起見，還可以對上面的方法創建 SQL 用戶定義的函數：

CREATE FUNCTION secondsdiff(t1 TIMESTAMP, t2 TIMESTAMP)

RETURNS INT

RETURN (

(DAYS(t1) - DAYS(t2)) * 86400 +?

(MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2))

)

@

如果需要確定給定年份是否是閏年，以下是一個很有用的 SQL 函數，您可以創建它來確定給定年份的天數：

CREATE FUNCTION daysinyear(yr INT)

RETURNS INT

RETURN (CASE (mod(yr, 400)) WHEN 0 THEN 366 ELSE

??????? CASE (mod(yr, 4))?? WHEN 0 THEN

??????? CASE (mod(yr, 100)) WHEN 0 THEN 365 ELSE 366 END

??????? ELSE 365 END

????????? END)@

最后，以下是一張用于日期操作的內置函數表。它旨在幫助您快速確定可能滿足您要求的函數，但未提供完整的參考。有關這些函數的更多信息，請參考 SQL 參考大全。

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

改變日期格式

在日期的表示方面，這也是我經常碰到的一個問題。用于日期的缺省格式由數據庫的地區代碼決定，該代碼在數據庫創建的時候被指定。例如，我在創建數據庫時使用 territory=US 來定義地區代碼，則日期的格式就會像下面的樣子：

values current date

1

----------

05/30/2003

?

1 record(s) selected.

也就是說，日期的格式是 MM/DD/YYYY. 如果想要改變這種格式，你可以通過綁定特定的 DB2 工具包來實現 . 其他被支持的日期格式包括 :

?

?

?

?

?

在 Windows 環境下，要將缺省的日期和時間格式轉化成 ISO 格式（ YYYY-MM-DD ），執行下列操作：

1.?????? 在命令行中，改變當前目錄為 sqllibbnd 。

例如 :
在 Windows 環境 : c:program filesIBMsqllibbnd
在 UNIX 環境 : /home/db2inst1/sqllib/bnd

2.?????? 從操作系統的命令行界面中用具有 SYSADM 權限的用戶連接到數據庫 :

3.???????????? db2 connect to DBNAME

4.???????????? db2 bind @db2ubind.lst datetime ISO blocking all grant public

( 在你的實際環境中， 用你的數據庫名稱和想使用的日期格式分別來替換 DBNAME and ISO 。 )

現在，你可以看到你的數據庫已經使用 ISO 作為日期格式了：

values current date

1

----------

2003-05-30

?

? 1 record(s) selected.

?

定制日期 / 時間格式

在上面的例子中，我們展示了如何將 DB2 當前的日期格式轉化成系統支持的特定格式。但是，如果你想將當前日期格式轉化成定制的格式（比如 ‘yyyymmdd’ ），那又該如何去做呢？按照我的經驗，最好的辦法就是編寫一個自己定制的格式化函數。

下面是這個 UDF 的代碼 :

create function ts_fmt(TS timestamp, fmt varchar(20))

returns varchar(50)

return

with tmp (dd,mm,yyyy,hh,mi,ss,nnnnnn) as

(

??? select

??? substr( digits (day(TS)),9),

??? substr( digits (month(TS)),9) ,

??? rtrim(char(year(TS))) ,

??? substr( digits (hour(TS)),9),

??? substr( digits (minute(TS)),9),

??? substr( digits (second(TS)),9),

??? rtrim(char(microsecond(TS)))

??? from sysibm.sysdummy1

??? )

select

case fmt

??? when 'yyyymmdd'

??? ????then yyyy || mm || dd

??? when 'mm/dd/yyyy'

??????? then mm || '/' || dd || '/' || yyyy

??? when 'yyyy/dd/mm hh:mi:ss'

??????? then yyyy || '/' || mm || '/' || dd || ' ' ||

?????????????? hh || ':' || mi || ':' || ss

??? when 'nnnnnn'

??????? then nnnnnn

??? else

??????? 'date format ' || coalesce(fmt,'? ') ||

??????? ' not recognized.'

??? end

from tmp

乍一看，函數的代碼可能顯得很復雜，但是在仔細研究之后，你會發現這段代碼其實非常簡單而且很優雅。最開始，我們使用了一個公共表表達式（ CTE ）來將一個時間戳記（第一個輸入參數）分別剝離為單獨的時間元素。然后，我們檢查提供的定制格式（第二個輸入參數）并將前面剝離出的元素按照該定制格式的要求加以組合。

這個函數還非常靈活。如果要增加另外一種模式，可以很容易地再添加一個 WHEN 子句來處理。在使用過程中，如果用戶提供的格式不符合任何在 WHEN 子句中定義的任何一種模式時，函數會返回一個錯誤信息。

使用方法示例：

values ts_fmt(current timestamp,'yyyymmdd')

?'20030818'

values ts_fmt(current timestamp,'asa')

?'date format asa not recognized.

?

?

2 ?DB2 編程性能注意
2.1 大數據的導表
應該是 export 后再 load 性能更好，因為 load 不寫日志。
比 select?into? 要好。

2.2 SQL 語句盡量寫復雜 SQL
??? 盡量使用大的復雜的 SQL 語句 , 將多而簡單的語句組合成大的 SQL 語句對性能會有所改善。
???DB2 的 SQL?Engieer 對復雜語句的優化能力比較強，基本上不用當心語句的性能問題。
Oracle? 則相反，推薦將復雜的語句簡單化， SQL?Engieer 的優化能力不是特別好。
這是因為每一個 SQL 語句都會有 reset?SQLCODE 和 SQLSTATE 等各種操作，會對數據庫性能有所消耗。
一個總的思想就是盡量減少 SQL 語句的個數。
2.3 SQL??SP 及 C?SP 的選擇
首先， C 的 sp 的性能比 sql? 的 sp? 的要高。
一般而言， SQL 語句比較復雜，而邏輯比較簡單， sql?sp? 與 ?c?sp? 的性能差異會比較小，這樣從工作量考慮，用 SQL 寫比較好。
而如果邏輯比較復雜， SQL 比較簡單，用 c 寫比較好。

2.4 查詢的優化 (HASH 及 RR_TO_RS)
db2set? DB2_HASH_JOIN=Y (HASH 排序優化 )
??? 指定排序時使用 HASH 排序，這樣 db2 在表 join 時，先對各表做 hash 排序，再 join ，這樣可以大大提高性能。
??? 劇沈剛說做實驗， 7 個一千萬條記錄表的做 join 取 10000 條記錄，再沒有索引的情況下 ??72 秒。

db2set? DB2_RR_TO_RS=Y???????
? 該設置后，不能定義 RR 隔離級別，如果定義 RR ， db2 也會自動降為 RS.
這樣， db2 不用管理 Next?key ，可以少管理一些東西，這樣可以提高性能。 ?????


2.5 避免使用 count(*)? 及 exists 的方法
1 、首先要避免使用 count(*) 操作，因為 count(*) 基本上要對表做全部掃描一遍，如果使用很多會導致很慢。
2 、 exists 比 count(*) 要快，但總的來說也會對表做掃描，它只是碰到第一條符合的記錄就停下來。

如果做這兩中操作的目的是為
???????select?into? 服務的話，就可以省略掉這兩步。
直接使用 select?into? 選擇記錄中的字段。

如果是沒有記錄選擇到的話， db2? 會將 ??sqlcode=100? 和 ?sqlstate=’20000’
如果是有多條記錄的話， db2 會產生一個錯誤。

程序可以創建 ??continue?handler?for??exception?
??????????????continue?handler?for??not?found
來檢測。
這是最快速的方法。

3 、如果是判斷是不是一條 , 可以使用游標來計算，用一個計數器，累加，達到預定值后就離開。這個速度也比 count(*)? 要快，因為它只要掃描到預定值就不再掃描了，不用做全表的 scan ，不過它寫起來比較麻煩。


3 DB2 表及 sp 管理
3.1 看存儲過程文本
select?text?from?syscat.procedures?where?procname='PROC1';
3.2 看表結構
describe?table?syscat.procedures
describe?select?*?from?syscat.procedures

3.3 查看各表對 sp 的影響 ( 被哪些 sp 使用 )
select?PROCNAME?from?SYSCAT.PROCEDURES?where?SPECIFICNAME?in(select?dname?from?sysibm.sysdependencies?where?bname?in?(?select?PKGNAME??from?syscat.packagedep?where?bname='TB_BRANCH'))

3.4 查看 sp 使用了哪些表
select?bname?from?syscat.packagedep?where?btype='T'?and?pkgname?in(select?bname?from?sysibm.sysdependencies?where?dname?in?(select?specificname?from?syscat.procedures?where?procname='PR_CLEAR_MATCH_DIVIDE_SHA'))
3.5 查看 function 被哪些 sp 使用
select?PROCNAME?from?SYSCAT.PROCEDURES?where?SPECIFICNAME?in(select?dname?from?sysibm.sysdependencies?where?bname?in?(?select?PKGNAME??from?syscat.packagedep?where?bname???in??(select?SPECIFICNAME?from?SYSCAT.functions?where?funcname='GET_CURRENT_DATE')))


使用 function 時要注意，如果想 drop? 掉該 function 必須要先將調用該 function 的其它存儲過程全部 drop 掉。
必須先創建 function ，調用該 function 的 sp 才可以創建成功。
3.6 修改表結構
一次給一個表增加多個字段
db2?"alter?table?tb_test?add?column?t1?char(1)?add?column?t2?char(2)?add?column?t3?int"


4 DB2 系統管理
4.1 DB2 安裝
?? 在 Windows?98? 下安裝 db2?7.1? 或其他版本，如果有 Jdbc 錯誤或者是 Windwos?98 不能啟動，則將 autoexec.bat? 中的內容用如下內容替換：


C:\PROGRA~1\TRENDP~1\PCSCAN.EXE?C:\?C:\WINDOWS\COMMAND\?/NS?/WIN95?
rem?C:\WINDOWS\COMMAND.COM?/E:32768
REM?[Header]

REM?[CD-ROM?Drive]

REM?[Miscellaneous]

REM?[Display]

set?PATH=%PATH%;C:\MSSQL\BINN;C:\PROGRA~1\SQLLIB\BIN;C:\PROGRA~1\SQLLIB\FUNCTION;C:\PROGRA~1\SQLLIB\SAMPLES\REPL;C:\PROGRA~1\SQLLIB\HELP
IF?EXIST?C:\PROGRA~1\IBM\IMNNQ\IMQENV.BAT?CALL?C:\PROGRA~1\IBM\IMNNQ\IMQENV.BAT
IF?EXIST?C:\PROGRA~1\IBM\IMNNQ\IMNENV.BAT?CALL?C:\PROGRA~1\IBM\IMNNQ\IMNENV.BAT
set?DB2INSTANCE=DB2
set?CLASSPATH=.;C:\PROGRA~1\SQLLIB\java\db2java.zip;C:\PROGRA~1\SQLLIB\java\runtime.zip;C:\PROGRA~1\SQLLIB\java\sqlj.zip;C:\PROGRA~1\SQLLIB\bin
set?MDIS_PROFILE=C:\PROGRA~1\SQLLIB\METADATA\PROFILES
set?LC_ALL=ZH_CN
set?INCLUDE=C:\PROGRA~1\SQLLIB\INCLUDE;C:\PROGRA~1\SQLLIB\LIB;C:\PROGRA~1\SQLLIB\TEMPLATES\INCLUDE
set?LIB=C:\PROGRA~1\SQLLIB\LIB
set?DB2PATH=C:\PROGRA~1\SQLLIB
set?DB2TEMPDIR=C:\PROGRA~1\SQLLIB
set?VWS_TEMPLATES=C:\PROGRA~1\SQLLIB\TEMPLATES
set?VWS_LOGGING=C:\PROGRA~1\SQLLIB\LOGGING
set?VWSPATH=C:\PROGRA~1\SQLLIB
set?VWS_FOLDER=IBM?DB2
set?ICM_FOLDER= 信息目錄管理器

win

4.2 創建 Database
create?database?head?using?codeset?IBM-eucCN?territory?CN;
這樣可以支持中文。


4.3 手工做數據庫遠程 ( 別名 ) 配置
db2??catalog?tcpip??node???node1??remote???172.28.200.200?server??50000
db2??catalog?db????head???as?????test1 at??node???node1

然后既可使用：
???db2?connect?to?test1??user?…??using?…
連上 head 庫了

4.4 停止啟動數據庫實例
db2start
db2stop?(force)


4.5 連接數據庫及看當前連接數據庫
連接數據庫
db2??connect?to?head?user?db2inst1??using?db2inst1

當前連接數據庫
db2??connect
4.6 停止啟動數據庫 head
db2??activate??db??head
db2??deactivate?db??head
要注意的是，如果有連接，使用 deactivate?db? 不起作用。
如果是用 activate?db 啟動的數據庫，一定要用 deactivate?db 才會停止該數據庫。（當然如果是 db2stop 也會停止）。
使用 activate?db ，這樣可以減少第一次連接時的等待時間。
Database 如果不是使用 activate?db 啟動而是通過連接數據庫而啟動的話，當所有的連接都退出后， db 也就自動停止。

4.7 查看及停止數據庫當前的應用程序
查看應用程序：
db2???list?? applications? show??detail?

授權標識 ?|? 應用程序名 ?|? 應用程序句柄 ?|?? 應用程序標識 ?|? 序號 #??|? 代理程序 ?|?? 協調程序 ?|? 狀態 ?|?? 狀態更改時間 ?|??DB? 名 ?|?DB? 路徑 |??????????????????????????????????????????????????????|???? 節點號 ?|???pid ／線程

其中：

1 、應用程序標識的第一部分是應用程序的 IP 地址，不過是已 16 進制表示的。
2 、 pid/ 線程即是在 unix 下看到的線程號。

停止應用程序：
db2?"force?application(236)"
db2?“force?application?all”

其中 : 該 236 是查看中的應用程序句柄。

4.8 查看本 instance 下有哪些 database
db2?LIST?DATABASE?DIRECTORY??[?on?/home/db2inst1?]
4.9 查看及更改數據庫 head 的配置
請注意，在大多數情況下，更改了數據的配置后，只有在所有的連接全部斷掉后才會生效。

查看數據庫 head 的配制
db2?get?db?cfg?for?head


更改數據庫 head 的某個設置的值
4.9.1 改排序堆的大小
db2?update?db?cfg?for?head?using?SORTHEAP?2048
將排序堆的大小改為 2048 個頁面，查詢比較多的應用最好將該值設置比較大一些。
4.9.2 改事物日志的大小
db2?update?db?cfg?for?head?using??logfilsiz??40000
該項內容的大小要和數據庫的事物處理相適應，如果事物比較大，應該要將該值改大一點。否則很容易處理日志文件滿的錯誤。

4.9.3 出現程序堆內存不足時修改程序堆內存大小
db2?update?db?cfg?for?head?using??applheapsz??40000
該值不能太小 , 否則會沒有足夠的內存來運行應用程序。

4.10 查看及更改數據庫實例的配置
查看數據庫實例配置
db2??get?dbm?cfg?
更改數據庫實例配制

4.10.1 打開對鎖定情況的監控。
db2?update?dbm?cfg?using?dft_mon_lock??on
4.10.2 更改診斷錯誤捕捉級別
db2?update?dbm?cfg?using?diaglevel 3
0? 為不記錄信息
1? 為僅記錄錯誤
2? 記錄服務和非服務錯誤
缺省是 3 ，記錄 db2 的錯誤和警告
4? 是記錄全部信息，包括成功執行的信息
一般情況下，請不要用 4 ，會造成 db2 的運行速度非常慢。

?

4.11 db2 環境變量
db2? 重裝后用如下方式設置 db2 的環境變量 , 以保證 sp 可編譯
將 set_cpl? 放到 AIX 上 ,?chmod?+x?set_cpl,? 再運行之

set_cpl 的內容
db2set?DB2_SQLROUTINE_COMPILE_COMMAND="xlc_r??-g?\
-I$HOME/sqllib/include?SQLROUTINE_FILENAME.c?\
-bE:SQLROUTINE_FILENAME.exp?-e?SQLROUTINE_ENTRY?\
-o?SQLROUTINE_FILENAME?-L$HOME/sqllib/lib?-lc?-ldb2"

db2set?DB2_SQLROUTINE_KEEP_FILES=1
4.12 db2 命令環境設置
db2=>list?command?options
db2=>update?command?options?using?C?off-- 或 on ，只是臨時改變
db2=>db2set?db2options=+c?-- 或 -c ，永久改變

4.13 改變隔離級別
DB2SET?DB2_SQLROUTINE_PREPOPTS=CS|RR|RS|UR

交互環境更改 session 的隔離級別，
???????db2?change?isolation??to?UR
請注意只有沒有連接數據庫時可以這樣來改變隔離級別。

4.14 管理 db\instance 的參數
get?db?cfg?for?head(db)
get?dbm?cfg(instance)

4.15 升級后消除版本問題
db2???bind??@db2ubind.lst
db2???bind???@db2cli.lst

4.16 查看數據庫表的死鎖
再用命令中心查詢數據時要注意 , 如果用了交互式查詢數據 , 命令中心將會給所查的記錄加了 s 鎖 . 這時如果要 update 記錄 , 由于 update 要使用 x 鎖 , 排它鎖 , 將會處于鎖等待 .

首先 , 將監視開關打開
db2?update?dbm?cfg?using?dft_mon_lock??on
快照
??db2?get?snapshot?for??Locks??on??cleardb???>snap.log
????????????????????tables?
bufferpools
tablespaces
database
??? 然后再看 snap.log 中的內容即可。
對 Lock 可根據 Application?handle （應用程序句柄）看每個應用程序的鎖的情況。
? 監視完畢后，不要忘了將監視器關閉
?????db2?update?dbm?cfg?using?dft_mon_lock??off

?

?

5. DB2 SQL 概述

5.1 模式

5.1.1 模式是已命名的對象（如表和視圖）的集合。模式提供了數據庫中對象的邏輯分類。

5.1.2 當在數據庫中創建對象的時候，系統就隱性的創建了模式。當然，也可以使用 CREATE SCHEMA 顯式的創建模式。

5.1.3 當命名對象的時候，需要注意對象的名稱有兩個部分，即，模式 . 對象名稱 , 形如： pjj.TempTable1 。如果不顯示指定模式，則系統使用默認模式（默認用戶的 ID ）。

5.2 數據類型

?

定長字符串　　　　　

CHAR(x) 　　　　　　　　　　　 x 值域（ 1 ～ 254 ）　　　　　　　一個字節序列

?

變長字符串　

VARCHAR(X)

LONG VARCHAR(X)

LOB( 大對象 )

?

定長圖形字符串　　

GRAPHCI(X) 　　　　　　　 x 值域（ 1 ～ 127 ）　　　　　　　兩個字節序列

?

變長圖形字符串　　

VARGRAPHCI(X)

LONG GRAPHCI(X)

DBCLOB( 大對象 )

? BLOB( 大對象 )

數字　　　　　　　　　　 ( 所有數字都有精度，精度是指除符號位以外的位數或者數字數 )

SMALLINT 　　　　　　　　　 精度為 5 　　　　　　　　　　　 2 字節整數

INTEGER 　　　　　　　　　　精度為 10 　　　　　　　　　　 4 字節整數

BIGINT 　　　　　　　　　　　　精度為 19 　　　　　　　　　　 8 字節整數

REAL 　　　　　　　　　　　　　實數的 32 位近似值

DOUBLE 　　　　　　　　　　 實數的 64 位近似值

DECIMAL(P,S) 　　　 P, 精度， S, 小數位數，十進制數， P 必須 <=32 ， S 必須 <=P ，缺省 :P=5,S=0

日期時間型　　　　　 14 位字符串，即非數字類型也非字符串類型

日期　　　　　　　　　　　 DATE 　　　　　　　　　　　　　年月日

時間　　　　　　　　　　　 TIME 　　　　　　　　　　　　　 24 小時制，分為 小時分鐘秒

時間戳記　　　　　　　 TIMESTAMP 　　　　　　　　 1 日期和時間的值，分為 年月日小時分鐘秒微秒

空值　　　　　　　　　　　 不同于任何非空值

?

5.3 其他

DB2 不區分大小寫（單引號或者雙引號內的內容除外）

?

?

5.4 創建表和視圖

?

5.4.1 創建表

?

CREATE TABLE PERS

(ID 　 SMALLINT 　　 NOT NULL,

NAME 　　　　　　　　　 VARCHAR(9),

DEPT 　　　　　　　　　　 SMALLINT 　　 WITH DEFAULT 10,

JOB 　　　　　　　　　　　 CHAR(5),

YEARS 　　　　　　　　　 SMALLINT,

SALARY 　　　　　　　　 DECIMAL(7,2),

COMM 　　　　　　　　　 DECIMAL(7,2),

BIRTH_DATE 　　　 DATE)

?

5.4.2 在表中插入值 ( 三種方式 )

?

INSERT INTO PERS

VALUES (12,'Harris',20,'Sales',5,18000,1000,'1950-1-1')

?

INSERT INTO PERS (NAME,JOB,ID)

VALUES 　　　 ('Swagerman','Pramr',500),('Limoges','Prgmr',510), 　　　　　　　　　　　　　　　 ('Li','Prgmr',520)

?

INSERT INTO PERS (ID,NAME,DEPT,JOB,YEARS,SALARY,COMM,BIRTH_DATE) SELECT ID,NAME,DEPT,JOB,YEARS,SALARY,COMM,BIRTH_DATE 　　　 FROM STAFF WHERE ID = 58

?

5.4.3 更新數據

?

UPDATE PERS SET JOB = 'Prgmr',SALARY = SALARY + 300 WHERE ID = 410

UPDATE PERS SET SALARY = SALARY * 1.15 WHERE JOB = 'Sales'

?

5.4.4 刪除數據

?

DELETE FROM PERS WHERE ID = 120

?

5.4.5 刪除表

?

DROP TABLE PERS

?

5.4.6 創建視圖

?

( 可以選用 WITH CHECK OPTION 選項，該選項針對 WHERE 的條件進行限定 )

CREATE VIEW STAFF_ONLY AS SELECT ID,NAME,DEPT,JOB,YEARS 　 FROM STAFF 　 WHERE JOB <> 'Mgr' AND DEPT = 20 WITH CHECK OPTION

?

?

5.5 使用 SQL 語句存取數據　

?

(? CREATE 顯示一般幫助提示信息 )

?

5.5.1 連接數據庫

?

CONNECT TO MYDB2 USER USERID USING PASSWORD

?

5.5.2 謂詞

?

　　　 x=y x<>y x<y x>y x>=y x<=y

?

　　　 IS NULL

?

　　　 IS NOT NULL

?

5.5.3 其他的一些去 SQL SERVER 類似的東西就省略

?

5.5.4 運算次序

?

　　　 FROM

?

　　　 WHERE

?

　　　 GROUP BY

?

　　　 HAVING

?

　　　 SELECT

?

　　　 ORDER BY

?

5.5.5 函數

5.5.5 .1 列函數

( 列函數對列中的一組值進行運算以得到單一的結果值！ )

?

　　　　　　　 AVG

?

　　　　　　　 COUNT

?

　　　　　　　 MAX

?

　　　　　　　 MIN

?

　　　　　　　

?

5.5. 5 .2 標量函數

?

( 標量函數對一個單一值進行運算以返回另一個單一的結果值！ )

?

　　　　　　　 ABS 　　　　　　 絕對值

?

　　　　　　　 HEX 　　　　　　 十六進制

?

　　　　　　　 LENGTH 　　　返回字節數（對于圖形字符串則返回雙字節字符串）

?

　　　　　　　 YEAR

?

5.5.5 .3 表函數

?

( 表函數僅可用于 FROM 子句，返回表的列！ )

?

5.6 表達式和子查詢

?

5.6.1 標量全查詢

?

( 返回一行，該行只包括一個值，用于從數據庫中檢索值 )

?

SELECT LASTNAME,FIRSTNAME

?

FROM EMPLOYEE

?

WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEE)

?

?

SELECT AVG(SALARY) AS "Average_Employee",

?

(SELECT AVG(SALARY) AS "Average_Staff" FROM STAFF)

?

FROM EMPLOYEE

?

?

?

?

?

注意 :

?

SQL SERVER 中 , 字符串用單引號 , 而 DB2 中則使用雙引號 !

?

SELECT XX AS "OO" 也可以寫為 SELECT XX "XX"

?

?

?

?

?

5.6.2 轉換數據類型

?

（使用 CAST,CAST 的另外一個用法是截斷數據）

?

SELECT CAST(NAME AS VARCHAR(2)) AS "NAME" FROM EMPLOYEE

?

?

?

?

?

5.6.3 條件表達式

?

(CASE 注意，單引號和雙引號 )

?

?

?

?

?

SELECT DEPTNAME

?

CASE DEPTNUMB

?

WHEN 10 THEN 'Market'

?

WHEN 20 THEN 'Sales'

?

WHEN 30 THEN 'Development'

?

ELSE 'NULL'

?

END AS FUNCTION

?

FROM ORG

?

?

?

?

?

-- 避免產生被 0 除

?

SELECT NAME,WORKDEPT

?

FROM EMPLOYEE

?

WHERE (CASE WHEN BONUS = 0 THEN NULL ELSE SALARY/BONUS END) > 10

?

?

?

?

?

-- 替代簡單的函數功能

?

CASE WHEN X<0 THEN -1 WHEN X=0 THEN 0 ELSE 1 END

?

?

?

?

?

5.6.4 表表達式（臨時的）

?

5.6.4 .1 嵌套表表達式

?

（嵌套于 FROM 字句中）

?

?

?

?

?

SELECT EDLEVEL, HIREYEAR, DECIMAL(AVG(TOTAL_PAY),7,2)

?

FROM (SELECT EDLEVEL, YEAR(HIREDATE) AS HIREYEAR,

?

SALARY+BONUS+COMM AS TOTAL_PAY

?

FROM EMPLOYEE

?

WHERE EDLEVEL > 16) AS PAY_LEVEL

?

GROUP BY EDLEVEL, HIREYEAR

?

ORDER BY EDLEVEL, HIREYEAR

?

?

?

?

?

5.6.4 .2 公共表達式

?

（以 WITH 開頭，對公共表達式的重復引用使用同一結果，而使用嵌套表達式則可能會出現不同的結果。）

?

?

?

?

?

WITH

?

PAYLEVEL AS

?

(SELECT EMPNO, EDLEVEL, YEAR(HIREDATE) AS HIREYEAR,

?

SALARY+BONUS+COMM AS TOTAL_PAY

?

FROM EMPLOYEE

?

WHERE EDLEVEL > 16),

?

?

?

?

?

PAYBYED (EDUC_LEVEL, YEAR_OF_HIRE, AVG_TOTAL_PAY) AS

?

(SELECT EDLEVEL, HIREYEAR, AVG(TOTAL_PAY)

?

FROM PAYLEVEL

?

GROUP BY EDLEVEL, HIREYEAR)

?

?

?

?

?

SELECT EMPNO, EDLEVEL, YEAR_OF_HIRE, TOTAL_PAY, DECIMAL(AVG_TOTAL_PAY,7,2)

?

FROM PAYLEVEL, PAYBYED

?

WHERE EDLEVEL = EDUC_LEVEL

?

AND HIREYEAR = YEAR_OF_HIRE

?

AND TOTAL_PAY < AVG_TOTAL_PAY

?

?

?

?

?

5.6.4 .3 相關名

?

（注意，一旦使用了相關名，則不能再在上下文中使用原名，否則出錯！）

?

?

SELECT NAME, DEPTNAME

?

FROM STAFF S, ORG O

?

WHERE O.MANAGER = S.ID

?

?

?

?

?

-- 另外，相關名還可以用來復制對象，例如說自身

?

SELECT E2.FIRSTNME, E2.LASTNAME, E2.JOB, E1.FIRSTNME AS MGR_FIRSTNAME,

?

E1.LASTNAME AS MGR_LASTNAME, E1.WORKDEPT

?

FROM EMPLOYEE E1, EMPLOYEE E2

?

WHERE E1.WORKDEPT = E2.WORKDEPT

?

AND E1.JOB = 'MANAGER'

?

AND E2.JOB <> 'MANAGER'

?

AND E2.JOB <> 'DESIGNER'

?

?

?

?

?

5.6.4 .4 不 / 相關子查詢

?

5.6.4 .4.1 不相關子查詢

?

SELECT EMPNO, LASTNAME

?

FROM EMPLOYEE

?

WHERE WORKDEPT = 'A00'

?

AND SALARY > (SELECT AVG(SALARY)

?

FROM EMPLOYEE

?

WHERE WORKDEPT = 'A00')

?

?

5.6.4 .4.2 相關子查詢

?

SELECT E1.EMPNO, E1.LASTNAME, E1.WORKDEPT

?

FROM EMPLOYEE E1

?

WHERE SALARY > (SELECT AVG(SALARY)

?

FROM EMPLOYEE E2

?

WHERE E2.WORKDEPT = E1.WORKDEPT)

?

ORDER BY E1.WORKDEPT

?

?

?

?

?

5.7 在查詢使用運算符與謂詞

?

5.7.1 集合運算符

?

5.7.1 .1UNION ：

?

組合兩個表，并消除重復行；如果 UNION ALL 則不消除重復行

?

SELECT ID, NAME FROM STAFF WHERE SALARY > 21000

?

UNION

?

SELECT ID, NAME FROM STAFF WHERE JOB='Mgr' AND YEARS < 8

?

ORDER BY ID

?

?

?

?

?

5.7.1 .2 EXCEPT:

?

包括所有在表 1 中而不在表 2 中的行并消除所有重復行；如果 EXCEPT ALL 則不消除重復行

?

?

?

?

?

SELECT ID, NAME FROM STAFF WHERE SALARY > 21000

?

EXCEPT

?

SELECT ID, NAME FROM STAFF WHERE JOB='Mgr' AND YEARS < 8

?

?

-- 也就是說，凡是滿足 EXCEPT 后面語句的數據都不在選擇的范圍內

?

?

?

?

?

5.7.1 .3 INTERSECT ：

?

只包括表 1 和表 2 都有的行并消除所有重復行；如果 INTERSECT ALL 則不消除重復行

?

?

SELECT ID, NAME FROM STAFF WHERE SALARY > 21000

?

INTERSECT

?

SELECT ID, NAME FROM STAFF WHERE JOB='Mgr' AND YEARS < 8

?

?

?

?

?

5.7.2 謂詞

?

5.7.2 .1 IN/NOT IN

?

SELECT NAME

?

FROM STAFF

?

WHERE DEPT IN (20, 15)

?

?

?

?

?

SELECT LASTNAME

?

FROM EMPLOYEE

?

WHERE EMPNO IN

?

(SELECT RESPEMP

?

FROM PROJECT

?

WHERE PROJNO = 'MA2100'

?

OR PROJNO = 'OP2012')

?

?

?

?

?

5.7.2 .2 BETWEEN/NOT BETWEEN

?

SELECT LASTNAME

?

FROM EMPLOYEE

?

WHERE SALARY BETWEEN 10000 AND 20000

?

?

?

?

?

5.7.2 .3 LIKE/NOT LIKE

?

注意 _ 表示任何單個字符， % 表示任何零個或者多個的字符

?

?

?

?

?

SELECT NAME

?

FROM STAFF

?

WHERE NAME NOT LIKE 'S%'

?

?

?

?

?

5.7.2 .4 EXISTS/NOT EXISTS

?

( 檢查存在性 )

?

?

?

?

?

SELECT DEPTNO, DEPTNAME

?

FROM DEPARTMENT X

?

WHERE NOT EXISTS

?

(SELECT *

?

FROM PROJECT

?

WHERE DEPTNO = X.DEPTNO)

?

ORDER BY DEPTNO

?

?

?

?

?

5.7.2 .5 定量謂詞

?

( 將一個值和一個值的集合進行比較 )

?

?

?

?

?

2.5.1 > ALL

?

( 必須全部符合，謂詞結果才為真！ )

?

查詢所有收入超過所有經理收入的雇員的姓名和職位（其實后面的子查詢中返回值有多個，所以用了 ALL ）

?

?

SELECT LASTNAME, JOB

?

FROM EMPLOYEE

?

WHERE SALARY > ALL

?

(SELECT SALARY

?

FROM EMPLOYEE

?

WHERE JOB='MANAGER')

?

5.7.2 .5.2 > ANY> SOME

?

( 兩個謂詞同意，只要有一個結果符合，謂詞結果即為真 )

?

查詢所有收入超過任何一個經理收入的雇員的姓名和職位（其實后面的子查詢中返回值有多個，所以用了 ALL ）

?

?

SELECT LASTNAME, JOB

?

FROM EMPLOYEE

?

WHERE SALARY > ANY

?

(SELECT SALARY

?

FROM EMPLOYEE

?

WHERE JOB='MANAGER')

?

?

?

?

?

5.8 定制和增強數據操作

5.8.1 用戶定義類型 UDT

5.8.1 .1 創建

CREATE DISTINCT TYPE PAY AS DECIMAL WITH COMPARISONS

?

5.8.1 .2 使用

SELECT * FROM EMPLOYEE WHERE DECIMAL(SALARY)=5120

?

5.8.1 .3 注意：

UDT 認為是于任何其他類型不同的類型，例如上例中的 PAY 和 DECIMAL 類型是不同的，但是可以相互轉換！

?

如 PAY(SALARY) 或者 DECIMAL (SALARY)

?

?

?

?

?

5.8.2 用戶自定義函數 UDF

?

5.8.2 .1 創建

?

CREATE FUNCTION MAX(PAY) RETURNS PAY

?

SOURCE MAX(DECIMAL)

?

5.8.2 .2 使用

?

SELECT column1,FunctionName(XXX) FROM TableXXX

?

5.8.2 .3 注意：

UDF 可以調用其他已有庫函數和其他 UDF 。

?

?

?

?

?

5.8.3 大對象 LOB

5.8.3 .1 簡介

　　　 BLOB,CLOB,DCLOB 分別代表二進制數大對象、字符大對象（多用于字符串）、雙字節字符大對象（多用于圖形）

5.8.3 .2 操作（略）

　　　

?

5.8.4 專用寄存器

5.8.4 .1 定義 :

　　　 由 DBMS 為連接定義的存儲區，用于 SQL 引用的信息。

5.8.4 . 2 存儲的常用數據：

　　　 CURRENT DATE 、 USER 、 CURRENT TIMESTAMP 、 CURRENT TIME 、 CURRENT TIMEZONE( 指定與世界時間的差別 ) 、 CURRENT SERVER

5.8.4 .3 調用 VALUES(CURRENT DATE) 或者 SELECT CURRENT TIME FROM TABLENAME

?

5.8.5 常用系統視圖

SYSCAT.CHECKS

?

SYSCAT.COLUMNS

?

SYSCAT.COLCHECKS

?

SYSCAT.KEYCOLUSE

?

SYSCAT.DATATYPES

?

SYSCAT.FUNCPARMS

?

SYSCAT.REFERENCES

?

SYSCAT.SCHEMATA

?

SYSCAT.TABCONST

?

SYSCAT.TABLES-- 模式

?

SYSCAT.TRIGGERS

?

SYSCAT.FUNCTIONS

?

SYSCAT.VIEWS