當在 DB2 UDB 數據庫中存儲多語種數據時，Unicode 往往是惟一一種能夠適應整個數據范圍的編碼。DB2 可以存儲和處理 Unicode 數據，但是它的排序功能只限于二進制排序和三種文化排序。另外，一個數據庫只能有一種排序規則，這是在創建數據庫時決定的。對于需要支持多種語言的用戶和數據的數據庫，這些排序限制是一個明顯的難題。IBM 創建了一個用于治理 Unicode 數據的函數庫，稱為 ICU（International Components for Unicode）。這個庫為 C 和 Java™ 編程語言提供了一整套操作 Unicode 數據的函數。這個庫是在一種無限制的開放源碼許可證下發布的，這使它可以在許多應用程序中使用。ICU 提供的函數實現了標準的 Unicode Collation Algorithm（UCA）以及許多文化調整功能。很輕易將適當的 ICU 函數組合成一個 UDF（用戶定義函數），從而讓 DB2 可以充分利用 ICU 的排序支持。（隨便說一句，DB2 for Linux, UNIX, and Windows 就是使用 ICU 庫來實現在 Unicode 數據上支持的三種文化排序。）本文主要關注 DB2 V8 for Linux, UNIX, and Windows，但是這個 UDF 示例也可以用在即將發布的 Viper 上。這個 UDF 也應該能夠用在 DB2 V7 FP3 或更高版本上，但是這沒有經過測試。另外，這個 UDF 可能能夠用在 DB2 for zSeries® 和 iSeries™ 上，但是這沒有經過測試，本文也不討論這些平臺。安裝示例數據庫本文假設要使用的數據庫稱為 SAMPLE，但是可以使用任何數據庫。無論使用哪個數據庫，它必須是 Unicode 數據庫。為了判定一個數據庫是否是 Unicode 數據庫，執行以下命令：db2 get database configuration for sample接近配置信息頂部的 “Database code page 條目必須是 “utf-8。假如沒有 Unicode 數據庫可用，那么可以用以下命令創建一個： 123456789下一頁 db2 create database sample using codeset utf-8 territory ca本文提供了針對 32 位 Intel 和 AMD 平臺的二進制代碼。下面介紹在 Linux 和 Windows 上如何安裝預構建的二進制代碼。對于所有其他平臺，編譯 ICU 和 UDF 中提供了說明。針對 Linux（32 位 Intel 或 AMD）預構建的二進制代碼要在 Linux 上安裝預構建的二進制代碼：從 “下載 中下載文件 sortkey-linux-x86.zip 并將它解壓到一個臨時目錄中。將以下文件：sortkeylibicui18n.so.34libicuuc.so.34libicudata.so.34復制到 sqllib/function 中。連接到數據庫，并運行 DDL 腳本來對這個函數進行編目：db2 connect to sampledb2 -tvf createfn.db2注：這個 Linux 預構建二進制代碼是在 Red Hat Linux 7.2 上用 gcc 2.96 編譯的。針對 Windows（32 位）預構建的二進制代碼要在 Windows 上安裝預構建的二進制代碼：從 “下載 中下載文件 sortkey-windows-32.zip 并將它解壓到一個臨時目錄中。將以下文件：sortkey.dllicuin34.dllicuuc34.dllicudt34.dll復制到 sqllib/function 中。使用一個 DB2 命令窗口連接到數據庫，并運行 DDL 腳本來對這個函數進行編目：db2 connect to sampledb2 -tvf createfn.db2編譯 ICU 和 UDF要編譯 ICU 和 UDF：下載并安裝 ICU 庫。參見 “參考資料 中 ICU 站點的鏈接，在這里可以下載二進制代碼或源代碼，以及學習如何編譯和安裝這個庫。假如需要的話，將包含 ICU 可執行文件的目錄添加到 PATH 環境變量中。在 Windows 中，還需要更新 LIB 和 INCLUDE 環境變量。LIB 變量應該引用 iculib 目錄，INCLUDE 應該引用 icuinclude 目錄。 上一頁123456789下一頁 從 “下載 中下載文件 sortkey-source.zip 并將它解壓到一個臨時目錄中將文件 sqllib/samples/c/bldrtn（在 Windows 上是 sqllibsamplescldrtn.bat）復制到臨時目錄并編輯復制的文件。對于編譯和鏈接步驟，需要添加關于 ICU 的信息。在 Linux 或 UNIX 上，定義變量 ICU_C_FLAGS 和 ICU_L_FLAGS，并將它們添加到編譯和鏈接命令中。這些變量應該在命令前面定義，并在編譯器或鏈接器可執行文件名后面直接使用。清單 1 顯示 Linux bldrtn 文件中的相關部分。修改之處以粗體顯示。清單 1. 修改的 Linux bldrtn 文件# If an embedded SQL program, precompile and bind it.if [ -f $1".sqc" ]then./embprep $1 $2fi# ICU optionsICU_C_FLAGS="`icu-config --cppflags --cxxflags`"ICU_L_FLAGS="`icu-config --ldflags`"# Compile the program.$CC $ICU_C_FLAGS $EXTRA_C_FLAGS-I$DB2PATH/include -c $1.c -D_REENTRANT# Link the program and create a shared library$CC $ICU_L_FLAGS $LINK_FLAGS -o $1 $1.o $EXTRA_LFLAG -L$DB2PATH/$LIB -ldb2-lpthread在 Windows 上，必須將 ICU 庫添加到鏈接命令中。清單 2 顯示 Windows bldrtn.bat 文件中的相關部分。修改之處以粗體顯示。清單 2. 修改的 Windows bldrtn.bat 文件:link_steprem Link the program.link -debug -out:%1.dll -dll %1.obj db2api.lib icudt.lib icuuc.libicuin.lib -def:%1.def 上一頁123456789下一頁 運行 bldrtn 腳本來編譯這個 UDF： bldrtn sortkey將 sortkey（在 Windows 上是 sortkey.dll）文件復制到 sqllib/function 中。連接到數據庫，并運行 DDL 腳本來對這個函數進行編目：db2 connect to sampledb2 -tvf createfn.db2注重，假如以這種方式編譯這個 UDF，那么在運行它的任何機器上必須完整地安裝 ICU。使用 SORTKEY UDF語法>>--SORTKEY--(--string-expression--,--collation-name--)--><這個 UDF 的模式是 ICU。假如模式 ICU 在 SQL PATH 中，那么就不必對這個 UDF 進行顯式限定引用。SORTKEY UDF 返回一個 VARCHAR(1200) FOR BIT DATA 字符串，代表在指定的 collation-name 中 string-expression 的排序鍵。假如排序鍵的長度超過 1200 字節，那么排序鍵被截斷并返回一個警告（SQLSTATE 01HKY）。SORTKEY 的結果是可空的。假如任何參數是 null，那么結果就是 NULL。可以對兩個字符串的 SORTKEY 結果進行二進制比較，從而判定它們在指定的 collation-name 中的次序。為了讓比較有意義，使用的 SORTKEY 結果必須來自相同的 collation-name。string-expression這個表達式返回一個 CHAR、VARCHAR、GRAPHIC 或 VARGRAPHIC 字符串，UDF 要判定這個字符串的排序鍵。string-expression 的最大長度是 100 個字符（SQLSTATE 22001）。假如 string-expression 是 CHAR 或 VARCHAR，這個表達式必須不是 FOR BIT DATA (SQLSTATE 42846)。假如 string-expression 是空字符串，那么結果是一個具有非零長度的有效排序鍵。collation-name這個字符串表達式指定在判定排序鍵時使用的排序規則。collation-name 的值不是大小寫敏感的，而且必須是空字符串（對應于默認的 UCA 排序）或者 “排序規則名 中定義的一個名稱。（SQLSTATE SKCOL）。 上一頁123456789下一頁 必須從 Unicode 數據庫調用 SORTKEY，否則返回一個錯誤。（SQLSTATE SKUTF）。示例example.db2 中的 Unicode 字符example.db2 文件包含幾個用 UTF-8 進行編碼的字符。理想情況下，應該從一個 Unicode shell 調用下面的示例。假如不是從 Unicode shell 調用示例，那么表 SORTKEY_GERMAN1 和 SORTKEY_GERMAN2 會包含不正確的數據，示例 4 會失敗。為了答應從非 Unicode shell 調用示例，可以將注冊表變量 DB2CODEPAGE 設置為 1208。這會使 DB2 客戶機以 UTF-8 格式處理數據。加重音的字符無法正確顯示，但是示例可以正確地運行。為了設置 DB2CODEPAGE 并運行示例，執行以下命令：db2 terminatedb2set DB2CODEPAGE=1208db2 connect to sampledb2 -tf example.db2當 DB2CODEPAGE 設置為 1208 時，它對其他應用程序可能會產生某些意料之外的效果。在運行示例之后，可以用以下命令恢復 DB2CODEPAGE：db2 terminatedb2set DB2CODEPAGE=這些示例使用的表、數據和查詢可以在文件 example.db2 中找到。當連接數據庫時，可以用以下命令執行它：db2 -tf example.db2ORDER BY不同的語言用不同的規則來決定字母的次序。例如，英語從字母 A 排序到 Z，沒有例外，如第一個示例所示。查詢 1. 用英語排序規則進行排序SELECT NAME FROM SORTKEY_NAMESORDER BY ICU.SORTKEY(NAME, 'LEN')結果 1. 用英語排序規則進行排序碼NAME--------------------AliceCelineCharlesCindyDonHillaryIanSam 上一頁123456789下一頁 但是在斯洛伐克語中，兩個字符的組合 CH 排在字母 H 和 I 之間。查詢 2. 用斯洛伐克語排序規則進行排序SELECT NAME FROM SORTKEY_NAMESORDER BY ICU.SORTKEY(NAME, 'LSK')結果 2. 用斯洛伐克語排序規則進行排序NAME--------------------AliceCelineCindyDonHillaryCharlesIanSam注重，Charles 現在位于 Hillary 和 Ian 之間。比較一些語言對于同一個字符有不同的表示方法。例如，在德語中，字母 ä 等于 ae，ö 等于 oe，ü 等于 ue。在 SQL 中進行比較時，并不考慮這些替代表示方法。假設有兩個德國城市名列表： NuernbergNürnbergLuebeckLuebeckKölnKoeln當使用普通的 SQL 聯結這兩個列表時，不考慮替代的字符表示方法。查詢 3. 未規范化的聯結SELECT G1.CITY AS CITY1, G2.CITY AS CITY2FROM SORTKEY_GERMAN1 AS G1, SORTKEY_GERMAN2 AS G2WHERE G1.CITY = G2.CITYORDER BY G1.CITY結果 3. 未規范化的聯結CITY1CITY2-------------------- --------------------LuebeckLuebeck注重，在這兩個表中只有拼寫完全相同的城市名才被認為是相等的。但是，SORTKEY UDF 可以處理字符的不同表示方法。在這種情況下，我們使用德語排序規則的 “phonebook 變體并將強度設置為 1，從而忽略重音差異。（排序規則選項的完整列表見 “排序規則名。） 上一頁123456789下一頁 查詢 4. 規范化的聯結SELECT G1.CITY AS CITY1, G2.CITY AS CITY2FROM SORTKEY_GERMAN1 AS G1, SORTKEY_GERMAN2 AS G2WHERE ICU.SORTKEY(G1.CITY, 'LDE_KPHONEBOOK_S1') =ICU.SORTKEY(G2.CITY, 'LDE_KPHONEBOOK_S1')ORDER BY G1.CITY結果 4. 規范化的聯結CITY1CITY2-------------------- --------------------K&ouml;lnKoelnLuebeckLuebeckNuernbergNürnberg通過使用排序鍵（而不是值本身）進行比較，適當地處理了名稱中的次要差異。通過選擇適當的排序規則，可以根據語言進行比較、忽略大小寫和重音差異或者完全忽略某些字符。在 ORDER BY 子句中，修改一個排序規則的強度來忽略字符屬性可能會導致不確定的查詢結果次序。例如， ORDER BY ICU.SORTKEY(COLUMN, 'S1') （這里使用的排序規則會忽略大小寫和重音）會正確地排列 A < B < C。但是，這個排序規則不區分 “apple、“Apple 和 “APPLE，可能以任意次序返回這些單詞。SORTKEY 產生的值依靠于使用的排序規則。因此，當在謂詞中使用 SORTKEY 時，比較的兩邊要使用完全相同的排序規則。性能考慮因素當在查詢中引入 UDF 時，它會損害查詢的性能。對數據庫和查詢的設計進行仔細地分析，可以將性能影響最小化。要考慮的一些因素是：使用固定的排序規則名盡可能少使用 SORTKEY使用生成的列使用固定的排序規則名預備要使用的排序規則是一種開銷很大的操作。因此，在查詢執行時不要改變排序規則名。例如，考慮以下表和查詢： 上一頁123456789下一頁 NAMES: NAMELANGUAGE------------BobLENMarcLFRSerge LDESELECT NAME FROM NAMESWHERE ICU.SORTKEY(NAME, LANGUAGE) = ICU.SORTKEY(:hv, LANGUAGE)在這個例子中，對于每一行都要預備一個新的排序規則。這樣做的效率非常低。假如 SORTKEY 的第二個參數替換為一個字面字符串或主機變量，那么查詢的性能會好得多。注重，可以在一個查詢中混合使用不同的排序規則，只要每個排序規則在不同的 SORTKEY 實例中。以下查詢的性能會好得多：SELECT NAME FROM NAMESWHERE ICU.SORTKEY(NAME, 'LFR') = ICU.SORTKEY(:hv, 'LFR')盡可能少使用 SORTKEY假如知道數據是一致的，那么就不需要對每個操作都使用 SORTKEY。例如，考慮前面的 查詢 3 和 查詢 4。假如數據是以一致的方式輸入的，比如總是使用 ä、ö 和 ü，或者已經對數據進行了清理，將所有 ae、oe 和 ue 替換為 ä、ö 和 ü，那么查詢 3 和查詢 4 會返回同樣的結果，而查詢 3 運行得快的多。假如數據是一致的，就不經常需要 SORTKEY。盡可能使用標準的 SQL 比較操作符，并在最后的 ORDER BY 中使用 SORTKEY。使用生成的列假如數據庫經常使用很少幾個排序規則，那么可以考慮使用生成的列預先計算 SORTKEY 的結果，并將這些結果存儲在數據庫中。例如，假設一個數據庫通常只需要法語和德語排序規則。在這種情況下，根據表的總規模，可以考慮創建生成的列來保存 SORTKEY 的結果。例如：清單 3. 創建生成的列來保存 SORTKEY 的結果CREATE TABLE NAMES(NAME VARCHAR(50),NAME_FR_KEY VARCHAR(1200) GENERATED ALWAYS AS (ICU.SORTKEY(NAME, 'LFR')),NAME_DE_KEY VARCHAR(1200) GENERATED ALWAYS AS (ICU.SORTKEY(NAME, 'LDE')))SELECT NAME FROM NAMESORDER BY ICU.SORTKEY(NAME, 'LFR') 上一頁123456789下一頁 當 DB2 查詢編譯器對這個查詢進行運算時，它會意識到 ICU.SORTKEY(NAME, 'LFR') 的值已經計算出來了，它會使用 NAME_FR_KEY 列來替代這個值。但是，假如查詢使用 ICU.SORTKEY(NAME, 'LES') （西班牙語排序規則），那么 SORTKEY 函數必須作為查詢的一部分執行。不幸的是，將生成的列記錄為 VARCHAR(1200) 值會占用表中的大量空間。好在，還有一些辦法。一個辦法是修改 createfn.db2，讓 SORTKEY 產生長度更短的結果類型。假如這樣做了，那么應該減小 sortkey.c 中的常量 MAX_RESULT，還應該重新編譯這個 UDF。另一個辦法是將 SORTKEY 的結果轉換為更短的 VARCHAR 值。但是，對于使用生成的列的優化器，必須在每個引用中使用同樣的轉換。這種辦法如下所示：清單 4. 在每個引用中使用同樣的轉換CREATE TABLE NAMES(NAME VARCHAR(50),NAME_FR_KEY VARCHAR(600)GENERATED ALWAYS AS (CAST(ICU.SORTKEY(NAME, 'LFR')AS VARCHAR(600))),NAME_DE_KEY VARCHAR(600)GENERATED ALWAYS AS (CAST(ICU.SORTKEY(NAME, 'LDE')AS VARCHAR(600))))SELECT NAME FROM NAMESORDER BY CAST(ICU.SORTKEY(NAME, 'LFR') AS VARCHAR(600))總是需要指定轉換，這使這種辦法不夠理想。可以使用下面的源函數將轉換隱藏起來：清單 5. 使用源函數將轉換隱藏起來CREATE FUNCTION MY_SORTKEY(VARCHAR(50), VARCHAR(50))RETURNS VARCHAR(600) FOR BIT DATASOURCE ICU.SORTKEYCREATE TABLE NAMES(NAME VARCHAR(50),NAME_FR_KEY VARCHAR(600) GENERATED ALWAYS AS (MY_SORTKEY(NAME, 'LFR')),NAME_DE_KEY VARCHAR(600) GENERATED ALWAYS AS (MY_SORTKEY(NAME, 'LDE')))SELECT NAME FROM NAMESORDER BY MY_SORTKEY(NAME, 'LFR')不管使用哪種方法，重要的考慮因素都是生成的列的長度。SORTKEY 結果的長度可能比原來的字符串長。簡單的規則是，對于輸入字符串中的每個字符，在輸出字符串中答應有 12 字節。（對于某些不常見的排序規則和輸入值組合，這個空間甚至也可能不夠。）但是，許多排序規則會產生比這短得多的排序鍵，因此在決定生成的列的大小時，對要使用的排序規則和數據進行一些實驗是有幫助的。 上一頁123456789