一些編程經驗，與大家共享！
1 DB2編程
1.1 建存儲過程時CREATE 後一定不要用TAB鍵 3
1.2 使用臨時表 3
1.3 從數據表中取指定前幾條記錄 3
1.4 游標的使用 4
注意commit和rollback 4
游標的兩種定義方式 4
修改游標的當前記錄的方法 5
1.5 類似DECODE的轉碼操作 5
1.6 類似CHARINDEX查找字符在字串中的位置 5
1.7 類似DATEDIF計算兩個日期的相差天數 5
1.8 寫UDF的例子 5
1.9 創建含IDENTITY值(即自動生成的ID)的表 6
1.10 預防字段空值的處理 6
1.11 取得處理的記錄數 6
1.12 從存儲過程返回結果集(游標)的用法 6
1.13 類型轉換函數 8
1.14 存儲過程的互相調用 8
1.15 C存儲過程參數注意 8
1.16 存儲過程FENCE及UNFENCE 8
1.17 SP錯誤處理用法 9
1.18 IMPORT用法 9
1.19 VALUES的使用 9
1.20 給SELECT 語句指定隔離級別 10
1.21 ATOMIC及NOT ATOMIC區別 10
2 DB2編程性能注意 10
2.1 大數據的導表 10
2.2 SQL語句盡量寫復雜SQL 10
2.3 SQL SP及C SP的選擇 10
2.4 查詢的優化(HASH及RR_TO_RS) 11
2.5 避免使用COUNT(*) 及EXISTS的方法 11
3 DB2表及SP管理 12
3.1 看存儲過程文本 12
3.2 看表結構 12
3.3 查看各表對SP的影響(被哪些SP使用) 12
3.4 查看SP使用了哪些表 12
3.5 查看FUNCTION被哪些SP使用 12
3.6 修改表結構 12
4 DB2系統管理 13
4.1 DB2安裝 13
4.2 創建DATABASE 14
4.3 手工做數據庫遠程(別名)配置 14
4.4 停止啟動數據庫實例 14
4.5 連接數據庫及看當前連接數據庫 14
4.6 停止啟動數據庫HEAD 15
4.7 查看及停止數據庫當前的應用程序 15
4.8 查看本INSTANCE下有哪些DATABASE 15
4.9 查看及更改數據庫HEAD的配置 16
4.9.1 改排序堆的大小 16
4.9.2 改事物日志的大小 16
4.9.3 出現程序堆內存不足時修改程序堆內存大小 16
4.10 查看及更改數據庫實例的配置 16
4.10.1 打開對鎖定情況的監控。 16
4.10.2 更改診斷錯誤捕捉級別 17
4.11 DB2環境變量 17
4.12 DB2命令環境設置 17
4.13 改變隔離級別 17
4.14 管理DB\INSTANCE的參數 18
4.15 升級後消除版本問題 18
4.16 查看數據庫表的死鎖 18
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函數返回()中表達式列表中第一個不為空的表達式，可以帶多個表達式。
和Oracle(大型網站數據庫平台)的isnull類似，但isnull好象只能兩個表達式。
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、建一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
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
3、動態SQL寫法
DECLARE CURSOR C1 FOR STMT1;
PREPARE STMT1 FROM
'ALLOCATE C2 CURSOR FOR RESULT SET ?';
4、注意：
一、 如果一個sp調用好幾次，只能取到最近一次調用的結果集。
二、 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。
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(Windows平台上強大的數據庫平台)\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
