Comapre 2 files and joining the non matching records



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Comapre 2 files and joining the non matching records

Postby karthick12s » Thu Oct 18, 2012 7:32 pm

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.
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

 

Re: Comapre 2 files and joining the non matching records

Postby 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.

//STEP0100 EXEC PGM=SORT       
//SYSOUT   DD SYSOUT=*         
//SORTIN   DD *               
//SORTOUT  DD SYSOUT=*         
//SYSIN    DD *               
  SORT FIELDS=COPY             
//*
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Comapre 2 files and joining the non matching records

Postby 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

Mumbai 123....
Kolkata345....
Chennai234....
Punjab 567....


input file 2--> FB having LRECL 80

Mumbai 001....
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.

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

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

Postby 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
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: Comapre 2 files and joining the non matching records

Postby skolusu » Fri Oct 19, 2012 9:55 pm

karthick12s,

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)                             
//*
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Comapre 2 files and joining the non matching records

Postby 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                                         
/*


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.
porwalrox
 
Posts: 38
Joined: Fri Oct 05, 2012 4:05 pm
Has thanked: 2 times
Been thanked: 0 time

Re: Comapre 2 files and joining the non matching records

Postby 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


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post