DB2數(shù)據(jù)庫SQL語法參考手冊

1、DB2數(shù)據(jù)庫SQL語法參考手冊DB2提供了關(guān)連式資料庫的查詢語言SQL (Structured Query Language)，是一種非?？谡Z化、既易學(xué)又易懂的語法。此一語言幾乎是每個資料庫系統(tǒng)都必須提供的，用以表示關(guān)連式的操作，包含了資料的定義(DDL)以及資料的處理(DML)。SQL原來拼成SEQUEL，這語言的原型以"系統(tǒng)R"的名字在IBM圣 荷西實驗室完成，經(jīng)過IBM內(nèi)部及其他的許多使用性及效率測試，其結(jié)果相當(dāng)令人滿意，并決定在系統(tǒng)R的技術(shù)基礎(chǔ)發(fā)展出來IBM的產(chǎn)品。而且美國國家標(biāo)準(zhǔn)學(xué)會 (ANSI)及國際標(biāo)準(zhǔn)化組織(ISO)在1987遵循一個幾乎是以IBM SQL為

2、基礎(chǔ)的標(biāo)準(zhǔn)關(guān)連式資料語言定義。一、資料定義 DDL(Data Definition Language)資料定義語言是指對資料的格式和形態(tài)下定義的語言，他是每個資料庫要建立時候時首先要面對的，舉凡資料分哪些表格關(guān)系、表格內(nèi)的有什麼欄位主鍵、表格和表格之間互相參考的關(guān)系等等，都是在開始 的時候所必須規(guī)劃好的。1. 建表格：CREATE TABLE table_name(column1 DATATYPE NOT NULL NOT NULL PRIMARY KEY,column2 DATATYPE NOT NULL,)說明：DATATYPE是資料的格式，詳見表。NUT NULL可不可以允許資料有空的(

3、尚未有資料填入)。PRIMARY KEY是本表的主鍵。2. 更改表格ALTER TABLE table_nameADD COLUMN column_name DATATYPE說明:增加一個欄位(沒有刪除某個欄位的語法。ALTER TABLE table_nameADD PRIMARY KEY (column_name)說明:更改表得的定義把某個欄位設(shè)為主鍵。ALTER TABLE table_nameDROP PRIMARY KEY (column_name)說明 :把主鍵的定義刪除。3. 建立索引CREATE INDEX index_name ON table_name (column_na

4、me)說明 :對某個表格的欄位建立索引以增加查詢時的速度。4. 刪除DROP table_nameDROP index_name二、的資料形態(tài) DATATYPEssmallint16 位元的整數(shù)。interger32 位元的整數(shù)。decimal(p,s)p 精確值和 s 大小的十進位整數(shù)， 精確值 p 是指全部有幾個數(shù) (digits) 大小值 ,s 是指小數(shù)點後有幾位數(shù)。 如果沒有特別指定，則系統(tǒng)會設(shè)為 p=5; s=0 。float32 位元的實數(shù)。double64 位元的實數(shù)。char(n)n 長度的字串， n 不能超過 254 。varchar(n)長度不固定且其最大長度為 n 的字串

5、， n 不能超過 4000 。graphic(n) 和 char(n) 一樣，不過其單位是兩個字元 double-bytes ， n 不能超過 127 。這個形態(tài)是為了支援兩個字元長度的字體，例如中文字。vargraphic(n) 可變長度且其最大長度為 n 的雙字元字串， n 不能超過 2000 。date 包含了 年份、月份、日期。 time 包含了 小時、分鐘、秒。timestamp 包含了 年、月、日、時、分、秒、千分之一秒。三、資料操作 DML (Data Manipulation Language) 資料定義好之後接下來的就是資料的操作。資料的操作不外乎增加資料(insert) 、

6、查詢資料 (query) 、更改資料 (update) 、刪除資料 (delete) 四種模式，以下分 別介紹他們的語法 :1. 增加資料 :INSERT INTO table_name (column1,column2,.)valueS ( value1,value2, .)說明:(1) 若沒有指定 column 系統(tǒng)則會按表格內(nèi)的欄位順序填入資料。(2) 欄位的資料形態(tài)和所填入的資料必須吻合。(3) table_name 也可以是景觀 view_name 。INSERT INTO table_name (column1,column2,.)SELECT columnx,columny,.

7、FROM another_table說明 :也可以經(jīng)過一個子查詢 (subquery) 把別的表格的資料填入。2. 查詢資料 :基本查詢SELECT column1,columns2,.FROM table_name說明 :把 table_name 的特定欄位資料全部列出來SELECT *FROM table_nameWHERE column1 = * AND column2 > yyy OR column3 <> zzz說明:(1) '*'表示全部的欄位都列出來(2) WHERE 之後是接條件式，把符合條件的資料列出來。SELECT column1,colu

8、mn2FROM table_nameORDER BY column2 DESC說明:ORDER BY 是指定以某個欄位做排序，DESC是指從大到小排列，若沒有指明，則是從小到大排列 ,組合,查詢組合查詢是指所查詢得資料來源并不只有單一的表格，而是聯(lián)合一個以上的表格才能夠得到結(jié)果的。SELECT *FROM table1,table2WHERE table1.colum1=table2.column1說明 :(1) 查詢兩個表格中其中 column1 值相同的資料。(2) 當(dāng)然兩個表格相互比較的欄位，其資料形態(tài)必須相同。(3) 一個復(fù)雜的查詢其動用到的表格可能會很多個。整合性的查詢 :SELEC

9、T COUNT (*) FROM table_nameWHERE column_name = xxx說明 :查詢符合條件的資料共有幾筆。SELECT SUM(column1)FROM table_name說明 :(1) 計算出總和，所選的欄位必須是可數(shù)的數(shù)字形態(tài)。(2) 除此以外還有 AVG()是計算平均、MAX()、MIN()計算最大最小值的整合性查詢。SELECT column1,AVG(column2)FROM table_nameGROUP BY column1HAVING AVG(column2) >xxx說明 :(1)GROUP BY: 以 column1 為一組計算 col

10、umn2 的平均值必須和 AVG、 SUM 等整合性查詢的關(guān) 鍵字一起使用。(2)HAVING : 必須和 GROUP BY 一起使用作為整合性的限制復(fù)合性的查詢SELECT *FROM table_name1WHERE EXISTS (SELECT *FROM table_name2WHERE conditions )說明:(1)WHERE 的 conditions 可以是另外一個的 query 。(2)EXISTS 在此是指存在與否。SELECT *FROM table_name1WHERE column1 IN (SELECT column1 FROM table_name2WHERE

11、conditions )說明:(1)IN 後面接的是一個集合，表示 column1 存在集合里面。(2)SELECT 出來的資料形態(tài)必須符合 column1 。其他查詢SELECT *FROM table_name1WHERE column1 LIKE 'x%'說明：LIKE必須和後面的x%'相呼應(yīng)表示以 x為開頭的字串。SELECT *FROM table_name1WHERE column1 IN ('xxx','yyy',.)說明 ：IN 後面接的是一個集合，表示 column1 存在集合里面。SELECT *FROM table_

12、name1WHERE column1 BETWEEN xx AND yy說明 ：BETWEEN 表示 column1 的值介於 xx 和 yy 之間。3. 更改資料 ：UPDATE table_nameSET column1='xxx'WHERE conditoins說明 ：(1) 更改某個欄位設(shè)定其值為 'xxx' 。(2) conditions 是所要符合的條件、若沒有 WHERE 則整個 table 的那個欄位都會全部被更改。4. 刪除資料 ：DELETE FROM table_nameWHERE conditions說明 :刪除符合條件的資料說明 :關(guān)于

13、 WHERE 條件后面如果包含有日期的比較，不同數(shù)據(jù)庫有不同的表達式。具體如下(1) 如果是 ACCESS 數(shù)據(jù)庫，則為 :WHERE mydate>#2000-01-01#(2) 如果是 ORACLE 數(shù)據(jù)庫，則為 :WHERE mydate>cast('2000-01-01' as date)WHERE mydate>to_date('2000-01-01','yyyy-mm-dd')在 Delphi 中寫成 * from abc where mydate>cast('+''''

14、+thedate+''''+' as date)');如果比較日期時間型，則為 :WHERE mydatetime>to_date('2000-01-01 10:00:01','yyyy-mm-dd hh24:mi:ss')DB2基礎(chǔ)：編碼DB2 SQL以獲得最佳性能簡介當(dāng)要保證用 IBM DB2? Universal Database?(DB2 UDB)和 Borland? 工具 (如 Delphi? 、C+Builder? 或 Kylix?) 構(gòu)建的企業(yè)應(yīng)用程序擁有最優(yōu)性能時， 程序員可以利用 DB2 優(yōu)

15、化器的能力來處 理即使是難以處理的” SQL語句并給出有效的存取路徑。盡管如此，拙劣編碼的SQL和應(yīng)用程序代碼仍可能給您帶來性能問題，通過學(xué)習(xí)幾條基本準(zhǔn)則可以輕易地避免這些問題。我將向您演示DB2 優(yōu)化器的工作方式，并提供編寫能發(fā)揮優(yōu)化器最大效率的 SQL 的準(zhǔn)則。但即使擁有了 DB2 的優(yōu)化能力，編寫有 效的 SQL 語句仍可能是一件復(fù)雜的事情。如果程序員和開發(fā)人員還不熟悉關(guān)系數(shù)據(jù)庫環(huán)境，這件事就尤 其顯得棘手。 因此，在我們深入研究編碼 SQL 以獲得最佳性能的細(xì)節(jié)之前， 先花一些時間來回顧 SQL 基 礎(chǔ)知識。基礎(chǔ)知識由于 SQL 與過程化語言不同，它提供了更高的抽象級別，因此它可以讓程

16、序員把精力集中到他們需要什么樣的數(shù)據(jù)，而不是如何檢索數(shù)據(jù)。您不必使用嵌入式數(shù)據(jù)導(dǎo)航指令來編碼SQL oDB2會分析SQL，并“在幕后 ”制定數(shù)據(jù)導(dǎo)航指令。這些數(shù)據(jù)導(dǎo)航指令叫作 存取路徑。讓 DBMS 確定到數(shù)據(jù)的最優(yōu)存取路徑 解除了程序員肩上沉重的負(fù)擔(dān)。此外，數(shù)據(jù)庫可以更好地理解它存儲的數(shù)據(jù)的狀態(tài)，從而可以生成到數(shù)據(jù) 的更有效和動態(tài)的存取路徑。其結(jié)果就是適當(dāng)使用的SQL可以用于更快的應(yīng)用程序開發(fā)。另一個SQL特性是它不僅僅是一種查詢語言。您還可以使用它來定義數(shù)據(jù)結(jié)構(gòu)；控制對數(shù)據(jù)的訪問；以及插入、修改和刪除數(shù)據(jù)的發(fā)生。通過提供一種公共語言，SQL簡化了 DBA、系統(tǒng)程序員、應(yīng)用程序員、系統(tǒng)分析員

17、和最終用戶之間的通信。當(dāng)項目的所有參與者都使用同一種語言時，他們之間所建立起來 的協(xié)作就可以減少整體系統(tǒng)開發(fā)時間。歷史證明，保證 SQL成功的最重要的一個特性就是它使用類似英語的語法輕松地檢索數(shù)據(jù)的能力。 理解這種語言比理解數(shù)據(jù)頁面的結(jié)構(gòu)和程序源代碼要容易得多：SELECT LASTNAMEFROM EMPWHERE EMPNO = '000010'想想看：當(dāng)訪問文件中的數(shù)據(jù)時，程序員必須編碼指令來打開文件、開始一個循環(huán)、讀取記錄、檢查EMPNO 字段是否等于適當(dāng)?shù)闹?、檢查文件結(jié)尾、回到循環(huán)的開頭等。SQL 本來就是非常靈活的。 它使用自由格式的結(jié)構(gòu)， 該結(jié)構(gòu)可以讓用戶開發(fā) S

18、QL 語句來適合他們的 需要。 DBMS 在執(zhí)行之前會分析每個 SQL 請求，以檢查語法是否正確和優(yōu)化該請求。 SQL 語句不需要 從任何給定的列中開始， 您可以將它們串在一行中， 或者把它們拆成幾行。 例如， 以下這條單行的 SQL 語 句與我前面使用的三行示例等價：SELECT LASTNAME FROM EMP WHERE EMPNO = '000010'SQL 的另一個靈活特性是您可以用許多形式不同但功能等價的方法來制定一個請求。例如： SQL 可 以連接表或嵌套查詢。您始終可以將嵌套查詢轉(zhuǎn)換成等價的連接。您可以在大量的函數(shù)和謂詞中看到這一 靈活性的其它示例。具有等價功

19、能的特性的示例包括：BETWEEN vs <= / >=IN vs 一系列和 OR 配合的謂詞INNER JOIN vs FROM 子句中串在一起并用逗號分隔的表OUTER JOIN vs 帶有 UNION 的簡單 SELECT 和相關(guān)的子查詢CASE 表達式 vs 復(fù)雜的 UNION ALL 語句SQL 展示的這一靈活性并不總是稱心的， 因為形式不同但功能等價的 SQL 公式可以提供非常不同的 性能。我將在本文的以后部分討論該靈活性所造成的結(jié)果，并提供開發(fā)有效的 SQL 的準(zhǔn)則。如我所說的， SQL 指定了要檢索或操作什么數(shù)據(jù)， 但沒有指定數(shù)據(jù)庫如何完成這些任務(wù)。 這就使 SQL

20、 本身變得很簡單。如果您能夠記得關(guān)系數(shù)據(jù)庫的一次處理一個集合 (set-at-a-time) 的特點，您就開始掌握 SQL 的本質(zhì)和性質(zhì)了。 一條 SQL 語句可以作用于多行。 作用于一組數(shù)據(jù)而不需要建立如何檢索和操作數(shù) 據(jù)的能力將 SQL 定義成非過程化語言因為 SQL 是一種非過程化語言，所以一條語句可以代替一系列過程。同樣，由于 SQL 使用集合級 別的處理以及 DB2 優(yōu)化查詢來確定數(shù)據(jù)導(dǎo)航邏輯，所以這是可能的。有時，如果不使用 SQL 語句，一 條或兩條 SQL 語句可以完成的任務(wù)就需要完整的過程化程序來完成。優(yōu)化器優(yōu)化器是 DB2 的心臟和靈魂。 它分析 SQL 語句并確定可以滿足

21、每條語句的最有效的存取路徑( 請參閱圖 1) 。 DB2 UDB 通過解析 SQL 語句來確定必須訪問哪些表和列，從而完成該操作。 DB2 優(yōu)化器然 后查詢存儲在 DB2 系統(tǒng)目錄中的系統(tǒng)信息和統(tǒng)計信息，以確定完成滿足 SQL 請求所必需的任務(wù)的最佳 方法。圖 1. 運行中的 DB2 優(yōu)化D日2 DBMSI z科I mEd削SOL Sl0tem»nt（s）Other Sysle-mInformal tonOB? OptlmirfifPlan TablesSytlem CallogOptimi2«dAccessPth優(yōu)化器在功能上等價于一個專家系統(tǒng)。專家系統(tǒng)是一個標(biāo)準(zhǔn)規(guī)則集合

22、，當(dāng)與情境數(shù)據(jù)組合時，它返回 一個專家”意見。例如，醫(yī)學(xué)專家系統(tǒng)采用一個規(guī)則集合，用來確定哪些藥可以用于哪些疾病，將規(guī)則集 與描述疾病癥狀的數(shù)據(jù)組合，并將知識庫應(yīng)用于輸入癥狀的列表。DB2優(yōu)化器會根據(jù)存儲在 DB2系統(tǒng)目錄中的情境數(shù)據(jù)和 SQL格式的查詢輸入來生成對數(shù)據(jù)檢索方法的專家意見。在DBMS中優(yōu)化數(shù)據(jù)訪問的概念是 DB2最強大的能力之一。請記住，您訪問 DB2數(shù)據(jù)時應(yīng)告訴DB2要檢索什么，而不是如何檢索。無論數(shù)據(jù)實際上是如何存儲和操作的，DB2和SQL都可以訪問該數(shù)據(jù)。從物理存儲特征中分離岀訪問標(biāo)準(zhǔn)叫作物理數(shù)據(jù)獨立性。DB2的優(yōu)化器是完成該物理數(shù)據(jù)獨立性的組件。如果您不要索引，DB2仍

23、然能夠訪問數(shù)據(jù)（盡管效率會降低）。如果將一列添加到正在被訪問的表中， DB2仍然可以在不更改程序代碼的情況下操作數(shù)據(jù)。因為到DB2數(shù)據(jù)的物理存取路徑并不是由程序員在應(yīng)用程序中編碼的，而是由 DB2生成的，所以這種情況是完全有可能發(fā)生的。這個特點與非 DBMS系統(tǒng)非常不同，在那種系統(tǒng)中，程序員必須知道數(shù)據(jù)的物理結(jié)構(gòu)。如果有索引，程序員就必須編寫適當(dāng)?shù)拇a來使用該索引。如果某人刪除了索引，程序就不能工作，除非程序員進行更 改。而使用 DB2和SQL就不必如此。這一靈活性完全歸功于DB2自動優(yōu)化數(shù)據(jù)操作請求的能力。優(yōu)化器根據(jù)許多信息執(zhí)行復(fù)雜的計算。要使優(yōu)化器的工作方式直觀化，可以將優(yōu)化器想象成執(zhí)行一

24、個 四步驟的過程：1、接收并驗證 SQL語句的語法。2、分析環(huán)境并優(yōu)化滿足 SQL語句的方法。3、 創(chuàng)建計算機可讀指令來執(zhí)行優(yōu)化的SQL oSQL語句？4、執(zhí)行指令或存儲它們以便將來執(zhí)行。這個過程的第二步是最有趣的。優(yōu)化器怎樣決定如何以它的方式執(zhí)行您可以發(fā)送的大量優(yōu)化器有許多類型的優(yōu)化SQL的策略。它如何選擇在優(yōu)化存取路徑中使用這些策略中的哪一個？IBM并沒有發(fā)布優(yōu)化器如何確定最佳存取路徑的真正和深入的詳細(xì)信息，但優(yōu)化器是一個基于成本的優(yōu)化器。這意味著優(yōu)化器將始終嘗試為每個查詢制定減少總體成本的存取路徑。要實現(xiàn)這個目標(biāo)，DB2優(yōu)化器會應(yīng)用查詢成本公式，該公式對每條可能的存取路徑的四個因素進行評

25、估和權(quán)衡：CPU成本、I/O成本、DB2系統(tǒng)目錄中的統(tǒng)計信息和實際的SQL語句。在DB2中提高Insert性能的技巧INSERT 處理過程概述首先讓我們快速地看看insert 一行時的處理步驟。這些步驟中的每一步都有優(yōu)化的潛力，對此我們在 后面會一一討論。1、 在客戶機準(zhǔn)備 語句。對于動態(tài) SQL，在語句執(zhí)行前就要做這一步，此處的性能是很重要的；對于 靜態(tài)SQL，這一步的性能實際上關(guān)系不大，因為語句的準(zhǔn)備是事先完成的。2、 在客戶機，將要插入的行的各個列值組裝起來，發(fā)送到 DB2服務(wù)器。3、DB2服務(wù)器確定將這一行插入到哪一頁中。4、 DB2在用于該頁的緩沖池中預(yù)留一個位置。如果DB2選定的是

26、一個已有的頁，那么就需要讀磁盤;如果使用一個新頁，則要在表空間（如果是SMS，也就是系統(tǒng)管理存儲的表空間）中為該頁物理地分配空間。插入了新行的每一頁最后都要從緩沖池寫入到磁盤。5、 在目標(biāo)頁中對該行進行格式化，并獲得該行上的一個X（exclusive，獨占的）行鎖。6、將反映該insert的一條記錄寫入到日志緩沖區(qū)中。7、 最后提交包含該insert的事務(wù)，如果這時日志緩沖區(qū)中的記錄還沒有被寫入日志文件的話，則將 這些記錄寫到日志文件中。此外，還可能發(fā)生很多類型的附加處理，這取決于數(shù)據(jù)庫配置，例如，索引或觸發(fā)器的存在。這種額外的處理對于性能來說也是意義重大的，我們在后面會討論到。insert的

27、替代方案在詳細(xì)討論insert的優(yōu)化之前，讓我們先考慮一下 insert的兩種替代方案：load和import。import 實用程序?qū)嶋H上是 SQL INSERT 的一個前端，但它的某些功能對于您來說也是有用的。load也有一些有用的額外功能，但是我們使用 load而不使用insert的主要原因是可以提高性能。load直接格式化數(shù)據(jù)頁，而避免了由于插入導(dǎo)致的對每一行進行處理的大部分開銷（例如，日志記錄在這里實際上是消除了 ）。而且，load可以更好地利用多 處理器機器上的并行性。在V8 load中有兩個新功能，它們對于load成為insert的替代方案有著特別的功效，這兩個功能是：從游標(biāo)裝載

28、和從調(diào)用層接 口 （CLI）應(yīng)用程序裝載。從游標(biāo)裝載這種方法可用于應(yīng)用程序的程序代碼 （通過 db2Load API） ，或用于 DB2 腳本。下面是后一種情況的 一個例子：declare staffcursor cursor forselect * from staff;load from staffcursor of cursor insert into myschema.new_staff;這兩行可以用下面一行替代：insert into myschema.new_staff select * from staff同等效的 INSERT . SELECT 語句相比，從游標(biāo)裝載幾乎可以提高

29、 20% 的性能。從 CLI 裝載這種方法顯然只限于調(diào)用層接口 （CLI） 應(yīng)用程序，但是它非?？?。這種技巧非常類似于數(shù)組插入，DB2附帶了這樣的示例，使用 load 時的速度是使用經(jīng)過完全優(yōu)化的數(shù)組插入時的兩倍，幾乎要比未經(jīng)優(yōu)化的 數(shù)組插入快 10 倍。所有 insert 可以改進的地方讓我們看看插入處理的一些必要步驟，以及我們可以用來優(yōu)化這些步驟的技巧。1. 語句準(zhǔn)備作為一條 SQL 語句， INSERT 語句在執(zhí)行之前必須由 DB2 進行編譯。 這一步驟可以自動發(fā)生 （例如 在 CLP 中，或者在一次 CLI SQLExecDirect 調(diào)用中 ），也可以顯式地進行 （ 例如，通過一條

30、SQL Prepare 、 CLI SQLPrepare 或 JDBC prepareStatement 語句 ） 。該編譯過程牽涉到授權(quán)檢查、優(yōu)化，以及將語句轉(zhuǎn) 化為可執(zhí)行格式時所需的其他一些活動。在編譯語句時，語句的訪問計劃被存儲在包緩存中。如果重復(fù)地執(zhí)行相同的 INSERT 語句，則該語句的訪問計劃 （通常 ）會進入到包緩存中，這樣就免除了 編譯的開銷。然而，如果 insert 語句對于每一行有不同的值，那么每一條語句都將被看成是惟一的，必須 單獨地進行編譯。因此，將像下面這樣的重復(fù)語句：insert into mytable values （1, 'abc'）inser

31、t into mytable values （2, 'def'）等等，換成帶有參數(shù)標(biāo)記的語句，一次準(zhǔn)備，重復(fù)執(zhí)行，這樣做是十分可取的：insert into mytable values （?, ?）使用參數(shù)標(biāo)記可以讓一系列的 insert 的運行速度提高數(shù)倍。 （在靜態(tài) SQL 程序中使用主機變量也可 以獲得類似的好處。 ）2. 發(fā)送列值到服務(wù)器可以歸為這一類的優(yōu)化技巧有好幾種。 最重要的一種技巧是在每條 insert 語句中包括多行， 這樣就可 以避免對于每一行都進行客戶機 -服務(wù)器通信，同時也減少了 DB2 開銷?？捎糜诙嘈胁迦氲募记捎校涸?VALUES 子句中包含多行的

32、內(nèi)容。 例如，下面的語句將插入三行： INSERT INTO mytable VALUES (1, 'abc'), (2, 'def'), (3, 'ghi')在 CLI 中使用數(shù)組插入 (array insert) 。這需要準(zhǔn)備一條帶參數(shù)標(biāo)記的 INSERT 語句，定義一個用于存儲 要插入的值的數(shù)組，將該數(shù)組綁定到參數(shù)標(biāo)記，以及對于每個數(shù)組中的一組內(nèi)容執(zhí)行一次 insert 。而且， 示例程序 sqllib/samples/cli/tbload.c 提供了數(shù)組插入的基本框架 (但是執(zhí)行的是 CLI LOAD) 。從不使用數(shù) 組改為使用包含 10

33、0 行的數(shù)組，可以將時間縮短大約 2.5 倍。所以應(yīng)該盡可能地使用包含至少 100 行 的數(shù)組。在 JDBC 中使用批處理操作。這跟 CLI 中的數(shù)組插入一樣，基于相同的概念，但是實現(xiàn)細(xì)節(jié)有所不同。 當(dāng)通過 prepareStatement 方法準(zhǔn)備了 insert 語句之后，剩下的步驟是針對每一列調(diào)用適當(dāng)?shù)?setXXXX 方法 (例如， setString 或 setInt) ，然后是 addBatch 。對于要插入的每一行，都要重復(fù)這些步驟，然后調(diào) 用 executeBatch 來執(zhí)行插入。要查看這方面的例子，請參閱 “參考資料 ”一節(jié)中的 JDBC Tutorial 。使用 load

34、將數(shù)據(jù)快速地裝入到一個 staging 表中，然后使用 INSERT . SELECT 填充主表。 ( 通過這種 方法節(jié)省下來的代價源于 load 的速度非?？?，再加上 INSERT . SELECT 是在 DB2 內(nèi)(在服務(wù)器上 ) 傳輸數(shù)據(jù)的，從而消除了通信上的代價。一般情況下我們不會使用這種方法，除非在INSERT . SELECT中還要另外做 load 無法完成的處理。如果不可能在一條 insert 語句中傳遞多行，那么最好是將多條 insert 語句組成一組，將它們一起從 客戶機傳遞到服務(wù)器。 (不過，這意味著每條 insert 都包含不同的值，都需要準(zhǔn)備，因而其性能實際上要 比使用

35、參數(shù)標(biāo)記情況下的性能更差一些。)將多條語句組合成一條語句可以通過Compound SQL 來實現(xiàn)：在 SQL 中，復(fù)合語句是通過在 CLI 中，復(fù)合語句可以通過 另一種生成復(fù)合語句的方法是在SQL_ATTR_CHAINING_BEGINBEGIN ATOMIC 或 BEGIN COMPOUND 語句創(chuàng)建的SQLExecDirect 和 SQLExecute 調(diào)用來建立。對于 DB2 V8 FixPak 4 (對一條預(yù)處理語句 ) 發(fā)出多個 SQLExecute 調(diào)用之前設(shè)置語句屬性，并在調(diào)用之后設(shè)置語句屬性 SQL_ATTR_CHAINING_END下面是關(guān)于該話題的其他一些建議： 如果可能的

36、話，讓客戶機與要存取的數(shù)據(jù)庫使用相同的代碼頁，以避免在服務(wù)器上的轉(zhuǎn)換代價。數(shù)據(jù)庫的 代碼頁可以通過運行 “get db cfg for 來確”定。在某些情況下， CLI 會自動執(zhí)行數(shù)據(jù)類型轉(zhuǎn)換， 但是這樣同時也會帶來看不見的 (小小的 )性能損耗。 因此 盡量使插入值直接處于與相應(yīng)列對應(yīng)的格式。CLI 中使用數(shù)組插入時，對于整個一組插入，應(yīng) 而不是對每一組數(shù)組內(nèi)容都執(zhí)行一次。對于個體( 如果使用了多維群集 (Multi-dimensional將應(yīng)用程序中與插入相關(guān)的設(shè)置開銷最小化。例如，當(dāng)在 該盡量保證對于每一列只執(zhí)行一次 SQLBindParameter 來說，這些調(diào)用的代價并不高，但是這些

37、代價是累積的。3. 找到存儲行的地方DB2 使用三種算法中的一種來確定將行插入到哪里。 Clustering ， MDC) ，則另當(dāng)別論，我們在這里不予討論。缺省模式是， DB2 搜索散布在表的各頁上的自由空間控制記錄 (Free Space Control Records ，F(xiàn)SCR) ， 以找到有足夠自由空間存放新行的頁。顯然，如果每頁上的自由空間都比較少的話，就要浪費很多的搜索 時間。為了應(yīng)付這一點， DB2 提供了 DB2MAXFSCRSEARCH 注冊表變量，以便允許將搜索范圍限制 為少于缺省的 5 頁。當(dāng)表是通過 ALTER TABLE 以 APPEND 模式放置時，就要使用第二種

38、算法。這樣就完全避免了FSCR 搜索，因為只需簡單地將行直接放到表的末尾。當(dāng)表有群集索引 (clustering index) 時，就要用到最后一種算法。在這種情況下， DB2 試圖將每一行插 入到有相似鍵值的一頁中。如果那一頁沒有空間了， DB2 就會嘗試附近的頁，如果附近的頁也沒有空間， DB2 就進行 FSCR 搜索。如果只考慮插入時間的優(yōu)化，那么使用 APPEND 模式對于批量插入是最快的一種方法，但是這種方 法的效果遠(yuǎn)不如我們這里討論的很多其他方法那么成效顯著。第二好的方法應(yīng)該是采用缺省算法，但是， 如果在最佳環(huán)境中，更改 DB2MAXFSCRSEARCH 的值影響很小，而在一個 I

39、/O 約束較少的環(huán)境中，這 種更改所造成的影響就比較可觀了。如果有群集索引，則對 insert 的性能會有很大的負(fù)面影響，這一點也不驚奇，因為使用群集索引的目 的就是通過在插入時做額外的工作來提高查詢(即 select) 性能的。如果的確需要群集索引，那么可以通過確保有足夠的自由空間來使其對插入的影響降至最小：使用 ALTER TABLE 增加 PCTFREE ，然后使用 REORG 預(yù)留自由空間。不過，如果允許太多自由空間的存在，則可能導(dǎo)致查詢時需要讀取額外的頁，這 反而大大違反了使用群集索引的本意。另一種選擇是，在批量插入之前先刪除群集索引，而后再重新創(chuàng)建 群集索引，也許這是最優(yōu)的方法 (

40、創(chuàng)建群集索引的開銷跟創(chuàng)建常規(guī)索引的開銷差不多，都不是很大，只是在 插入時有額外的開銷 ) 。4. 緩沖池、 I/O 和頁清除每一條 insert 在執(zhí)行時，都是先將新行存儲在一個頁中，并最終將那個頁寫到磁盤上。一旦像前面討 論的那樣指定了頁，那么在將行添加到該頁之前，該頁必須已經(jīng)在緩沖池中。對于批量插入，大部分頁都 是最新指派給表的，因此讓我們關(guān)注一下對新頁的處理。如果表在系統(tǒng)管理存儲的 (System Managed Storage ，SMS) 表空間中，當(dāng)需要新頁時，缺省情況下是 從文件系統(tǒng)中分別為每一頁分配空間。 但是， 如果對數(shù)據(jù)庫運行了 db2empfa 命令，那么每個 SMS 表空

41、 間就會為新頁一次性分配一個區(qū)段。我們建議運行 db2empfa 命令，并使用 32 頁的區(qū)段。對于數(shù)據(jù)庫管理的存儲 (Database Managed Storage ，DMS) 表空間，空間是在創(chuàng)建表空間時就預(yù)先分 配的，但是頁的區(qū)段則是在插入處理過程中指派給表的。與 SMS 相比， DMS 對空間的預(yù)分配可以提高 大約 20% 的性能 - 使用 DMS 時，更改區(qū)段大小并沒有明顯的效果。如果表上有索引，則對于每個插入的行，都要添加一個條目到每條索引。這要求在緩沖池中存在適當(dāng) 的索引頁。晚些時候我們將討論索引的維護，但是現(xiàn)在只需記住，插入時對緩沖池和 I/O 的考慮也類似地 適用于索引頁，

42、對于數(shù)據(jù)頁也是一樣。隨著插入的進行，越來越多的頁中將填入被插入的行，但是， DB2 不要求在 insert 或 Commit 后將 任何新插入的或更新后的數(shù)據(jù)或索引寫入到磁盤。(這是由于 DB2 的 writeahead 日志記錄算法。但是有一個例外，這將在關(guān)于日志記錄的小節(jié)中論述到 能會在數(shù)據(jù)庫關(guān)閉時才會輪到。）然而，這些頁需要在某一時刻寫到磁盤上，這個時刻可一般來說，對于批量插入，您會希望積極地進行 異步頁清除 （asynchronous page cleaning） ，這樣在 緩沖池中就總有可用于新頁的空余位置。頁清除率，或者說總?cè)表撀?，可能?dǎo)致計時上的很大不同，使得 性能比較容易產(chǎn)生誤

43、解。 例如， 如果使用 100,000 頁的緩沖池， 并且不存在頁清除，則批量插入在結(jié)束前 不會有任何新的或更改過的 （“臟的 ”頁）寫到磁盤上，但是隨后的操作 （例如選擇，甚至乎關(guān)閉數(shù)據(jù)庫 ）都將被 大大推遲， 因為這時有至多 100,000 個在插入時產(chǎn)生的臟頁要寫到磁盤上。 另一方面， 如果在同一情況下 進行了積極的頁清除，則批量插入過程可能要花更長的時間，但是此后緩沖池中的臟頁要少一些，從而使 得隨后的任務(wù)執(zhí)行起來性能更佳。至于那些結(jié)果中到底哪個要更好些，我們并不是總能分得清，但是通常 來說，將所有臟頁都存儲在緩沖池中是不可能的， 所以為了取得最佳性能， 采取有效的頁清除是有必要的。為

44、了盡可能好地進行頁清除：將 CHNGPGS_THRESH 數(shù)據(jù)庫配置參數(shù)的值從缺省的 60 減少到 5 這么低。 這個參數(shù)決定緩沖池中臟 頁的閾值百分比，當(dāng)臟頁達到這個百分比時，就會啟動頁清除。嘗試啟用注冊表變量 DB2_USE_ALTERNATE_PAGE_CLEANING（ 在 DB2 V8 FixPak 4 中最新提供 ）。 通過將這個變量設(shè)置成 ON ，可以為頁清除提供一種比缺省方法 （基于 CHNGPGS_THRESH 和 LSN 間 隙觸發(fā)器 ）更積極的方法。我沒有評測過其效果。請參閱FixPak 4 Release Notes 以了解這方面的信息。確保 NUM_IOCLEANER

45、S 數(shù)據(jù)庫配置參數(shù)的值至少等于數(shù)據(jù)庫中物理存儲設(shè)備的數(shù)量。至于 I/O 本身，當(dāng)需要建立索引時，可以通過使用盡可能大的緩沖池來將 I/O 活動減至最少。如果 不存在索引，則使用較大的緩沖池幫助不大，而只是推遲了 I/O 。也就是說，它允許所有新頁暫時安放在 緩沖池中，但是最終仍需要將這些頁寫到磁盤上。當(dāng)發(fā)生將頁寫到磁盤的 I/O 時，通過一些常規(guī)的 I/O 調(diào)優(yōu)步驟可以加快這一過程，例如：將表空間分布在多個容器 （這些容器映射到不同磁盤 ） 。 盡可能使用最快的 硬件 和存儲管理配置，這包括磁盤和通道速度、寫緩存以及并行寫等因素。 避免 RAID5（ 除非是與像 Shark 這樣有效的存儲設(shè)備

46、一起使用 ） 。5. 鎖缺省情況下， 每一個插入的行之上都有一個 X 鎖，這個鎖是在該行創(chuàng)建時就開始有的， 一直到 insert 被提交。有兩個跟 insert 和鎖相關(guān)的性能問題：為獲得和釋放鎖而產(chǎn)生的 CPU 開銷。 可能由于鎖沖突而導(dǎo)致的并發(fā)問題。對于經(jīng)過良好優(yōu)化的批量插入，由獲得每一行之上的一個 X 鎖以及后來釋放該鎖引起的 CPU 開銷 是比較可觀的。對于每個新行之上的鎖，惟一可以替代的是表鎖 （DB2 中沒有頁鎖 ）。當(dāng)使用表鎖時，耗時 減少了 3%。有 3 種情況可以導(dǎo)致表鎖的使用，在討論表鎖的缺點之前，我們先用一點時間看看這 3 種 情況：運行 ALTER TABLE LOCK

47、SIZE TABLE 。這將導(dǎo)致 DB2 為隨后使用該表的所有 SQL 語句使用一個表 鎖，直到 locksize 參數(shù)改回到 ROW 。運行 LOCK TABLE IN EXCLUSIVE MODE 。這將導(dǎo)致表上立即上了一個 X 鎖。注意，在下一次提交 （ 或 回滾 ）的時候，這個表將被釋放，因此，如果您要運行一個測試，測試中每N 行提交一次，那么就需要在每次提交之后重復(fù)執(zhí)行 LOCK TABLE 。使用缺省鎖，但是讓 LOCKLIST 和 MAXLOCKS 數(shù)據(jù)庫配置參數(shù)的值比較小。當(dāng)獲得少量的行鎖時，行 鎖就會自動地逐漸升級為表鎖。當(dāng)然，所有這些的缺點就在于并發(fā)的影響：如果表上有一個

48、X 鎖，那么其他應(yīng)用程序除非使用了隔 離級別 UR（ 未提交的讀 ），否則都不能訪問該表。 如果知道獨占訪問不會導(dǎo)致問題， 那么就應(yīng)該盡量使用表 鎖。但是，即使您堅持使用行鎖，也應(yīng)記住，在批量插入期間，表中可能存在數(shù)千個有 X 鎖的新行，所 以就可能與其他使用該表的應(yīng)用程序產(chǎn)生沖突。通過一些方法可以將這些沖突減至最少： 確保鎖的升級不會無故發(fā)生。您可能需要加大 LOCKLIST 和 /或 MAXLOCKS 的值，以允許插入應(yīng)用程 序有足夠的鎖。對于其他的應(yīng)用程序，使用隔離級別 UR對于 V8 FixPak 4 ，或許也可以通過 量設(shè)置為 YES ，那么在很多情況下，DB2_EVALUNCOMM

49、ITTED 注冊表變量來減少鎖沖突：如果將該變 只能獲得那些符合某個謂詞的行上的鎖， 而并不是獲得被檢查的所有 行上的鎖。發(fā)出一個 COMMIT 命令以釋放鎖，因此如果更頻繁地提交的話就足以減輕鎖沖突的負(fù)擔(dān)。注意 在 V7 中，存在涉及 insert 和鍵鎖的并發(fā)問題，但是在 V8 中，由于提供了 type-2 索引，這些問題實 際上已經(jīng)不見了。 如果要遷移到 V8 中來，那么應(yīng)該確保使用帶 CONVERT 關(guān)鍵字的 REORG INDEXES 命令，以便將索引從 type-1 轉(zhuǎn)換為 type-2 。在 V7 中，插入過程中可能使用 W 或 NW 鎖，但是在 V8 中只有在使用了 type-

50、1 索引或者隔離級別 為 RR 的情況下才會出現(xiàn)這兩種鎖。因此，應(yīng)盡可能避免這兩種情況。一條 insert 所據(jù)有的鎖 （通常是一個 X 鎖 ）通常不會受隔離級別的影響。例如，使用隔離級別UR 不會阻止從插入的行上獲得鎖。然而，如果使用了 INSERT . SELECT ，則隔離級別將影響從 SELECT 獲得的 鎖。6. 日志記錄缺省情況下，每條 insert 都會被記錄下來，以用于恢復(fù)。日志記錄首先被寫到 內(nèi)存 中的日志緩沖池， 然后再寫到日志文件，通常是在日志緩沖池已滿或者發(fā)生了一次提交時寫到日志文件的。對批量插入的日 志記錄的優(yōu)化實際上就是最小化日志記錄寫的次數(shù)，以及使寫的速度盡可能快

51、。這里首先考慮的是日志緩沖池的大小， 這由數(shù)據(jù)庫配置參數(shù) LOGBUFSZ 來控制。 該參數(shù)缺省值為 8 頁或 32 K ，這與大多數(shù)批量插入所需的理想日志緩沖池大小相比要小些。舉個例子，對于一個批量插入， 假設(shè)對于每一行的日志內(nèi)容有 200 字節(jié)，則在插入了 160 行之后，日志緩沖池就將被填滿。如果要插入 1000 行， 因為日志緩沖池將被填滿幾次， 再加上提交， 所以大概有 6 次日志寫。 如果將 LOGBUFSZ 的 值增加到64頁（256K）或者更大，緩沖池就不會被填滿， 這樣的話對于該批量插入就只有一次日志寫（在提交時）。通過使用更大的 LOGBUFSZ 可以獲得大約 13% 的性能提升。較大日志緩沖池的不利之處是， 緊急事故恢復(fù)所花的時間可能要稍微長一點。ALTER TABLE ACTIVATE NOT減少日志寫的另一種可能性是對新行要插入到的那個表使用LOGGED INITIALLY” （NLI） 。如果這樣做了，那么在該