Well an advert shift from Natural to SORT, what might have triggered this. Well nothing much but just that mainframe developers tend NOT to use more advanced features and stick to their guns in using program as opposed to the available new features. Well one power packed feature is what SORTJOIN has to offer us. The join facility of SyncSort provides the capability to join records from two source files. Each record from the first file with a given value in one or more fields (the join key) is joined to each record from the second files that has an identical value in its join key, something like a match & merge process. The joined records are passed to the sort/copy process. The power of this facility is enhanced by the ability to eliminate records from either or both files.
The following diagram correctly captures how the SORTJOIN works in its sequence of execution (courtesy SYNSORT manual).
Join processing is controlled by three control statements:
Input File 2: Name & Address File
DCB=(RECFM=FB,LRECL=20)
Position Length Description
1 10 Customer Number
11 10 City
JOINKEYS Enables join feature processing and identifies the fields used to select records for join processing.
JOIN Specifies the disposition of paired and unpaired records in a join.
REFORMAT Defines the record layout to be produced by join processing.
The following examples detail how SORT JOIN can be used:
- Problem (Using Simple Join with Record selection)
There are two flat files, we are trying to merge the matching data, i.e. by customer number in both the files (There is a 10-digit customer number that is common to the records in both files.) - Names from File 1 & City from the second file. The data required for this task is located in two separate files. The first file contains Name & Customer Number information. The second file contains the Customer Number & City of all the customers. There is a 10-digit customer number that is common to the records in both files.
Input File 1: Transaction File
DCB=(RECFM=FB,LRECL=24)
Position Length Description
1 14 Customer Name
15 10 Customer Number
1---+----0----+----0----+----0----+----0----+
GREG DAVIS 6456332132
MARK LUKE 5435313132
JUDY FOSTER 7343423456
GEORGE STORMS 8933313324
STEVE JONES 2135212331
MEGAN MADRUS 5645321323
MONICA FELDER 3234139897
MARY COX 9893133733
ARTHUR FREEMAN4233232989
JAMES BOND 6462168111
|
Input File 2: Name & Address File
DCB=(RECFM=FB,LRECL=20)
Position Length Description
1 10 Customer Number
11 10 City
1---+----0----+----0----+----0----+----0----+----0----+----0----+----0
6456332132NEW YORK
5435313132CALIFORNIA
7343423456FLORIDA
8933313321WALES
2135212331VEGAS
5645321323WARWICK
3234139899DETROIT
9893133733JERSEY CITY
4233232989CAROLINA
6462168111VIRGINIA
7833452312HOUSTON
7192837465HAWAI
|
Approach
JOINKEYS Specifies the location in each file of the field (customer number) used to join the files.
REFORMAT Defines the record layout to be produced by the join processing.
SORT Specifies the sort key and order (customer number, ascending).
Control Statements
JOINKEYS FILES=F1, FIELDS=(15,10,A), CUSTOMER NUMBER
JOINKEYS FILES=F2, FIELDS=(1,10,A) CUSTOMER NUMBER
REFORMAT FIELDS=(F1:1,24, F2:1,10) NAME, Customer Number & City
SORT FIELDS=(1,10,CH,A)
O/p data as follows
GREG DAVIS 6456332132NEW YORK
MARK LUKE 5435313132CALIFORNIA
JUDY FOSTER 7343423456FLORIDA
STEVE JONES 2135212331VEGAS
MEGAN MADRUS 5645321323WARWICK
MARY COX 9893133733JERSEY CITY
ARTHUR FREEMAN4233232989CAROLINA
JAMES BOND 6462168111VIRGINIA
|
- Problem (Using Simple Join with UNPAIRED selection)
Using the same example as above, we want to now extract only records in File 1 but not in File 2 for the key (Customer Number)
Control Statements
JOINKEYS FILES=F1, FIELDS=(15,10,A), CUSTOMER NUMBER
JOINKEYS FILES=F2, FIELDS=(1,10,A) CUSTOMER NUMBER
JOIN UNPAIRED,F1,ONLY
SORT FIELDS=(1,10,CH,A)
Approach
JOINKEYS Specifies the location in each file of the field (customer number) used to join the files.
JOIN Requests only those records that appear in File 1 but not in File2.
SORT Specifies the sort key and order (customer number, ascending).
O/p data as follows:
GEORGE STORMS 8933313324
MONICA FELDER 3234139897
|
