A user-defined function for culturally correct collation in DB2 UDB

Document options Document options requiring JavaScript are not displayed Discuss Sample code New site feature Check out our new article design and features. Tell us what you think. Rate this page Help us improve this content

Level: Intermediate

Doug Doole (doole@ca.ibm.com), Senior Software Developer, IBM　

02 Feb 2006
Updated 18 Dec 2007

Learn about the user-defined function (UDF) for IBM DB2® Universal Database™ for Linux®, UNIX®, and Windows® (DB2 UDB) that provides culturally correct collation for Unicode databases. The UDF can be used to order query results in a culturally sensitive manner. It can also be used to provide culturally correct comparisons. The UDF is independent of the collation specified when the database was created.

Introduction

When storing multilingual data in a DB2 UDB database, Unicode is often the only encoding that can accommodate the entire range of data. While DB2 can store and process Unicode data, its collation (sorting) capabilities are restricted to binary collations and three cultural collations. Furthermore, a database is restricted to a single collation that is determined when the database is first created. For a database that needs to support users and data in many languages, these collation restrictions are a significant obstacle.

Update note DB2 has introduced the function COLLATION_KEY_BIT that provides the same feature as this UDF. The COLLATION_KEY_BIT function is available in: Available in DB2 9.5 Available in DB2 9.1, fixpack 3 Available in DB2 8.2, fixpack 15 Consult the DB2 documentation for the syntax of the function and for the supported collation names.

IBM has created a library for managing Unicode data called ICU (International Components for Unicode). This library provides a full set of functions for manipulating Unicode data for both the C and Java™ programming languages. The library is released under a non-restrictive open source license, which makes it usable in many applications.

The ICU library provides functions that implement the standard Unicode Collation Algorithm (UCA) along with many cultural tailorings. It is fairly simple to incorporate the appropriate ICU functions into a UDF (user-defined function), which makes the full power of ICU's collation support available to DB2. (Incidentally, DB2 for Linux, UNIX, and Windows uses the ICU library to implement the three cultural collations that it supports on Unicode data.)

This article focuses on DB2 V8 for Linux, UNIX, and Windows, but the example UDF will also work in the upcoming Viper release. The UDF should also work in DB2 V7 FP3 or later, but this has not been tested. Also, the UDF will likely work in DB2 for zSeries® and iSeries™ as well, but this has not been tested, and these platforms are not covered in this article.

Back to top

Installation

Sample database This article assumes that the database to be used is called SAMPLE, but any database can be used. Whatever database is used, it must be a Unicode database. To determine if a database is a Unicode database, issue the command: db2 get database configuration for sample Near the top of the configuration information is the entry for "Database code page," which must be "utf-8." If no Unicode database is available, then one can be created with the command: db2 create database sample using codeset utf-8 territory ca

Binaries for 32-bit Intel and AMD platforms are provided with this article. Instructions for installing the pre-built binaries are provided for Linux and Windows. For all other platforms, instructions are provided for compiling ICU and the UDF.

Pre-built binary for Linux (32-bit Intel or AMD)

To install the pre-built binary on Linux:

1. Download the file sortkey-linux-x86.zip from "Downloads," and extract it to a temporary directory.
2. Copy the files:
   • sortkey
   • libicui18n.so.34
   • libicuuc.so.34
   • libicudata.so.34
   into sqllib/function.
3. Connect to the database, and run the DDL script to catalog the function:

   db2 connect to sample
db2 -tvf createfn.db2

Note:The pre-built binary for Linux was compiled on Red Hat Linux 7.2 with gcc 2.96.

Pre-built binary for Windows (32-bit)

To install the pre-built binary on Windows:

1. Download the file sortkey-windows-32.zip from "Downloads," and extract it to a temporary directory.
2. Copy the files:
   • sortkey.dll
   • icuin34.dll
   • icuuc34.dll
   • icudt34.dll
   into sqllib/function.
3. Using a DB2 command window, connect to the database, and run the DDL script to catalog the function:

   db2 connect to sample
db2 -tvf createfn.db2

Compiling ICU and the UDF

To compile ICU and the UDF:

1. Download and install the ICU library. See "Resources" for a link to the ICU site, where binaries or source code can be downloaded, along with instructions on how to compile and install the library.
2. If necessary, add the directory containing the ICU executables to the PATH environment variable.
   • In Windows, it is also necessary to update the LIB and INCLUDE environment variables. The LIB variable should reference the icu\lib directory, and INCLUDE should reference the icu\include directory.
3. Download the file sortkey-source.zip from "Downloads," and extract it to a temporary directory.
4. Copy the file sqllib/samples/c/bldrtn (sqllib\samples\c\bldrtn.bat on Windows) to the temporary directory and edit the copied file.
5. It is necessary to add information about ICU to the compile and link steps.
   • On Linux or UNIX, define the variables ICU_C_FLAGS and ICU_L_FLAGS, and add them to the compile and link commands. The variables should be defined just before the commands, and the variables should be used immediately after the compiler or linker executable.

     Listing 1 shows the relevant part of the modified Linux bldrtn file. The changes have been made bold.

   Listing 1. Modified Linux bldrtn file

   # 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

   
   
   • On Windows, the ICU libraries must be added to the link command.

     Listing 2 shows the relevant part of the modified Windows bldrtn.bat file. The changes have been made bold.

   Listing 2. Modified Windows bldrtn.bat file

   :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

   
   
6. Run the bldrtn script to compile the UDF:

   bldrtn sortkey

7. Copy the sortkey (sortkey.dll on Windows) file into sqllib/function.
8. Connect to the database, and run the DDL script to catalog the function:

   db2 connect to sample
db2 -tvf createfn.db2

Note that when the UDF has been compiled in this manner, it depends on ICU being fully installed on any machine where the UDF will be run.

Back to top

Using the SORTKEY UDF

Syntax

>>--SORTKEY--(--string-expression--,--collation-name--)--><

The schema of the UDF is ICU. If the schema ICU is on the SQL PATH, then it is not necessary to explicitly qualify references to the UDF.

The SORTKEY UDF returns a VARCHAR(1200) FOR BIT DATA string representing the sort key of the string-expression in the specified collation-name. If the sort key is longer than 1200 bytes, then the sort key is truncated and a warning (SQLSTATE 01HKY) is returned. The result of SORTKEY is nullable. If any argument is null, then the result is NULL.

The results of SORTKEY for two strings can be binary compared to determine their order within the specified collation-name. For the comparison to be meaningful, the results of the SORTKEY used must be from the same collation-name.

string-expression
An expression that returns a CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC string for which the sort key should be determined. The maximum length of string-expression is 100 characters (SQLSTATE 22001). If string-expression is CHAR or VARCHAR, the expression must not be FOR BIT DATA (SQLSTATE 42846). If string-expression is an empty string, the result is a valid sort key with a non-zero length.

collation-name
A character string expression that specifies the collation to use when determining the sort key. The value of collation-name is not case sensitive and must be either the empty string (which corresponds to the default UCA collation) or a name as defined in "Collation names." (SQLSTATE SKCOL).

SORTKEY must be called from a Unicode database, or an error is returned. (SQLSTATE SKUTF).

Examples

Unicode characters in example.db2 The example.db2 file contains several characters encoded in UTF-8. Ideally the examples should be invoked from a Unicode shell. If the examples are not invoked from a Unicode shell, then the tables SORTKEY_GERMAN1 and SORTKEY_GERMAN2 will be created with incorrect data, and example 4 will fail. To allow the example to work from a non-Unicode shell, the registry variable DB2CODEPAGE can be set to 1208. This causes the DB2 client to process data in UTF-8. The accented characters will not display correctly, but the example will behave properly. To set DB2CODEPAGE and run the example, invoke the following: db2 terminate db2set DB2CODEPAGE=1208 db2 connect to sample db2 -tf example.db2 DB2CODEPAGE may have some unexpected effects on other applications when it is set to 1208. After running the example, DB2CODEPAGE can be reset with the following commands: db2 terminate db2set DB2CODEPAGE=

The tables, data, and queries for these examples can be found in the file example.db2. It can be executed with the command:

db2 -tf example.db2

when connected to the database.

ORDER BY

Different languages have different rules for determining the order of letters. English, for example, sorts the letters A to Z with no exceptions, as shown in the first example.

Query 1. Sort with English collation

                    
                    SELECT NAME FROM SORTKEY_NAMES
   ORDER BY ICU.SORTKEY(NAME, 'LEN')

                    NAME
--------------------
Alice
Celine
Charles
Cindy
Don
Hillary
Ian
Sam

In Slovak, however, the two character combination of CH sorts between the letters H and I.

Query 2. Sort with Slovak collation

                    
                    SELECT NAME FROM SORTKEY_NAMES
   ORDER BY ICU.SORTKEY(NAME, 'LSK')

                    NAME
--------------------
Alice
Celine
Cindy
Don
Hillary
Charles
Ian
Sam

Notice that the name Charles is now between Hillary and Ian.

Comparison

Some languages have different representations for the same character. For example, in German, the letter ä is equivalent to ae, ö is equivalent oe, and ü is equivalent to ue. When a comparison is done in SQL, it does not take these alternate representations into consideration.

Suppose there are two lists of German city names:

Nuernberg    Nürnberg
Luebeck      Luebeck
Köln         Koeln

                    
                    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

                    CITY1                CITY2
-------------------- --------------------
Luebeck              Luebeck

Notice that only the city name that had identical spelling in both tables was considered equal.

The SORTKEY UDF, however, can handle the different representation of the characters. In this case, we are using the "phonebook" variant of the German collation and setting the strength to 1, so accent differences are ignored. (See "Collation Names" for a complete list of the collation options.)

Query 4. Join with normalization

                    
                    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

                    CITY1                CITY2
-------------------- --------------------
Köln                 Koeln
Luebeck              Luebeck
Nuernberg            Nürnberg

By using the sort keys, rather than the values themselves, for the comparison, the minor differences in the names were appropriately handled. By choosing appropriate collations, it is possible to compare by language, to ignore case and accent differences, or even to ignore entire characters.

Notes

• Modifying the strength of a collation to ignore attributes of characters in an ORDER BY may result in non-deterministic ordering of the query results. For example, ORDER BY ICU.SORTKEY(COLUMN, 'S1') (a collation that ignores case and accent) will correctly order A < B < C. However, the collation will not distinguish between "apple," "Apple," and "APPLE" and may return them in any order.
• The value generated by SORTKEY is specific to the collation used. Therefore, when using SORTKEY in a predicate, use the exact same collation on both sides of the comparison.

Back to top

Performance considerations

Whenever a UDF is introduced into a query, it can hurt the performance of the query. Careful consideration of database and query design can minimize the performance impact.

Some things to consider are:

• Use constant collation names
• Minimize the use of SORTKEY
• Use generated columns

Use constant collation names

Preparing a collation for use is an expensive operation. Therefore, do not change collation names as the query is executing. For example, consider the following table and query:

NAMES: NAME  LANGUAGE
       ----  --------
       Bob   LEN
       Marc  LFR
       Serge LDE

SELECT NAME FROM NAMES
   WHERE ICU.SORTKEY(NAME, LANGUAGE) = ICU.SORTKEY(:hv, LANGUAGE)

In this example, a new collation will have to be prepared for every row. This is very inefficient. The query would perform much better if the second parameter of SORTKEY was replaced with a literal string or host variable.

Note that there is no problem mixing collations within a query as long as each collation is used in a different instance of SORTKEY. The following query will perform much better:

                SELECT NAME FROM NAMES
   WHERE ICU.SORTKEY(NAME, 'LFR') = ICU.SORTKEY(:hv, 'LFR')

Minimize the use of SORTKEY

When the data is known to be consistent, there is no need to use SORTKEY for every operation. For example, consider query 3 and query 4 from before. If the data had been entered consistently such that ä, ö, and ü had always been used, or the data had been cleansed to replace all occurrences of ae, oe, and ue with ä, ö, and ü, then queries 3 and 4 would return the same results, and query 3 would run much faster.

When the data is consistent, SORTKEY is frequently not needed. Use the standard SQL comparison operators where possible, and use SORTKEY in the final ORDER BY.

Use generated columns

If a database regularly uses a small number of collations, consider using generated columns to precompute the result of SORTKEY and store it in the database.

For example, consider a database that typically only needs French and German collation. In this case, it may be possible (depending on the overall size of the tables) to create generated columns to hold the result of SORTKEY. For example:

                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')

When the DB2 query compiler evaluates the query, it realizes that the value for ICU.SORTKEY(NAME, 'LFR') is already computed and the column NAME_FR_KEY is used instead. If, however, a query uses ICU.SORTKEY(NAME, 'LES') (Spanish collation), then the SORTKEY function will have to be evaluated as part of the query.

Unfortunately, recording the generated column as a VARCHAR(1200) value uses up a significant portion of the space available in the table. Fortunately, there are some options.

One option is to simply change createfn.db2 to create the SORTKEY function with a shorter length for the result type. If this is done, then the constant MAX_RESULT should be reduced to match in sortkey.c and the UDF should be recompiled.

Alternatively, it is possible to cast the result of SORTKEY to a shorter VARCHAR value. However, for the optimizer to use the generated column, the same cast must be used in every reference. This is what it would look like:

                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))
            

The need to always specify the cast makes this approach less than ideal. The cast can be hidden by using a sourced function as follows:

                CREATE FUNCTION MY_SORTKEY(VARCHAR(50), VARCHAR(50))
   RETURNS VARCHAR(600) FOR BIT DATA
                SOURCE ICU.SORTKEY
                CREATE 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')

Regardless of which approach is used, one important consideration is the length of the generated column. The result of SORTKEY can be substantially longer than the original string. A rule of thumb is that for each character in the input string, allow 12 bytes in the output string. (For some unusual combinations of collations and input values this may even not be enough space.) Many collations, however, produce sort keys that are much shorter than this, so some experimentation with the collations and data to be used will be useful in determining the size of the generated column.

Back to top

Appendix: Collation names

This appendix describes the possible collation attributes, their values, and typical usage examples. The information in this appendix is derived from the ICU documentation, and more details can be found in the Collation sections of the ICU User Guide and in the ICU API reference. See "Resources" for a link to the ICU documentation.

An empty string without any attribute implements the default UCA. The default UCA cannot encompass the collation sequence of every language supported by Unicode. To address this deficiency, optional attributes can be specified to customize the default UCA ordering. To specify the attributes, use the attribute short forms, separated by the underscore (_) character.

In the following table, "X" turns an attribute off, "O" turns an attribute on, and "D" uses the default for the attribute.

Table 2 lists the valid locale names for the collation. All the collations conform to version 1.2 of the Common Locale Data Repository (CLDR), as published by the Unicode Consortium Web site (see "Resources"). The "Default collation attributes" column shows the full name of the collation for the specific locale. For example, LAR is equivalent to LAR_AN_CX_EX_FX_HX_NX_S3.

Back to top

Downloads

Description	Name	Size	Download method
Source code for the SORTKEY function	sortkey-source.zip	6KB	FTP|HTTP
Pre-built SORTKEY for 32-bit Red Hat Linux 7.2	sortkey-linux-x86.zip	4.7MB	FTP|HTTP
Pre-built SORTKEY for 32-bit Windows	sortkey-windows-32.zip	4.3MB	FTP|HTTP

	Information about download methods

Resources

• IBM Globalization - ICU Web site: Learn all the capabilities of the ICU library.
  
  
• Unicode Consortium Web site: Learn all about Unicode.
  
  
• "Access your database from everywhere: A practical approach to DB2 UDB for Linux, UNIX, and Windows Unicode support" (developerWorks, January 2006): This article explains the basics of Unicode support in DB2 UDB.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  
• developerWorks IBM DB2 Universal Database for Linux, UNIX, and Windows page: Learn more about DB2 Universal Database, IBM's relational database management system that provides the foundation of information on demand.
  
  
• developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.

• Download and compile ICU: Product documentation.
  
  
• Download a trial version of DB2 Universal Database Enterprise Server Edition.
  
  
• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

• Participate in the discussion forum.
  
  
• developerWorks blogs: Get involved in the developerWorks community.
  
  

About the author

		Doug Doole is a Senior Software Developer, working on Unicode support for DB2 UDB at the IBM Toronto Lab. He has been working on DB2 UDB for the past 14 years and on Unicode for the past two years.

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Share this.... Digg this story del.icio.us Slashdot it!

Back to top