計(jì)算機(jī)專業(yè)文獻(xiàn)翻譯優(yōu)調(diào)SQL過(guò)程

1、外文資料譯文及原文院（系）：計(jì)算機(jī)學(xué)院專 業(yè)：計(jì)算機(jī)科學(xué)與技術(shù)班 級(jí)：2401102學(xué) 號(hào)：20023011059姓 名： 指導(dǎo)教師： 2005年6月簡(jiǎn)介有關(guān)如何調(diào)優(yōu)數(shù)據(jù)庫(kù)系統(tǒng)和應(yīng)用程序的好的建議的來(lái)源有很多。比如oltp應(yīng)用程序的db2調(diào)優(yōu)技巧(以前在 ibm® db2® 開(kāi)發(fā)者園地上發(fā)表)之類的文章通過(guò)使用事務(wù)和數(shù)據(jù)并行性以及分析查詢方案，給出了從表空間和索引設(shè)計(jì)到緩沖池的內(nèi)存分配等方面的建議。這些方面的內(nèi)容是性能調(diào)優(yōu)的基礎(chǔ)知識(shí)。 但是，有關(guān)如何組織存儲(chǔ)過(guò)程自身中的邏輯并著眼于其性能的專門建議卻并不多見(jiàn)。本文就提供了這樣一種建議。盡管本文著重于介紹 sql 過(guò)程，但是這

2、里所提供的大多數(shù)信息同樣適用于用其它語(yǔ)言編寫的在應(yīng)用程序中或存儲(chǔ)過(guò)程中嵌入的 sql 邏輯。背景知識(shí)和術(shù)語(yǔ)在深入研究詳細(xì)問(wèn)題之前，讓我們先想想db2 中有關(guān)過(guò)程化 sql 的一些基本術(shù)語(yǔ)和概念。過(guò)程化 sql 構(gòu)造（例如標(biāo)量變量、if 語(yǔ)句和 while 循環(huán)）是在 db2 universal database (udb) v7 發(fā)行版中引入 db2 的。以前的 db2 發(fā)行版支持 c 和 java 作為存儲(chǔ)過(guò)程的語(yǔ)言。v7 引入了 sql 存儲(chǔ)過(guò)程，以及其它許多可以促進(jìn) oltp 應(yīng)用程序開(kāi)發(fā)的特性（例如臨時(shí)表、應(yīng)用程序保存點(diǎn)和標(biāo)識(shí)列）。當(dāng)創(chuàng)建 sql 過(guò)程時(shí)，db2 將過(guò)程主體中的 sql

3、 查詢與過(guò)程邏輯區(qū)分開(kāi)來(lái)。為了使性能最優(yōu)，sql 查詢被靜態(tài)地編譯成包中的節(jié)。（對(duì)于靜態(tài)編譯的查詢而言，節(jié)主要是由 db2 優(yōu)化器為該查詢選擇的存取方案構(gòu)成的。包是節(jié)的集合。在過(guò)程的執(zhí)行期間，每當(dāng)控制從過(guò)程邏輯流向 sql 語(yǔ)句時(shí)，在 dll 和 db2 引擎之間就存在“上下文切換”。（在 db2 v8 中，sql 過(guò)程是在“不受保護(hù)的方式”下運(yùn)行的，即與 db2 引擎在相同的尋址空間中。因此我們這里談及的上下文切換并不是操作系統(tǒng)級(jí)別上的完全的上下文切換，而是指 db2 中層的更換。）減少頻繁調(diào)用的過(guò)程（例如 oltp 應(yīng)用程序中的過(guò)程）或者處理大量行的過(guò)程（例如執(zhí)行數(shù)據(jù)清理的過(guò)程）中的上下文

4、切換次數(shù)，對(duì)它們的性能有顯著的影響。本文中的幾個(gè)技巧恰好旨在減少這些上下文切換。剛開(kāi)始的時(shí)候（db2 通用數(shù)據(jù)庫(kù) v7 ga），只允許在 sql 過(guò)程中使用 sql 過(guò)程語(yǔ)言（通常稱為 sql pl）。后來(lái)（在 db2 udb v7.2 中），在 sql 函數(shù)和觸發(fā)器主體中開(kāi)始支持該語(yǔ)言的子集。sql pl 的這個(gè)子集即所謂的 內(nèi)聯(lián)（inline）sql pl?！皟?nèi)聯(lián)”一詞突出顯示了它與完整語(yǔ)言的重要區(qū)別。sql pl 過(guò)程是通過(guò)將其單獨(dú)的 sql 查詢靜態(tài)地編譯成包中的節(jié)實(shí)現(xiàn)的，而內(nèi)聯(lián) sql pl 函數(shù)就象其名稱所展示的，是通過(guò)將函數(shù)主體內(nèi)聯(lián)到使用它的查詢中實(shí)現(xiàn)的。稍后我們將再看一下內(nèi)聯(lián)

5、sql pl 及其用法的一些示例。 從多個(gè) sql 語(yǔ)句到一個(gè) sql 表達(dá)式跟其它編程語(yǔ)言一樣，sql 語(yǔ)言提供了兩類條件構(gòu)造：過(guò)程型（if 和 case 語(yǔ)句）和函數(shù)型（case 表達(dá)式）。在大多數(shù)環(huán)境中，可使用任何一種構(gòu)造來(lái)表達(dá)計(jì)算，到底使用哪一種只是喜好問(wèn)題。但是，使用 case 表達(dá)式編寫的邏輯不但比使用 case 或 if 語(yǔ)句編寫的邏輯更緊湊，而且更有效。使用 sql 的一次處理一個(gè)集合語(yǔ)義,諸如循環(huán)、賦值和游標(biāo)之類的過(guò)程化構(gòu)造允許我們表達(dá)那些只使用 sql dml 語(yǔ)句是不可能表達(dá)的計(jì)算。但是，當(dāng)我們擁有一些可以隨意使用的過(guò)程語(yǔ)句時(shí)，即使我們手頭的計(jì)算實(shí)際上僅使用 sql dm

6、l 語(yǔ)句就可表達(dá)，但轉(zhuǎn)換成過(guò)程語(yǔ)句還是有風(fēng)險(xiǎn)的。正如我們以前提到的，過(guò)程計(jì)算的性能與使用 dml 語(yǔ)句表達(dá)的同一個(gè)計(jì)算的性能相比會(huì)慢幾個(gè)數(shù)量級(jí)。在研究改進(jìn)現(xiàn)有過(guò)程邏輯的性能時(shí)，為消除游標(biāo)循環(huán)而花費(fèi)的任何時(shí)間都可能是值得的。改進(jìn)游標(biāo)性能如果存儲(chǔ)過(guò)程中的邏輯確實(shí)需要游標(biāo)，那么要使性能最優(yōu)，請(qǐng)牢記下面這些內(nèi)容。首先，請(qǐng)確保不使用高于您所需的隔離級(jí)別。隔離級(jí)別決定了 db2 對(duì)過(guò)程讀取或更新的行應(yīng)用的鎖定的數(shù)量。隔離級(jí)別越高，db2 將執(zhí)行的鎖定越多，因此為同一資源而競(jìng)爭(zhēng)的應(yīng)用程序之間的并發(fā)就越少。例如，使用可重復(fù)讀（repeatable read，rr）隔離級(jí)別的過(guò)程將形成對(duì)其讀取的任何行的共享鎖，

7、而使用游標(biāo)穩(wěn)定性（cursor stability，cs）的過(guò)程只會(huì)鎖定任何可更新游標(biāo)的當(dāng)前行?？梢允褂?db2_sqlroutine_prepopts 注冊(cè)表變量來(lái)指定 sql 過(guò)程的隔離級(jí)別。db2 中缺省的隔離級(jí)別是游標(biāo)穩(wěn)定性。但是，當(dāng)然了，為了保持應(yīng)用程序的正確性，有時(shí)需要使用可重復(fù)讀。還需記住一件重要的事情，一旦創(chuàng)建了需要可重復(fù)讀的過(guò)程，必須將 db2_sqlroutine_prepopts 重新設(shè)置回較低的隔離級(jí)別。在嘗試改進(jìn)游標(biāo)性能時(shí)需要牢記的一個(gè)相關(guān)問(wèn)題是游標(biāo)的可更新能力。如果游標(biāo)涉及的行是可以使用 insert 或 delete 語(yǔ)句中的 where current of 子

8、句進(jìn)行更新或刪除，那么它就是 可刪除的。當(dāng)游標(biāo)可刪除時(shí)，db2 必須獲取行上的 互斥鎖（與 共享鎖相對(duì)），并且不能執(zhí)行行分塊。行上的互斥鎖甚至可以防止其它應(yīng)用程序讀取該行（在互斥鎖被釋放之前，這些應(yīng)用程序必須等待，除非它們的隔離級(jí)別是 ur），而行分塊通過(guò)在一個(gè)操作中檢索行塊，從而減少了用于游標(biāo)的數(shù)據(jù)庫(kù)管理器開(kāi)銷。 只有不可刪除的游標(biāo)才可以進(jìn)行行分塊。這就是為什么讓 db2 了解將如何使用游標(biāo)是很重要的原因。通過(guò)在 select 語(yǔ)句中指定 for read only 子句，可以將游標(biāo)顯式地聲明為不可刪除，或者通過(guò)在 select 語(yǔ)句中使用 for update 子句將其聲明為可刪除。根據(jù)該

9、信息（并且還根據(jù)下面描述的 blocking 選項(xiàng)），db2 將確定是否將行分塊用于給定的游標(biāo)。缺省情況下，對(duì)于那些使用 for read only 子句定義的游標(biāo)，db2 將始終使用行分塊，除非指定了 blocking no 綁定選項(xiàng)。另一方面，如果使用了 blocking all 綁定選項(xiàng)，那么對(duì)于含混游標(biāo)（既不是定義成 for read only 也不是定義成 for update 的游標(biāo)），db2 將使用行分塊。簡(jiǎn)而言之：如果可能，則在游標(biāo)定義中使用 for read only 子句；如果您的過(guò)程包含含混游標(biāo)，那么請(qǐng)使用 blocking all 綁定選項(xiàng)。要設(shè)置 blocking 綁

10、定選項(xiàng)的值，我們還可以使用 db2_sqlroutine_prepopts 注冊(cè)表變量。在無(wú)副作用的情況下，請(qǐng)使用 sql 函數(shù)。正如我們?cè)诤?jiǎn)介中提及的，sql 過(guò)程和 sql 函數(shù)是使用不同技術(shù)實(shí)現(xiàn)的。sql 過(guò)程中的查詢是單獨(dú)編譯的，每個(gè)查詢都成為包中的一個(gè)節(jié)。編譯是在過(guò)程創(chuàng)建時(shí)進(jìn)行的，直到重新創(chuàng)建過(guò)程或者直到重新綁定其相關(guān)的包時(shí)才重新編譯這些查詢。另一方面，sql 函數(shù)中的查詢是一起編譯的，就好像函數(shù)體是一個(gè)查詢一樣。每當(dāng)編譯一條使用 sql 函數(shù)的語(yǔ)句時(shí)，也會(huì)對(duì) sql 函數(shù)進(jìn)行編譯。與 sql 過(guò)程中所發(fā)生的情況不同，sql 函數(shù)中的過(guò)程語(yǔ)句與數(shù)據(jù)流語(yǔ)句是在同一個(gè)層中執(zhí)行的。因此，每

11、當(dāng)控制從過(guò)程語(yǔ)句流向數(shù)據(jù)流語(yǔ)句或相反時(shí)，并不發(fā)生上下文切換。因?yàn)榇嬖谶@些區(qū)別，所以當(dāng)給定的過(guò)程代碼段作為函數(shù)實(shí)現(xiàn)時(shí)的執(zhí)行速度通常比作為過(guò)程實(shí)現(xiàn)時(shí)要快。但是，當(dāng)然了，有一個(gè)小問(wèn)題。函數(shù)只能包含那些不會(huì)改變數(shù)據(jù)庫(kù)狀態(tài)的語(yǔ)句（例如 insert、update 或 delete 語(yǔ)句是不允許的）。并且只允許完整 sql pl 語(yǔ)言的子集出現(xiàn)在 sql 函數(shù)中（不能是 call 語(yǔ)句、游標(biāo)和條件處理）。盡管有這些限制，但大多數(shù) sql 過(guò)程都可以在無(wú)副作用的情況下轉(zhuǎn)換成 sql 函數(shù)。因此，正如本節(jié)標(biāo)題所展示的，當(dāng)您只是從數(shù)據(jù)庫(kù)抽取數(shù)據(jù)而不執(zhí)行任何更改時(shí)，請(qǐng)考慮使用 sql 函數(shù)而不是使用 sql 過(guò)程

12、。使用用于臨時(shí)數(shù)據(jù)的臨時(shí)表在 v7 中，db2 引入了臨時(shí)表。對(duì)臨時(shí)表的操作通常比對(duì)常規(guī)表的操作快。讓我們看一些原因：· 首先，臨時(shí)表的創(chuàng)建不會(huì)涉及向目錄中插入項(xiàng)，并且臨時(shí)表的使用也不會(huì)涉及對(duì)目錄的訪問(wèn)；因此，不會(huì)有目錄爭(zhēng)用問(wèn)題。 · 因?yàn)榕R時(shí)表只能由創(chuàng)建它們的應(yīng)用程序訪問(wèn)，因此在其操作中不會(huì)涉及鎖定問(wèn)題。 · 如果指定了 not logged 選項(xiàng)，則不對(duì)臨時(shí)表上的操作記錄日志（當(dāng)然，這樣就不可能回滾更改）。因此，如果您的存儲(chǔ)過(guò)程生成了大量臨時(shí)數(shù)據(jù)，并只打算在數(shù)據(jù)庫(kù)的一個(gè)會(huì)話中使用它們，那么請(qǐng)將這些數(shù)據(jù)存儲(chǔ)進(jìn)臨時(shí)表，這樣可以顯著地改進(jìn)性能。 在對(duì) sql 過(guò)程中

13、的臨時(shí)表進(jìn)行任何應(yīng)用之前，表定義在編譯環(huán)境中必須是可用的。在執(zhí)行了 connect reset 命令后，臨時(shí)表將不復(fù)存在。在運(yùn)行時(shí)，應(yīng)用程序必須確保在執(zhí)行使用臨時(shí)表的首個(gè)查詢之前該表是存在的。最后的這個(gè)觀察引出了一個(gè)我們從未提及的要點(diǎn)：引用臨時(shí)表的任何查詢都將被動(dòng)態(tài)地編譯，即使該查詢被寫成靜態(tài)的 sql。跟其它任何動(dòng)態(tài)查詢一樣，在編譯該查詢之后，它將以已編譯的形式保留在包高速緩存中。在下一次執(zhí)行相同的查詢時(shí)，僅當(dāng)無(wú)法在高速緩存發(fā)現(xiàn)它時(shí)，db2 才重新編譯它。如果您打算創(chuàng)建相對(duì)較大的臨時(shí)表，并對(duì)這些表運(yùn)行幾個(gè)查詢，請(qǐng)考慮定義索引并對(duì)它們運(yùn)行 runstats（顯然后者是填充了表后進(jìn)行的）。有關(guān)在

14、 sql 過(guò)程中使用臨時(shí)表的最后一個(gè)說(shuō)明是：如果需要根據(jù)在同一個(gè)過(guò)程中創(chuàng)建的臨時(shí)表返回結(jié)果集，那么必須在嵌套的復(fù)合語(yǔ)句中定義結(jié)果集。必須在嵌套的復(fù)合語(yǔ)句中定義結(jié)果集的理由是，declare global temporary table 是一個(gè)可執(zhí)行語(yǔ)句，而可執(zhí)行語(yǔ)句只能在聲明語(yǔ)句（例如 declare cursor）之后編寫。如果我們?cè)谟螛?biāo)定義之后在外部作用域中聲明表，那么當(dāng)編譯 declare cursor 語(yǔ)句時(shí)，該表在編譯環(huán)境中將不可用，因此編譯會(huì)失敗。關(guān)于作者gustavo arocena是 db2 sql 編譯器方面的技術(shù)經(jīng)理。他于 1998 年加入 ibm 多倫多實(shí)驗(yàn)室，目前負(fù)責(zé)

15、sql 過(guò)程和 sql 解析器的開(kāi)發(fā)。gustavo 擁有多倫多大學(xué)計(jì)算機(jī)科學(xué)碩士學(xué)位，研究方向是數(shù)據(jù)庫(kù)查詢語(yǔ)言。 優(yōu)調(diào)sql過(guò)程資深軟件開(kāi)發(fā)人員, ibm canada ltd. 2005 年 2 月普通 sql 過(guò)程如果您有 microsoft sql server 或 sybase 方面的背景知識(shí)，那么您就會(huì)很好地了解這些過(guò)程。除了一條 call 語(yǔ)句，這個(gè)范例的應(yīng)用程序中將不存在任何 sql。通過(guò)這個(gè)范例所認(rèn)識(shí)到的好處有兩個(gè)：首先是封裝。例如，如果查詢十分復(fù)雜，就不應(yīng)重復(fù)查詢多次，而是應(yīng)該將它存儲(chǔ)在某一個(gè)地方。 其次是緩存。存儲(chǔ)過(guò)程中的代碼是預(yù)先編譯的。而應(yīng)用程序中的代碼通常是動(dòng)態(tài)代碼

16、。 為了解決第一個(gè)問(wèn)題，db2 支持內(nèi)聯(lián) sql pl 的概念。db2 允許將簡(jiǎn)單邏輯或查詢封裝在 sql 函數(shù)中。當(dāng)從調(diào)用者執(zhí)行該 sql 函數(shù)時(shí)，其主體就是擴(kuò)展到調(diào)用者中的宏。為了解決第二個(gè)問(wèn)題，db2 使用了包緩存。該緩存不僅記住最近執(zhí)行的過(guò)程，還記住了以前執(zhí)行的語(yǔ)句。因此，在第一次編譯 sql 語(yǔ)句之后，后來(lái)的調(diào)用只要繼續(xù)執(zhí)行相同的執(zhí)行計(jì)劃即可。真是令人印象深刻！僅僅通過(guò)使用內(nèi)聯(lián) sql pl 就使所監(jiān)控代碼的運(yùn)行速度提高了三倍。甚至連不返回結(jié)果集的簡(jiǎn)單 sql 過(guò)程也可以用 sql 函數(shù)取代。對(duì)于返回不止一個(gè)參數(shù)的過(guò)程，可以使用 sql 表函數(shù)，它返回一個(gè)包含一行的表 每個(gè)輸出參數(shù)為

17、一列。對(duì)于包含一個(gè)輸出的過(guò)程，可以只使用一個(gè)標(biāo)量 sql 函數(shù)。注意，db2 v8.2 還支持在 sql 表函數(shù)中包含 update、delete、insert 和 merge。這意味著您甚至可以使用內(nèi)聯(lián) sql pl 封裝數(shù)據(jù)庫(kù)更改。使用臨時(shí)表sql 術(shù)語(yǔ)來(lái)說(shuō)，declare 表示所定義對(duì)象的目錄中將不包含條目。因此，它在定義上是私有的。與之相比，db2 udb for zos® 所支持的已創(chuàng)建的全局臨時(shí)表（created global temporary table）是在目錄中定義的。當(dāng)然，雖然它們的內(nèi)容是私有的，但其定義卻不是私有的。具有聲明對(duì)象的靈活性（只要用戶臨時(shí)表空間是立

18、即可用的，dba 就不會(huì)再關(guān)心該應(yīng)用程序在做什么）也帶來(lái)了一個(gè)不足：對(duì)于要進(jìn)行編譯的語(yǔ)句來(lái)說(shuō)，這個(gè)對(duì)象必須存在。如果連接失敗，或者該表已刪除，那么每次重新聲明這個(gè)表時(shí)，都需要重新編譯引用 dgtt 的任何語(yǔ)句。declare 和 drop 之間的 insert 語(yǔ)句每次都要重新進(jìn)行編譯，因?yàn)?db2 無(wú)法知道該 dgtt 下次是否將具有相同的屬性。實(shí)際上，declare 和 drop 之間有許多語(yǔ)句，都可用于處理該臨時(shí)表結(jié)果集的格式，直到它最終滿足要求為止。為了避免這種瘋狂編譯，將該臨時(shí)表的聲明移至一個(gè)單獨(dú)過(guò)程中會(huì)更合適一些，該過(guò)程只在啟動(dòng)工作負(fù)載時(shí)執(zhí)行一次。正如您可以在上面看到的，其結(jié)果是令

19、人震驚的。但這里將有更多要了解的東西。請(qǐng)觀察取代 drop 語(yǔ)句的 delete 語(yǔ)句。它將總是因用戶引起的錯(cuò)誤而失敗，然后，將由一個(gè) continue 處理程序解決這個(gè)錯(cuò)誤。這里發(fā)生了什么事？為了提高速度，需要優(yōu)化臨時(shí)表，因此，當(dāng)插入行時(shí)，db2 不用費(fèi)心在臨時(shí)表中尋找空閑空間，而是將表行為替換為 append only。雖然常規(guī)的 delete 將刪除這些行，但是它不會(huì)真正讓 db2 回收這些空間。應(yīng)用程序?qū)⒗^續(xù)消費(fèi)越來(lái)越多的用戶臨時(shí)表空間。實(shí)際上，另外一個(gè)因素在這里提供了幫助。那就是，所有 dgtt 都被聲明為 not logged。畢竟，您可以相當(dāng)容易地重新構(gòu)建臨時(shí)表的內(nèi)容。如果 no

20、t logged 表在執(zhí)行數(shù)據(jù)修改語(yǔ)句期間碰到了執(zhí)行錯(cuò)誤，那么對(duì)于 db2 來(lái)說(shuō)，就只有一個(gè)選擇：清空（truncate）該表。而這就是 delete 語(yǔ)句所進(jìn)行的工作。條件處理程序計(jì)算機(jī)語(yǔ)言有兩種處理錯(cuò)誤的常用方法。第一種方法要求程序在在進(jìn)行每一個(gè)重大操作之后，檢查錯(cuò)誤。在 db2 中編寫 c-udf 或 c 存儲(chǔ)過(guò)程的開(kāi)發(fā)人員已經(jīng)學(xué)會(huì)在其代碼的每條 exec sql 語(yǔ)句之后檢查 sqlca。第二種方法就是具有專用的處理程序，“捕捉”各條語(yǔ)句所“拋出”的任何或特定錯(cuò)誤條件。java 和 c+ 編程人員都熟悉這一概念。sql/psm 標(biāo)準(zhǔn)為 sql 過(guò)程語(yǔ)言定義了使用處理程序的第二種方法。然

21、而，通常當(dāng)從 sybase 或 microsoft sql 服務(wù)器移植 tsql 過(guò)程時(shí)，或當(dāng)僅僅對(duì) sql pl 應(yīng)用 c 技巧時(shí)，都可以避免更現(xiàn)代的處理程序方法，而支持更為傳統(tǒng)的錯(cuò)誤檢查方法。這些情況下要做的事情就是定義一個(gè)通用的“萬(wàn)能（catch-all）”處理程序，用于在局部變量中保存所有錯(cuò)誤信息。然后，使用過(guò)程體中的顯式錯(cuò)誤處理代碼進(jìn)行錯(cuò)誤處理。具有諷刺意味的是，在許多情況下，程序甚至可能不在乎檢查 select into 的結(jié)果是什么，因此，條件處理程序的工作是徒勞的。本例中，將完成以下幾件事。首先，可以顯式地檢查 not found 警告（如果想這樣做的話），而不必在局部變量中保

22、存 sqlcode 和 sqlstate。事實(shí)的確如此，因?yàn)樵摼婵捎糜?sql 過(guò)程中的下一條語(yǔ)句。其次，所有錯(cuò)誤或意想不到的警告實(shí)際上應(yīng)該由異常處理程序來(lái)處理。除了按照發(fā)明者所計(jì)劃的方式來(lái)使用該語(yǔ)言之外，這樣做可避免復(fù)制和重新設(shè)置局部變量。- 10 -introductionthere are many sources of advice available on how to tune database systems and applications. articles like db2 tuning tips for oltp applications, previously pub

23、lished on the ibm® db2® developer domain, provide advice on topics ranging from tablespace and index design to memory allocation for buffer pools, exploiting transaction and data parallelism and analyzing query plans. these topics are the abcs of performance tuning.however, specific advice

24、 on how to organize the logic in the stored procedures themselves with an eye on performance is not that common. that is the kind of advice i provide in this article. even though the article focuses on sql procedures, most of the information provided here is applicable to sql logic embedded in appli

25、cations or in stored procedures written in other languagesbackground and terminologybefore going into the details, let's first review some basic terminology and concepts related to procedural sql in db2. procedural sql constructs (such as scalar variables, if statements and while loops) were int

26、roduced in db2 with the release of db2 universal databasetm (udb) version 7. prior releases of db2 supported c and javatm as languages for stored procedures. version 7 introduced sql stored procedures. when an sql procedure is created, db2 separates the sql queries in the procedure body from the pro

27、cedural logic. to maximize performance, the sql queries are statically compiled into sections in a package. (for a statically compiled query, a section consists mainly of the access plan selected by the db2 optimizer for that query. a package is a collection of sections. for more information on pack

28、ages and sections, please refer to the db2 sql reference,volume1.) on the other hand, the procedural logic is compiled into a dll (dynamically linked library).during the execution of a procedure, every time control flows from the procedural logic to an sql statement, there is a "context switch&

29、quot; between the dll and the db2 engine. (in db2 v8, sql procedures run in "unfenced mode", i.e., in the same addressing space as the db2 engine. therefore the context switch we refer to here is not a full context switch at the operating system level, but rather a change of layer within d

30、b2.) reducing the number of context switches in procedures that are invoked very often (such as procedures in an oltp application) or that process large numbers of rows (for example, procedures that perform data cleansing) can have a noticeable impact on their performance. several of the tips in thi

31、s article aim precisely at reducing these context switches.the sql procedural language (commonly referred to as sql pl) was first allowed only in sql procedures (db2 universal database v7 ga). later on (db2 udb v7.2), a subset of the language started to be supported in sql functions and trigger bodi

32、es. this subset of sql pl is known as inline sql pl. the word "inline" highlights an important difference with the full language. whereas an sql pl procedure is implemented by statically compiling its individual sql queries into sections in a package, an inline sql pl function is implement

33、ed, as the name suggests, by inlining the body of the function into the query that uses it. we'll revisit inline sql pl later on, along with examples of its use.from multiple sql statements to a single sql expressionlike other programming languages, the sql language provides two types of conditi

34、onal constructs: procedural (if and case statements) and functional (case expressions). in most circumstances where either type can be used to express a computation, using one or the other is a matter of taste. however, logic written using case expressions is not only more compact, but also more eff

35、icient than logic written using case or if statementsexploit the set-at-a-time semantics of sql,procedural constructs such as loops, assignment and cursors allow us to express computations that would not be possible to express using just sql dml statements. but when we have procedural statements at

36、our disposal, there is a risk that we could turn to them even when the computation at hand can, in fact, be expressed using just sql dml statements. as we've mentioned earlier, the performance of a procedural computation can be orders of magnitude slower than the performance of an equivalent com

37、putation expressed using dml statements. when looking at improving the performance of existing procedural logic, any time spent in eliminating cursor loops will likely pay off.improve cursor performance.if the logic in your stored procedures does require cursors, here are a few things to keep in min

38、d to maximize their performance.first of all, make sure you don't use an isolation level higher than what you need. the isolation level determines the amount of locking that db2 applies on the rows that the procedure reads or updates. the higher the isolation level, the more locking db2 will per

39、form, and therefore, the less concurrency between applications competing for the same resources. for instance, a procedure using repeatable read (rr) isolation level will cause share locks on any row it reads, whereas a procedure using cursor stability (cs) will only lock the current row of any upda

40、table cursor. the isolation level for sql procedures can be specified with the db2_sqlroutine_prepopts registry variable.the default isolation level in db2 is cursor stability. but of course, to preserve the correctness of an application, it is sometimes necessary to use repeatable read. what is imp

41、ortant to remember is to revert db2_sqlroutine_prepopts back to a lower isolation level once procedures requiring repeatable read are created.the query above will execute with isolation level ur regardless the isolation level specified in db2_sqlroutine_prepopts.a related issue to keep in mind when

42、trying to improve cursor performance is cursor updatability. a cursor is deletable if the rows it ranges over can be updated or deleted using the where current of clause in update or delete statements. when a cursor is deletable, db2 has to obtain exclusive locks (as opposed to share locks) on rows,

43、 and cannot perform row blocking. an exclusive lock on a row prevents other applications from even reading the row (they have to wait until the lock is released, unless their isolation level is ur), whereas row blocking reduces database manager overhead for cursors by retrieving a block of rows in a

44、 single operation.row blocking can only happen for non-deletable cursors. that is why it is important to let db2 know how a cursor is going to be used. cursors can be explicitly declared as non-deletable by specifying the for read only clause in the select statement, or as deletable by using the for

45、 update clause in the select statement. based on this information (and also on the blocking option described below), db2 will decide whether to use row blocking for a given cursor or not.by default, db2 will always use row blocking for cursors defined using the for read only clause, unless the block

46、ing no bind option has been specified. on the other hand, db2 will use row blocking for ambiguous cursors (cursors that are not defined as either for read only or for update) if the blocking all bind option is used.to make a long story short: when possible, use the for read only clause in your curso

47、r definitions; if your procedures contain ambiguous cursors, use the blocking all bind option. to set the value of the blocking bind option, we also use the db2_sqlroutine_prepopts registry variable.in the absence of side-effects, use sql functions.as we mentioned in the introduction, sql procedures

48、 and sql functions are implemented using different technologies. queries in an sql procedure are compiled individually, each of them becoming a section in a package. the compilation occurs when the procedure is created, and the queries are not recompiled until the procedure is recreated or until its

49、 associated package is rebound.on the other hand, queries in sql functions are compiled together, as if the function body were a single query. the compilation occurs every time a statement that uses the function is compiled.unlike what happens in sql procedures, procedural statements in sql function

50、s are not executed in a different layer than dataflow statements. therefore, there is no context switch every time control flows from a procedural to a dataflow statement or vice versa.as a result of these differences, a given piece of procedural code will usually execute much faster when implemente

51、d as a function than when implemented as a procedure. but of course, there is a catch. functions can only contain statements that do not alter the state of the database (for example, insert, update or delete statements are not allowed). and also only a subset of the full sql pl language is allowed i

52、n sql functions (no call statements, no cursors, no condition handling).despite these restrictions, most sql procedures with no side-effects can be converted into sql functions.so, as the title of this section indicates, consider using sql functions instead of sql procedures when you're just ext

53、racting data from the database without performing any changes.use temporary tables for temporary datain version 7, db2 introduced temporary tables. operations on temporary tables are usually much faster than operations on regular tables. let's look at some of the reasons:to begin with, the creat

54、ion of a temporary table does not involve the insertion of entries in the catalogs, and use of the temporary table does not involve access to the catalogs either; therefore, there is no catalog contention. because temporary tables are only accessible to the application that created them, there is no

55、 locking involved in their manipulation. if the not logged option is specified, operations on temporary tables are not logged (at the expense, of course, of the possibility of rolling back changes). therefore, if your stored procedures generate large amounts of temporary data that are only meant to

56、be used within a session with the database, storing those data in temporary tables can result in significant performance gains.before any use of a temporary table in an sql procedure, the table definition must be available in the compilation environment.after the connect reset command, the temporary

57、 table will no longer exist. at runtime, the application will have to ensure that the table exists before the first query that uses it is executed. this last observation brings up an important point we haven't mentioned yet: any query that references a temporary table will be compiled dynamicall

58、y, even if the query is written as static sql. like any other dynamic query, after the query is compiled, it will stay in compiled form in the package cache. the next time the same query is executed, db2 will recompile it only if it is not found in the cache.if you plan to create relatively big temp

59、orary tables and run several queries on them, consider defining indexes and running runstats on them (the latter obviously after the table has been populated).one last comment on using temporary tables in sql procedures: if you need to return a result set based on a temporary table that is created in the same procedure, the result set must be defined in a nested compound statement.the reason the result se