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



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

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

Postby troubledguy » Tue Jul 28, 2015 1:22 pm

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?
troubledguy
 
Posts: 2
Joined: Mon Jul 27, 2015 8:52 pm
Has thanked: 1 time
Been thanked: 0 time

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

 

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

Postby NicC » Tue Jul 28, 2015 2:48 pm

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.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic

These users thanked the author NicC for the post:
troubledguy (Tue Jul 28, 2015 3:23 pm)
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: how to get get unmatched record to a file using icetool?

Postby BillyBoyo » Tue Jul 28, 2015 4:02 pm

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.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

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

Postby troubledguy » Tue Jul 28, 2015 4:17 pm

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.
troubledguy
 
Posts: 2
Joined: Mon Jul 27, 2015 8:52 pm
Has thanked: 1 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post