講解DB2數據庫編程所應掌握的基本知識

2024-07-21 02:42:12

字體：大中小

來源：轉載

供稿：網友

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/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=信息目錄管理器

◆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

| 節點號 | 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鎖,排它鎖,將會處于鎖等待。

首先，將監視開關打開，