Page 1 of 1

how to get get unmatched record to a file using icetool?

PostPosted: Tue Jul 28, 2015 1:22 pm
by troubledguy
hi all,
i am new to the forum. i appolozise if i posted a previously asked question or in wrong forum.


i have 2 input files . according to the requiremnt i need only those records present in both files matching on col <5-9> and having col <11-15> as "xxxxx" and col<19-20> as "ee" in file 1 and col<19-20> as "ee" in file 2.

file IN1:

----+----1----+----2----+----3
111 AAAAA XXXXX   EE         
111 BBBBB YYYYY   TT         
111 CCCCC XXXXX   EE         
111 DDDDD XXXXX   EE         
111 EEEEE  ZZZZZ   RR         


file IN2:

----+----1----+----2----+----3
111 AAAAA 11111   EE         
111 BBBBB 22222   TT         
111 SSSSS 33333   EE         
111 CCCCC 44444   EE         
111 FFFFF 55555   RR 



file OUT1: matched record
----+----1----+----2----+----3
111 AAAAA 11111   EEA
111 CCCCC 44444   EEA


file OUT2: unmatched from file 1
----+----1----+----2----+----3
111 DDDDD XXXXX   EE



below is the icetool jcl i have written to serve the above purpose.

//TOOLIN   DD *                                               
  COPY FROM(IN1) TO(OUT1)   USING(CTL1)                                             
/*                                                           
//CTL1CNTL DD *                                               
   JOINKEYS F1=IN1,FIELDS=(5,5,A),                           
    INCLUDE=(19,2,CH,EQ,C'EA',AND,11,5,CH,EQ,C'xxxxx')   
   JOINKEYS F2=IN2,FIELDS=(5,5,A),                         
    INCLUDE=(19,2,CH,EQ,C'EA')                               
   REFORMAT FIELDS=(F1:1,10,F2:11,10)
   OUTREC BUILD=(1,20,X'C1')                                 
/*                           


now my new requirement is to get the unmached records from file 1 which has col <11-15> as "xxxxx" and col<19-20> as "ee" in a different output file.
is there any way to modify the jcl to get the unmatched records as well?

Re: how to get get unmatched record to a file using icetool?

PostPosted: Tue Jul 28, 2015 2:48 pm
by NicC
In future please use the code tags when presenting code and data. By doing so a fixed font is used and spaces are not trimmed. Yoou can see the code tags button in the editor toolbar when yu use the full editor which is more easily entered by using the Postreply button instead of the QuickReply button.

Re: how to get get unmatched record to a file using icetool?

PostPosted: Tue Jul 28, 2015 4:02 pm
by BillyBoyo
Yes,

First, is there a particular reason to use ICETOOL here? JOINKEYS is part of SORT.

Although you can put the INCLUDE/OMIT on the JOINKEYS, the recommended way is to use JNFnCTNL files.

Unless needed, I use INREC in preference to OUTREC. If you are appending something to a record, it will be clearer to use OVERLAY than BUILD.

//SYSIN DD *
   JOINKEYS F1=IN1,FIELDS=(5,5,A)                         
   JOINKEYS F2=IN2,FIELDS=(5,5,A)             
   REFORMAT FIELDS=(F1:1,10,F2:11,10)
   INREC OVERLAY=(21:X'C1')
//JNF1CNTL DD *
  INCLUDE=(19,2,CH,EQ,C'EA',
          AND,
             11,5,CH,EQ,C'xxxxx')   
//JNF1CNTL DD *
  INCLUDE=(19,2,CH,EQ,C'EA')                               

   


Then you'll need a JOIN statement. A JOIN is not needed if you only want matches, but you'll need JOIN UNPAIRED,F1.

In the REFORMAT statement, include the "match marker", a ?, which in your case, due to the JOIN, will be B (on both files, ie matched) or 1 (on File 1 only, unmatched).

Then you'll need two OUTFILs, one for each output file. One should INCLUDE= the Bs and the other should use SAVE.

Re: how to get get unmatched record to a file using icetool?

PostPosted: Tue Jul 28, 2015 4:17 pm
by troubledguy
thanks BillyBoyo,

will implement your logic and i hope that works out for me.

the reason i used icetool was because i had 2 pair of files to compare and i wanted to do in a single step. that all.