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

.Word資料DB2數(shù)據(jù)庫SQL語法參考手冊\o"db2"DB2提供了關(guān)連式資料庫的查詢語言SQL(StructuredQueryLanguage)，是一種非?？谡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遵循一個幾乎是以IBMSQL為基礎(chǔ)的標(biāo)準(zhǔn)關(guān)連式資料語言定義。

一、資料定義DDL(DataDefinitionLanguage)資料定義語言是指對資料的格式和形態(tài)下定義的語言，他是每個資料庫要建立時候時首先要面對的，舉凡資料分哪些表格關(guān)系、表格內(nèi)的有什麼欄位主鍵、表格和表格之間互相參考的關(guān)系等等，都是在開始的時候所必須規(guī)劃好的。1.建表格:CREATETABLEtable_name(

column1DATATYPE[NOTNULL][NOTNULLPRIMARYKEY],

column2DATATYPE[NOTNULL],

...)說明:DATATYPE是資料的格式，詳見表。NUTNULL可不可以允許資料有空的(尚未有資料填入)。PRIMARYKEY是本表的主鍵。2.更改表格ALTERTABLEtable_name

ADDCOLUMNcolumn_nameDATATYPE說明:增加一個欄位(沒有刪除某個欄位的語法。ALTERTABLEtable_name

ADDPRIMARYKEY(column_name)說明:更改表得的定義把某個欄位設(shè)為主鍵。ALTERTABLEtable_name

DROPPRIMARYKEY(column_name)說明:把主鍵的定義刪除。3.建立索引CREATEINDEXindex_nameONtable_name(column_name)說明:對某個表格的欄位建立索引以增加查詢時的速度。4.刪除DROPtable_nameDROPindex_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的字串，n不能超過4000。graphic(n)和char(n)一樣，不過其單位是兩個字元double-bytes，n不能超過127。這個形態(tài)是為了支援兩個字元長度的字體，例如中文字。vargraphic(n)可變長度且其最大長度為n的雙字元字串，n不能超過2000。date包含了年份、月份、日期。time包含了小時、分鐘、秒。timestamp包含了年、月、日、時、分、秒、千分之一秒。

三、資料操作DML(DataManipulationLanguage)資料定義好之後接下來的就是資料的操作。資料的操作不外乎增加資料(insert)、查詢資料(query)、更改資料(update)、刪除資料(delete)四種模式，以下分別介紹他們的語法:1.增加資料:INSERTINTOtable_name(column1,column2,...)

valueS(value1,value2,...)說明:(1)若沒有指定column系統(tǒng)則會按表格內(nèi)的欄位順序填入資料。(2)欄位的資料形態(tài)和所填入的資料必須吻合。(3)table_name也可以是景觀view_name。INSERTINTOtable_name(column1,column2,...)

SELECTcolumnx,columny,...FROManother_table說明:也可以經(jīng)過一個子查詢(subquery)把別的表格的資料填入。2.查詢資料:基本查詢SELECTcolumn1,columns2,...

FROMtable_name說明:把table_name的特定欄位資料全部列出來SELECT*FROMtable_nameWHEREcolumn1=***[ANDcolumn2>yyy][ORcolumn3<>zzz]說明:(1)'*'表示全部的欄位都列出來。(2)WHERE之後是接條件式，把符合條件的資料列出來。SELECTcolumn1,column2FROMtable_nameORDERBYcolumn2[DESC]說明:ORDERBY是指定以某個欄位做排序，[DESC]是指從大到小排列，若沒有指明，則是從小到大排列,組合,查詢組合查詢是指所查詢得資料來源并不只有單一的表格，而是聯(lián)合一個以上的表格才能夠得到結(jié)果的。SELECT*FROMtable1,table2WHEREtable1.colum1=table2.column1說明:(1)查詢兩個表格中其中column1值相同的資料。(2)當(dāng)然兩個表格相互比較的欄位，其資料形態(tài)必須相同。(3)一個復(fù)雜的查詢其動用到的表格可能會很多個。整合性的查詢:SELECTCOUNT(*)FROMtable_nameWHEREcolumn_name=xxx說明:查詢符合條件的資料共有幾筆。SELECTSUM(column1)FROMtable_name說明:(1)計算出總和，所選的欄位必須是可數(shù)的數(shù)字形態(tài)。(2)除此以外還有AVG()是計算平均、MAX()、MIN()計算最大最小值的整合性查詢。SELECTcolumn1,AVG(column2)FROMtable_nameGROUPBYcolumn1HAVINGAVG(column2)>xxx說明:(1)GROUPBY:以column1為一組計算column2的平均值必須和AVG、SUM等整合性查詢的關(guān)鍵字一起使用。(2)HAVING:必須和GROUPBY一起使用作為整合性的限制。復(fù)合性的查詢SELECT*FROMtable_name1WHEREEXISTS(SELECT*FROMtable_name2WHEREconditions)說明:(1)WHERE的conditions可以是另外一個的query。(2)EXISTS在此是指存在與否。SELECT*FROMtable_name1WHEREcolumn1IN(SELECTcolumn1FROMtable_name2WHEREconditions)說明:(1)IN後面接的是一個集合，表示column1存在集合里面。(2)SELECT出來的資料形態(tài)必須符合column1。其他查詢SELECT*FROMtable_name1WHEREcolumn1LIKE'x%'說明:LIKE必須和後面的'x%'相呼應(yīng)表示以x為開頭的字串。SELECT*FROMtable_name1WHEREcolumn1IN('xxx','yyy',..)說明:IN後面接的是一個集合，表示column1存在集合里面。SELECT*FROMtable_name1WHEREcolumn1BETWEENxxANDyy說明:BETWEEN表示column1的值介於xx和yy之間。3.更改資料:UPDATEtable_nameSETcolumn1='xxx'WHEREconditoins說明:(1)更改某個欄位設(shè)定其值為'xxx'。(2)conditions是所要符合的條件、若沒有WHERE則整個table的那個欄位都會全部被更改。4.刪除資料:DELETEFROMtable_nameWHEREconditions說明:刪除符合條件的資料。說明:關(guān)于WHERE條件后面如果包含有日期的比較，不同數(shù)據(jù)庫有不同的表達式。具體如下:(1)如果是ACCESS數(shù)據(jù)庫，則為:WHEREmydate>#2000-01-01#(2)如果是ORACLE數(shù)據(jù)庫，則為:WHEREmydate>cast('2000-01-01'asdate)WHEREmydate>to_date('2000-01-01','yyyy-mm-dd')在Delphi中寫成:thedate='2000-01-01';query1.SQL.add('select*fromabcwheremydate>cast('+''''+thedate+''''+'asdate)');如果比較日期時間型，則為:WHEREmydatetime>to_date('2000-01-0110:00:01','yyyy-mm-ddhh24:mi:ss')DB2基礎(chǔ):編碼DB2SQL以獲得最佳性能簡介當(dāng)要保證用IBMDB2?UniversalDatabase?(DB2UDB)和Borland?工具(如Delphi?、C++Builder?或Kylix?)構(gòu)建的企業(yè)應(yīng)用程序擁有最優(yōu)性能時，程序員可以利用DB2優(yōu)化器的能力來處理即使是“難以處理的”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以獲得最佳性能的細節(jié)之前，先花一些時間來回顧SQL基礎(chǔ)知識。基礎(chǔ)知識由于SQL與過程化語言不同，它提供了更高的抽象級別，因此它可以讓程序員把精力集中到他們需要什么樣的數(shù)據(jù)，而不是如何檢索數(shù)據(jù)。您不必使用嵌入式數(shù)據(jù)導(dǎo)航指令來編碼SQL。DB2會分析SQL，并“在幕后”制定數(shù)據(jù)導(dǎo)航指令。這些數(shù)據(jù)導(dǎo)航指令叫作存取路徑。讓DBMS確定到數(shù)據(jù)的最優(yōu)存取路徑解除了程序員肩上沉重的負擔(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)分析員和最終用戶之間的通信。當(dāng)項目的所有參與者都使用同一種語言時，他們之間所建立起來的協(xié)作就可以減少整體系統(tǒng)開發(fā)時間。歷史證明，保證SQL成功的最重要的一個特性就是它使用類似英語的語法輕松地檢索數(shù)據(jù)的能力。理解這種語言比理解數(shù)據(jù)頁面的結(jié)構(gòu)和程序源代碼要容易得多：SELECTLASTNAMEFROMEMPWHEREEMPNO='000010';想想看：當(dāng)訪問文件中的數(shù)據(jù)時，程序員必須編碼指令來打開文件、開始一個循環(huán)、讀取記錄、檢查EMPNO字段是否等于適當(dāng)?shù)闹怠z查文件結(jié)尾、回到循環(huán)的開頭等。SQL本來就是非常靈活的。它使用自由格式的結(jié)構(gòu)，該結(jié)構(gòu)可以讓用戶開發(fā)SQL語句來適合他們的需要。DBMS在執(zhí)行之前會分析每個SQL請求，以檢查語法是否正確和優(yōu)化該請求。SQL語句不需要從任何給定的列中開始，您可以將它們串在一行中，或者把它們拆成幾行。例如，以下這條單行的SQL語句與我前面使用的三行示例等價：SELECTLASTNAMEFROMEMPWHEREEMPNO='000010';SQL的另一個靈活特性是您可以用許多形式不同但功能等價的方法來制定一個請求。例如：SQL可以連接表或嵌套查詢。您始終可以將嵌套查詢轉(zhuǎn)換成等價的連接。您可以在大量的函數(shù)和謂詞中看到這一靈活性的其它示例。具有等價功能的特性的示例包括：BETWEENvs<=/>=INvs一系列和OR配合的謂詞INNERJOINvsFROM子句中串在一起并用逗號分隔的表OUTERJOINvs帶有UNION的簡單SELECT和相關(guān)的子查詢CASE表達式vs復(fù)雜的UNIONALL語句SQL展示的這一靈活性并不總是稱心的，因為形式不同但功能等價的SQL公式可以提供非常不同的性能。我將在本文的以后部分討論該靈活性所造成的結(jié)果，并提供開發(fā)有效的SQL的準(zhǔn)則。如我所說的，SQL指定了要檢索或操作什么數(shù)據(jù)，但沒有指定數(shù)據(jù)庫如何完成這些任務(wù)。這就使SQL本身變得很簡單。如果您能夠記得關(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語句并確定可以滿足每條語句的最有效的存取路徑(請參閱圖1)。DB2UDB通過解析SQL語句來確定必須訪問哪些表和列，從而完成該操作。DB2優(yōu)化器然后查詢存儲在DB2系統(tǒng)目錄中的系統(tǒng)信息和統(tǒng)計信息，以確定完成滿足SQL請求所必需的任務(wù)的最佳方法。圖1.運行中的DB2優(yōu)化優(yōu)化器在功能上等價于一個專家系統(tǒng)。專家系統(tǒng)是一個標(biāo)準(zhǔn)規(guī)則集合，當(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仍然能夠訪問數(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í)行一個四步驟的過程：1、接收并驗證SQL語句的語法。2、分析環(huán)境并優(yōu)化滿足SQL語句的方法。3、創(chuàng)建計算機可讀指令來執(zhí)行優(yōu)化的SQL。4、執(zhí)行指令或存儲它們以便將來執(zhí)行。這個過程的第二步是最有趣的。優(yōu)化器怎樣決定如何以它的方式執(zhí)行您可以發(fā)送的大量SQL語句?優(yōu)化器有許多類型的優(yōu)化SQL的策略。它如何選擇在優(yōu)化存取路徑中使用這些策略中的哪一個?IBM并沒有發(fā)布優(yōu)化器如何確定最佳存取路徑的真正和深入的詳細信息，但優(yōu)化器是一個基于成本的優(yōu)化器。這意味著優(yōu)化器將始終嘗試為每個查詢制定減少總體成本的存取路徑。要實現(xiàn)這個目標(biāo)，DB2優(yōu)化器會應(yīng)用查詢成本公式，該公式對每條可能的存取路徑的四個因素進行評估和權(quán)衡：CPU成本、I/O成本、DB2系統(tǒng)目錄中的統(tǒng)計信息和實際的SQL語句。在DB2中提高Insert性能的技巧INSERT處理過程概述首先讓我們快速地看看\o"insert"insert一行時的處理步驟。這些步驟中的每一步都有優(yōu)化的潛力，對此我們在后面會一一討論。1、在客戶機準(zhǔn)備語句。對于動態(tài)SQL，在語句執(zhí)行前就要做這一步，此處的性能是很重要的;對于靜態(tài)SQL，這一步的性能實際上關(guān)系不大，因為語句的準(zhǔn)備是事先完成的。2、在客戶機，將要插入的行的各個列值組裝起來，發(fā)送到DB2服務(wù)器。3、\o"db2"DB2服務(wù)器確定將這一行插入到哪一頁中。4、DB2在用于該頁的緩沖池中預(yù)留一個位置。如果DB2選定的是一個已有的頁，那么就需要讀磁盤;如果使用一個新頁，則要在表空間(如果是SMS，也就是系統(tǒng)管理存儲的表空間)中為該頁物理地分配空間。插入了新行的每一頁最后都要從緩沖池寫入到磁盤。5、在目標(biāo)頁中對該行進行格式化，并獲得該行上的一個X(exclusive，獨占的)行鎖。6、將反映該insert的一條記錄寫入到日志緩沖區(qū)中。7、最后提交包含該insert的事務(wù)，如果這時日志緩沖區(qū)中的記錄還沒有被寫入日志文件的話，則將這些記錄寫到日志文件中。此外，還可能發(fā)生很多類型的附加處理，這取決于數(shù)據(jù)庫配置，例如，索引或觸發(fā)器的存在。這種額外的處理對于性能來說也是意義重大的，我們在后面會討論到。insert的替代方案在詳細討論insert的優(yōu)化之前，讓我們先考慮一下insert的兩種替代方案：load和import。import實用程序?qū)嶋H上是SQLINSERT的一個前端，但它的某些功能對于您來說也是有用的。load也有一些有用的額外功能，但是我們使用load而不使用insert的主要原因是可以提高性能。load直接格式化數(shù)據(jù)頁，而避免了由于插入導(dǎo)致的對每一行進行處理的大部分開銷(例如，日志記錄在這里實際上是消除了)。而且，load可以更好地利用多處理器機器上的并行性。在V8load中有兩個新功能，它們對于load成為insert的替代方案有著特別的功效，這兩個功能是：從游標(biāo)裝載和從調(diào)用層接口(CLI)應(yīng)用程序裝載。從游標(biāo)裝載這種方法可用于應(yīng)用程序的程序代碼(通過db2LoadAPI)，或用于DB2腳本。下面是后一種情況的一個例子：declarestaffcursorcursorforselect*fromstaff;loadfromstaffcursorofcursorinsertintomyschema.new_staff;這兩行可以用下面一行替代：insertintomyschema.new_staffselect*fromstaff同等效的INSERT...SELECT語句相比，從游標(biāo)裝載幾乎可以提高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中，或者在一次CLISQLExecDirect調(diào)用中)，也可以顯式地進行(例如，通過一條SQLPrepare、CLISQLPrepare或JDBCprepareStatement語句)。該編譯過程牽涉到授權(quán)檢查、優(yōu)化，以及將語句轉(zhuǎn)化為可執(zhí)行格式時所需的其他一些活動。在編譯語句時，語句的訪問計劃被存儲在包緩存中。如果重復(fù)地執(zhí)行相同的INSERT語句，則該語句的訪問計劃(通常)會進入到包緩存中，這樣就免除了編譯的開銷。然而，如果insert語句對于每一行有不同的值，那么每一條語句都將被看成是惟一的，必須單獨地進行編譯。因此，將像下面這樣的重復(fù)語句：insertintomytablevalues(1,'abc')insertintomytablevalues(2,'def')等等，換成帶有參數(shù)標(biāo)記的語句，一次準(zhǔn)備，重復(fù)執(zhí)行，這樣做是十分可取的：insertintomytablevalues(?,?)使用參數(shù)標(biāo)記可以讓一系列的insert的運行速度提高數(shù)倍。(在靜態(tài)SQL程序中使用主機變量也可以獲得類似的好處。)2.發(fā)送列值到服務(wù)器可以歸為這一類的優(yōu)化技巧有好幾種。最重要的一種技巧是在每條insert語句中包括多行，這樣就可以避免對于每一行都進行客戶機-服務(wù)器通信，同時也減少了DB2開銷?？捎糜诙嘈胁迦氲募记捎校涸赩ALUES子句中包含多行的內(nèi)容。例如，下面的語句將插入三行：INSERTINTOmytableVALUES(1,'abc'),(2,'def'),(3,'ghi')在CLI中使用數(shù)組插入(arrayinsert)。這需要準(zhǔn)備一條帶參數(shù)標(biāo)記的INSERT語句，定義一個用于存儲要插入的值的數(shù)組，將該數(shù)組綁定到參數(shù)標(biāo)記，以及對于每個數(shù)組中的一組內(nèi)容執(zhí)行一次insert。而且，示例程序sqllib/samples/cli/tbload.c提供了數(shù)組插入的基本框架(但是執(zhí)行的是CLILOAD)。從不使用數(shù)組改為使用包含100行的數(shù)組，可以將時間縮短大約2.5倍。所以應(yīng)該盡可能地使用包含至少100行的數(shù)組。在JDBC中使用批處理操作。這跟CLI中的數(shù)組插入一樣，基于相同的概念，但是實現(xiàn)細節(jié)有所不同。當(dāng)通過prepareStatement方法準(zhǔn)備了insert語句之后，剩下的步驟是針對每一列調(diào)用適當(dāng)?shù)膕etXXXX方法(例如，setString或setInt)，然后是addBatch。對于要插入的每一行，都要重復(fù)這些步驟，然后調(diào)用executeBatch來執(zhí)行插入。要查看這方面的例子，請參閱“參考資料”一節(jié)中的JDBCTutorial。使用load將數(shù)據(jù)快速地裝入到一個staging表中，然后使用INSERT...SELECT填充主表。(通過這種方法節(jié)省下來的代價源于load的速度非?？?，再加上INSERT...SELECT是在DB2內(nèi)(在服務(wù)器上)傳輸數(shù)據(jù)的，從而消除了通信上的代價。一般情況下我們不會使用這種方法，除非在INSERT...SELECT中還要另外做load無法完成的處理。如果不可能在一條insert語句中傳遞多行，那么最好是將多條insert語句組成一組，將它們一起從客戶機傳遞到服務(wù)器。(不過，這意味著每條insert都包含不同的值，都需要準(zhǔn)備，因而其性能實際上要比使用參數(shù)標(biāo)記情況下的性能更差一些。)將多條語句組合成一條語句可以通過CompoundSQL來實現(xiàn)：在SQL中，復(fù)合語句是通過BEGINATOMIC或BEGINCOMPOUND語句創(chuàng)建的。在CLI中，復(fù)合語句可以通過SQLExecDirect和SQLExecute調(diào)用來建立。對于DB2V8FixPak4，另一種生成復(fù)合語句的方法是在(對一條預(yù)處理語句)發(fā)出多個SQLExecute調(diào)用之前設(shè)置語句屬性SQL_ATTR_CHAINING_BEGIN，并在調(diào)用之后設(shè)置語句屬性SQL_ATTR_CHAINING_END。下面是關(guān)于該話題的其他一些建議：如果可能的話，讓客戶機與要存取的數(shù)據(jù)庫使用相同的代碼頁，以避免在服務(wù)器上的轉(zhuǎn)換代價。數(shù)據(jù)庫的代碼頁可以通過運行“getdbcfgfor”來確定。在某些情況下，CLI會自動執(zhí)行數(shù)據(jù)類型轉(zhuǎn)換，但是這樣同時也會帶來看不見的(小小的)性能損耗。因此，盡量使插入值直接處于與相應(yīng)列對應(yīng)的格式。將應(yīng)用程序中與插入相關(guān)的設(shè)置開銷最小化。例如，當(dāng)在CLI中使用數(shù)組插入時，對于整個一組插入，應(yīng)該盡量保證對于每一列只執(zhí)行一次SQLBindParameter，而不是對每一組數(shù)組內(nèi)容都執(zhí)行一次。對于個體來說，這些調(diào)用的代價并不高，但是這些代價是累積的。3.找到存儲行的地方DB2使用三種算法中的一種來確定將行插入到哪里。(如果使用了多維群集(Multi-dimensionalClustering，MDC)，則另當(dāng)別論，我們在這里不予討論。)缺省模式是，DB2搜索散布在表的各頁上的自由空間控制記錄(FreeSpaceControlRecords，F(xiàn)SCR)，以找到有足夠自由空間存放新行的頁。顯然，如果每頁上的自由空間都比較少的話，就要浪費很多的搜索時間。為了應(yīng)付這一點，DB2提供了DB2MAXFSCRSEARCH注冊表變量，以便允許將搜索范圍限制為少于缺省的5頁。當(dāng)表是通過ALTERTABLE以APPEND模式放置時，就要使用第二種算法。這樣就完全避免了FSCR搜索，因為只需簡單地將行直接放到表的末尾。當(dāng)表有群集索引(clusteringindex)時，就要用到最后一種算法。在這種情況下，DB2試圖將每一行插入到有相似鍵值的一頁中。如果那一頁沒有空間了，DB2就會嘗試附近的頁，如果附近的頁也沒有空間，DB2就進行FSCR搜索。如果只考慮插入時間的優(yōu)化，那么使用APPEND模式對于批量插入是最快的一種方法，但是這種方法的效果遠不如我們這里討論的很多其他方法那么成效顯著。第二好的方法應(yīng)該是采用缺省算法，但是，如果在最佳環(huán)境中，更改DB2MAXFSCRSEARCH的值影響很小，而在一個I/O約束較少的環(huán)境中，這種更改所造成的影響就比較可觀了。如果有群集索引，則對insert的性能會有很大的負面影響，這一點也不驚奇，因為使用群集索引的目的就是通過在插入時做額外的工作來提高查詢(即select)性能的。如果的確需要群集索引，那么可以通過確保有足夠的自由空間來使其對插入的影響降至最?。菏褂肁LTERTABLE增加PCTFREE，然后使用REORG預(yù)留自由空間。不過，如果允許太多自由空間的存在，則可能導(dǎo)致查詢時需要讀取額外的頁，這反而大大違反了使用群集索引的本意。另一種選擇是，在批量插入之前先刪除群集索引，而后再重新創(chuàng)建群集索引，也許這是最優(yōu)的方法(創(chuàng)建群集索引的開銷跟創(chuàng)建常規(guī)索引的開銷差不多，都不是很大，只是在插入時有額外的開銷)。4.緩沖池、I/O和頁清除每一條insert在執(zhí)行時，都是先將新行存儲在一個頁中，并最終將那個頁寫到磁盤上。一旦像前面討論的那樣指定了頁，那么在將行添加到該頁之前，該頁必須已經(jīng)在緩沖池中。對于批量插入，大部分頁都是最新指派給表的，因此讓我們關(guān)注一下對新頁的處理。如果表在系統(tǒng)管理存儲的(SystemManagedStorage，SMS)表空間中，當(dāng)需要新頁時，缺省情況下是從文件系統(tǒng)中分別為每一頁分配空間。但是，如果對數(shù)據(jù)庫運行了db2empfa命令，那么每個SMS表空間就會為新頁一次性分配一個區(qū)段。我們建議運行db2empfa命令，并使用32頁的區(qū)段。對于數(shù)據(jù)庫管理的存儲(DatabaseManagedStorage，DMS)表空間，空間是在創(chuàng)建表空間時就預(yù)先分配的，但是頁的區(qū)段則是在插入處理過程中指派給表的。與SMS相比，DMS對空間的預(yù)分配可以提高大約20%的性能--使用DMS時，更改區(qū)段大小并沒有明顯的效果。如果表上有索引，則對于每個插入的行，都要添加一個條目到每條索引。這要求在緩沖池中存在適當(dāng)?shù)乃饕摗Ｍ硇r候我們將討論索引的維護，但是現(xiàn)在只需記住，插入時對緩沖池和I/O的考慮也類似地適用于索引頁，對于數(shù)據(jù)頁也是一樣。隨著插入的進行，越來越多的頁中將填入被插入的行，但是，DB2不要求在insert或Commit后將任何新插入的或更新后的數(shù)據(jù)或索引寫入到磁盤。(這是由于DB2的writeahead日志記錄算法。但是有一個例外，這將在關(guān)于日志記錄的小節(jié)中論述到。)然而，這些頁需要在某一時刻寫到磁盤上，這個時刻可能會在數(shù)據(jù)庫關(guān)閉時才會輪到。一般來說，對于批量插入，您會希望積極地進行異步頁清除(asynchronouspagecleaning)，這樣在緩沖池中就總有可用于新頁的空余位置。頁清除率，或者說總?cè)表撀?，可能?dǎo)致計時上的很大不同，使得性能比較容易產(chǎn)生誤解。例如，如果使用100,000頁的緩沖池，并且不存在頁清除，則批量插入在結(jié)束前不會有任何新的或更改過的(“臟的”)頁寫到磁盤上，但是隨后的操作(例如選擇，甚至乎關(guān)閉數(shù)據(jù)庫)都將被大大推遲，因為這時有至多100,000個在插入時產(chǎn)生的臟頁要寫到磁盤上。另一方面，如果在同一情況下進行了積極的頁清除，則批量插入過程可能要花更長的時間，但是此后緩沖池中的臟頁要少一些，從而使得隨后的任務(wù)執(zhí)行起來性能更佳。至于那些結(jié)果中到底哪個要更好些，我們并不是總能分得清，但是通常來說，將所有臟頁都存儲在緩沖池中是不可能的，所以為了取得最佳性能，采取有效的頁清除是有必要的。為了盡可能好地進行頁清除：將CHNGPGS_THRESH數(shù)據(jù)庫配置參數(shù)的值從缺省的60減少到5這么低。這個參數(shù)決定緩沖池中臟頁的閾值百分比，當(dāng)臟頁達到這個百分比時，就會啟動頁清除。嘗試啟用注冊表變量DB2_USE_ALTERNATE_PAGE_CLEANING(在DB2V8FixPak4中最新提供)。通過將這個變量設(shè)置成ON，可以為頁清除提供一種比缺省方法(基于CHNGPGS_THRESH和LSN間隙觸發(fā)器)更積極的方法。我沒有評測過其效果。請參閱FixPak4ReleaseNotes以了解這方面的信息。確保NUM_IOCLEANERS數(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è)備一起使用)。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種情況：運行ALTERTABLELOCKSIZETABLE。這將導(dǎo)致DB2為隨后使用該表的所有SQL語句使用一個表鎖，直到locksize參數(shù)改回到ROW。運行LOCKTABLEINEXCLUSIVEMODE。這將導(dǎo)致表上立即上了一個X鎖。注意，在下一次提交(或回滾)的時候，這個表將被釋放，因此，如果您要運行一個測試，測試中每N行提交一次，那么就需要在每次提交之后重復(fù)執(zhí)行LOCKTABLE。使用缺省鎖，但是讓LOCKLIST和MAXLOCKS數(shù)據(jù)庫配置參數(shù)的值比較小。當(dāng)獲得少量的行鎖時，行鎖就會自動地逐漸升級為表鎖。當(dāng)然，所有這些的缺點就在于并發(fā)的影響：如果表上有一個X鎖，那么其他應(yīng)用程序除非使用了隔離級別UR(未提交的讀)，否則都不能訪問該表。如果知道獨占訪問不會導(dǎo)致問題，那么就應(yīng)該盡量使用表鎖。但是，即使您堅持使用行鎖，也應(yīng)記住，在批量插入期間，表中可能存在數(shù)千個有X鎖的新行，所以就可能與其他使用該表的應(yīng)用程序產(chǎn)生沖突。通過一些方法可以將這些沖突減至最少：確保鎖的升級不會無故發(fā)生。您可能需要加大LOCKLIST和/或MAXLOCKS的值，以允許插入應(yīng)用程序有足夠的鎖。對于其他的應(yīng)用程序，使用隔離級別UR。對于V8FixPak4，或許也可以通過DB2_EVALUNCOMMITTED注冊表變量來減少鎖沖突：如果將該變量設(shè)置為YES，那么在很多情況下，只能獲得那些符合某個謂詞的行上的鎖，而并不是獲得被檢查的所有行上的鎖。發(fā)出一個COMMIT命令以釋放鎖，因此如果更頻繁地提交的話就足以減輕鎖沖突的負擔(dān)。注意在V7中，存在涉及insert和鍵鎖的并發(fā)問題，但是在V8中，由于提供了type-2索引，這些問題實際上已經(jīng)不見了。如果要遷移到V8中來，那么應(yīng)該確保使用帶CONVERT關(guān)鍵字的REORGINDEXES命令，以便將索引從type-1轉(zhuǎn)換為type-2。在V7中，插入過程中可能使用W或NW鎖，但是在V8中只有在使用了type-1索引或者隔離級別為RR的情況下才會出現(xiàn)這兩種鎖。因此，應(yīng)盡可能避免這兩種情況。一條insert所據(jù)有的鎖(通常是一個X鎖)通常不會受隔離級別的影響。例如，使用隔離級別UR不會阻止從插入的行上獲得鎖。然而，如果使用了INSERT...SELECT，則隔離級別將影響從SELECT獲得的鎖。6.日志記錄缺省情況下，每條insert都會被記錄下來，以用于恢復(fù)。日志記錄首先被寫到內(nèi)存中的日志緩沖池，然后再寫到日志文件，通常是在日志緩沖池已滿或者發(fā)生了一次提交時寫到日志文件的。對批量插入的日志記錄的優(yōu)化實際上就是最小化日志記錄寫的次數(shù)，以及使寫的速度盡可能快。這里首先考慮的是日志緩沖池的大小，這由數(shù)據(jù)庫配置參數(shù)LOGBUFSZ來控制。該參數(shù)缺省值為8頁或32K，這與大多數(shù)批量插入所需的理想日志緩沖池大小相比要小些。舉個例子，對于一個批量插入，假設(shè)對于每一行的日志內(nèi)容有200字節(jié)，則在插入了160行之后，日志緩沖池就將被填滿。如果要插入1000行，因為日志緩沖池將被填滿幾次，再加上提交，所以大概有6次日志寫。如果將LOGBUFSZ的值增加到64頁(256K)或者更大，緩沖池就不會被填滿，這樣的話對于該批量插入就只有一次日志寫(在提交時)。通過使用更大的LOGBUFSZ可以獲得大約13%的性能提升。較大日志緩沖池的不利之處是，緊急事故恢復(fù)所花的時間可能要稍微長一點。減少日志寫的另一種可能性是對新行要插入到的那個表使用“ALTERTABLEACTIVATENOTLOGGEDINITIALLY”(NLI)。