Previous topic |
Next topic |
Contents |
Contact z/OS |
Library |
PDF
Joining records z/OS DFSORT: Getting Started SC23-6880-00 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Often, you have two data sets with common key fields and different data fields, and you want to join their records, that is, for records with matching keys, you want to create output records with some fields from one data set and some fields from the other data set. You might want a one record to one record join, a one record to many records join, a many records to one record join, or even a many records to many records join. A JOINKEYS application helps you to perform various "join" applications on two data sets by one or more keys. You can do an inner join, full outer join, left outer join, right outer join and unpaired combinations. The two data sets can be of different types (fixed, variable, VSAM, and so on) and lengths, and have keys in different locations. The records from the input data sets can be processed in a variety of ways before and after they are joined using most of the DFSORT control statements you learned about previously including SORT or COPY, INCLUDE or OMIT, INREC, OUTREC and OUTFIL. Suppose you have two input data sets, REGION.IN1 and REGION.IN2 as shown in Table 1 and Table 2. REGION.IN1 has RECFM=FB and LRECL=35 and REGION.IN2 has RECFM=FB and LRECL=27, so their LRECLs are different.
From these two input data sets, you want to create an output data set, REGION.OUT. For each record in REGION.IN2, you want to look up the corresponding Region in REGION.IN1, and combine fields from the two records into one output record in REGION.OUT, as shown in Table 3.
Write the following DFSORT JCL and control statements to use a JOINKEYS application to create REGION.OUT from REGION.IN1 and REGION.IN2.
Two JOINKEYS statements are required: one for the F1 data set and another for the F2 data set. In this case, the first JOINKEYS statement identifies IN1 as the ddname for the F1 data set and indicates an ascending key (Region) in positions 1-5 of that data set. The second JOINKEYS statement identifies IN2 as the ddname for the F2 data set and indicates an ascending key (Region) is at positions 5-9 of that data set. Each key in the F1 data set must be of the same length and order (ascending or descending) as the corresponding key in the F2 data set, but does not have to be in the same location. The keys are always treated as unsigned binary (INREC can be used to "normalize" the keys in each data set before the records are joined, if necessary). The F1 data set will be sorted by the key in positions 1-5. The F2 data set will be sorted by the key in positions 5-9. If the records in a data set are already sorted by the key, you can specify SORTED on the JOINKEYS statement to tell DFSORT to copy the records of that data set rather than sorting them. Records with the same key in both data sets are joined and constructed
as directed by the REFORMAT statement using F1: for fields from the
F1 record and F2: for fields from the F2 record. This REFORMAT statement
creates joined records from the following fields:
The resulting joined records are 53 bytes long and are copied to the SORTOUT data set (REGION.OUT). If we wanted to sort the resulting joined records on the Headquarters
and Office fields, we could replace the OPTION COPY statement with
the following SORT statement:
The resulting sorted output records in REGION.OUT are shown in Table 4.
You can also use a JOINKEYS application to match records from two different input data sets in various ways. Suppose you have two input data sets, CITIES.IN1 and CITIES.IN2 as shown in Table 5 and Table 6.
From these two data sets, you want to create a CITIES.OUT data set with the records for Cities that appear in CITIES.IN1, but not in CITIES.IN2, as shown in Table 7.
Write the following DFSORT JCL and control statements to use a
JOINKEYS application to create CITIES.OUT from CITIES.IN1 and CITIES.IN2.
The first JOINKEYS statement identifies SORTJNF1 as the ddname for the F1 data set and indicates ascending keys (State and City) in positions 21-35 and 1-20 of that data set. Since the F1 records are already in order by the State and City fields, SORTED is used to do a Copy of the F1 records rather than a Sort. The second JOINKEYS statement identifies SORTJNF2 as the ddname for the F2 data set and indicates ascending keys (State and City) in positions 1-15 and 16-35 of that data set. Since the F2 records are not already in order by the State and City fields, SORTED is not used and a Sort in performed for the F2 records on the indicated keys. JOIN UNPAIRED,F1,ONLY is used to restrict the output (SORTOUT) to the records in F1 that do not have matching keys in F2. Since we want the entire F1 record, we do not need a REFORMAT statement. Now suppose we want to use the CITIES.IN1 and CITIES.IN2 data sets
again, but this time we want to produce the following output from
these two data sets:
Write the following DFSORT JCL and control statements to use a
JOINKEYS application to create BOTH.OUT, F1ONLY.OUT and F2ONLY.OUT
from CITIES.IN1 and CITIES.IN2.
The first JOINKEYS statement identifies CITIES1 as the ddname for the F1 data set and indicates ascending keys (State and City) in positions 21-35 and 1-20 of that data set. The JNF1CNTL data set contains an OMIT statement to remove the VERMONT records from the F1 data set so they will not be joined. Since the F1 records are already in order by the State and City fields, SORTED is used to do a Copy rather than a Sort for the F1 data set. The second JOINKEYS statement identifies CITIES2 as the ddname for the F2 data set and indicates ascending keys (State and City) in positions 1-15 and 16-35 of that data set. The JNF2CNTL data set contains an OMIT statement to remove the VERMONT records from the F2 data set so they will not be joined. Since the F2 records are not already in order by the State and City fields, SORTED is not used and a Sort in performed for the F2 data set. Since we want to separate out the BOTH, F1ONLY and F2ONLY joined records, we use a JOIN statement with UNPAIRED,F1,F2 to keep the unpaired joined records as well as the paired join records. In the REFORMAT statement, we use ? as the first field to give us an indicator of whether each key was found in both records ('B' indicator), only in F1 ('1' indicator) or only in F2 ('2' indicator). After the indicator, we put positions 1-37 (City, State, District) from F1 and 1-35 (State, City) from F2. After the records are joined, they will look as shown in Table 8.
Now we can use OUTFIL statements to create our three different output data sets. We write positions 2-38 (F1 City, F1 State, F1 District) of the joined records with an indicator of 'B' to positions 1-37 of the BOTH.OUT data set. We write positions 2-38 (F1 City, F1 State, F1 District) of the joined records with an indicator of '1' to positions 1-37 of the F1ONLY.OUT data set. We write positions 54-73 (F2 City) and 39-53 (F2 State) of the joined records with an indicator of '2' to positions 1-35 of the F2ONLY.OUT data set. The resulting records are shown in Table 9, Table 10 and Table 11.
These are just a few of the many types of join operations you can do with JOINKEYS. See z/OS DFSORT Application Programming Guide for complete details of using the JOINKEYS, JOIN and REFORMAT statements along with the other DFSORT statements to perform different JOINKEYS applications.
|
Copyright IBM Corporation 1990, 2014
|