Want to join two flat files into a new file

trevor_rebelo · New User Joined: 18 Aug 2005 Posts: 17

Hi All,

I have a req. I have 2 file and i want to join them in new file based on keys. (Like the left Join in SQL)

E.g
File A (records)
1234ABCD
1234EFGH
2222KKKK
1111LMNO

File B (records)
1234TREVOR
1111REBELO

Output File
1234ABCDTREVOR
1234EFGHTREVOR
2222KKKK
1111LMNOREBELO

is there a way to do it other then writing a program.

The Splice option is removing the dulpicates.

Thanks in advance

sril.krishy · Posted: Thu Jun 29, 2006 1:45 pm

Hi,
There were a lot of examples for this in the forum.Please check for them.

Here is the logic you need to use for this.

1)Generate the sequence number for both files.
2)Match the records on the sequence number.

Thank you
Krishy

trevor_rebelo · New User Joined: 18 Aug 2005 Posts: 17

Hi Krishy

file A has multiple records for each record in file B
and i need all the records in file A to Output File. (Kind a LEFT OUTER JOIN in SQL)

How can the sequence No match?

sril.krishy · Posted: Thu Jun 29, 2006 5:21 pm

Hi,
If you are having the SYNCSORT FOR Z/OS 1.2.0.0RI,the below JCL will give you the desired results.

Frank Yaeger · Posted: Thu Jun 29, 2006 9:12 pm

Trevor,

Here's a DFSORT/ICETOOL job that will do what you asked for. Notice that KEEPNODUPS with SPLICE is used to keep the non-duplicates. I assumed your input files have RECFM=FB and LRECL=80, but the job can be changed for other attributes.

trevor_rebelo · New User Joined: 18 Aug 2005 Posts: 17

Hi Frank,

I tried ur JCL
It is giving Syntax error in the following code
INREC BUILD=(1,4,9:5,6,88:X)
INREC OVERLAY=(81:SEQNUM,8,ZD)

so i used the following instead
OUTREC FIELDS=(1,4,9:5,6)
OUTREC FIELDS=(1,8)

The SPLICE option worked fine

But in the SORT option it is giving following error
CTL3CNTL :
SORT FIELDS=(1,4,CH,A)
OUTREC FIELDS=(1,80)
* OUTREC BUILD=(1,80)
PARMLIST :
OPTION RESINV=0,ARESINV=0,MSGDDN=DFSMSG,SORTIN=T2,SORTOUT=OUT,SORTDD=CTL3,DYNAL
OC
WER428I CALLER-PROVIDED IDENTIFIER IS "0004"
WER164B 4,860K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER164B 0 BYTES RESERVE REQUESTED, 1,020K BYTES USED
WER146B 4K BYTES OF EMERGENCY SPACE ALLOCATED
WER189A T2 DCB RECFM REQUIRED
WER108I T2 : RECFM= ; LRECL= ; BLKSIZE=
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000

But if T2 is sorted in a seperate Step with the following parameter it is working fine
SORT FIELDS=(1,4,CH,A)
OUTREC FIELDS=(1,14,66X)

Is the SEQNUM use only to keep the records in the original order (as in FILEA)?

Thanks for ur solution.

trevor_rebelo · New User Joined: 18 Aug 2005 Posts: 17

Hi Krishy

I am not familiar with SYNCTOOL Can u plz suggest any handout?

Thanks for ur efforts

Frank Yaeger · Posted: Fri Jun 30, 2006 8:58 pm

Trevor,

The WER messages indicate you're using Syncsort, not DFSORT. The job I showed works fine with DFSORT.

I'm a DFSORT developer. DFSORT and Syncsort are competitive products. I'm happy to answer questions on DFSORT and DFSORT's ICETOOL, but I don't answer questions on Syncsort.

sril.krishy · Posted: Sun Jul 02, 2006 11:35 pm

Hi,
Since your shop is having SYNCSORT,Please run the code shown above by mw with JOIN kewords and let me know if you face any problems.

Thank you
Krishy