MVSFORUMS.com

ranga_subham · Intermediate Joined: 31 Jan 2006 Posts: 255 Topics: 72

Hi,

Is it possible to do multiple file look up using SORT? File01 has some records and these should be searched in 4 other files and if found should be written to four different output files - using SORT.

Please suggest.

Thanks.
_________________
Ranga
*****
None of us is as smart as all of us - Ken Blanchard

Frank Yaeger · Posted: Fri Dec 05, 2008 1:47 pm Post subject:

Please show an example of the records in each input file and what you want for output. Give the RECFM and LRECL of the input files. Give the starting position, length and format of all relevant fields. If there can be duplicates within any of the input files, show that in the example.
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

amargulies · Beginner Joined: 10 Jan 2007 Posts: 123 Topics: 0

Also, is there a field in each record to identify which file the record is from?
_________________
Alissa Margulies
SyncSort Mainframe Product Services
201-930-8260
zos_tech@syncsort.com

ranga_subham · Intermediate Joined: 31 Jan 2006 Posts: 255 Topics: 72

All the files are LRECL=80 and RECFM=FB. Duplicates are certainly possible. The look up should happen based on 1st column with a length of 3 and 20th column with a length of 6. All the matching records should be written to respective output files else an empty file.

This is how data appears......

File01:

kolusu · Posted: Tue Dec 09, 2008 11:41 am Post subject:

ranga_subham,

Does file 1 contains duplicates ? Do you expect FILE 1 to be huge? It is just a look up file how many records does it have now? what is the expected output ?

Kolusu

ranga_subham · Intermediate Joined: 31 Jan 2006 Posts: 255 Topics: 72

Kolusu, the File01 possibly will have 1000 records which are put by end-user. It cannot have duplicates. Each individual output file should have all those matching records (including duplicates too) from File01.

Thanks.
_________________
Ranga
*****
None of us is as smart as all of us - Ken Blanchard

ranga_subham · Intermediate Joined: 31 Jan 2006 Posts: 255 Topics: 72

The expected output should be this.....

Output-File02:

samlwho · Beginner Joined: 08 Feb 2007 Posts: 21 Topics: 2

If I understand your dilemma correctly, the SYNCSORT JOIN below will produce what you desire. In the example below, the F1 is File01 and F2 is DataFile02.

Syncsort Parameters :
JOINKEYS = Matching position 1,3 and 20,6 is the same as matching on 1,25. As such, the key for both files are positions 1,25.

JOIN UNPAIRED = Results in Syncsort writing an output file that contains records with matching keys as well as unmatched records from both files.

REFORMAT FIELDS = Defines the output file. Here, the total record is written from both files. By doing this the Include verb can be used to create three different files.
1. Records that match
2. Records that are on F1 only
3. Records that are on F2 only

Note: The step below would need to be replicated four times for each input file and each step will result in listed output files.

I hope this helps

ranga_subham · Intermediate Joined: 31 Jan 2006 Posts: 255 Topics: 72

I will try it and let you know.

Thanks.
_________________
Ranga
*****
None of us is as smart as all of us - Ken Blanchard

ranga_subham · Intermediate Joined: 31 Jan 2006 Posts: 255 Topics: 72

SAMLWHO, what will happen to File03, File04 & File05 ? Where are they referred?

Thanks.
_________________
Ranga
*****
None of us is as smart as all of us - Ken Blanchard