Hi..
I need to compare 2 files and write the non matched records to a separate file. Key fields 8 - 10. Input files are already sorted.
My input file 1--> FB having LRECL 80
Mumbai 123....
Chennai234....
Kolkata345....
Punjab 567....
input file 2--> FB having LRECL 80
Mumbai 001....
Chennai234....
Kolkata789....
Punjab 567....
Delhi 567....
Output file --> FB having LRECL 160 should be like below
Mumbai 123....(80 characters) + Mumbai 001....(80 characters) --> 160 record length
Kolkata345....(80 characters) + Kolkata789....80 characters)--> 160 record length
Punjab 567....(80 characters) + spaces(as there are no match records in file 2)--> 160 record length
spaces(as there are no match records in file 2) + Delhi 567....--> 160 record length
Can it be done using Icetool or Sort. Please advice.
Comapre 2 files and joining the non matching records
Previous topic • Next topic • 7 posts
• Page 1 of 1
Comapre 2 files and joining the non matching records
by karthick12s » Thu Oct 18, 2012 7:32 pm
- karthick12s
- Posts: 3
- Joined: Thu Oct 18, 2012 6:58 pm
- Has thanked: 0 time
- Been thanked: 0 time
Re: Comapre 2 files and joining the non matching records
by skolusu » Thu Oct 18, 2012 9:52 pm
karthick12s,
Your output does NOT match the requirements. Both files have Punjab 567 and why do you say that it does NOT have a match? What happened to Chennai234... record ? It does not have a match and what do you do with these records?
What is the matching key for both files? Is it the city name or seqnum? or both city name and seqnum ?
Let us the know the position and length of the key to match. Also please run the following JCL and show us the complete sysout , so that we can determine the level of DFSORT you running.
Your output does NOT match the requirements. Both files have Punjab 567 and why do you say that it does NOT have a match? What happened to Chennai234... record ? It does not have a match and what do you do with these records?
What is the matching key for both files? Is it the city name or seqnum? or both city name and seqnum ?
Let us the know the position and length of the key to match. Also please run the following JCL and show us the complete sysout , so that we can determine the level of DFSORT you running.
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=COPY
//*
//SYSOUT DD SYSOUT=*
//SORTIN DD *
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=COPY
//*
Re: Comapre 2 files and joining the non matching records
by karthick12s » Fri Oct 19, 2012 10:44 am
Hi Kolusu
Sorry I missed out something as erroneous.
My output should n't contain the matched records.(no need to include Chennai). And both city name and seqnum is the key here (position 1 - 10).
My input file 1--> FB having LRECL 80
input file 2--> FB having LRECL 80
Modified Output file --> FB having LRECL 160 should be like below
And also here is my sysout details:
Let me know if you want any further details.
(coded)
Sorry I missed out something as erroneous.
My output should n't contain the matched records.(no need to include Chennai). And both city name and seqnum is the key here (position 1 - 10).
My input file 1--> FB having LRECL 80
Mumbai 123....
Kolkata345....
Chennai234....
Punjab 567....
Kolkata345....
Chennai234....
Punjab 567....
input file 2--> FB having LRECL 80
Mumbai 001....
Kolkata789....
Chennai234....
Delhi 567....
Kolkata789....
Chennai234....
Delhi 567....
Modified Output file --> FB having LRECL 160 should be like below
Mumbai 123....(80 characters) + Mumbai 001....(80 characters) --> 160 record length
Kolkata345....(80 characters) + Kolkata789....80 characters)--> 160 record length
Punjab 567....(80 characters) + spaces(as there are no match records in file 2)--> 160 record length
spaces(as there are no match records in file 2) + Delhi 567....--> 160 record length.
Kolkata345....(80 characters) + Kolkata789....80 characters)--> 160 record length
Punjab 567....(80 characters) + spaces(as there are no match records in file 2)--> 160 record length
spaces(as there are no match records in file 2) + Delhi 567....--> 160 record length.
And also here is my sysout details:
1ICE201I H RECORD TYPE IS F - DATA STARTS IN POSITION 1
ICE751I 0 C5-K76982 C6-K90026 C7-K58148 C8-K67572 E9-K60824 C9-BASE E5-K76585
ICE143I 0 BLOCKSET COPY TECHNIQUE SELECTED
ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES A
ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R12 - 00:58 ON FRI O
0 SORT FIELDS=COPY
ICE201I H RECORD TYPE IS F - DATA STARTS IN POSITION 1
ICE751I 0 C5-K76982 C6-K90026 C7-K58148 C8-K67572 E9-K60824 C9-BASE E5-K76585
ICE193I 0 ICEAM1 INVOCATION ENVIRONMENT IN EFFECT - ICEAM1 ENVIRONMENT SELECTED
ICE252I 1 PARMLIB OPTIONS WERE MERGED WITH INSTALLATION MODULE DEFAULTS
ICE093I 0 MAIN STORAGE = (MAX,4194304,4181086)
ICE156I 0 MAIN STORAGE ABOVE 16MB = (4060638,4060638)
ICE127I 0 OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC16,VLSCMP=N,SZERO=Y
ICE128I 0 OPTIONS: SIZE=4194304,MAXLIM=1048576,MINLIM=450560,EQUALS=N,LIST=Y,ER
ICE129I 0 OPTIONS: VIO=N,RESDNT=NONE,SMF=SHORT,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT
ICE130I 0 OPTIONS: RESALL=4096,RESINV=0,SVC=109 ,CHECK=N,WRKREL=N,OUTREL=Y,CKPT
ICE131I 0 OPTIONS: TMAXLIM=4194304,ARESALL=0,ARESINV=0,OVERRGN=65536,CINV=N,CFW
ICE132I 0 OPTIONS: VLSHRT=N,ZDPRINT=Y,IEXIT=Y,TEXIT=N,LISTX=N,EFS=NONE ,EXIT
ICE133I 0 OPTIONS: HIPRMAX=OPTIMAL,DSPSIZE=0 ,ODMAXBF=0,SOLRF=N,VLLONG=N,VSAM
ICE235I 0 OPTIONS: NULLOUT=RC0
ICE236I 0 OPTIONS: DYNAPCT=10 ,MOWRK=Y
ICE084I 0 BSAM ACCESS METHOD USED FOR SORTOUT
ICE084I 0 BSAM ACCESS METHOD USED FOR SORTIN
ICE751I 1 EF-BASE F0-K66717 E8-K70685
ICE090I 0 OUTPUT LRECL = 80, BLKSIZE = 80, TYPE = F
ICE055I 0 INSERT 0, DELETE 0
ICE054I 0 RECORDS - IN: 0, OUT: 0
ICE173I 0 NO RECORDS FOR THE SORTOUT DATA SET - RC=0
ICE052I 0 END OF DFSORT
ICE751I 0 C5-K76982 C6-K90026 C7-K58148 C8-K67572 E9-K60824 C9-BASE E5-K76585
ICE143I 0 BLOCKSET COPY TECHNIQUE SELECTED
ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES A
ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R12 - 00:58 ON FRI O
0 SORT FIELDS=COPY
ICE201I H RECORD TYPE IS F - DATA STARTS IN POSITION 1
ICE751I 0 C5-K76982 C6-K90026 C7-K58148 C8-K67572 E9-K60824 C9-BASE E5-K76585
ICE193I 0 ICEAM1 INVOCATION ENVIRONMENT IN EFFECT - ICEAM1 ENVIRONMENT SELECTED
ICE252I 1 PARMLIB OPTIONS WERE MERGED WITH INSTALLATION MODULE DEFAULTS
ICE093I 0 MAIN STORAGE = (MAX,4194304,4181086)
ICE156I 0 MAIN STORAGE ABOVE 16MB = (4060638,4060638)
ICE127I 0 OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC16,VLSCMP=N,SZERO=Y
ICE128I 0 OPTIONS: SIZE=4194304,MAXLIM=1048576,MINLIM=450560,EQUALS=N,LIST=Y,ER
ICE129I 0 OPTIONS: VIO=N,RESDNT=NONE,SMF=SHORT,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT
ICE130I 0 OPTIONS: RESALL=4096,RESINV=0,SVC=109 ,CHECK=N,WRKREL=N,OUTREL=Y,CKPT
ICE131I 0 OPTIONS: TMAXLIM=4194304,ARESALL=0,ARESINV=0,OVERRGN=65536,CINV=N,CFW
ICE132I 0 OPTIONS: VLSHRT=N,ZDPRINT=Y,IEXIT=Y,TEXIT=N,LISTX=N,EFS=NONE ,EXIT
ICE133I 0 OPTIONS: HIPRMAX=OPTIMAL,DSPSIZE=0 ,ODMAXBF=0,SOLRF=N,VLLONG=N,VSAM
ICE235I 0 OPTIONS: NULLOUT=RC0
ICE236I 0 OPTIONS: DYNAPCT=10 ,MOWRK=Y
ICE084I 0 BSAM ACCESS METHOD USED FOR SORTOUT
ICE084I 0 BSAM ACCESS METHOD USED FOR SORTIN
ICE751I 1 EF-BASE F0-K66717 E8-K70685
ICE090I 0 OUTPUT LRECL = 80, BLKSIZE = 80, TYPE = F
ICE055I 0 INSERT 0, DELETE 0
ICE054I 0 RECORDS - IN: 0, OUT: 0
ICE173I 0 NO RECORDS FOR THE SORTOUT DATA SET - RC=0
ICE052I 0 END OF DFSORT
Let me know if you want any further details.
(coded)
- karthick12s
- Posts: 3
- Joined: Thu Oct 18, 2012 6:58 pm
- Has thanked: 0 time
- Been thanked: 0 time
Re: Comapre 2 files and joining the non matching records
by NicC » Fri Oct 19, 2012 11:48 am
It helps if you 'code' your data/code/screen shots. Until you know how to hand-code use the 'PostReply' button to make a reply, or, from the Quick Reply use the Full Editor button. You should be able to figure it out from there.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
Regards
Nic
- NicC
- Global moderator
- Posts: 3025
- Joined: Sun Jul 04, 2010 12:13 am
- Location: Pushing up the daisies (almost)
- Has thanked: 4 times
- Been thanked: 136 times
Re: Comapre 2 files and joining the non matching records
by skolusu » Fri Oct 19, 2012 9:55 pm
karthick12s,
Use the following DFSORT JCL which will give you the desired results
Use the following DFSORT JCL which will give you the desired results
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//INA DD *
----+----1----+----2----+----3----+----4----+----5----+----6-
MUMBAI 123
KOLKATA345
CHENNAI234
PUNJAB 567
//INB DD *
MUMBAI 001
KOLKATA789
CHENNAI234
DELHI 567
//SORTOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS F1=INA,FIELDS=(1,7,A)
JOINKEYS F2=INB,FIELDS=(1,7,A)
JOIN UNPAIRED
OMIT COND=(1,10,CH,EQ,81,10,CH)
REFORMAT FIELDS=(F1:1,80,F2:1,80,F1:81,8,F2:81,8,?)
INREC IFTHEN=(WHEN=(177,1,CH,EQ,C'1'),OVERLAY=(169:161,8))
SORT FIELDS=(169,9,CH,A)
OUTREC BUILD=(1,160)
//*
//JNF1CNTL DD *
INREC OVERLAY=(81:SEQNUM,8,ZD)
//*
//JNF2CNTL DD *
INREC OVERLAY=(81:SEQNUM,8,ZD)
//*
//SYSOUT DD SYSOUT=*
//INA DD *
----+----1----+----2----+----3----+----4----+----5----+----6-
MUMBAI 123
KOLKATA345
CHENNAI234
PUNJAB 567
//INB DD *
MUMBAI 001
KOLKATA789
CHENNAI234
DELHI 567
//SORTOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS F1=INA,FIELDS=(1,7,A)
JOINKEYS F2=INB,FIELDS=(1,7,A)
JOIN UNPAIRED
OMIT COND=(1,10,CH,EQ,81,10,CH)
REFORMAT FIELDS=(F1:1,80,F2:1,80,F1:81,8,F2:81,8,?)
INREC IFTHEN=(WHEN=(177,1,CH,EQ,C'1'),OVERLAY=(169:161,8))
SORT FIELDS=(169,9,CH,A)
OUTREC BUILD=(1,160)
//*
//JNF1CNTL DD *
INREC OVERLAY=(81:SEQNUM,8,ZD)
//*
//JNF2CNTL DD *
INREC OVERLAY=(81:SEQNUM,8,ZD)
//*
Re: Comapre 2 files and joining the non matching records
by porwalrox » Mon Oct 29, 2012 4:26 pm
//STEP EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=U299060.JN2FL2.PS,DISP=SHR
//IN2 DD DSN=U299060.JN2FL1.PS,DISP=SHR
//SORTOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS F1=IN1,FIELDS=(1,7,A)
JOINKEYS F2=IN2,FIELDS=(1,7,A)
REFORMAT FIELDS=(F1:1,80,F2:1,80)
JOIN UNPAIRED,F1,F2
OPTION COPY
/*
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=U299060.JN2FL2.PS,DISP=SHR
//IN2 DD DSN=U299060.JN2FL1.PS,DISP=SHR
//SORTOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS F1=IN1,FIELDS=(1,7,A)
JOINKEYS F2=IN2,FIELDS=(1,7,A)
REFORMAT FIELDS=(F1:1,80,F2:1,80)
JOIN UNPAIRED,F1,F2
OPTION COPY
/*
Create JN2FL1.PS & JN2FL2.PS as a variable block for inputs or write your data in "IN1 DD * & IN2 DD *". It will give you the desired result.
Re: Comapre 2 files and joining the non matching records
by karthick12s » Mon Oct 29, 2012 4:30 pm
Thanks kolusu and porwalrox. I got the desired result using your suggestions.
- karthick12s
- Posts: 3
- Joined: Thu Oct 18, 2012 6:58 pm
- Has thanked: 0 time
- Been thanked: 0 time
Previous topic • Next topic • 7 posts
• Page 1 of 1
Return to DFSORT/ICETOOL/ICEGENER
-
- Related topics
- Replies
- Views
- Last post
-
- ICETOOL: Matching Records for VB Files
by anil4321 » Thu Nov 20, 2008 3:39 am - 1 Replies
- 3248 Views
- Last post by Frank Yaeger
Thu Nov 20, 2008 4:12 am
- ICETOOL: Matching Records for VB Files
-
- Variable length files matching records
by whowillwait4u » Thu Jun 28, 2012 3:55 pm - 5 Replies
- 7731 Views
- Last post by skolusu
Tue Jul 03, 2012 11:29 pm
- Variable length files matching records
-
- Compare files and write matching records
by deesunke » Wed Mar 13, 2013 4:54 pm - 2 Replies
- 2640 Views
- Last post by deesunke
Wed Mar 13, 2013 8:54 pm
- Compare files and write matching records
-
- Compare files and create file with non-matching records
by Aaron Chessell » Tue Jul 22, 2008 5:23 am - 4 Replies
- 6150 Views
- Last post by Frank Yaeger
Tue Mar 30, 2010 10:33 pm
- Compare files and create file with non-matching records