/KEYS - Connect_ETL

Purpose

To specify the key fields used for ordering input records for a sort or a merge.

Format

/KEYS

field [, field …]

where

field	=	field_name [COLLATINGSEQUENCE sequence]	[sense]
sequence	=	{LOCALE } {ASCII } {FASCII } {EBCDIC } {FEBCDIC } {MULTINATIONAL } {custom_seq}
sense	=	{ASCENDING } {DESCENDING}

field_name	The name of a field. For a summary of valid naming and formatting conventions for identifiers and constants, see syntax reference in the Connect help.
custom_seq	Name of a custom collating sequence defined using /COLLATINGSEQUENCE.

Location

The option may appear anywhere in the task definition unless there is input record selection.

Defaults

When no /KEYS option is present for a sort or merge, Connect ETL uses the complete data portion of the record as a single key of type character collated according to the task defalt collating sequence.

The default sense is ASCENDING.

Notes

You must specify the major key as the first field in the /KEYS option. Subsequent key fields should be specified in decreasing order of priority.

/KEYS is valid only for a sort or merge.

Collating sequence

A collating sequence specifies the collating order and comparison rules of text data. Only a character field can have an associated sequence. The default sequence for ASCII and LOCALE encoded data is the collating sequence of the current locale. Unicode encoded (UTF-*) fields are always collated according to the Unicode collation algorithm, described at http://www.unicode.org/unicode/reports/tr10/, using a collation strength of three levels for comparisons.

To define a default or customized collating sequence, whose name is specified after the COLLATINGSEQUENCE keyword within the /KEYS option, use the /COLLATINGSEQUENCE option.

Examples

/KEYS volume_id

The option specifies that the records are to be sorted such that the values in the volume_id field are in ascending order (the default).

/KEYS state ascending, county ascending, amount descending

The option specifies that the records are to be sorted according to the values in three fields, state, county and amount. The fields are specified in the /KEYS option in decreasing order of priority. Thus, when Connect ETL compares two records to determine which is first in order, the values in the state field in the two records are compared first. If the values differ, the record with the smaller value is placed before the record with the larger value (state is sorted in ascending order). However, if the values are the same, Connect ETL compares the values in the second key field, county. Again, if the values differ, the record with the smaller value is placed before the record with the larger value (county is sorted in ascending order). Only if both state and county values are the same in the two records does Connect ETL compare the values in the third key field, amount. If the amount values differ, the record with the larger value is placed before the record with the smaller value (amount is sorted in descending order). When all three key fields contain the same value in the two records the order of the records is not predictable, unless the /STABLE option has also been specified.

/KEYS - Connect_ETL - 9.13