一、db2 基礎
基本語法
注釋：“--”(兩個減號)
字符串連接：“||”
如set msg=’aaaa’||’bbbb’，則msg為’aaaabbbb’
字符串的引用：‘’(一定用單引號)，如果需要輸入單引號，輸入兩個單引號即可。
語句結束：“;”
語法來源：PASCLE
轉義字符：
如果你想查詢字符串中包含‘%’或‘_’ ，就得使用轉義字符(Escape Characters)。比如，要想查詢book_title中包含字符串’99%’的紀錄：
SELECT * FROM books WHERE book_title like ‘%99!%%’ escape ‘!’
後面的escape ‘!’是定一個轉義字符‘!’, 指明緊跟著轉義字符’!'後的%不再是統配符。
DB2命令參數選項
Db2 list command options 可以查看
-a 顯示 SQLCA OFF
-c 自動落實 ON
-e 顯示 SQLCODE/SQLSTATE OFF
-f 讀取輸入文件 OFF
-l 將命令記錄到歷史文件中 OFF
-n 除去換行字符 OFF
-o 顯示輸出 ON
-p 顯示交互式輸入提示 ON
-r 將輸出保存到報告文件 OFF
-s 在命令出錯時停止執行 OFF
-t 設置語句終止字符 OFF
-v 回送當前命令 OFF
-w 顯示 FETCH/SELECT 警告信息 ON
-x 不打印列標題 OFF
-z 將所有輸出保存到輸出文件 OFF
這些選項的具體功能及其缺省設置為：
.a 顯示 SQLCA 的數據，缺省為 OFF。
.c 是否自動落實 SQL 命令，缺省為 ON。
.e {c|s} 顯示 SQLCODE 或 SQLSTATE，缺省為 OFF。
.f 文件名將命令的輸入從標准輸入指定到某一文件，缺省為 OFF。
注：命令“db2 < 文件名”與“db2 -f 文件名”作用相同。
.l 文件名將命令記錄到歷史文件中，缺省為 OFF。
.n 除去換行字符，缺省為 OFF。
.o 將輸出數據及信息送到標准輸出，缺省為 ON。
.p 在交互方式下顯示命令行處理器的提示信息，缺省為 ON。
.r 將輸出保存到指定文件中，缺省為 OFF。
.s 執行批處理文件中或交互方式下的命令出錯時即停止執行操作，缺省為 OFF。
.t 設置語句終止字符，缺省為 OFF。
.v 回送當前命令到標准輸出，缺省為 OFF。
.w 顯示 FETCH 或 SELECT 警告信息，缺省為 ON。
.x 不打印列標題，缺省為 OFF。
.z 文件名將所有輸出保存到輸出文件，缺省為 OFF。
SQLSTATE含義
在db2命令行方式下輸入：? 20012(SQLSTATE值)，可以獲取sql錯誤含義
import和export的用法
IMPORT FROM CO_ACCEPTANCEDRAFT.del OF DEL MESSAGES importmsgs.txt INSERT INTO cmmcorc.CO_ACCEPTANCEDRAFT;
EXPORT TO CO_ACCEPTANCEDRAFT.del OF DEL MESSAGES exportmsgs.txt SELECT * FROM cmmcorc.CO_ACCEPTANCEDRAFT;
如果是Sybase導出的文本，並且用TAB分隔符的文檔，那麼可以采用
Db2 import from filename.txt of del modifIEd by COLDEL0x09 insert into tablename;
其中COLDEL是關鍵字，0x09是16進制，表示tab符號
Load用法
load from tempfile of del modifIEd by delprioritychar replace into TABLENAME nonrecoverable;
說明：
在不相關的數據表export數據時，可以采取並發的形式，以提高效率;
TABLENAME指待清理table的名稱;
modifIEd by delprioritychar防止數據庫記錄中存在換行符,導致數據無法裝入的情況;
replace into對現數據庫中的內容進行替換，即將現行的數據記錄清理，替換為數據文件內容;
nonrecoverable無日志方式裝入;
查詢出用戶表
SELECT * FROM SYSIBM.SYSTABLES WHERE CREATOR='USER'
如何知道當前DB2的版本?
select * from sysibm.sysversions
如何知道TABLESPACE的狀況?
select * from sysibm.SYSTABLESPACES
如何知道INDEX的狀況?
select * from sysibm.sysindexes where tbname=’XXXX’
or
describe indexes for table table_name show detail
測試SQL的執行性能
db2batch -d DB_NAME -f select.sql -r benchmark.txt -o p3
select.sql是select語句寫在文件中
如何獲取連接的進程
List applications
刪除當前正在使用的application:
db2 "force application (Id1,Id2,Id3)"
Id1,Id2,Id3 是List顯示的應用號;
刪除所有的進程
db2 force application all
查看當前應用號的執行狀態：
db2 get snapshot for application agentid 299 |grep Row
如何修改緩沖池
db2 alter bufferpool ibmdefaultbp size 10240
如何知道表的字段的狀況?
select * from sysibm.syscolumns where tbname=’XXXX’
如何知道DB2的數據類型?
select name,* from sysibm.sysdatatypes
如何知道BUFFERPOOLS狀況?
select * from sysibm.sysbufferpools
如何查看表的結構?
describe table table_name
or
describe select * from schema.table_name
如何快速清除一個大表?
alter table table_name activate not logged initally with empty table
or
import from null_file of del replace into table_name
如何查看數據庫的包?
select * from syscat.packages
如何查看數據庫的存儲過程?
select procname,text,* from syscat.procedures
Or
select procname,text from sysibm.sysprocedures
如何查看數據庫SAMPLE的配置文件的內容?
get database configuration for sample
or
get db cfg for sample
如何將數據庫SAMPLE的參數設置為默認數值?
reset database configuration for sample
or
reset db cfg for sample
如何修改數據庫SAMPLE配置參數數值?
update database configuration for sample
using
or
update db cfg for sample using
如何重新啟動數據庫?
Restart db db_name
如何激活數據庫?
Activate db db_name
如何停止數據庫?
Deactivate db db_name
如何重命名表?
Rename old_tablename to new_tablename
如何設置DB2環境變量
Db2set命令，語法如下：
Db2set variant=value
如何除去quIEsce狀態
1. 連接到數據庫
2. 用 list tablespaces 判斷哪個tablespace處於quIEsce狀態和和取得對象(object)ID
3. 判斷對象ID對應的表
a)用 db2 "select tabname from syscat.tables where tablid=對象ID" 得到表名
b)用 db2 list history 判斷是那個表
4. 用 db2 quiesce tablespaces for table 表名 reset 去除quIEsce狀態
如何實施已刪除表的恢復(Dropped Table Recovery)
1. 首先數據庫要可以前滾恢復(數據庫配置參數logretain或userexit打開)。
2. 對要實施Drop Table Recovery的表空間(限regular tablespace)，執行：
alter tablespace 表空間名 dropped table recovery on
3. 用 list history dropped table all for 數據庫名 得到刪除表的tableid(例如 0000000000006d0000020003)和表結構的生成語句(DDL)，記錄tableid和該語句以便恢復。之後，用drop命令刪除的表中的數據可以在前滾恢復時導出。
4. 恢復數據庫後，如果想恢復已刪除的表，在前滾時加recover dropped table tableid to 目標目錄 。 在該目錄下被刪除的表中的數據導出，例如導出至 ../NODE0000/data文件。利用上面提到表結構生成語句生成被刪除了的表，然後用import命令將數據導入表中。
如何備份數據庫
db2 "backup database sample tablespace (syscatspace, userspace1) online to /dev/rmt0 without prompting"
or
db2 "backup database sample user db2admin using db2admin to c:ackup with 3 buffers buffer 1000 without prompting"
如何生成所有對象的DDL
db2look -d DB_NAME -a -e -m -l -f -o filename.sql
如何恢復數據庫
以下是引用片段：
restoredbdb1to/tstdb2/catalogintodbnewlogpath/tstdb2/db2logbuffer2048
replaceexistingredirectparallelism16;
settablespacecontainersfor1using(path'/tstdb2/db2tmp');
settablespacecontainersfor2using
(device'/dev/rtstcontlv00'2621440,device'/dev/rtstcontlv01'2621440,
device'/dev/rtstcontlv02'2621440,device'/dev/rtstcontlv03'2621440);
restoredbdb1continue;
恢復完成後，執行db2s命令，如果出錯，再執行如下命令：
db2 rollforward db db to end of logs and complete
如何查看數據庫目錄
1)首先用 db2 list database directory 命令查看系統資料庫目錄(System Database Directory)中有沒有該資料庫。
如果有，應該再確定該資料庫是沒有用的資料庫之後用 db2 drop database 資料庫名將其刪除。
2)如果沒有，再用 db2 list database directory on location 看在本端資料庫目錄(Local Database Directory)中有沒有該資料庫，location指定資料庫的位置(如Windows下的C: ,Unix下的/home/db2inst1)。
如何更改本地系統名稱
1.db2set db2system=新系統名
2. db2 terminate
3. db2 uncatalog node 原節點名
4. db2 terminate
如何使用DB2MOVE
以下是引用片段：
Db2moveDB_NAMEaction-uUSERID–pPASSWord
Action:export,import,load
Options如下：
OptionDefaultNotes
-tctable-creatorsall_creatorsEXPORT.Wildcard
-tntable-namesall_user_tablesEXPORT.Wildcard
-snschema-namesall_schemasEXPORT.Wildcard
-tstblspace-namesall_tablespaceEXPORT.Wildcard
-tftablesfromfile
-ioimport-optionREPLACE_CREATEIMPORT_only.
-loload-optionINSERTLOAD_only.
-llobpathscurrent_dirseparated_by_commas.NO
-uuseridlogged_on_userid
-ppasswordlogged_on_passWord
-awallow-warningsfalseinclude
warningsduringexport.
例如：
Db2move ehrdb export –u ehr –p ehr
Db2move ehrdb import –u her –p ehr
IMPORT如何避免日志滿出錯
在使用IMPORT命令向數據庫出入數據時,如何避免日志空間滿錯誤?
在執行IMPORT命令時, 如果使用循環日志, 有時會出現日志滿錯誤,
這時可用COMMITCOUNT參數來解決.
因為日志空間滿往往是因為所有的日志均處於活動狀態導致的.
而COMMIT執行後, 會釋放所占據的資源, 其中包括日志 .
這樣, 被當前事務使用的日志在COMMIT命令執行後, 即變成非活動狀態了.
如何處理日志
-日志處理
DB2日志是以文件的形式存放在文件系統中，分為兩種模式：循環日志和歸檔日志。當創建新數據庫時，日志的缺省模式是循環日志。在這種模式下，只能實現數據庫的脫機備份和恢復。如果要實現聯機備份和恢復，必須設為歸檔日志模式。
目前在綜合業務系統中，設置的均是歸檔日志模式;其它系統(如事後監督、經營決策、中間業務等)一般都設置為循環日志模式。至於采用何種模式，可以通過修改數據庫配置參數(LOGRETAIN)來實現： 歸檔日志模式：db2 update db cfg for using logretain on 注：改為on後，查看數據庫配置參數logretain的值時，實際顯示的是recovery。改變此參數後，再次連接數據庫會顯示數據庫處於備份暫掛(BACKUP PENDING)狀態。這時，需要做一次對數據庫的脫機備份(db2 backup db )，才能使數據庫狀態變為正常。
循環日志模式：db2 update db cfg for using logretain off
-處理步驟
必須按照以下正確的步驟進行操作：
要求必須使用DB2命令PRUNE進行清理，不建議使用rm命令刪除。
刪除前應保證應用已停止(即聯機已下來)。
查看當前使用的日志文件目錄及第一活動日志文件
用 “db2 get db cfg for ”命令查看日志文件目錄(Path to log files)參數，確定數據庫當前使用的日志文件目錄。 例如：Path to log files = /db2log/，說明DB2日志存放目錄是/db2log
用 “db2 get db cfg for ”命令查看第一活動日志文件(First active log file)參數，該參數對應的日志文件之前的日志文件均為歸檔日志文件，如果確認沒有用，可以刪除。例如：First active log file = S0015913.LOG，說明當前第一活動日志文件是S0015913.LOG。
備份好要刪除的歸檔日志
刪除歸檔日志 以應用用戶(如BTP)登錄，執行：
$ db2 connect to
$ db2 prune logfile prior to S???????.LOG
注：S???????.LOG為查看到的第一活動日志文件。此命令可以將當前第一活動日志文件之前的歸檔日志文件全部刪除。
如何清理db2diag.log文件
db2diag.log，是用來記錄DB2數據庫運行中的信息的文件。可以通過此文件，查看記錄的有關DB2數據庫詳細的錯誤信息。此文件也是不斷增大的，需要定期進行清理。
可以通過查看實例的配置參數DIAGPATH，來確定db2diag.log文件是放在哪個目錄下：db2 get dbm cfg 如果Diagnostic data directory path(DIAGPATH) = /home/db2inst1/sqllib/db2dump，則此文件是放在/home/db2inst1/sqllib/db2dump目錄下。當文件系統/home的使用率達到80%-90%左右時，應及時刪除db2diag.log文件。
請按以下正確步驟操作：確認應用(如BTP)、DB2已經停止。
將原db2diag.log文件備份到其它文件系統下。
刪除db2diag.log文件。刪除後，DB2會自動創建一個新的文件。
標准函數
length: 返回自變量中的字節數
CAST: 變量類型轉換或截斷字符串
如：CAST(RESUME AS VARCHAR(370))
Select CAST(colname as integer) from tablename;
decimal: 變量轉換為指定精度的數值
如: select decimal(amount,16,2) from tablename;
WHERE 子句條件
謂詞 功能 x=y x 等於 y x<>y x 不等於 y x<y x 小於 y x>y x 大於 y x<=y x 小於或等於 y x>=y x 大於或等於 y IS NULL/IS NOT NULL 測試空值
獲取系統日期或系統時間
select current time into curtime from (values 1) as tmp;
select current date into curdate from (values 2) as tmp;
select year(current date) into curdate from (values 2) as tmp; --獲取系統年份
select month(current date) into curdate from (values 2) as tmp; --獲取系統月份
select day(current date) into curdate from (values 2) as tmp; --獲取系統日份
(CURRENT TIMESTAMP 精度達到微秒)
如何預防空值
DB2的COALESCE函數返回()中表達式列表中第一個不為空的表達式，可以帶多個表達式， 和Oracle的isnull類似。語法格式如下：
CLEASCE(colname，default_value)
多表的關聯更新方法
db2的update語法不支持“update table1 set t1.col1=t2.value1 from table1 t1,table2 t2 where …”的寫法，但是可以通過如下方法解決：
update table1 t1 set t1.col1=(select t2.col1 from table2 t2 where …)
例：
update test t1 set (t1.username,t1.instcode) = (select t2.instcode,t2.instname from sysinsttb t2 where t2.instcode=t1.instcode);
獲取操作(insert、update)的記錄數
GET DIAGNOSTICS rcount=ROW_COUNT;
注：
get diagnostics rcount =ROW_COUNT;
只對update,insert,delete起作用.
不對select into 有效
如何執行RUNSTATS等優化命令
db2 runstats on table with distribution and detailed indexes all
db2 reorgchk update statistics on table all
查看什麼時候進行了runstats
db2 "select name, stats_time from sysibm.systables"
完整執行如下：
db2 connect to ocrm1 user db2iocrm using db2iocrm
db2 runstats on table db2iocrm.eosOperator with distribution and indexes all
db2 reorg table db2iocrm.eosOperator allow read Access
db2 reorg indexes all for table db2iocrm.eosOperator allow read Access
db2 connect reset
如何獲取結果集的前N行數據
Select * from tablename fetch first N rows only
安裝DB2默認值?
在Windows或OS/2中默認實例的是DB2
在Linux或UNIX環境下默認實例的是DB2INST1
在Windows或OS/2中默認帳戶的是DB2ADMIN
在Linux或UNIX環境下默認帳戶的是DB2AS
如何定義序列
CREATE SEQUENCE ORDERSEQ START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 24
例如：
create sequence pk_only_empid;
select NEXTVAL FOR pk_only_empid from (values 1) as tmp;
如何關閉表的日志
ALTER TABLE TABLE_NAME ACTIVE NOT LOGGED INIALLY
如何獲取SQL執行計劃
SQL 解釋工具
SQL 解釋工具提供查詢優化器為 SQL 語句所選擇的訪問計劃的有關詳細信息。該信息存儲在 EXPLAIN 表中，可以在稍後使用諸如 Visual Explain、db2expln、dynexpln 和 db2exfmt 的工具進行格式化，從而以友好的可視方式進行表示。
EXPLAIN 表可以在您第一次使用 Visual Explain 時自動進行創建。即使沒有創建它們，您也可以手工進行創建，如下：
% cd sqllibmisc
% db2 connect to bank
% db2 -tvf EXPLAIN.DDL
本文中，我們使用 db2exfmt 工具。例如，使用 db2exfmt 解釋動態 SQL 語句，在 DB2 命令窗口中按照下列步驟進行：
以下是引用片段：
% db2 connect to <database_name>
% db2 set current explain mode explain
% db2 -tvf <Input file with an SQL statement ended with a semicolon>
% db2 set current explain mode no
% db2exfmt -d <dbname> -g TIC -w -1 -n % -s % -# 0 -o <output file>
如何創建事例?
DB2ICRT INSTNAME <...PARAMETERS>
如果是客戶端，加上clIEnt關鍵字
例如：
1) 用root登錄，命令為smitty創建用戶db2inst1，用於數據庫管理。
2) 用root登錄，使用db2icrt創建實例DB2 實例，使用以下命令。
cd /usr/opt/db2*/instance/
./db2icrt -s clIEnt db2inst1
3) 用db2inst1用戶登錄，創建數據庫的本地節點目錄，建立遠程數據庫映射別名，使用以下命令。
cd /usr/opt/db2*/bin
db2 catalog tcpip node wmsint remote 182.247.70.94 server 60000
db2 catalog database wmsint as wmsint at node wmsint
db2set db2codepage=1386
db2 terminate
4) 為了使root用戶可以使用db2命令，要把home/db2inst1下的.profile文件中的下面一段文字加到根目錄下的.profile中。
以下是引用片段：
PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:.
exportPATH
#ThefollowingthreelineshavebeenaddedbyUDBDB2.
if[-f/home/db2inst1/sqllib/db2profile];then
./home/db2inst1/sqllib/db2profile
fi
5)退出root用戶，重新登入
SQL寫法技巧
多個字段時如何不通過使用select子句使用in/not in
select * from tabschema.tabname where (colA, colB, colC) [not] in (values (valueA1, valueB1, valueC1), (valueA2, valueB2, valueC2), ...(valueAn, valueBn, valueCn))
Update tablenameA t1
Set (colA,colB,colC)=(selelct b.colA,b.colB,b.colC from tablenameB t2 where t1.key = t2.key)
使用部分命令
列出所有實例 DB2ILIST
獲取當前實例 GET INSTANCE
更新事例的配置 DB2IUPDT
刪除事例 DB2IDROP INSTANCE_NAME
具體步驟如下:
停止事例上所有應用程序
在所有打開的命令行上執行DB2 TERMINATE
運行DB2STOP
備份DB2INSTPROF注冊變量指出的事例目錄
退出事例所有登陸者
使用DB2IDROP
也可以刪除ID
列出本地系統上有許可信息的所有產品 DB2LICM -L
增加一產品許可DB2LICM -A FILENAME
刪除一個產品的許可 DB2LICM -R PRODUCT PASSWord
更新已購買的許可數量DB2LICM -U
強制只使用已經購買的數量DB2LICM -E HARD
更新系統上所使用的許可政策類型 DB2LICM -P REGISTERED CONCURRENT
更新系統上的處理器的數量 DB2LICM -N
查詢許可文件記錄的版本信息 DB2LICM -V
查詢DB2LICM的幫助信息 DB2LICM -H
二、存儲過程
如何聲明一個存儲過程
CREATE PROCEDURE 存儲過程名(IN 輸入變量名 輸入變量類型,OUT 輸出變量名 輸出變量類型)
緊跟其後的是存儲過程屬性列表
常用的有：LANGUAGE SQL、MODIFIES SQL DATA、RESULT SETS 1(返回結果集個數)
l 存儲過程體以begin開始
l 存儲過程體以end結束
存儲過程約束規則
存儲過程中調用存儲過程
CALL 存儲過程名(參數1，參數2，參數n)
例：
以下是引用片段：
callspco_init_custom(bankcode,errno,errmsg);
GETDIAGNOSTICSretval=RETURN_STATUS;
if(retval<>0)then
seterrno=errno;
seterrmsg=errmsg;
returnerrno;
endif;
變量的定義
變量使用前必須先定義，方法為
DECLARE 變量名 變量類型 (default 默認值)
例：
以下是引用片段：
DECLARESQLCODEINTEGERDEFAULT0;
DECLAREinumINTEGERDEFAULT0;
DECLAREcurtimechar(8);
DECLAREbcodechar(6);
DECLAREsqlstatechar(5);
if 表達式
if 條件1 then
邏輯體;
elseif 條件2 then
邏輯體;
else
邏輯體;
end if;
例：
以下是引用片段：
IFrating=1THEN
UPDATEemployee
SETsalary=salary*1.10,bonus=1000
WHEREempno=employee_number;
ELSEIFrating=2THEN
UPDATEemployee
SETsalary=salary*1.05,bonus=500
WHEREempno=employee_number;
ELSE
UPDATEemployee
SETsalary=salary*1.03,bonus=0
WHEREempno=employee_number;
ENDIF;
case表達式
case 變量名 when
變量值1 then
. . .
when
變量值2 then
- - -
else
. . .
end case;
或
case when
變量名=變量值1 then
. . .
when
變量名=變量值2 then
- - -
else
. . .
end case;
例一：
以下是引用片段：
CASEv_workdept
WHEN'A00'
THENUPDATEdepartment
SETdeptname='DATAAccess1';
WHEN'B01'
THENUPDATEdepartment
SETdeptname='DATAAccess2';
ELSEUPDATEdepartment
SETdeptname='DATAAccess3';
ENDCASE;
例二：
以下是引用片段：
CASE
WHENv_workdept='A00'
THENUPDATEdepartment
SETdeptname='DATAAccess1';
WHENv_workdept='B01'
THENUPDATEdepartment
SETdeptname='DATAAccess2';
ELSEUPDATEdepartment
SETdeptname='DATAAccess3';
ENDCASE;
for 表達式
for 循環名 as
游標名或select 表達式
do
sql表達式;
end for;
例：
1)
以下是引用片段：
DECLAREfullnameCHAR(40);
FORvlAS
SELECTfirstnme,midinit,lastnameFROMemployee
DO
SETfullname=lastname||','||firstnme||''||midinit;
INSERTINTOtnamesVALUE(fullname);
ENDFOR
2)
以下是引用片段：
forloopcs1ascousor1cursoras
selectmarket_codeasmarket_code
fromtb_market_code
forupdate
do
endfor;
goto表達式
goto 標示名;
標示名：
邏輯體;
例：
GOTO FAIL;
...
SUCCESS: RETURN 0
FAIL: RETURN -200
while表達式
while 條件表達式 do
邏輯體;
end while;
LOOP表達式
LOOP... END LOOP;
例：
以下是引用片段：
OPENc1;
ins_loop:
LOOP
FETCHc1INTOv_dept,v_deptname,v_admdept;
IFat_end=1THEN
LEAVEins_loop;--中斷循環
ELSEIFv_dept='D11'THEN
ITERATEins_loop;--下一個循環
ENDIF;
INSERTINTOdepartment(deptno,deptname,admrdept)
VALUES('NEW',v_deptname,v_admdept);
ENDLOOP;
CLOSEc1;
關於游標
定義游標：
DECLARE 游標名 CURSOR FOR
Select 語句;
打開游標：
OPEN 游標名;
取值：
FETCH 游標名 INTO 變量列表
例：
以下是引用片段：
DECLAREc1CURSORFOR
SELECTCAST(salaryASDOUBLE)
FROMstaff
WHEREDEPT=deptNumber
ORDERBYsalary;
DECLAREEXITHANDLERFORNOTFOUND
SETmedianSalary=6666;
SETmedianSalary=0;
SELECTCOUNT(*)INTOv_numRecords
FROMstaff
WHEREDEPT=deptNumber;
OPENc1;
WHILEv_counter<(v_numRecords/2+1)DO
FETCHc1INTOmedianSalary;
SETv_counter=v_counter+1;
ENDWHILE;
CLOSEc1;
注：游標的申明如果放在中間段，要用”begin。。。end;”.段分割標志分割開;
動態sql
1)
以下是引用片段：
declarestmtvarchar(1024);
setstmt='createtablezhouhaiming(f1smallint,f2varchar(9),f3char(5))';
prepares1fromstmt;
executes1;
setstmt='insertintozhouhaimingvalues(1,'www','aaa')';
prepares1fromstmt;
executes1;
2)
以下是引用片段：
DECLARECURSORC1FORSTMT1;
PREPARESTMT1FROM
'ALLOCATEC2CURSORFORRESULTSET?';
臨時表的建立
以下是引用片段：
DECLAREGLOBALTEMPORARYTABLETABLE_NAME
AS(FULLSELECT)DEFINITIONONLY
EXCLUDINGIDENTITYCOLUMNATTRIBUTES
ONCOMMITDELETEROWS
NOTLOGGEDIN臨時表空間名withreplace;
第一行規定臨時表的名稱.
第二行規定臨時表的列的定義.
第三行規定不是從源結果表定義中復制的恆等列.
第四行規定如果沒有打開WITH GOLD光標,將會刪除表的所有行.
第五行規定不對表的改變進行記錄.
With replace選項會隱式的自動刪除該臨時表。
例如:
以下是引用片段：
DECLAREGLOBALTEMPORARYTABLEDEC_BSEMPMS
AS(SELECT*FROMBSEMPMS)DEFINITIONONLY
EXCLUDINGIDENTITYCOLUMNATTRIBUTES
ONCOMMITDELETEROWS
NOTLOGGED;
DB2中的幾個全局變量
ROW_COUNT—影響行數
UPDATE CORPDATA.PROJECT
SET PRSTAFF = PRSTAFF + 1.5
WHERE DEPTNO = deptnbr;
GET DIAGNOSTICSrcount = ROW_COUNT;
RETURN_STATUS--返回狀態
CALL TRYIT;--調用存儲過程
GET DIAGNOSTICSRETVAL = RETURN_STATUS;
IF RETVAL <> 0 THEN
...
LEAVE A1;
ELSE
...
END IF;
SQLSTATE—SQL返回錯誤代碼
注：使用前必先定義
以下是引用片段：
declaresqlstatechar(5);
declarestatechar(5);
insertintotbnamevalues(…)
setstate=sqlstate;
if(state<>'00000')then
return-1;
endif;
關於ATOMIC和NOT ATOMIC
P1:BEGIN ATOMIC –P1段的事務會自動回滾
P1:BEGIN NOT ATOMIC –P1段的事務不會自動回滾
DB2中的條件句柄
句柄類型:
CONTINUE
EXIT
UNDO
條件類型：
SQLSTATE string
SQLEXCEPTION
SQLWARNING
NOT FOUND
例：
1)
以下是引用片段：
DECLAREEXITHANDLERFORNOTFOUND
SETmedianSalary=6666;
2)
以下是引用片段：
DECLAREnot_foundCONDITIONFORSQLSTATE'02000';
DECLAREEXITHANDLERFORnot_found
SETrating=-1;
3)
以下是引用片段：
DECLAREnot_foundCONDITIONFORSQLSTATE'02000';
DECLAREc1CURSORFOR
SELECTdeptno,deptname,admrdept
FROMdepartment
ORDERBYdeptno;
DECLARECONTINUEHANDLERFORnot_found
SETat_end=1;
如何抽取/提交存儲過程
db2 "get routine into 文件名 from procedure 存儲過程名"
抽取存儲過程;
提交存儲過程
db2 "put routine from 文件名"
安裝已編譯好的存儲過程。
如何在命令符下提交存儲過程
在存儲過程的最後加上@符號，然後在命令符下打入：db2 -td@ -vf procfile.sql 就可以生成過程。
非存儲過程的SQL文件，在命令符下打入：db2 –tvf sqlfile.sql
從存儲過程返回結果集(游標)的用法
1、建一sp返回結果集
以下是引用片段：
CREATEPROCEDUREDB2INST1.Proc1(
LANGUAGESQL
resultsets2--(返回兩個結果集)
P1:BEGIN
declarec1cursorwithreturntocallerfor
selectmarket_code
fromtb_market_code;
--指定該結果集用於返回給調用者
以下是引用片段：
declarec2cursorwithreturntocallerfor
selectmarket_code
fromtb_market_code;
openc1;
openc2;
ENDP1
2、建一SP調該sp且使用它的結果集
以下是引用片段：
CREATEPROCEDUREDB2INST1.Proc2(
outout_market_codechar(1))
LANGUAGESQL
P1:BEGIN
declareloc1,loc2result_set_locatorvarying;
--建立一個結果集數組
callproc1;
--調用該SP返回結果集。
associateresultsetlocator(loc1,loc2)withprocedureproc1;
--將返回結果集和結果集數組關聯
allocatecursor1cursorforresultsetloc1;
allocatecursor2cursorforresultsetloc2;
--將結果集數組分配給cursor
fetchcursor1intoout_market_code;
--直接從結果集中賦值
closecursor1;
ENDP1
