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