IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Syncsort - Remove duplicates based on a field


IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
venkatatcts

New User


Joined: 16 Mar 2009
Posts: 55
Location: India

PostPosted: Mon Jul 02, 2012 2:16 pm
Reply with quote

Hi,

I have a file which as multiple records for the same key but with a field which tells whether the transaction is deleted or added or changed.

For example,

XX1-A-DW D
XX1-A-DW C
XX1-A-DW A

XX1-B-DW C
XX1-B-DW A
XX1-B-DW A

Here D - deleted, A - Add and C - Change

If any one records has D then the key should be removed. But if there record does not have D then all the record should be writted to file. The output should be


XX1-B-DW C
XX1-B-DW A
XX1-B-DW A

Thanks,
Venkat B
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Mon Jul 02, 2012 2:23 pm
Reply with quote

Very simple - what have you tried? I will give you a clue - you want to omit some records.
Back to top
View user's profile Send private message
venkatatcts

New User


Joined: 16 Mar 2009
Posts: 55
Location: India

PostPosted: Mon Jul 02, 2012 2:32 pm
Reply with quote

The file is sorted based on key but the field which tells whether it is deleted or changed or added is sorted in desc order.

So if there is any record which is deleted for a key it will be first one. So that key should not be there. For any record all the three are possible Delete, Add, Change.

Omit will not work for the first sceanrio.

Thanks,
Venkat
Back to top
View user's profile Send private message
xknight

Active User


Joined: 22 Jan 2008
Posts: 117
Location: Liberty city

PostPosted: Mon Jul 02, 2012 4:39 pm
Reply with quote

Hello,

Is the set occurance of records would be 3 always?

Quote:
XX1-A-DW D
XX1-A-DW C
XX1-A-DW A

XX1-B-DW C
XX1-B-DW A
XX1-B-DW A
Back to top
View user's profile Send private message
venkatatcts

New User


Joined: 16 Mar 2009
Posts: 55
Location: India

PostPosted: Mon Jul 02, 2012 5:05 pm
Reply with quote

No it can be 1 or 2 or maxmium of 999 occurence for a single key
Back to top
View user's profile Send private message
xknight

Active User


Joined: 22 Jan 2008
Posts: 117
Location: Liberty city

PostPosted: Mon Jul 02, 2012 5:49 pm
Reply with quote

Hello,

Try the below snippet,

Code:
//STEP1 EXEC PGM=ICETOOL                                 
//DFSMSG DD SYSOUT=*                                     
//TOOLMSG DD SYSOUT=*                                     
//IN1 DD *                                               
XX1-A-DW D                                               
XX1-A-DW C                                               
XX1-A-DW A                                               
XX1-B-DW C                                               
XX1-B-DW A                                               
XX1-B-DW A                                               
//OUT1 DD DSN=&TMP1,                                     
//        DISP=(NEW,CATLG,DELETE),                       
//             RECFM=FB,LRECL=90,BLKSIZE=0,               
//        SPACE=(CYL,(10,10),RLSE),UNIT=SYSDA             
//OUT2 DD SYSOUT=*                                       
//TOOLIN DD *                                             
  COPY FROM(IN1) TO(OUT1) USING(CTL1)                     
/*                                                                 
//CTL1CNTL DD *                                                   
  INREC IFTHEN=(WHEN=(10,1,CH,EQ,C'D'),OVERLAY=(81:1,8))           
  OUTREC FIELDS=(1:81,8)                                           
/*                                                                 
//STEP02 EXEC PGM=SORT                                             
//SORTJNF1 DD DSN=&TMP1,DISP=SHR                                   
//SORTJNF2 DD *                                                   
XX1-A-DW D                                                         
XX1-A-DW C                                                         
XX1-A-DW A                                                         
XX1-B-DW C                                                         
XX1-B-DW A                                                         
XX1-B-DW A                                                         
//SYSOUT   DD SYSOUT=*                                             
//SORTOUT  DD SYSOUT=*                                             
//SYSIN   DD *                                                     
  JOINKEYS FILE=F1,FIELDS=(1,8,A)                                 
  JOINKEYS FILE=F2,FIELDS=(1,8,A)                                 
  JOIN UNPAIRED,F2,ONLY                                           
  REFORMAT FIELDS=(F2:1,80)                                       
  SORT FIELDS=COPY


More one than one step has been used since the occurance is unknown. You can reduce it to single step if you have clear information.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Mon Jul 02, 2012 7:19 pm
Reply with quote

Hello,

Which release of which sort product is being used?
Back to top
View user's profile Send private message
Naish

New User


Joined: 07 Dec 2006
Posts: 82
Location: UK

PostPosted: Mon Jul 02, 2012 8:41 pm
Reply with quote

First thing first. You have not answered Nic's question. You have also not searched the forum and the like. Are you waiting for a custom made solution?

Hint #2 - This can be achieved in one step. Use WHEN=GROUP on the key if you are using DFSORT.
Back to top
View user's profile Send private message
venkatatcts

New User


Joined: 16 Mar 2009
Posts: 55
Location: India

PostPosted: Mon Jul 02, 2012 10:48 pm
Reply with quote

Hi,

I am using syncsort 1.3

I cannot achieve this by omit condition. Based on the column which tell whether it is delete or add or change I have to decide whether the key need to be deleted.

Thanks,
Venkat b
Back to top
View user's profile Send private message
xknight

Active User


Joined: 22 Jan 2008
Posts: 117
Location: Liberty city

PostPosted: Tue Jul 03, 2012 10:22 am
Reply with quote

Hello,

Quote:
I am using syncsort 1.3


So did you tried or icon_question.gif
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1702
Location: Australia

PostPosted: Tue Jul 03, 2012 11:12 am
Reply with quote

Hi,

I agree with Naish, when GROUP is a better option and only requires 1 pass of the file.


Gerry
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Jul 03, 2012 4:52 pm
Reply with quote

The examples are unclear.

Why would a "D" come first? Can a "D" come anywhere else in the sequence of records for a key? Why does "C" come before "A"?

Without KEYBEGIN the GROUP is more tricky.

Does Syncsort have OMIT/INCLUDE available on JOINKEYS?
Back to top
View user's profile Send private message
venkatatcts

New User


Joined: 16 Mar 2009
Posts: 55
Location: India

PostPosted: Tue Jul 03, 2012 5:39 pm
Reply with quote

I have sorted the file by desc so that D comes first and then C and then A.

Sycnsort has include and omit conditon.

Thanks,
Venkat
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


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

PostPosted: Tue Jul 03, 2012 6:25 pm
Reply with quote

You need to use something like this


Code:

JOINKEYS FILES=F1,FIELDS=(1,8,A),INCLUDE=(10,1,CH,EQ,C'D')
JOINKEYS FILES=F2,FIELDS=(1,8,A)
REFORMAT FIELDS=(F1:1,10,F2:1,10),FILL=X'FF'
JOIN UNPAIRED F1,F2
SORT FIELDS=COPY
OMIT COND=(1,10,CH,EQ,11,10,CH)


UNTESTED
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Jul 03, 2012 6:39 pm
Reply with quote

Pandora-Box,

You'd just need the "unmatched" records from the main file, no extra messing, so UNPAIRED,F2,ONLY.

Any matches must be for "deleteds". Same source file, so no possible "extras" on the deleteds file. the JOINKEYS gives the records wanted (all, without any which have D).

If the data is already sorted, that can be indicated. With this solution the "D"s are not required to be first, just to be present.
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


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

PostPosted: Tue Jul 03, 2012 6:40 pm
Reply with quote

Ah Agreed Bill icon_redface.gif
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Tue Jul 03, 2012 7:57 pm
Reply with quote

Hello,

Quote:
I am using syncsort 1.3
Is there more to the release shown (i.e. 1.3.x)?

My 1.3 doc shows when=group, but i don't recall if there was a sublevel of th release that is required.

1.3 is rather outdated now and i'd suggest your orgainzation consider the upgrade to the current release.
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1702
Location: Australia

PostPosted: Wed Jul 04, 2012 3:25 am
Reply with quote

Hi,

here is a way using when=group

Code:
//STEP0100 EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
//SORTIN   DD *                                                     
XX1-A-DW D                                                         
XX1-A-DW C                                                         
XX1-A-DW A                                                         
XX1-B-DW C                                                         
XX1-B-DW A                                                         
XX1-B-DW A                                                         
//SORTOUT  DD SYSOUT=*                                             
//SYSIN    DD *                                                     
  SORT FIELDS=COPY                                                 
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,8))), 
        IFTHEN=(WHEN=GROUP,BEGIN=(81,8,ZD,EQ,+1),                   
               PUSH=(89:10,1))                                     
  OUTFIL OMIT=(89,1,CH,EQ,C'D'),BUILD=(1,80)                       
//*                                                                 


Gerry
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Wed Jul 04, 2012 5:13 am
Reply with quote

Venkat B,

You now have a couple of solutions.

If the only purpose of the descending sort on the A/C/D is for this task, then the JOINKEYS approach outlined by xnight and Pandora-Box can remove the need for that sort, as it does not matter where the D occurs as only the D is extracted for that file.

If you have some other need of the file in that descending order, then Gerry's solution should run better than the JOINKEYS.

If you go with the JOINKEYS and your file is in its "natural" order, then be aware that you can stop the sort on each of the JOINKEYS files by adding SORTED.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> JCL & VSAM

 


Similar Topics
Topic Forum Replies
No new posts Sortjoin and Search for a String and ... DFSORT/ICETOOL 1
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts Remove leading zeroes SYNCSORT 4
No new posts Join 2 files according to one key field. JCL & VSAM 3
Search our Forums:

Back to Top