二、基本管理
1.sql語句環(huán)境下使用臨時表
-- 定義一個全局臨時表SESSION.tmptb
| DECLARE GLOBAL TEMPORARY TABLE SESSION.tmptb ( TMP_id VARCHAR(10), TMP_name VARCHAR(300) ) WITH REPLACE NOT LOGGED; |
-- 插入數(shù)據(jù)到臨時表
| INSERT INTO SESSION.tmptb SELECT id,name FROM user where id like ‘0%’; |
| SELECT * FROM SESSION.tmptb; |
注：在SQL語句中，當(dāng)SQL語句執(zhí)行處于自動提交模式下時，執(zhí)行后就COMMIT了，而一旦執(zhí)行了COMMIT語句，臨時表將從內(nèi)存中清除。所以可能查詢該臨時表后可能無數(shù)據(jù)顯示。那么自動提交模式怎樣確定呢？在默認情況下，自動提交特性是打開的（-c）。這個選項指定每個語句是否自動提交或回滾。如果一個語句成功了，它就和它前面執(zhí)行的關(guān)閉了自動提交（+c 或 -c-）的所有成功語句一起提交。但是，如果它失敗了，它就和它前面執(zhí)行的關(guān)閉了自動提交的所有成功語句一起回滾。如果這個語句關(guān)閉了自動提交，就必須顯式地執(zhí)行提交或回滾命令。
示例：
| [maps@localhost ~]$ db2 +c "create table T(c1 int)" DB20000I The SQL command completed successfully. [maps@localhost ~]$ db2 +c "select * from T" C1 ----------- 0 record(s) selected. [maps@localhost ~]$ db2 rollback DB20000I The SQL command completed successfully. [maps@localhost ~]$ db2 +c "select * from T" SQL0204N "MAPS.T" is an undefined name. SQLSTATE=42704 |
2.存儲過程環(huán)境下使用臨時表
| CREATE PROCEDURE SP_TEST_TMEP ( ) DYNAMIC RESULT SETS 1 P1: BEGIN -- 定義一個全局臨時表SESSION.TMPTB DECLARE GLOBAL TEMPORARY TABLE SESSION. TMPTB ( TMP_ID VARCHAR(10), TMP_NAME VARCHAR(300) ) WITH REPLACE -- 如果存在此臨時表，則替換 NOT LOGGED; -- 不在日志里紀(jì)錄 -- 插入數(shù)據(jù)到臨時表 INSERT INTO SESSION.TMPTB SELECT ID,NAME FROM USER; P2: BEGIN -- 游標(biāo)對客戶機應(yīng)用程序保持打開 DECLARE CUR_RES CURSOR WITH RETURN FOR SELECT * FROM SESSION.TMPTB; OPEN CUR_RES; END P2; END P1 |
注：在SQL過程中，臨時表定義后，如果沒有顯式執(zhí)行到COMMIT語句，則臨時表一直存在。并且臨時表支持INSERT INTO ... SELECT ... 的語句。
臨時表總結(jié)：在DB2下，臨時表的模式必須為SESSION，SESSION模式下表是一個內(nèi)存表，這個SESSION是DB2特有的SCHEMA，SESSION對象的聲明周期僅僅限于一次數(shù)據(jù)連接“會話”，一旦會話結(jié)束，SESSION對象就被從內(nèi)存中清除了，這和JSP中的內(nèi)置對象SESSION類似。
3.查看端口號
切換到db2inst1用戶 $su - db2inst1
查找TCP/IP Service name $db2 get dbm cfg | grep -i service
通過上一句命令的輸出（如db2c_db2inst1）在/etc/services文件中找對應(yīng)的端口號 grep db2c_db2inst1 /etc/services
這是你將會看到這么一行從上一句的輸出 db2c_db2inst1 50000/tcp
其中50000就是db2數(shù)據(jù)庫所占用的端口號
4.遠程訪問
如果需要在本地應(yīng)用程序中訪問遠程數(shù)據(jù)庫服務(wù)器，只需要在本地應(yīng)用程序環(huán)境下安裝db2客戶端后做如下操作就可以跟直接訪問數(shù)據(jù)庫服務(wù)器一樣進行操作了：
編目一個TCP/IP節(jié)點:
db2 catalog tcpip node host191 remote 10.0.7.191 SERVER 60000
查看本地節(jié)點目錄:
db2 list node directory
編目數(shù)據(jù)庫:
db2 catalog DATABASE cisdb AS cisdb191 AT NODE HOST191
查看系統(tǒng)數(shù)據(jù)庫目錄:
db2 list database directory
測試遠程連接:
db2 connect to cisdb191 user db2 using passwd
5.常用命令
啟動和停止實例：db2start/db2stop
查看表結(jié)構(gòu)：db2 describe table user1.department
查看表索引：db2 describe indexes for table user1.department
顯示當(dāng)前用戶所有表：list tables
列出所有的系統(tǒng)表：list tables for system
顯示當(dāng)前活動數(shù)據(jù)庫：list active databases
查看命令選項：list command options
信息幫助 (? XXXnnnnn ) ：例:? SQL30081
SQL 幫助(說明 SQL 語句的語法) ：help statement 例如，help SELECT
SQLSTATE 幫助(說明 SQL 的狀態(tài)和類別代碼) ：? sqlstate 或 ? class-code update dbm cfg using
三、備份和恢復(fù) 四、常用技巧 五、異常處理 六、資料推薦
1.db2move備份和導(dǎo)入數(shù)據(jù)
db2move YOURDB export
db2move NEWDB load
2.備份和恢復(fù)數(shù)據(jù)庫
備份：$DB2 BACKUP DB SAMPLES
除去數(shù)據(jù)庫：再建立同名數(shù)據(jù)庫時候，會提示錯誤，需要刪除數(shù)據(jù)庫
$db2 catalog db dbname on /opt
SQL1005N在本地數(shù)據(jù)庫目錄或系統(tǒng)數(shù)據(jù)庫目錄中已經(jīng)存在數(shù)據(jù)庫別名dbname
$db2 drop db dbname
DB20000I DROP DATABASE 命令成功完成
恢復(fù)：恢復(fù)到一個新數(shù)據(jù)庫，恢復(fù)過程自動創(chuàng)建數(shù)據(jù)庫
$DB2 RESTORE DB dbname INTO dbname WITHOUT ROLLING FORWARD WITHOUT PROMPTING
恢復(fù)成功，但是有錯誤 （57019）
$db2 connect to dbname user db2admin using xxxxxx
SQL1117N 由于ROLL-FORWARD PENDING，不能連接或激活數(shù)據(jù)庫 dbname.SQLSTATE=57019
$db2 rollforward db dbname to end of logs
前滾狀態(tài)
輸入數(shù)據(jù)庫別名=dbname
節(jié)點數(shù)已返回狀態(tài)=1
節(jié)點號=0
前滾狀態(tài)= DB 工作
下一個要讀取的日志文件=S0000000.LOG
已處理的日志文件 = -
上次提交的事務(wù)=2005-03-14-17.47.33.000000
DB20000I ROLLFORWARD 命令成功完成。
$db2 connect to dbname user db2admin using xxxxx
SQL1117N 由于ROLL-FORWARD PENDING，不能連接或激活數(shù)據(jù)庫dbname，SQLSTATE=57019
$db2 rollforward db dbname complete
前滾狀態(tài)
輸入數(shù)據(jù)庫別名=dbname
節(jié)點數(shù)已返回狀態(tài)=1
節(jié)點號=0
前滾狀態(tài)=未暫掛
下一個要讀取的日志文件=
已處理的日志文件= -
上次提交的事務(wù)=2005-03-14-17.47.33.000000
DB20000I ROLLFORWARD 命令成功完成。
$db2 connect to dbname user db2admin using xxxxxx
Database Connection Information
3.export/load備份恢復(fù)數(shù)據(jù)
export to tempfile of del select * from TABLENAME where not 清理條件；
load from tempfile of del modified by delprioritychar replace into TABLENAME nonrecoverable；
說明：
在不相關(guān)的數(shù)據(jù)表export數(shù)據(jù)時，可以采取并發(fā)的形式，以提高效率；
modified by delprioritychar防止數(shù)據(jù)庫記錄中存在換行符,導(dǎo)致數(shù)據(jù)無法裝入的情況;
replace into對現(xiàn)數(shù)據(jù)庫中的內(nèi)容進行替換，即將現(xiàn)行的數(shù)據(jù)記錄清理，替換為數(shù)據(jù)文件內(nèi)容；
nonrecoverable無日志方式裝入；
1.導(dǎo)出建庫腳本
db2look -d YOURDB -a -e -x -o creatab.sql
2.取前Ｎ條數(shù)據(jù)
select * from tab_name where expression fetch first n rows only
3.關(guān)于日志
1)Log retain for recovery enabled (LOGRETAIN) = OFF
User exit for logging enabled (USEREXIT) = OFF
這兩項這樣設(shè)，當(dāng)一個事務(wù)結(jié)束后，日志會自動清，但是你設(shè)置的日志大小將占用相應(yīng)的硬盤空間，總大小為單個日志大小×主日志文件數(shù)！一般夠用！
db2diag.log：太大的話就刪除或者移走，系統(tǒng)在需要的時候會自己建立新的。
2)系統(tǒng)日志（log），如果logretain參數(shù)是設(shè)定為off的，為循環(huán)日志，總的大小是不會增加的。如果設(shè)定為recovery，需要用db2 PRUNE LOGFILE PRIOR TO Sxxx.LOG 的指令來刪除非活動的日志文件，具體編號可以從db2 get db cfg for xxx取得。
1.數(shù)據(jù)庫掛起
在進行一些數(shù)據(jù)庫操作時可能因為種種原因發(fā)生中斷，會使數(shù)據(jù)庫暫掛 ：
db2 list tablespaces show detail
狀態(tài)更改對象標(biāo)識 = 59
db2 select tabname,tableid from syscat.tables where tableid=59
查看是哪張表掛起，表名知道后到db2move.lst(在db2move YOURDB export的目錄中)中找到相應(yīng)的.ixf文件
db2 load from tab11.ixf of ixf terminate into db2admin.xxxxxxxxx
tab11.ixf對應(yīng)的是xxxxxxxxx表，數(shù)據(jù)庫會恢復(fù)正常，可再用db2 list tablespaces show detail查看
2.應(yīng)用長時間未提交導(dǎo)致日志空間不可用
即在日志空間并未用盡的情況下，當(dāng)某個占有最舊活動日志的應(yīng)用長時間未作提交操作，阻止了日志的 LSN 的分配，造成日志空間無法使用，同樣會引發(fā)這一日志滿的報錯。對于這種情況，可以提交該交易或利用 FORCE 命令來終止此應(yīng)用程序，以便釋放它所占用的日志空間，使 LSN 可以繼續(xù)分配，空閑的日志空間可用。
首先檢查 DB2 診斷日志文件 db2diag.log，在其中查找類似如下信息：
2003-01-16-02.53.54.935308 Instance:db2inst1 Node:016
PID:144252(db2agntp (SAMPLE) 16) Appid:*.*
data_protection sqlpgrsp Probe:50 Database:SAMPLE
Log Full -- active log held by appl. handle 787273
End this application by COMMIT, ROLLBACK or FORCE APPLICATION.
由此，可以找到最早持有日志空間的應(yīng)用程序，其句柄為 787273。如果使用 DB2 的快照工具，通過從快照的輸出中查找類似以下信息：
Appl id holding the oldest transaction = 787273
同樣可以找到這個應(yīng)用程序的句柄。這時使用以下命令可以在無需斷開數(shù)據(jù)庫其它應(yīng)用程序的連接的情況下強行終止該應(yīng)用程序：
db2 force application (787273)
DB20000I FORCE APPLICATION 命令成功完成。
DB21024I 該命令為異步的，可能不會立即生效。
根據(jù)提示，由于該命令是異步操作，可再次使用：
db2 list applications
驗證應(yīng)用是否已被真正停止，如果輸出中已沒有該應(yīng)用，它所占有的日志空間會因應(yīng)用程序被回滾而立即釋放，而 DB2 日志因此重新可用。
3.日志滿的解決辦法
DB2 使用的活動日志的最大空間是由下面公式：
(logprimary + logsecond) * logfilsiz * 4096
計算出的大小來決定的（logprimary，logsecond，logfilsiz是數(shù)據(jù)庫配置參數(shù)）。若該空間已全部被分配，而應(yīng)用仍試圖請求更多活動日志空間時，就會發(fā)生日志滿的情況，此時，用戶的更新、刪除或插入操作都會使 DB2DIAG.LOG 中寫入以下信息：
SQL0964C 數(shù)據(jù)庫的事務(wù)日志已滿。
DB2 活動日志滿通常是由于存在大量未提交事務(wù)的數(shù)據(jù)，使得活動日志的空間不能及時釋放，使新的事務(wù)無法申請到可用日志空間，而最終報出 SQL0964C 的錯誤所致。為使應(yīng)用程序成功運行，而不是被回滾，通常會考慮根據(jù)情況選擇增大以上公式中的某些數(shù)據(jù)庫參數(shù)，以增大活動日志空間來解決這一問題。
db2 => get db cfg for testdatabase 查看數(shù)據(jù)庫配置信息
db2 => update db cfg for testdatabase using logfilsiz 6000
db2 => update db cfg for testdatabase using logprimary 4
db2 => update db cfg for testdatabase using logsecond 25
1.DB2開發(fā)與應(yīng)用社區(qū)：http://www.db2china.net/
2.DB2信息中心：http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp
3.DB2管理資料：http://blog.csdn.net/gubaohua/archive/2005/07/01/409153.aspx
4.DB2數(shù)據(jù)庫編程與開發(fā)：http://focus.it168.com/200809/db2develop/index.html
5.DB2新手入門：http://www-128.ibm.com/developerworks/cn/db2/newto/
6.DB2數(shù)據(jù)庫管理開發(fā)性能優(yōu)化：http://publish.itpub.net/lists/7231/0/7231.shtml
