當在 DB2 UDB 數(shù)據(jù)庫中存儲多語種數(shù)據(jù)時，Unicode 往往是惟一一種能夠適應整個數(shù)據(jù)范圍的編碼。DB2 可以存儲和處理 Unicode 數(shù)據(jù)，但是它的排序功能只限于二進制排序和三種文化排序。另外，一個數(shù)據(jù)庫只能有一種排序規(guī)則，這是在創(chuàng)建數(shù)據(jù)庫時決定的。對于需要支持多種語言的用戶和數(shù)據(jù)的數(shù)據(jù)庫，這些排序限制是一個明顯的難題。IBM 創(chuàng)建了一個用于治理 Unicode 數(shù)據(jù)的函數(shù)庫，稱為 ICU（International Components for Unicode）。這個庫為 C 和 Java™ 編程語言提供了一整套操作 Unicode 數(shù)據(jù)的函數(shù)。這個庫是在一種無限制的開放源碼許可證下發(fā)布的，這使它可以在許多應用程序中使用。ICU 提供的函數(shù)實現(xiàn)了標準的 Unicode Collation Algorithm（UCA）以及許多文化調(diào)整功能。很輕易將適當?shù)?ICU 函數(shù)組合成一個 UDF（用戶定義函數(shù)），從而讓 DB2 可以充分利用 ICU 的排序支持。（隨便說一句，DB2 for Linux, UNIX, and Windows 就是使用 ICU 庫來實現(xiàn)在 Unicode 數(shù)據(jù)上支持的三種文化排序。）本文主要關注 DB2 V8 for Linux, UNIX, and Windows，但是這個 UDF 示例也可以用在即將發(fā)布的 Viper 上。這個 UDF 也應該能夠用在 DB2 V7 FP3 或更高版本上，但是這沒有經(jīng)過測試。另外，這個 UDF 可能能夠用在 DB2 for zSeries® 和 iSeries™ 上，但是這沒有經(jīng)過測試，本文也不討論這些平臺。安裝示例數(shù)據(jù)庫本文假設要使用的數(shù)據(jù)庫稱為 SAMPLE，但是可以使用任何數(shù)據(jù)庫。無論使用哪個數(shù)據(jù)庫，它必須是 Unicode 數(shù)據(jù)庫。為了判定一個數(shù)據(jù)庫是否是 Unicode 數(shù)據(jù)庫，執(zhí)行以下命令：db2 get database configuration for sample接近配置信息頂部的 “Database code page 條目必須是 “utf-8。假如沒有 Unicode 數(shù)據(jù)庫可用，那么可以用以下命令創(chuàng)建一個： 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 中。連接到數(shù)據(jù)庫，并運行 DDL 腳本來對這個函數(shù)進行編目：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 命令窗口連接到數(shù)據(jù)庫，并運行 DDL 腳本來對這個函數(shù)進行編目：db2 connect to sampledb2 -tvf createfn.db2編譯 ICU 和 UDF要編譯 ICU 和 UDF：下載并安裝 ICU 庫。參見 “參考資料 中 ICU 站點的鏈接，在這里可以下載二進制代碼或源代碼，以及學習如何編譯和安裝這個庫。假如需要的話，將包含 ICU 可執(zhí)行文件的目錄添加到 PATH 環(huán)境變量中。在 Windows 中，還需要更新 LIB 和 INCLUDE 環(huán)境變量。LIB 變量應該引用 iculib 目錄，INCLUDE 應該引用 icuinclude 目錄。 上一頁123456789下一頁 從 “下載 中下載文件 sortkey-source.zip 并將它解壓到一個臨時目錄中將文件 sqllib/samples/c/bldrtn（在 Windows 上是 sqllibsamplescldrtn.bat）復制到臨時目錄并編輯復制的文件。對于編譯和鏈接步驟，需要添加關于 ICU 的信息。在 Linux 或 UNIX 上，定義變量 ICU_C_FLAGS 和 ICU_L_FLAGS，并將它們添加到編譯和鏈接命令中。這些變量應該在命令前面定義，并在編譯器或鏈接器可執(zhí)行文件名后面直接使用。清單 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 中。連接到數(shù)據(jù)庫，并運行 DDL 腳本來對這個函數(shù)進行編目：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 字節(jié)，那么排序鍵被截斷并返回一個警告（SQLSTATE 01HKY）。SORTKEY 的結果是可空的。假如任何參數(shù)是 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這個字符串表達式指定在判定排序鍵時使用的排序規(guī)則。collation-name 的值不是大小寫敏感的，而且必須是空字符串（對應于默認的 UCA 排序）或者 “排序規(guī)則名 中定義的一個名稱。（SQLSTATE SKCOL）。 上一頁123456789下一頁 必須從 Unicode 數(shù)據(jù)庫調(diào)用 SORTKEY，否則返回一個錯誤。（SQLSTATE SKUTF）。示例example.db2 中的 Unicode 字符example.db2 文件包含幾個用 UTF-8 進行編碼的字符。理想情況下，應該從一個 Unicode shell 調(diào)用下面的示例。假如不是從 Unicode shell 調(diào)用示例，那么表 SORTKEY_GERMAN1 和 SORTKEY_GERMAN2 會包含不正確的數(shù)據(jù)，示例 4 會失敗。為了答應從非 Unicode shell 調(diào)用示例，可以將注冊表變量 DB2CODEPAGE 設置為 1208。這會使 DB2 客戶機以 UTF-8 格式處理數(shù)據(jù)。加重音的字符無法正確顯示，但是示例可以正確地運行。為了設置 DB2CODEPAGE 并運行示例，執(zhí)行以下命令：db2 terminatedb2set DB2CODEPAGE=1208db2 connect to sampledb2 -tf example.db2當 DB2CODEPAGE 設置為 1208 時，它對其他應用程序可能會產(chǎn)生某些意料之外的效果。在運行示例之后，可以用以下命令恢復 DB2CODEPAGE：db2 terminatedb2set DB2CODEPAGE=這些示例使用的表、數(shù)據(jù)和查詢可以在文件 example.db2 中找到。當連接數(shù)據(jù)庫時，可以用以下命令執(zhí)行它：db2 -tf example.db2ORDER BY不同的語言用不同的規(guī)則來決定字母的次序。例如，英語從字母 A 排序到 Z，沒有例外，如第一個示例所示。查詢 1. 用英語排序規(guī)則進行排序SELECT NAME FROM SORTKEY_NAMESORDER BY ICU.SORTKEY(NAME, 'LEN')結果 1. 用英語排序規(guī)則進行排序碼NAME--------------------AliceCelineCharlesCindyDonHillaryIanSam 上一頁123456789下一頁 但是在斯洛伐克語中，兩個字符的組合 CH 排在字母 H 和 I 之間。查詢 2. 用斯洛伐克語排序規(guī)則進行排序SELECT NAME FROM SORTKEY_NAMESORDER BY ICU.SORTKEY(NAME, 'LSK')結果 2. 用斯洛伐克語排序規(guī)則進行排序NAME--------------------AliceCelineCindyDonHillaryCharlesIanSam注重，Charles 現(xiàn)在位于 Hillary 和 Ian 之間。比較一些語言對于同一個字符有不同的表示方法。例如，在德語中，字母 ä 等于 ae，ö 等于 oe，ü 等于 ue。在 SQL 中進行比較時，并不考慮這些替代表示方法。假設有兩個德國城市名列表： NuernbergNürnbergLuebeckLuebeckKölnKoeln當使用普通的 SQL 聯(lián)結這兩個列表時，不考慮替代的字符表示方法。查詢 3. 未規(guī)范化的聯(lián)結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. 未規(guī)范化的聯(lián)結CITY1CITY2-------------------- --------------------LuebeckLuebeck注重，在這兩個表中只有拼寫完全相同的城市名才被認為是相等的。但是，SORTKEY UDF 可以處理字符的不同表示方法。在這種情況下，我們使用德語排序規(guī)則的 “phonebook 變體并將強度設置為 1，從而忽略重音差異。（排序規(guī)則選項的完整列表見 “排序規(guī)則名。） 上一頁123456789下一頁 查詢 4. 規(guī)范化的聯(lián)結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. 規(guī)范化的聯(lián)結CITY1CITY2-------------------- --------------------K&ouml;lnKoelnLuebeckLuebeckNuernbergNürnberg通過使用排序鍵（而不是值本身）進行比較，適當?shù)靥幚砹嗣Q中的次要差異。通過選擇適當?shù)呐判蛞?guī)則，可以根據(jù)語言進行比較、忽略大小寫和重音差異或者完全忽略某些字符。在 ORDER BY 子句中，修改一個排序規(guī)則的強度來忽略字符屬性可能會導致不確定的查詢結果次序。例如， ORDER BY ICU.SORTKEY(COLUMN, 'S1') （這里使用的排序規(guī)則會忽略大小寫和重音）會正確地排列 A < B < C。但是，這個排序規(guī)則不區(qū)分 “apple、“Apple 和 “APPLE，可能以任意次序返回這些單詞。SORTKEY 產(chǎn)生的值依靠于使用的排序規(guī)則。因此，當在謂詞中使用 SORTKEY 時，比較的兩邊要使用完全相同的排序規(guī)則。性能考慮因素當在查詢中引入 UDF 時，它會損害查詢的性能。對數(shù)據(jù)庫和查詢的設計進行仔細地分析，可以將性能影響最小化。要考慮的一些因素是：使用固定的排序規(guī)則名盡可能少使用 SORTKEY使用生成的列使用固定的排序規(guī)則名預備要使用的排序規(guī)則是一種開銷很大的操作。因此，在查詢執(zhí)行時不要改變排序規(guī)則名。例如，考慮以下表和查詢： 上一頁123456789下一頁 NAMES: NAMELANGUAGE------------BobLENMarcLFRSerge LDESELECT NAME FROM NAMESWHERE ICU.SORTKEY(NAME, LANGUAGE) = ICU.SORTKEY(:hv, LANGUAGE)在這個例子中，對于每一行都要預備一個新的排序規(guī)則。這樣做的效率非常低。假如 SORTKEY 的第二個參數(shù)替換為一個字面字符串或主機變量，那么查詢的性能會好得多。注重，可以在一個查詢中混合使用不同的排序規(guī)則，只要每個排序規(guī)則在不同的 SORTKEY 實例中。以下查詢的性能會好得多：SELECT NAME FROM NAMESWHERE ICU.SORTKEY(NAME, 'LFR') = ICU.SORTKEY(:hv, 'LFR')盡可能少使用 SORTKEY假如知道數(shù)據(jù)是一致的，那么就不需要對每個操作都使用 SORTKEY。例如，考慮前面的 查詢 3 和 查詢 4。假如數(shù)據(jù)是以一致的方式輸入的，比如總是使用 ä、ö 和 ü，或者已經(jīng)對數(shù)據(jù)進行了清理，將所有 ae、oe 和 ue 替換為 ä、ö 和 ü，那么查詢 3 和查詢 4 會返回同樣的結果，而查詢 3 運行得快的多。假如數(shù)據(jù)是一致的，就不經(jīng)常需要 SORTKEY。盡可能使用標準的 SQL 比較操作符，并在最后的 ORDER BY 中使用 SORTKEY。使用生成的列假如數(shù)據(jù)庫經(jīng)常使用很少幾個排序規(guī)則，那么可以考慮使用生成的列預先計算 SORTKEY 的結果，并將這些結果存儲在數(shù)據(jù)庫中。例如，假設一個數(shù)據(jù)庫通常只需要法語和德語排序規(guī)則。在這種情況下，根據(jù)表的總規(guī)模，可以考慮創(chuàng)建生成的列來保存 SORTKEY 的結果。例如：清單 3. 創(chuàng)建生成的列來保存 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') 的值已經(jīng)計算出來了，它會使用 NAME_FR_KEY 列來替代這個值。但是，假如查詢使用 ICU.SORTKEY(NAME, 'LES') （西班牙語排序規(guī)則），那么 SORTKEY 函數(shù)必須作為查詢的一部分執(zhí)行。不幸的是，將生成的列記錄為 VARCHAR(1200) 值會占用表中的大量空間。好在，還有一些辦法。一個辦法是修改 createfn.db2，讓 SORTKEY 產(chǎn)生長度更短的結果類型。假如這樣做了，那么應該減小 sortkey.c 中的常量 MAX_RESULT，還應該重新編譯這個 UDF。另一個辦法是將 SORTKEY 的結果轉(zhuǎn)換為更短的 VARCHAR 值。但是，對于使用生成的列的優(yōu)化器，必須在每個引用中使用同樣的轉(zhuǎn)換。這種辦法如下所示：清單 4. 在每個引用中使用同樣的轉(zhuǎn)換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))總是需要指定轉(zhuǎn)換，這使這種辦法不夠理想。可以使用下面的源函數(shù)將轉(zhuǎn)換隱藏起來：清單 5. 使用源函數(shù)將轉(zhuǎn)換隱藏起來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 結果的長度可能比原來的字符串長。簡單的規(guī)則是，對于輸入字符串中的每個字符，在輸出字符串中答應有 12 字節(jié)。（對于某些不常見的排序規(guī)則和輸入值組合，這個空間甚至也可能不夠。）但是，許多排序規(guī)則會產(chǎn)生比這短得多的排序鍵，因此在決定生成的列的大小時，對要使用的排序規(guī)則和數(shù)據(jù)進行一些實驗是有幫助的。 上一頁123456789