DB2數(shù)據(jù)庫(kù)SQL語(yǔ)法參考手冊(cè)

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

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

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

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

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

6、資料(delete)四種模式，以下分 別介紹他們的語(yǔ)法:1.增加資料:INSERT INTO table_name (column1,column2,.)valueS ( value1,value2, .)說(shuō)明:(1)若沒(méi)有指定column 系統(tǒng)則會(huì)按表格內(nèi)的欄位順序填入資料。(2)欄位的資料形態(tài)和所填入的資料必須吻合。(3)table_name 也可以是景觀 view_name。INSERT INTO table_name (column1,column2,.)SELECT columnx,columny,. FROM another_table說(shuō)明:也可以經(jīng)過(guò)一個(gè)子查詢(subquery)

7、把別的表格的資料填入。2.查詢資料:基本查詢SELECT column1,columns2,.FROM table_name說(shuō)明:把table_name 的特定欄位資料全部列出來(lái)SELECT *FROM table_nameWHERE column1 = * AND column2 > yyy OR column3 <> zzz說(shuō)明:(1)'*'表示全部的欄位都列出來(lái)。(2)WHERE 之後是接條件式，把符合條件的資料列出來(lái)。SELECT column1,column2FROM table_nameORDER BY column2 DESC說(shuō)明:ORDER B

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

9、符合條件的資料共有幾筆。SELECT SUM(column1)FROM table_name說(shuō)明:(1)計(jì)算出總和，所選的欄位必須是可數(shù)的數(shù)字形態(tài)。(2)除此以外還有 AVG() 是計(jì)算平均、MAX()、MIN()計(jì)算最大最小值的整合性查詢。SELECT column1,AVG(column2)FROM table_nameGROUP BY column1HAVING AVG(column2) > xxx說(shuō)明:(1)GROUP BY: 以column1 為一組計(jì)算 column2 的平均值必須和 AVG、SUM等整合性查詢的關(guān)鍵字一起使用。(2)HAVING : 必須和 GROUP BY

10、 一起使用作為整合性的限制。復(fù)合性的查詢SELECT *FROM table_name1WHERE EXISTS (SELECT *FROM table_name2WHERE conditions )說(shuō)明:(1)WHERE 的 conditions 可以是另外一個(gè)的 query。(2)EXISTS 在此是指存在與否。SELECT *FROM table_name1WHERE column1 IN (SELECT column1 FROM table_name2WHERE conditions )說(shuō)明:(1)IN 後面接的是一個(gè)集合，表示column1 存在集合里面。(2)SELECT 出來(lái)的資

11、料形態(tài)必須符合 column1。其他查詢SELECT *FROM table_name1WHERE column1 LIKE 'x%'說(shuō)明:LIKE 必須和後面的'x%' 相呼應(yīng)表示以 x為開(kāi)頭的字串。SELECT *FROM table_name1WHERE column1 IN ('xxx','yyy',.)說(shuō)明:IN 後面接的是一個(gè)集合，表示column1 存在集合里面。SELECT *FROM table_name1WHERE column1 BETWEEN xx AND yy說(shuō)明:BETWEEN 表示 column1 的

12、值介於 xx 和 yy 之間。3.更改資料:UPDATE table_nameSET column1='xxx'WHERE conditoins說(shuō)明:(1)更改某個(gè)欄位設(shè)定其值為'xxx'。(2)conditions 是所要符合的條件、若沒(méi)有 WHERE 則整個(gè) table 的那個(gè)欄位都會(huì)全部被更改。4.刪除資料:DELETE FROM table_nameWHERE conditions說(shuō)明:刪除符合條件的資料。說(shuō)明:關(guān)于WHERE條件后面如果包含有日期的比較，不同數(shù)據(jù)庫(kù)有不同的表達(dá)式。具體如下:(1)如果是ACCESS數(shù)據(jù)庫(kù)，則為:WHERE mydate&

13、gt;#2000-01-01#(2)如果是ORACLE數(shù)據(jù)庫(kù)，則為:WHERE mydate>cast('2000-01-01' as date)WHERE mydate>to_date('2000-01-01','yyyy-mm-dd')在Delphi中寫(xiě)成:thedate='2000-01-01'query1.SQL.add('select * from abc where mydate>cast('+''''+thedate+'''&#

14、39;+' as date)');如果比較日期時(shí)間型，則為:WHERE mydatetime>to_date('2000-01-01 10:00:01','yyyy-mm-dd hh24:mi:ss')DB2基礎(chǔ): 編碼DB2 SQL以獲得最佳性能簡(jiǎn)介當(dāng)要保證用 IBM DB2® Universal Database(DB2 UDB)和 Borland® 工具(如 Delphi、C+Builder 或 Kylix)構(gòu)建的企業(yè)應(yīng)用程序擁有最優(yōu)性能時(shí)，程序員可以利用 DB2 優(yōu)化器的能力來(lái)處理即使是“難以處理的”SQL 語(yǔ)句

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

16、檢索數(shù)據(jù)。您不必使用嵌入式數(shù)據(jù)導(dǎo)航指令來(lái)編碼 SQL。DB2 會(huì)分析 SQL，并“在幕后”制定數(shù)據(jù)導(dǎo)航指令。這些數(shù)據(jù)導(dǎo)航指令叫作 存取路徑。讓 DBMS 確定到數(shù)據(jù)的最優(yōu)存取路徑解除了程序員肩上沉重的負(fù)擔(dān)。此外，數(shù)據(jù)庫(kù)可以更好地理解它存儲(chǔ)的數(shù)據(jù)的狀態(tài)，從而可以生成到數(shù)據(jù)的更有效和動(dòng)態(tài)的存取路徑。其結(jié)果就是適當(dāng)使用的 SQL 可以用于更快的應(yīng)用程序開(kāi)發(fā)。另一個(gè) SQL 特性是它不僅僅是一種查詢語(yǔ)言。您還可以使用它來(lái)定義數(shù)據(jù)結(jié)構(gòu);控制對(duì)數(shù)據(jù)的訪問(wèn);以及插入、修改和刪除數(shù)據(jù)的發(fā)生。通過(guò)提供一種公共語(yǔ)言，SQL 簡(jiǎn)化了 DBA、系統(tǒng)程序員、應(yīng)用程序員、系統(tǒng)分析員和最終用戶之間的通信。當(dāng)項(xiàng)目的所有參與者

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

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

19、/ >= · IN vs 一系列和 OR 配合的謂詞 · INNER JOIN vs FROM 子句中串在一起并用逗號(hào)分隔的表 · OUTER JOIN vs 帶有 UNION 的簡(jiǎn)單 SELECT 和相關(guān)的子查詢 · CASE 表達(dá)式 vs 復(fù)雜的 UNION ALL 語(yǔ)句SQL 展示的這一靈活性并不總是稱心的，因?yàn)樾问讲煌δ艿葍r(jià)的 SQL 公式可以提供非常不同的性能。我將在本文的以后部分討論該靈活性所造成的結(jié)果，并提供開(kāi)發(fā)有效的 SQL 的準(zhǔn)則。如我所說(shuō)的，SQL 指定了要檢索或操作什么數(shù)據(jù)，但沒(méi)有指定數(shù)據(jù)庫(kù)如何完成這些任務(wù)。這就使 SQL

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

21、的存取路徑(請(qǐng)參閱圖 1)。DB2 UDB 通過(guò)解析 SQL 語(yǔ)句來(lái)確定必須訪問(wèn)哪些表和列，從而完成該操作。DB2 優(yōu)化器然后查詢存儲(chǔ)在 DB2 系統(tǒng)目錄中的系統(tǒng)信息和統(tǒng)計(jì)信息，以確定完成滿足 SQL 請(qǐng)求所必需的任務(wù)的最佳方法。圖 1. 運(yùn)行中的 DB2 優(yōu)化優(yōu)化器在功能上等價(jià)于一個(gè)專(zhuān)家系統(tǒng)。專(zhuān)家系統(tǒng)是一個(gè)標(biāo)準(zhǔn)規(guī)則集合，當(dāng)與情境數(shù)據(jù)組合時(shí)，它返回一個(gè)“專(zhuān)家”意見(jiàn)。例如，醫(yī)學(xué)專(zhuān)家系統(tǒng)采用一個(gè)規(guī)則集合，用來(lái)確定哪些藥可以用于哪些疾病，將規(guī)則集與描述疾病癥狀的數(shù)據(jù)組合，并將知識(shí)庫(kù)應(yīng)用于輸入癥狀的列表。DB2 優(yōu)化器會(huì)根據(jù)存儲(chǔ)在 DB2 系統(tǒng)目錄中的情境數(shù)據(jù)和 SQL 格式的查詢輸入來(lái)生成對(duì)數(shù)據(jù)檢索

22、方法的專(zhuān)家意見(jiàn)。在 DBMS 中優(yōu)化數(shù)據(jù)訪問(wèn)的概念是 DB2 最強(qiáng)大的能力之一。請(qǐng)記住，您訪問(wèn) DB2 數(shù)據(jù)時(shí)應(yīng)告訴 DB2 要檢索什么，而不是如何檢索。無(wú)論數(shù)據(jù)實(shí)際上是如何存儲(chǔ)和操作的，DB2 和 SQL 都可以訪問(wèn)該數(shù)據(jù)。從物理存儲(chǔ)特征中分離出訪問(wèn)標(biāo)準(zhǔn)叫作物理數(shù)據(jù)獨(dú)立性。DB2 的優(yōu)化器是完成該物理數(shù)據(jù)獨(dú)立性的組件。如果您不要索引，DB2 仍然能夠訪問(wèn)數(shù)據(jù)(盡管效率會(huì)降低)。如果將一列添加到正在被訪問(wèn)的表中，DB2 仍然可以在不更改程序代碼的情況下操作數(shù)據(jù)。因?yàn)榈?DB2 數(shù)據(jù)的物理存取路徑并不是由程序員在應(yīng)用程序中編碼的，而是由 DB2 生成的，所以這種情況是完全有可能發(fā)生的。這個(gè)特點(diǎn)與

23、非 DBMS 系統(tǒng)非常不同，在那種系統(tǒng)中，程序員必須知道數(shù)據(jù)的物理結(jié)構(gòu)。如果有索引，程序員就必須編寫(xiě)適當(dāng)?shù)拇a來(lái)使用該索引。如果某人刪除了索引，程序就不能工作，除非程序員進(jìn)行更改。而使用 DB2 和 SQL 就不必如此。這一靈活性完全歸功于 DB2 自動(dòng)優(yōu)化數(shù)據(jù)操作請(qǐng)求的能力。優(yōu)化器根據(jù)許多信息執(zhí)行復(fù)雜的計(jì)算。要使優(yōu)化器的工作方式直觀化，可以將優(yōu)化器想象成執(zhí)行一個(gè)四步驟的過(guò)程：1、接收并驗(yàn)證 SQL 語(yǔ)句的語(yǔ)法。2、分析環(huán)境并優(yōu)化滿足 SQL 語(yǔ)句的方法。3、創(chuàng)建計(jì)算機(jī)可讀指令來(lái)執(zhí)行優(yōu)化的 SQL。4、執(zhí)行指令或存儲(chǔ)它們以便將來(lái)執(zhí)行。這個(gè)過(guò)程的第二步是最有趣的。優(yōu)化器怎樣決定如何以它的方式執(zhí)行

24、您可以發(fā)送的大量 SQL 語(yǔ)句?優(yōu)化器有許多類(lèi)型的優(yōu)化 SQL 的策略。它如何選擇在優(yōu)化存取路徑中使用這些策略中的哪一個(gè)?IBM 并沒(méi)有發(fā)布優(yōu)化器如何確定最佳存取路徑的真正和深入的詳細(xì)信息，但優(yōu)化器是一個(gè) 基于成本的優(yōu)化器。這意味著優(yōu)化器將始終嘗試為每個(gè)查詢制定減少總體成本的存取路徑。要實(shí)現(xiàn)這個(gè)目標(biāo)，DB2 優(yōu)化器會(huì)應(yīng)用查詢成本公式，該公式對(duì)每條可能的存取路徑的四個(gè)因素進(jìn)行評(píng)估和權(quán)衡：CPU 成本、I/O 成本、DB2 系統(tǒng)目錄中的統(tǒng)計(jì)信息和實(shí)際的 SQL 語(yǔ)句。在DB2中提高Insert性能的技巧INSERT 處理過(guò)程概述首先讓我們快速地看看insert一行時(shí)的處理步驟。這些步驟中的每一步都

25、有優(yōu)化的潛力，對(duì)此我們?cè)诤竺鏁?huì)一一討論。1、在客戶機(jī)準(zhǔn)備 語(yǔ)句。對(duì)于動(dòng)態(tài) SQL，在語(yǔ)句執(zhí)行前就要做這一步，此處的性能是很重要的;對(duì)于靜態(tài) SQL，這一步的性能實(shí)際上關(guān)系不大，因?yàn)檎Z(yǔ)句的準(zhǔn)備是事先完成的。2、在客戶機(jī)，將要插入的行的各個(gè) 列值組裝起來(lái)，發(fā)送到 DB2 服務(wù)器。3、DB2 服務(wù)器確定將這一行插入到哪一頁(yè)中。4、DB2 在 用于該頁(yè)的緩沖池中預(yù)留一個(gè)位置。如果 DB2 選定的是一個(gè)已有的頁(yè)，那么就需要讀磁盤(pán);如果使用一個(gè)新頁(yè)，則要在表空間(如果是SMS，也就是系統(tǒng)管理存儲(chǔ)的表空間)中為該頁(yè)物理地分配空間。插入了新行的每一頁(yè)最后都要從緩沖池寫(xiě)入到磁盤(pán)。5、在目標(biāo)頁(yè)中對(duì)該行進(jìn)行格式化，

26、并獲得該行上的一個(gè)X(exclusive，獨(dú)占的) 行鎖。6、將反映該 insert 的一條記錄寫(xiě)入到日志緩沖區(qū)中。7、最后提交包含該 insert 的事務(wù)，如果這時(shí)日志緩沖區(qū)中的記錄還沒(méi)有被寫(xiě)入日志文件的話，則將這些記錄寫(xiě)到日志文件中。此外，還可能發(fā)生很多類(lèi)型的附加處理，這取決于數(shù)據(jù)庫(kù)配置，例如，索引或觸發(fā)器的存在。這種額外的處理對(duì)于性能來(lái)說(shuō)也是意義重大的，我們?cè)诤竺鏁?huì)討論到。insert 的替代方案在詳細(xì)討論 insert 的優(yōu)化之前，讓我們先考慮一下 insert 的兩種替代方案：load 和 import。import 實(shí)用程序?qū)嶋H上是 SQL INSERT 的一個(gè)前端，但它的某些功能

27、對(duì)于您來(lái)說(shuō)也是有用的。load 也有一些有用的額外功能，但是我們使用 load 而不使用 insert 的主要原因是可以提高性能。load 直接格式化數(shù)據(jù)頁(yè)，而避免了由于插入導(dǎo)致的對(duì)每一行進(jìn)行處理的大部分開(kāi)銷(xiāo)(例如，日志記錄在這里實(shí)際上是消除了)。而且，load 可以更好地利用多處理器機(jī)器上的并行性。在 V8 load 中有兩個(gè)新功能，它們對(duì)于 load 成為 insert 的替代方案有著特別的功效，這兩個(gè)功能是：從游標(biāo)裝載和從調(diào)用層接口(CLI)應(yīng)用程序裝載。從游標(biāo)裝載這種方法可用于應(yīng)用程序的程序代碼(通過(guò) db2Load API)，或用于 DB2 腳本。下面是后一種情況的一個(gè)例子：decl

28、are 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 語(yǔ)句相比，從游標(biāo)裝載幾乎可以提高 20% 的性能。從 CLI 裝載這種方法顯然只限于調(diào)用層接口(CLI)應(yīng)用程序，但是它非?？?。這種技巧非常類(lèi)似于數(shù)組插入，DB2 附帶了這樣的示例，使用 load 時(shí)的速度是使用經(jīng)

29、過(guò)完全優(yōu)化的數(shù)組插入時(shí)的兩倍，幾乎要比未經(jīng)優(yōu)化的數(shù)組插入快 10 倍。所有 insert 可以改進(jìn)的地方讓我們看看插入處理的一些必要步驟，以及我們可以用來(lái)優(yōu)化這些步驟的技巧。1. 語(yǔ)句準(zhǔn)備作為一條 SQL 語(yǔ)句，INSERT 語(yǔ)句在執(zhí)行之前必須由 DB2 進(jìn)行編譯。這一步驟可以自動(dòng)發(fā)生(例如在 CLP 中，或者在一次 CLI SQLExecDirect 調(diào)用中)，也可以顯式地進(jìn)行(例如，通過(guò)一條 SQL Prepare、CLI SQLPrepare 或 JDBC prepareStatement 語(yǔ)句)。該編譯過(guò)程牽涉到授權(quán)檢查、優(yōu)化，以及將語(yǔ)句轉(zhuǎn)化為可執(zhí)行格式時(shí)所需的其他一些活動(dòng)。在編譯語(yǔ)句

30、時(shí)，語(yǔ)句的訪問(wèn)計(jì)劃被存儲(chǔ)在包緩存中。如果重復(fù)地執(zhí)行相同的 INSERT 語(yǔ)句，則該語(yǔ)句的訪問(wèn)計(jì)劃(通常)會(huì)進(jìn)入到包緩存中，這樣就免除了編譯的開(kāi)銷(xiāo)。然而，如果 insert 語(yǔ)句對(duì)于每一行有不同的值，那么每一條語(yǔ)句都將被看成是惟一的，必須單獨(dú)地進(jìn)行編譯。因此，將像下面這樣的重復(fù)語(yǔ)句：insert into mytable values (1, 'abc')insert into mytable values (2, 'def')等等，換成帶有參數(shù)標(biāo)記的語(yǔ)句，一次準(zhǔn)備，重復(fù)執(zhí)行，這樣做是十分可取的：insert into mytable values (?, ?)使

31、用參數(shù)標(biāo)記可以讓一系列的 insert 的運(yùn)行速度提高數(shù)倍。(在靜態(tài) SQL 程序中使用主機(jī)變量也可以獲得類(lèi)似的好處。)2. 發(fā)送列值到服務(wù)器可以歸為這一類(lèi)的優(yōu)化技巧有好幾種。最重要的一種技巧是在每條 insert 語(yǔ)句中包括多行，這樣就可以避免對(duì)于每一行都進(jìn)行客戶機(jī)-服務(wù)器通信，同時(shí)也減少了 DB2 開(kāi)銷(xiāo)。可用于多行插入的技巧有：· 在 VALUES 子句中包含多行的內(nèi)容。例如，下面的語(yǔ)句將插入三行：INSERT INTO mytable VALUES (1, 'abc'), (2, 'def'), (3, 'ghi') ·

32、 在 CLI 中使用數(shù)組插入(array insert)。這需要準(zhǔn)備一條帶參數(shù)標(biāo)記的 INSERT 語(yǔ)句，定義一個(gè)用于存儲(chǔ)要插入的值的數(shù)組，將該數(shù)組綁定到參數(shù)標(biāo)記，以及對(duì)于每個(gè)數(shù)組中的一組內(nèi)容執(zhí)行一次 insert。而且，示例程序 sqllib/samples/cli/tbload.c 提供了數(shù)組插入的基本框架(但是執(zhí)行的是 CLI LOAD)。從不使用數(shù)組改為使用包含 100 行的數(shù)組，可以將時(shí)間縮短大約 2.5 倍。所以應(yīng)該盡可能地使用包含至少 100 行的數(shù)組。 · 在 JDBC 中使用批處理操作。這跟 CLI 中的數(shù)組插入一樣，基于相同的概念，但是實(shí)現(xiàn)細(xì)節(jié)有所不同。當(dāng)通過(guò) p

33、repareStatement 方法準(zhǔn)備了 insert 語(yǔ)句之后，剩下的步驟是針對(duì)每一列調(diào)用適當(dāng)?shù)?setXXXX 方法(例如，setString 或 setInt)，然后是 addBatch。對(duì)于要插入的每一行，都要重復(fù)這些步驟，然后調(diào)用 executeBatch 來(lái)執(zhí)行插入。要查看這方面的例子，請(qǐng)參閱“參考資料”一節(jié)中的 JDBC Tutorial。 · 使用 load 將數(shù)據(jù)快速地裝入到一個(gè) staging 表中，然后使用 INSERT . SELECT 填充主表。(通過(guò)這種方法節(jié)省下來(lái)的代價(jià)源于 load 的速度非常快，再加上 INSERT . SELECT 是在 DB2

34、內(nèi)(在服務(wù)器上)傳輸數(shù)據(jù)的，從而消除了通信上的代價(jià)。一般情況下我們不會(huì)使用這種方法，除非在 INSERT . SELECT 中還要另外做 load 無(wú)法完成的處理。如果不可能在一條 insert 語(yǔ)句中傳遞多行，那么最好是將多條 insert 語(yǔ)句組成一組，將它們一起從客戶機(jī)傳遞到服務(wù)器。(不過(guò)，這意味著每條 insert 都包含不同的值，都需要準(zhǔn)備，因而其性能實(shí)際上要比使用參數(shù)標(biāo)記情況下的性能更差一些。)將多條語(yǔ)句組合成一條語(yǔ)句可以通過(guò) Compound SQL 來(lái)實(shí)現(xiàn)：· 在 SQL 中，復(fù)合語(yǔ)句是通過(guò) BEGIN ATOMIC 或 BEGIN COMPOUND 語(yǔ)句創(chuàng)建的。 &

35、#183; 在 CLI 中，復(fù)合語(yǔ)句可以通過(guò) SQLExecDirect 和 SQLExecute 調(diào)用來(lái)建立。對(duì)于 DB2 V8 FixPak 4，另一種生成復(fù)合語(yǔ)句的方法是在(對(duì)一條預(yù)處理語(yǔ)句)發(fā)出多個(gè) SQLExecute 調(diào)用之前設(shè)置語(yǔ)句屬性 SQL_ATTR_CHAINING_BEGIN，并在調(diào)用之后設(shè)置語(yǔ)句屬性 SQL_ATTR_CHAINING_END。下面是關(guān)于該話題的其他一些建議：· 如果可能的話，讓客戶機(jī)與要存取的數(shù)據(jù)庫(kù)使用相同的代碼頁(yè)，以避免在服務(wù)器上的轉(zhuǎn)換代價(jià)。數(shù)據(jù)庫(kù)的代碼頁(yè)可以通過(guò)運(yùn)行“get db cfg for ”來(lái)確定。 · 在某些情況下，C

36、LI 會(huì)自動(dòng)執(zhí)行數(shù)據(jù)類(lèi)型轉(zhuǎn)換，但是這樣同時(shí)也會(huì)帶來(lái)看不見(jiàn)的(小小的)性能損耗。因此，盡量使插入值直接處于與相應(yīng)列對(duì)應(yīng)的格式。 · 將應(yīng)用程序中與插入相關(guān)的設(shè)置開(kāi)銷(xiāo)最小化。例如，當(dāng)在 CLI 中使用數(shù)組插入時(shí)，對(duì)于整個(gè)一組插入，應(yīng)該盡量保證對(duì)于每一列只執(zhí)行一次 SQLBindParameter，而不是對(duì)每一組數(shù)組內(nèi)容都執(zhí)行一次。對(duì)于個(gè)體來(lái)說(shuō)，這些調(diào)用的代價(jià)并不高，但是這些代價(jià)是累積的。3. 找到存儲(chǔ)行的地方DB2 使用三種算法中的一種來(lái)確定將行插入到哪里。(如果使用了多維群集(Multi-dimensional Clustering，MDC)，則另當(dāng)別論，我們?cè)谶@里不予討論。)缺省模式

37、是，DB2 搜索散布在表的各頁(yè)上的自由空間控制記錄(Free Space Control Records，F(xiàn)SCR)，以找到有足夠自由空間存放新行的頁(yè)。顯然，如果每頁(yè)上的自由空間都比較少的話，就要浪費(fèi)很多的搜索時(shí)間。為了應(yīng)付這一點(diǎn)， DB2 提供了 DB2MAXFSCRSEARCH 注冊(cè)表變量，以便允許將搜索范圍限制為少于缺省的 5 頁(yè)。當(dāng)表是通過(guò) ALTER TABLE 以 APPEND 模式放置時(shí)，就要使用第二種算法。這樣就完全避免了 FSCR 搜索，因?yàn)橹恍韬?jiǎn)單地將行直接放到表的末尾。當(dāng)表有群集索引(clustering index)時(shí)，就要用到最后一種算法。在這種情況下，DB2 試圖將

38、每一行插入到有相似鍵值的一頁(yè)中。如果那一頁(yè)沒(méi)有空間了，DB2 就會(huì)嘗試附近的頁(yè)，如果附近的頁(yè)也沒(méi)有空間，DB2 就進(jìn)行 FSCR 搜索。如果只考慮插入時(shí)間的優(yōu)化，那么使用 APPEND 模式對(duì)于批量插入是最快的一種方法，但是這種方法的效果遠(yuǎn)不如我們這里討論的很多其他方法那么成效顯著。第二好的方法應(yīng)該是采用缺省算法，但是，如果在最佳環(huán)境中，更改 DB2MAXFSCRSEARCH 的值影響很小，而在一個(gè) I/O 約束較少的環(huán)境中，這種更改所造成的影響就比較可觀了。如果有群集索引，則對(duì) insert 的性能會(huì)有很大的負(fù)面影響，這一點(diǎn)也不驚奇，因?yàn)槭褂萌杭饕哪康木褪峭ㄟ^(guò)在插入時(shí)做額外的工作來(lái)提高查

39、詢(即 select)性能的。如果的確需要群集索引，那么可以通過(guò)確保有足夠的自由空間來(lái)使其對(duì)插入的影響降至最?。菏褂?ALTER TABLE 增加 PCTFREE，然后使用 REORG 預(yù)留自由空間。不過(guò)，如果允許太多自由空間的存在，則可能導(dǎo)致查詢時(shí)需要讀取額外的頁(yè)，這反而大大違反了使用群集索引的本意。另一種選擇是，在批量插入之前先刪除群集索引，而后再重新創(chuàng)建群集索引，也許這是最優(yōu)的方法(創(chuàng)建群集索引的開(kāi)銷(xiāo)跟創(chuàng)建常規(guī)索引的開(kāi)銷(xiāo)差不多，都不是很大，只是在插入時(shí)有額外的開(kāi)銷(xiāo))。4. 緩沖池、I/O 和頁(yè)清除每一條 insert 在執(zhí)行時(shí)，都是先將新行存儲(chǔ)在一個(gè)頁(yè)中，并最終將那個(gè)頁(yè)寫(xiě)到磁盤(pán)上。一旦像

40、前面討論的那樣指定了頁(yè)，那么在將行添加到該頁(yè)之前，該頁(yè)必須已經(jīng)在緩沖池中。對(duì)于批量插入，大部分頁(yè)都是最新指派給表的，因此讓我們關(guān)注一下對(duì)新頁(yè)的處理。如果表在系統(tǒng)管理存儲(chǔ)的(System Managed Storage，SMS)表空間中，當(dāng)需要新頁(yè)時(shí)，缺省情況下是從文件系統(tǒng)中分別為每一頁(yè)分配空間。但是，如果對(duì)數(shù)據(jù)庫(kù)運(yùn)行了 db2empfa 命令，那么每個(gè) SMS 表空間就會(huì)為新頁(yè)一次性分配一個(gè)區(qū)段。我們建議運(yùn)行 db2empfa 命令，并使用 32 頁(yè)的區(qū)段。對(duì)于數(shù)據(jù)庫(kù)管理的存儲(chǔ)(Database Managed Storage，DMS)表空間，空間是在創(chuàng)建表空間時(shí)就預(yù)先分配的，但是頁(yè)的區(qū)段則是

41、在插入處理過(guò)程中指派給表的。與 SMS 相比，DMS 對(duì)空間的預(yù)分配可以提高大約 20% 的性能 - 使用 DMS 時(shí)，更改區(qū)段大小并沒(méi)有明顯的效果。如果表上有索引，則對(duì)于每個(gè)插入的行，都要添加一個(gè)條目到每條索引。這要求在緩沖池中存在適當(dāng)?shù)乃饕?yè)。晚些時(shí)候我們將討論索引的維護(hù)，但是現(xiàn)在只需記住，插入時(shí)對(duì)緩沖池和 I/O 的考慮也類(lèi)似地適用于索引頁(yè)，對(duì)于數(shù)據(jù)頁(yè)也是一樣。隨著插入的進(jìn)行，越來(lái)越多的頁(yè)中將填入被插入的行，但是，DB2 不要求在 insert 或 Commit 后將任何新插入的或更新后的數(shù)據(jù)或索引寫(xiě)入到磁盤(pán)。(這是由于 DB2 的 writeahead 日志記錄算法。但是有一個(gè)例外，這

42、將在關(guān)于日志記錄的小節(jié)中論述到。)然而，這些頁(yè)需要在某一時(shí)刻寫(xiě)到磁盤(pán)上，這個(gè)時(shí)刻可能會(huì)在數(shù)據(jù)庫(kù)關(guān)閉時(shí)才會(huì)輪到。一般來(lái)說(shuō)，對(duì)于批量插入，您會(huì)希望積極地進(jìn)行 異步頁(yè)清除(asynchronous page cleaning)，這樣在緩沖池中就總有可用于新頁(yè)的空余位置。頁(yè)清除率，或者說(shuō)總?cè)表?yè)率，可能導(dǎo)致計(jì)時(shí)上的很大不同，使得性能比較容易產(chǎn)生誤解。例如，如果使用 100,000 頁(yè)的緩沖池，并且不存在頁(yè)清除，則批量插入在結(jié)束前不會(huì)有任何新的或更改過(guò)的(“臟的”)頁(yè)寫(xiě)到磁盤(pán)上，但是隨后的操作(例如選擇，甚至乎關(guān)閉數(shù)據(jù)庫(kù))都將被大大推遲，因?yàn)檫@時(shí)有至多 100,000 個(gè)在插入時(shí)產(chǎn)生的臟頁(yè)要寫(xiě)到磁盤(pán)上。另

43、一方面，如果在同一情況下進(jìn)行了積極的頁(yè)清除，則批量插入過(guò)程可能要花更長(zhǎng)的時(shí)間，但是此后緩沖池中的臟頁(yè)要少一些，從而使得隨后的任務(wù)執(zhí)行起來(lái)性能更佳。至于那些結(jié)果中到底哪個(gè)要更好些，我們并不是總能分得清，但是通常來(lái)說(shuō)，將所有臟頁(yè)都存儲(chǔ)在緩沖池中是不可能的，所以為了取得最佳性能，采取有效的頁(yè)清除是有必要的。為了盡可能好地進(jìn)行頁(yè)清除：· 將 CHNGPGS_THRESH 數(shù)據(jù)庫(kù)配置參數(shù)的值從缺省的 60 減少到 5 這么低。這個(gè)參數(shù)決定緩沖池中臟頁(yè)的閾值百分比，當(dāng)臟頁(yè)達(dá)到這個(gè)百分比時(shí)，就會(huì)啟動(dòng)頁(yè)清除。 · 嘗試啟用注冊(cè)表變量 DB2_USE_ALTERNATE_PAGE_CLEAN

44、ING(在 DB2 V8 FixPak 4 中最新提供)。通過(guò)將這個(gè)變量設(shè)置成 ON，可以為頁(yè)清除提供一種比缺省方法(基于 CHNGPGS_THRESH 和 LSN 間隙觸發(fā)器)更積極的方法。我沒(méi)有評(píng)測(cè)過(guò)其效果。請(qǐng)參閱 FixPak 4 Release Notes 以了解這方面的信息。 · 確保 NUM_IOCLEANERS 數(shù)據(jù)庫(kù)配置參數(shù)的值至少等于數(shù)據(jù)庫(kù)中物理存儲(chǔ)設(shè)備的數(shù)量。至于 I/O 本身，當(dāng)需要建立索引時(shí)，可以通過(guò)使用盡可能大的緩沖池來(lái)將 I/O 活動(dòng)減至最少。如果不存在索引，則使用較大的緩沖池幫助不大，而只是推遲了 I/O。也就是說(shuō)，它允許所有新頁(yè)暫時(shí)安放在緩沖池中，但是

45、最終仍需要將這些頁(yè)寫(xiě)到磁盤(pán)上。當(dāng)發(fā)生將頁(yè)寫(xiě)到磁盤(pán)的 I/O 時(shí)，通過(guò)一些常規(guī)的 I/O 調(diào)優(yōu)步驟可以加快這一過(guò)程，例如：· 將表空間分布在多個(gè)容器(這些容器映射到不同磁盤(pán))。 · 盡可能使用最快的硬件和存儲(chǔ)管理配置，這包括磁盤(pán)和通道速度、寫(xiě)緩存以及并行寫(xiě)等因素。 · 避免 RAID5(除非是與像 Shark 這樣有效的存儲(chǔ)設(shè)備一起使用)。5. 鎖缺省情況下，每一個(gè)插入的行之上都有一個(gè) X 鎖，這個(gè)鎖是在該行創(chuàng)建時(shí)就開(kāi)始有的，一直到 insert 被提交。有兩個(gè)跟 insert 和鎖相關(guān)的性能問(wèn)題：· 為獲得和釋放鎖而產(chǎn)生的 CPU 開(kāi)銷(xiāo)。 ·

46、可能由于鎖沖突而導(dǎo)致的并發(fā)問(wèn)題。對(duì)于經(jīng)過(guò)良好優(yōu)化的批量插入，由獲得每一行之上的一個(gè) X 鎖以及后來(lái)釋放該鎖引起的 CPU 開(kāi)銷(xiāo)是比較可觀的。對(duì)于每個(gè)新行之上的鎖，惟一可以替代的是表鎖(DB2 中沒(méi)有頁(yè)鎖)。當(dāng)使用表鎖時(shí)，耗時(shí)減少了 3%。有 3 種情況可以導(dǎo)致表鎖的使用，在討論表鎖的缺點(diǎn)之前，我們先用一點(diǎn)時(shí)間看看這 3 種情況：· 運(yùn)行 ALTER TABLE LOCKSIZE TABLE。這將導(dǎo)致 DB2 為隨后使用該表的所有 SQL 語(yǔ)句使用一個(gè)表鎖，直到 locksize 參數(shù)改回到 ROW。 · 運(yùn)行 LOCK TABLE IN EXCLUSIVE MODE。這將導(dǎo)

47、致表上立即上了一個(gè) X 鎖。注意，在下一次提交(或回滾)的時(shí)候，這個(gè)表將被釋放，因此，如果您要運(yùn)行一個(gè)測(cè)試，測(cè)試中每 N 行提交一次，那么就需要在每次提交之后重復(fù)執(zhí)行 LOCK TABLE。 · 使用缺省鎖，但是讓 LOCKLIST 和 MAXLOCKS 數(shù)據(jù)庫(kù)配置參數(shù)的值比較小。當(dāng)獲得少量的行鎖時(shí)，行鎖就會(huì)自動(dòng)地逐漸升級(jí)為表鎖。當(dāng)然，所有這些的缺點(diǎn)就在于并發(fā)的影響：如果表上有一個(gè) X 鎖，那么其他應(yīng)用程序除非使用了隔離級(jí)別 UR(未提交的讀)，否則都不能訪問(wèn)該表。如果知道獨(dú)占訪問(wèn)不會(huì)導(dǎo)致問(wèn)題，那么就應(yīng)該盡量使用表鎖。但是，即使您堅(jiān)持使用行鎖，也應(yīng)記住，在批量插入期間，表中可能存在數(shù)

48、千個(gè)有 X 鎖的新行，所以就可能與其他使用該表的應(yīng)用程序產(chǎn)生沖突。通過(guò)一些方法可以將這些沖突減至最少：· 確保鎖的升級(jí)不會(huì)無(wú)故發(fā)生。您可能需要加大 LOCKLIST 和/或 MAXLOCKS 的值，以允許插入應(yīng)用程序有足夠的鎖。 · 對(duì)于其他的應(yīng)用程序，使用隔離級(jí)別 UR。 · 對(duì)于 V8 FixPak 4，或許也可以通過(guò) DB2_EVALUNCOMMITTED 注冊(cè)表變量來(lái)減少鎖沖突：如果將該變量設(shè)置為 YES，那么在很多情況下，只能獲得那些符合某個(gè)謂詞的行上的鎖，而并不是獲得被檢查的所有行上的鎖。 · 發(fā)出一個(gè) COMMIT 命令以釋放鎖，因此如果更

49、頻繁地提交的話就足以減輕鎖沖突的負(fù)擔(dān)。注意· 在 V7 中，存在涉及 insert 和鍵鎖的并發(fā)問(wèn)題，但是在 V8 中，由于提供了 type-2 索引，這些問(wèn)題實(shí)際上已經(jīng)不見(jiàn)了。如果要遷移到 V8 中來(lái)，那么應(yīng)該確保使用帶 CONVERT 關(guān)鍵字的 REORG INDEXES 命令，以便將索引從 type-1 轉(zhuǎn)換為 type-2。 · 在 V7 中，插入過(guò)程中可能使用 W 或 NW 鎖，但是在 V8 中只有在使用了 type-1 索引或者隔離級(jí)別為 RR 的情況下才會(huì)出現(xiàn)這兩種鎖。因此，應(yīng)盡可能避免這兩種情況。 · 一條 insert 所據(jù)有的鎖(通常是一個(gè) X

50、 鎖)通常不會(huì)受隔離級(jí)別的影響。例如，使用隔離級(jí)別 UR 不會(huì)阻止從插入的行上獲得鎖。然而，如果使用了 INSERT . SELECT，則隔離級(jí)別將影響從 SELECT 獲得的鎖。6. 日志記錄缺省情況下，每條 insert 都會(huì)被記錄下來(lái)，以用于恢復(fù)。日志記錄首先被寫(xiě)到內(nèi)存中的日志緩沖池，然后再寫(xiě)到日志文件，通常是在日志緩沖池已滿或者發(fā)生了一次提交時(shí)寫(xiě)到日志文件的。對(duì)批量插入的日志記錄的優(yōu)化實(shí)際上就是最小化日志記錄寫(xiě)的次數(shù)，以及使寫(xiě)的速度盡可能快。這里首先考慮的是日志緩沖池的大小，這由數(shù)據(jù)庫(kù)配置參數(shù) LOGBUFSZ 來(lái)控制。該參數(shù)缺省值為 8 頁(yè)或 32 K，這與大多數(shù)批量插入所需的理想日

51、志緩沖池大小相比要小些。舉個(gè)例子，對(duì)于一個(gè)批量插入，假設(shè)對(duì)于每一行的日志內(nèi)容有 200 字節(jié)，則在插入了 160 行之后，日志緩沖池就將被填滿。如果要插入 1000 行，因?yàn)槿罩揪彌_池將被填滿幾次，再加上提交，所以大概有 6 次日志寫(xiě)。如果將 LOGBUFSZ 的值增加到 64 頁(yè)(256K)或者更大，緩沖池就不會(huì)被填滿，這樣的話對(duì)于該批量插入就只有一次日志寫(xiě)(在提交時(shí))。通過(guò)使用更大的 LOGBUFSZ 可以獲得大約 13% 的性能提升。較大日志緩沖池的不利之處是，緊急事故恢復(fù)所花的時(shí)間可能要稍微長(zhǎng)一點(diǎn)。減少日志寫(xiě)的另一種可能性是對(duì)新行要插入到的那個(gè)表使用“ALTER TABLE ACTIVATE NOT LOGGED INITIALLY”(NLI)。如果這樣做了，那么在該工作單元內(nèi)不會(huì)記錄任何 insert 操作，但是這里存在兩個(gè)與