Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Remove all Duplicate Records from the input.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> SYNCSORT
View previous topic :: :: View next topic  
Author Message
vamsimul

New User


Joined: 07 Oct 2005
Posts: 5

PostPosted: Thu Feb 13, 2014 8:07 pm    Post subject: Remove all Duplicate Records from the input.
Reply with quote

Hi,

I've got a requirement where I've 2 files each one having records on some key. I'd need only unique records.

For eg:
File 1 has following data.
01 INDIA
02 HOLLAND
03 MEXICO
04 NORWAY

File 2 has following data.
03 MEXICO
05 Brazil

Afer I sort on the Country Name, I should get the output as below
01 INDIA
02 HOLLAND
04 NORWAY
05 Brazil

and I should write duplicate records in one more output as
02 HOLLAND
02 HOLLAND

Can any one help me in this.
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Thu Feb 13, 2014 8:24 pm    Post subject: Reply to: Remove all Duplicate Records from the input.
Reply with quote

Should Holland be Mexico? Do you mean you sort of the code, not the number?

Is the data already in order (either, or both files)?
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Feb 13, 2014 8:25 pm    Post subject:
Reply with quote

Quote:
and I should write duplicate records in one more output as
02 HOLLAND
02 HOLLAND


I hope this should be ??

03 MEXICO
03 MEXICO
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Feb 13, 2014 8:27 pm    Post subject:
Reply with quote

and Is FILE1 and FILE2 similar in its layout ?
Back to top
View user's profile Send private message
vamsimul

New User


Joined: 07 Oct 2005
Posts: 5

PostPosted: Thu Feb 13, 2014 8:28 pm    Post subject: Reply to: Remove all Duplicate Records from the input.
Reply with quote

yes HOLLAND has to be MEXICO.. it was a TYPO from my side. Thanks for the response.
Back to top
View user's profile Send private message
vamsimul

New User


Joined: 07 Oct 2005
Posts: 5

PostPosted: Thu Feb 13, 2014 8:31 pm    Post subject:
Reply with quote

yes both the files are same in the format
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Feb 13, 2014 8:37 pm    Post subject:
Reply with quote

Try something like this

Code:
//STEP0100 EXEC PGM=ICETOOL
//SYSOUT   DD SYSOUT=*
//INPUT    DD *
01 INDIA
02 HOLLAND
03 MEXICO
04 NORWAY
//         DD *
03 MEXICO
05 BRAZIL
//UNIQ     DD SYSOUT=*
//DUPS     DD SYSOUT=*
//TOOLMSG  DD SYSOUT=*
//DFSMSG   DD SYSOUT=*
//TOOLIN   DD *
  SELECT FROM(INPUT) TO(UNIQ) ON(1,2,CH) NODUPS DISCARD(DUPS)
Back to top
View user's profile Send private message
vamsimul

New User


Joined: 07 Oct 2005
Posts: 5

PostPosted: Thu Feb 13, 2014 8:44 pm    Post subject:
Reply with quote

Is there any way we can do it by using EXEC PGM=SORT. 'COS in our shop we can't use ICETOOL.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Feb 13, 2014 9:17 pm    Post subject:
Reply with quote

Try this then

Code:
//STEP0100 EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//INA      DD *
01 INDIA
02 HOLLAND
03 MEXICO
04 NORWAY
//INB      DD *
03 MEXICO
05 BRAZIL
//OUT1     DD SYSOUT=*
//OUT2     DD SYSOUT=*
//SYSIN    DD *
  OPTION COPY
  JOINKEYS F1=INA,FIELDS=(1,2,A)
  JOINKEYS F2=INB,FIELDS=(1,2,A)
  JOIN UNPAIRED F1,F2,ONLY
  REFORMAT FIELDS=(F1:1,10,F2:1,10)
  OUTREC IFTHEN=(WHEN=INIT,BUILD=(1:1,21,JFY=(SHIFT=LEFT)))
  OUTFIL FNAMES=OUT1,INCLUDE=(11,10,CH,NE,C' '),BUILD=(1,10,/11,10)
  OUTFIL FNAMES=OUT2,SAVE
//*
Back to top
View user's profile Send private message
sureshpathi10

Active User


Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur

PostPosted: Fri Feb 14, 2014 7:24 am    Post subject:
Reply with quote

I guess, you can use something like this.

Code:
SORT FIELDS=(3,7,CH,A)
SUM FIELDS=NONE,XSUM 


Please refer http://ibmmainframes.com/viewtopic.php?t=966&highlight=xsum for more info.

XSUM option is unique for SYNCSORT and its not available in DFSORT.
Back to top
View user's profile Send private message
vamsimul

New User


Joined: 07 Oct 2005
Posts: 5

PostPosted: Fri Feb 14, 2014 12:40 pm    Post subject:
Reply with quote

Hi Pandora,

I tried executing your job, I'm getting the following issue: Since my input is matched according to 12 bytes I've used length as 12 for keys.

SYNCSORT LICENSED FOR CPU SERIAL NUMBER 2D2E6, MODEL 2817 724
SYSIN :
OPTION COPY
JOINKEYS F1=IN1,FILEDS=(1,12,A)
*
JOINKEYS F2=IN2,FILEDS=(1,12,A)
*
JOIN UNPAIRED F1,F2 ONLY
REFORMAT FIELDS=(F1:1,12,F2:1,12)
OUTREC IFTHEN=(WHEN=INIT,BUILD=(1:1,24,JFY=(SHIFT=LEFT)))
OUTFIL FNAMES=OUT1,INCLUDE=(13,12,CH,EQ,C' '), BUILD=(1,12,/13,12)
OUTFIL FNAMES=OUT2,SAVE
*
WER268A JOINKEYS STATEMENT: SYNTAX ERROR
WER268A JOINKEYS STATEMENT: SYNTAX ERROR
WER268A OUTFIL STATEMENT : SYNTAX ERROR
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000
WER449I SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Fri Feb 14, 2014 12:51 pm    Post subject:
Reply with quote

You have SYNCSORT product

Also the solution from suresh wont help you as well

As you wanted the Unique keys in one file and duplicates in another
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Fri Feb 14, 2014 2:04 pm    Post subject: Reply to: Remove all Duplicate Records from the input.
Reply with quote

XSUM, on the SUM or using DUPKEYS (if you are up-to-date enough) would be correct for SyncSort.
Back to top
View user's profile Send private message
sureshpathi10

Active User


Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur

PostPosted: Fri Feb 14, 2014 2:21 pm    Post subject:
Reply with quote

I thought for a minute that I was right... But again...

Vamsi wants all the unique records in 1 file and all duplicates to another file.

if SUM=NONE,XSUM is being used, The First duplicate record will send to the Unique file.

I guess, Bill will have some other solution icon_smile.gif
Back to top
View user's profile Send private message
JAYACHANDRAN THAMPY

New User


Joined: 06 Jun 2006
Posts: 8

PostPosted: Thu Feb 20, 2014 3:38 am    Post subject:
Reply with quote

If you have Syncsort V1.4, then the below syncsort jcl provides you the result using DUPKEYS ALLDUPS options.

Quote:
//STEP010 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
01 INDIA
02 HOLLAND
03 MEXICO
04 NORWAY
// DD *
03 MEXICO
05 BRAZIL
//SYSOUT DD SYSOUT=*
//SORTXDUP DD SYSOUT=*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=(1,9,CH,A)
DUPKEYS ALLDUPS,XDUP
//*
SORTOUT output have the duplicate records and the non duplicate records are written to SORTXDUP.

SORTOUT Output
03 MEXICO
03 MEXICO

SORTXDUP output
01 INDIA
02 HOLLAND
04 NORWAY
05 BRAZIL
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> SYNCSORT All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us