IBM Mainframe Forum

by **selva1849** » Thu Jul 07, 2011 11:17 pm

I have 3 flat files.
File A
Field1 Field 2
12345 8778878
12999 787887
23456 778675
67334 785787856
35332 7576767

File B
Field1 Field 2
12345 7846744
23456 467467

File C
Field1 Field2
12345 5634564
67334 5456456
35332 5645644

I want to compare these three files. The first field in each file is the key. My requirement is to take Field1 value(12345) from File A and look for this value in File B and File C. If the value is found then corresponding field2 value from File B(7846744) and File C(5634564) shd be taken and wriiten in output file like this

Output

12345 8778878 7846744 5634564

If the lookup value is not present in File B or File C or both then the output file shd have value 0 in those fields like this

Output
12999 787887 0 0
23456 778675 467467 0

Is this possible through DFSORT?

by **skolusu** » Fri Jul 08, 2011 12:17 am

selva1849,

What is the LRECL and RECFM of all the 3 files?
What is the position, length and format of the key to be matched in all files
What is the position, length and format of the field to be merged ?

by **Frank Yaeger** » Fri Jul 08, 2011 1:28 am

selva1849,

You can use a DFSORT/ICETOOL job like the following to do what you asked for. I assumed your input files have RECFM=FB and LRECL=80, your key is in positions 1-5 and your data field is in position 7-15. Adjust as necessary.

Select all

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD *
12345 8778878
12999 787887
23456 778675
67334 785787856
35332 7576767
99999 123456789
//IN2 DD *
12345 7846744
23456 467467
99999 123456789
//IN3 DD *
12345 5634564
67334 5456456
35332 5645644
99999 123456789
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD SYSOUT=*
//TOOLIN DD *
COPY JKFROM TO(T1) USING(CTL1)
COPY JKFROM TO(OUT) USING(CTL2)
//CTL1CNTL DD *
JOINKEYS F1=IN1,FIELDS=(1,5,A)
JOINKEYS F2=IN2,FIELDS=(1,5,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,15,F2:6,10)
OPTION COPY
OUTREC OVERLAY=(80:X)
//CTL2CNTL DD *
JOINKEYS F1=T1,FIELDS=(1,5,A),SORTED
JOINKEYS F2=IN3,FIELDS=(1,5,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,25,F2:6,10)
OPTION COPY
OUTREC IFOUTLEN=80,
IFTHEN=(WHEN=(17,9,CH,EQ,C' '),OVERLAY=(17:C'0'),HIT=NEXT),
IFTHEN=(WHEN=(27,9,CH,EQ,C' '),OVERLAY=(27:C'0'))
/*

OUT would have:

Select all

12345 8778878 7846744 5634564
12999 787887 0 0
23456 778675 467467 0
35332 7576767 0 5645644
67334 785787856 0 5456456
99999 123456789 123456789 123456789

by **selva1849** » Sat Jul 09, 2011 1:41 pm

Frank,

Thank you! I will try this once I reach my office.

Thanks,
Selva1849

by **selva1849** » Mon Jul 11, 2011 8:00 am

Frank,

I used the above code but getting SPANINC=16 error. Can you please help me in solving this?

Thanks,
Selva1849

by **dick scherrer** » Mon Jul 11, 2011 9:25 am

Hello,

You need to post the complete diagnostic information generated by the run including all message ids.

by **Frank Yaeger** » Mon Jul 11, 2011 10:36 pm

I used the above code but getting SPANINC=16 error. Can you please help me in solving this?

SPANINC=16 is a keyword which applies to VBS records, not an error, so I don't know what you mean.

Do your files have RECFM=VBS? I assumed they had RECFM=FB. I guess you need to answer Kolusu's questions about attributes, etc.

Also, show the complete JES log from your run.

IBM Mainframe Forum

Lookup in 3 files

Lookup in 3 files

Re: Lookup in 3 files

Re: Lookup in 3 files

Re: Lookup in 3 files

Re: Lookup in 3 files

Re: Lookup in 3 files

Re: Lookup in 3 files