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: 7315

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: 1535
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: 1535
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: 1535
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: 1535
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: 1535
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: 7315

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 Need input in Scheduling Benchwarmer CA Products 5 Tue Jun 06, 2017 1:44 am
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
No new posts Using ICETOOL, how we can seperate th... bshkris SYNCSORT 5 Tue May 09, 2017 8:33 pm
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts Format record to remove Leading zeroes Learncoholic DFSORT/ICETOOL 14 Wed Apr 05, 2017 2:43 pm


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