SUM FIELDS = NONE in JOINKEY Operation



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

SUM FIELDS = NONE in JOINKEY Operation

Postby fornanthakumar » Fri Jun 22, 2012 4:16 pm

Hi,

Good Day.

My objective is to compare two files and get the unpaired F2 data. (if F2 does have duplicates or the content are in both files duplicates then it should be removed) . I am using DFSORT and using JOINKEY operations. Please find my code below.
JOINKEYS FILES=F1,FIELDS=(23,18,A,42,9,A,63,17,A)
JOINKEYS FILES=F2,FIELDS=(23,18,A,42,9,A,63,17,A)
JOIN UNPAIRED,F2,ONLY
REFORMAT FIELDS(F2:1,186)
SUM FIELDS=NONE
OPTION COPY

My second input file does contain duplicates also contains the records which is there in first input file. when i am using the above code, duplicates are not eliminated from the file.

Could some one please help..

can i use some code to elimate in JNF2CNTL ...
Regards,
Nanthu.Y.
fornanthakumar
 
Posts: 69
Joined: Fri Oct 22, 2010 1:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: SUM FIELDS = NONE in JOINKEY Operation

Postby skolusu » Fri Jun 22, 2012 11:31 pm

fornanthakumar,

Your Main task is using a COPY operation so the SUM FIELDS=NONE statement is ignored. If you want to remove duplicates move the SUM FIELDS=NONE to JNF2CNTL DD like shown below
//SYSIN    DD *                                       
  OPTION COPY                                         
  JOINKEYS FILES=F1,FIELDS=(23,18,A,42,9,A,63,17,A)   
  JOINKEYS FILES=F2,FIELDS=(23,18,A,42,9,A,63,17,A)   
  JOIN UNPAIRED,F2,ONLY                               
//*                 
//JNF2CNTL DD *     
  SUM FIELDS=NONE   
//*
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: SUM FIELDS = NONE in JOINKEY Operation

Postby fornanthakumar » Thu Jun 28, 2012 2:03 pm

Hi,

Thanks for the above reply, i have got some kick from your points ,

I have some questions as well,

1. In the ABOVE case , where/how the key fields for file2 will picked from ? will it from the SYSIN ?
2. I am just confused on having sum fields without SORT fields. Should i mentined SORT FIELDS=(23,18,CH,A,42,9,CH,A,63,17,CH,A) SUM FIELDS- NONE in JNF2CNTL.
3. Instead of option copy in SYSIN statement, can we have the SORT fields there ?
Regards,
Nanthu.Y.
fornanthakumar
 
Posts: 69
Joined: Fri Oct 22, 2010 1:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: SUM FIELDS = NONE in JOINKEY Operation

Postby BillyBoyo » Thu Jun 28, 2012 8:07 pm

fornanthakumar wrote:Hi,

Thanks for the above reply, i have got some kick from your points ,

I have some questions as well,

1. In the ABOVE case , where/how the key fields for file2 will picked from ? will it from the SYSIN ?
2. I am just confused on having sum fields without SORT fields. Should i mentined SORT FIELDS=(23,18,CH,A,42,9,CH,A,63,17,CH,A) SUM FIELDS- NONE in JNF2CNTL.
3. Instead of option copy in SYSIN statement, can we have the SORT fields there ?


When you have doubts like this, the manual is a good source of information.

JOINKEYS, unless told not to, does a SORT on the keys specified for the join (else it won't work, except by fluke, or if files already in order, in which case you tell that to the JOINKEYS).
So, by default, you have your full SORT key for the joinkeys and that is what it will do the SUM on.

You could sort in the main task of the JOINKEYS but since the reformat file will be in that order anyway it will be something of a waste. Plus you will have had more data going into the JOINKEYS than necessary. If getting rid of stuff, best to do it as early as possibible, for clarity with a performance bonus.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: SUM FIELDS = NONE in JOINKEY Operation

Postby skolusu » Thu Jun 28, 2012 10:23 pm

fornanthakumar,

Check this link which will answer all your queries.

http://publibz.boulder.ibm.com/cgi-bin/ ... E1CA60/4.0
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


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post