Purpose
To specify the key fields used to join records from one side of a join task to records from the other side of the join task.
Format
/JOINKEYS field [, field ...]
where
| field | = | field_name [COLLATINGSEQUENCE sequence] |
| sequence | = | LOCALE } {ASCII } {FASCII } {EBCDIC } {FEBCDIC } {MULTINATIONAL } {custom_seq} |
| 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 | The name of a custom collating sequence defined using /COLLATINGSEQUENCE. |
Location
The /JOINKEYS option for the left side of the join must appear after the source options, for example, /INFILE, that define the left side and before the source options that define the right side. The /JOINKEYS option for the right side of the join must appear after the source options that define the right side.
Defaults
When you specify a /JOINKEYS option without providing a /REFORMAT, Connect ETL joins the records so that the join key appears as the first field, followed by all remaining fields from the left side record, followed by the remaining fields from the right-side record.
Notes
Joining the records from files
To join the records from multiple files, provide an /INFILE option for every source file. A record from the first file(s) (the left side) is joined to a record from the second file(s) (the right side) when the contents of specific fields from the first record equal the contents of specific fields from the second record. For each side, specify a /JOINKEYS option to identify the fields used in the join.
Each record from the left side with a given value in its join keys is joined to each record from the right side with the identical value in its join keys. Thus, if m records from the left side have a given join keys value, and n from the right side have the same join keys value, the join results in m * n records with the keys value.
Prior to running dmexpress to perform the join, records on either side may be pre- sorted according to the join keys. If the records in the files of a side are already sorted on the join keys, you could optimize performance by indicating that the records are pre-sorted. To do this, add the PRESORTED clause to the /TASKTYPE option.
The join keys
The join keys can either be all single delimited fields or all fixed position fields. The two types cannot be mixed. Join keys cannot be of BIT data type.
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.
You can reference a collating sequence applicable to the join field in the COLLATINGSEQUENCE clause of the /JOINKEYS option. To define a default or customized collating sequence, see the /COLLATINGSEQUENCE option.
Examples
/JOINKEYS third_field/TASKTYPE join presorted leftside
/JOINKEYS cust_number, trans_date/INFILE PreviousTransactionFile.txt ALIAS old STREAM CRLF LAYOUT recordlayout ALIAS rl1
/JOINKEYS rl1.id
/INFILE CurrentTransactionFile.txt ALIAS new STREAM CRLF LAYOUT recordlayout ALIAS rl2
/JOINKEYS rl2.idUsing the ALIAS sub-option when associating input record layouts, one record layout definition is used with both sides of the join. In a reformat, you can refer to fields from different sides with the appropriate record layout alias. For example, to refer to the "id" field in your left side input, refer to it as rl1.id.