DB2用户定义函数实现多种语言的排序（1）

当在 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