快捷搜索:

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

当在 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 数据库可用,那么可以用以下敕令创建一个:;

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 并将它解压到一个临时目录中。将以下文件:sortkey

libicui18n.so.34

libicuuc.so.34

libicudata.so.34  复制到 sqllib/function 中。  连接到数据库,并运行 DDL 脚原先对这个函数进行编目:db2 connect to sample

db2 -tvf createfn.db2  注:这个 Linux 预构建二进制代码是在 Red Hat Linux 7.2 上用 gcc 2.96 编译的。  针对 Windows(32 位)预构建的二进制代码  要在 Windows 上安装预构建的二进制代码:  从 “下载 中下载文件 sortkey-windows-32.zip 并将它解压到一个临时目录中。  将以下文件:  sortkey.dll  icuin34.dll  icuuc34.dll  icudt34.dll  复制到 sqllib/function 中。  应用一个 DB2 敕令窗口连接到数据库,并运行 DDL 脚原先对这个函数进行编目:  db2 connect to sample  db2 -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 $2

fi

# ICU options

ICU_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_step

rem Link the program.

link -debug -out:%1.dll -dll %1.obj db2api.lib icudt.lib icuuc.lib

icuin.lib -def:%1.def

运行 bldrtn 脚原先编译这个 UDF: bldrtn sortkey  将 sortkey(在 Windows 上是 sortkey.dll)文件复制到 sqllib/function 中。连接到数据库,并运行 DDL 脚原先对这个函数进行编目:  db2 connect to sample  db2 -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)。

必须从 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 terminate

db2set DB2CODEPAGE=1208

db2 connect to sample

db2 -tf example.db2  当 DB2CODEPAGE 设置为 1208 时,它对其他利用法度榜样可能会孕育发生某些料想之外的效果。在运行示例之后,可以用以下敕令规复 DB2CODEPAGE:db2 terminate

db2set DB2CODEPAGE=  这些示例应用的表、数据和查询可以在文件 example.db2 中找到。当连接数据库时,可以用以下敕令履行它:  db2 -tf example.db2  ORDER BY  不合的说话用不合的规则来抉择字母的序次。例如,英语从字母 A 排序到 Z,没有例外,如第一个示例所示。  查询 1. 用英语排序规则进行排序SELECT NAME FROM SORTKEY_NAMES

ORDER BY ICU.SORTKEY(NAME, 'LEN')  结果 1. 用英语排序规则进行排序  码NAME

--------------------

Alice

Celine

Charles

Cindy

Don

Hillary

Ian

Sam 上一页123456789下一页   然则在斯洛伐克语中,两个字符的组合 CH 排在字母 H 和 I 之间。  查询 2. 用斯洛伐克语排序规则进行排序SELECT NAME FROM SORTKEY_NAMES

ORDER BY ICU.SORTKEY(NAME, 'LSK')  结果 2. 用斯洛伐克语排序规则进行排序NAME

--------------------

Alice

Celine

Cindy

Don

Hillary

Charles

Ian

Sam  重视,Charles 现在位于 Hillary 和 Ian 之间。  对照  一些说话对付同一个字符有不合的表示措施。例如,在德语中,字母 ä 即是 ae,ö 即是 oe,ü 即是 ue。在 SQL 中进行对照时,并不斟酌这些替代表示措施。  假设有两个德国城市名列表: NuernbergNürnberg  LuebeckLuebeck  KölnKoeln  当应用通俗的 SQL 联络这两个列表时,不斟酌替代的字符表示措施。  查询 3. 未规范化的联络SELECT G1.CITY AS CITY1, G2.CITY AS CITY2

FROM SORTKEY_GERMAN1 AS G1, SORTKEY_GERMAN2 AS G2

WHERE G1.CITY = G2.CITY

ORDER BY G1.CITY  结果 3. 未规范化的联络CITY1CITY2

-------------------- --------------------

LuebeckLuebeck  重视,在这两个表中只有拼写完全相同的城市名才被觉得是相等的。  然则,SORTKEY UDF 可以处置惩罚字符的不合表示措施。在这种环境下,我们应用德语排序规则的 “phonebook 变体并将强度设置为 1,从而轻忽重音差异。(排序规则选项的完备列表见 “排序规则名。)

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

ORDER 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 NAMES

ORDER BY MY_SORTKEY(NAME, 'LFR')  不管应用哪种措施,紧张的斟酌身分都是天生的列的长度。SORTKEY 结果的长度可能比原本的字符串长。简单的规则是,对付输入字符串中的每个字符,在输出字符串中准许有 12 字节。(对付某些不常见的排序规则和输入值组合,这个空间以致也可能不敷。)然则,许多排序规则会孕育发生比这短得多的排序键,是以在抉择天生的列的大年夜小时,对要应用的排序规则和数据进行一些实验是有赞助的。

查询 4. 规范化的联络SELECT G1.CITY AS CITY1, G2.CITY AS CITY2

FROM SORTKEY_GERMAN1 AS G1, SORTKEY_GERMAN2 AS G2

WHERE ICU.SORTKEY(G1.CITY, 'LDE_KPHONEBOOK_S1') =

ICU.SORTKEY(G2.CITY, 'LDE_KPHONEBOOK_S1')

ORDER BY G1.CITY  结果 4. 规范化的联络CITY1CITY2

-------------------- --------------------

KölnKoeln

LuebeckLuebeck

NuernbergNürnberg  经由过程应用排序键(而不是值本身)进行对照,适当地处置惩罚了名称中的次要差异。经由过程选择适当的排序规则,可以根据说话进行对照、轻忽大年夜小写和重音差异或者完全轻忽某些字符。  在 ORDER BY 子句中,改动一个排序规则的强度来轻忽字符属性可能会导致不确定的查询结果序次。例如, ORDER BY ICU.SORTKEY(COLUMN, 'S1') (这里应用的排序规则会轻忽大年夜小写和重音)会精确地排列 A 789下一页 NAMES: NAMELANGUAGE

------------

BobLEN

MarcLFR

Serge LDE

SELECT NAME FROM NAMES

WHERE ICU.SORTKEY(NAME, LANGUAGE) = ICU.SORTKEY(:hv, LANGUAGE)  在这个例子中,对付每一行都要预备一个新的排序规则。这样做的效率异常低。要是 SORTKEY 的第二个参数调换为一个字面字符串或主机变量,那么查询的机能会好得多。  重视,可以在一个查询中混杂应用不合的排序规则,只要每个排序规则在不合的 SORTKEY 实例中。以下查询的机能会好得多:SELECT NAME FROM NAMES

WHERE 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 NAMES

ORDER BY ICU.SORTKEY(NAME, 'LFR')

您可能还会对下面的文章感兴趣: