Specifying the sorting sequence for a language

To specify the sorting sequence for a language, perform one of the following actions:

• In your SQL statement, use the COLLATION_KEY function with the collation-name parameter to specify a particular sorting sequence.

  A collation name specifies how Db2 is to sort data. It specifies attributes such as the language of the data, whether case should be considered, and how punctuation characters should be treated. You must specify a value that is acceptable for the z/OS® CUNBOPR_Collation_Keyword parameter.

  The COLLATION_KEY function returns a binary value that can be used to sort data according to the rules that are specified in the Unicode Collation algorithm.

  For example, suppose that you issue the following query:

  SELECT FIRSTNAME, LASTNAME
  FROM EMPLOYEE
  ORDER BY COLLATION_KEY(LASTNAME, 'UCA400R1_AS_LSV_S2');

  This query orders the employees by their surnames (in the LASTNAME column) based on the following options that are specified in the collation name UCA400R1_AS_LSV_S2:

  Table 1. Example collation options and corresponding collation keywords

  Corresponding collation keyword	Option
  UCA400R1	Use Unicode Collation Algorithm (UCA) version 4.0.1
  AS	Ignore spaces, punctuation and symbols
  LSV	Use Swedish linguistic conventions
  S2	Compare case-insensitively

• Create an index that maintains the sorting sequence by using the COLLATION_KEY function in the CREATE INDEX statement.

  Invoking the COLLATION_KEY function for every row in the table can slow performance. Creating an index based on the collation key shifts this performance cost from query time to insert or update time. That performance shift assumes that Db2 chooses to use the index for the query.

  For example, suppose that you want to use the following basic query:

  SELECT C1 FROM T1 ORDER BY C1

  However, you want to ensure that the result is ordered according to the rules for a particular locale. For this example, assume the language of the data is French. In this case, you can use the COLLATION_KEY function, as shown in the following statement:

  SELECT C1 FROM T1 ORDER BY COLLATION_KEY(C1,'UCA410_LFR_FO')

  The collation name UCA410_LFR_FO has the following meaning:

  Table 2. Example collation options and corresponding collation keywords

  Corresponding collation keyword	Option
  UCA410	Specifies that Db2 is to use the collation service UCA410
  LFR	Specifies that the locale is French. (L = language, FR= French)
  FO	Specifies that the French sorting attribute is to be used. (F = French attribute, O = On) Strings are to be sorted by examining the accents starting from the end of the string. This attribute is automatically set to on for the French locales. Therefore, in this case, it is not required.

  You might want to check if you can improve the performance of this query by creating an index on C1 that is based on the collation key. The following example statements show how to create such an index and use EXPLAIN statements to confirm that the index is used for faster access. You can view the results of the EXPLAIN statements by querying the plan table. The EXPLAIN output for this example shows only some of the plan table columns.

  

  EXPLAIN ALL SET QUERYNO = 110 FOR SELECT C1 FROM T1 
     ORDER BY COLLATION_KEY(C1,'UCA410_LFR_FO');   
  CREATE INDEX I1 ON T1 (COLLATION_KEY(C1,'UCA410_LFR_FO')); 
  EXPLAIN ALL SET QUERYNO = 210 FOR SELECT C1 FROM T1 
     ORDER BY COLLATION_KEY(C1,'UCA410_LFR_FO');  
  SELECT * FROM PLAN_TABLE;                   

  The last statement returns the following output:

       +-----------------------------------------------------------------------------------------
       | QUERYNO | QBLOCKNO | PROGNAME | PLANNO | METHOD | CREATOR | TNAME | TABNO | ACCESSTYPE |
       +-----------------------------------------------------------------------------------------
     1_|     110 |        1 | DSNTEP2  |      1 |      0 | ADMF001 | T1    |     1 | R          |
     2_|     110 |        1 | DSNTEP2  |      2 |      3 |         |       |     0 |            |
     3_|     210 |        1 | DSNTEP2  |      1 |      0 | ADMF001 | T1    |     1 | I          |
       +-----------------------------------------------------------------------------------------
  
       --------------------------------------------------------------------------------
       | MATCHCOLS | ACCESSNAME | INDEXONLY | SORTN_UNIQ | SORTN_JOIN | SORTN_ORDERBY |
       --------------------------------------------------------------------------------
     1_|         0 |            | N         | N          | N          | N             |
     2_|         0 |            | N         | N          | N          | N             |
     3_|         0 | I1         | N         | N          | N          | N             |
       -------------------------------------------------------------------------------- 
  
       ----------------------------------------------------------------------------------------
       | SORTN_GROUPBY | SORTC_UNIQ | SORTC_JOIN | SORTC_ORDERBY | SORTC_GROUPBY | PREFETCH   |
       ---------------------------------------------------------------------------------------- 
     1_| N             | N          | N          | N             | N             |  S         |
     2_| N             | N          | N          | Y             | N             |            |
     3_| N             | N          | N          | N             | N             |            |
       ----------------------------------------------------------------------------------------