COLLATION_KEY

string-expression

An expression that returns a character or graphic string that is not a LOB for which the collation key is to be determined. If string-expression is a character string, it must not be FOR BIT DATA. If string-expression is not in Unicode UTF-16 (CCSID 1200), it is converted to Unicode UTF-16 before the corresponding collation key is obtained. The length of string-expression must not exceed 32704 bytes of the UTF-16 representation.

collation-name

A string constant or a string host variable that is not a binary string, CLOB, or DBCLOB. collation-name specifies the collation to use when determining the collation key. If collation-name is not an EBCDIC value, it is converted to EBCDIC. The length of collation-name must be between 1 and 255 bytes of the EBCDIC representation. The value of collation-name is not case sensitive and must be a left justified, valid "short path" collation setting for the parameter CUNBOPRM_Collation_Keyword in area CUN4BOPR. For detailed information about the "short path" setting in the parameter CUNBOPRM_Collation_Keyword, see z/OS Support for Unicode: Using Conversion Services.

The value of the host variable must not be null. If the host variable has an associated indicator variable, the value of the indicator variable must not indicate a null value. collation-name must be left justified within the host variable. It must also be padded on the right with blanks if the length is less than that of the host variable and the host variable is a fixed length CHAR or GRAPHIC data type.

collation-name is in the form of CUN4BOPR_Collation_Keyword specification. You must specify a value that is acceptable for the z/OS® CUNBOPR_Collation_Keyword parameter.

The following table lists some supported values:

Table 1. Collation Keywords Reference

Attribute name	Key	Possible values
Locale	L.R.V	<locale>
Strength	S	1, 2, 3, 4, I, D
Case_Level	K	X, O, D
Case_First	C	X, L, U, D
Alternate	A	N, S, D
Variable_Top	T	<hex digits>
Normalization	N	X, O, D
French	F	X, O, D
Hinayana	H	X, O, D

The following table describes the abbreviations for the collation keywords:

Abbreviation

Definition

D

default

O

on

X

off

1

primary

2

secondary

3

tertiary

4

quaternary

I

identical

S

shifted

N

non-ignorable

L

lower-first

U

upper-first

The following examples show keywords using the above specifications:

'UCA400R1_AS_LSV_S3_CU'

UCA version 4.0.1; ignore spaces, punctuation and symbols; use Swedish linguistic conventions; use case-first upper; compare case-sensitive.

'UCA400R1_AN_LSV_S3_CL_NO'

UCA version 4.0.1; do not ignore spaces, punctuation and symbols; use Swedish linguistic conventions; use case-first lower (or does not set it to mean the same, since lower is used in most locales as the default); normalization ON; compare case-sensitive.

integer

An integer value that specifies the length attribute of the result. If specified, the value must be an integer constant between 1 and 32704.

If the length is not specified, the length attribute of the result is determined as follows:

string-expression	Result length attribute
CHAR(n) or VARCHAR(n)	MIN (VARBINARY(12n), 32704)
GRAPHIC(n) or VARGRAPHIC(n)	MIN (VARBINARY(12n), 32704)

Regardless of whether the length is specified, the length of the collation key must be less than or equal to the length attribute of the result. The actual result length of the collation key is approximately six times of the length of string-expression where the length of string-expression is in Unicode byte representation. For certain collation-name such as UCA410_LKO_RKR (for Korean collation) the default length attribute of the result, 12n, might not be large enough and an error will be returned. To avoid such an error, the length attribute of the result must be explicitly specified to a larger constant. For the proper length attribute of the result, see z/OS Support for Unicode: Using Conversion Services for information about target buffer length considerations for Collation Services.

   SELECT FIRSTNAME, LASTNAME 
     FROM DSN8A10.EMP
     ORDER BY COLLATION_KEY(LASTNAME, 'UCA400R1_AS_LSV_S2');

   SELECT E.WORKDEPT 
     FROM EMPLOYEE AS E INNER JOIN SALES AS S
     ON COLLATION_KEY(E.LASTNAME, 'UCA400R1_LFR') =
                      COLLATION_KEY(S.SALES_PERSON, 'UCA400R1_LFR')
     WHERE S.REGION = 'Quebec';

   CREATE INDEX EMPLOYEE_NAME_SORT_KEY
     ON EMPLOYEE (COLLATION_KEY(LASTNAME, 'UCA410_LDE', 600),
                  COLLATION_KEY(FIRSTNAME, 'UCA410_LDE', 600),
                  ID);