IBM Mainframe Forum

by **lal** » Tue Mar 23, 2010 12:53 am

Hi Friends,
I have a ICETOOL jcl which takes the input file and splits into two file (one with all the duplicates and other file without duplicates). The input file is of LRECL=80 with the key in colum 1 thru column 10. Can someone please suggest me to do the same using SYNCSORT? I also need slight modification in the output file with all the duplicates (file=OUTPUT.ALLDUPS), it should have a value of 'X' at column 30.

Select all

//SORT030 EXEC PGM=ICETOOL
//*
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//SORTIN DD DSN=INPUT.FILE1,
// DISP=SHR
//SORTOF1 DD DSN=OUTPUT.ALLDUPS,
// DISP=(,CATLG,CATLG),
// UNIT=SYSDA,
// SPACE=(CYL,(2,1),RLSE),
// DCB=(RECFM=FB,LRECL=80)
//SORTOF2 DD DSN=OUTPUT.NODUPS,
// DISP=(,CATLG,CATLG),
// UNIT=SYSDA,
// SPACE=(CYL,(2,1),RLSE),
// DCB=(RECFM=FB,LRECL=80)
//TOOLIN DD *
SELECT FROM(SORTIN) TO(SORTOF1) ON(1,10,CH) ALLDUPS
SELECT FROM(SORTIN) TO(SORTOF2) ON(1,10,CH) NODUPS
/*

Sample data:-

Input file:-

Select all

AAAAAAAAAA111111111111111
BBBBBBBBBB333333333333333
CCCCCCCCCC555555555555555
DDDDDDDDDD666666666666666
AAAAAAAAAA888888888888888
CCCCCCCCCC000000000000000

Output file with duplicates:-

Select all

AAAAAAAAAA111111111111111 X
AAAAAAAAAA888888888888888 X
CCCCCCCCCC555555555555555 X
CCCCCCCCCC000000000000000 X

Output file without duplicates:-

Select all

BBBBBBBBBB333333333333333
DDDDDDDDDD666666666666666

by **dick scherrer** » Tue Mar 23, 2010 1:10 am

Hello,

Syncsort supports both ALLDUPS and NODUPS. . .

Have you tried to run this with a small set of sample data? What happened?

by **lal** » Tue Mar 23, 2010 1:27 am

Hi Dick Scherrer,
I should have made my query more clear, yes that's true that both NODUPS & ALLDUPS are supported by SYNCTOOL. But I want the solution not using the SYNCTOOL.

Thanks,
lal

by **Alissa Margulies** » Tue Mar 23, 2010 1:59 am

Here is one way to accomplish this task:

Select all

//SORT1 EXEC PGM=SORT
//SORTOUT DD DISP=(NEW,PASS),DSN=&&OUT
//SORTIN DD *
AAAAAAAAAA111111111111111
BBBBBBBBBB333333333333333
CCCCCCCCCC555555555555555
DDDDDDDDDD666666666666666
AAAAAAAAAA888888888888888
CCCCCCCCCC000000000000000
//SYSOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=(1,10,CH,D)
OUTREC FIELDS=(1,80,81:C'1')
OUTFIL SECTIONS=(1,10,TRAILER3=(81:TOT=(81,1,ZD,EDIT=(T)))),
REMOVECC
/*
//SORT2 EXEC PGM=SORT
//SORTOF01 DD DSN=UNIQUE.RECORDS
//SORTOF02 DD DSN=OUTPUT.ALLDUPS
//SORTIN DD DISP=SHR,DSN=&&OUT
//SYSOUT DD SYSOUT=*
//SYSIN DD *
INREC OVERLAY=(82:SEQNUM,8,ZD)
SORT FIELDS=(82,8,CH,D)
OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(1,1,CH,EQ,C' '),PUSH=(81:81,1))
OUTFIL FILES=01,BUILD=(1,80),
INCLUDE=(81,1,ZD,EQ,1,AND,1,1,CH,NE,C' ')
OUTFIL FILES=02,BUILD=(1,29,C'X',80:X),
INCLUDE=(81,1,ZD,NE,1,AND,1,1,CH,NE,C' ')

by **dick scherrer** » Tue Mar 23, 2010 2:02 am

Hello,

But I want the solution not using the SYNCTOOL.

There is always COBOL, Easytrieve, etc. . .

Your company has paid for the full Syncsort product. Doesn't make much business sense to invest in developing workarounds for something that has already been purchased. . .

I find it quite puzzling that there is certain "management" that do not want to use xxxTOOL, but a very complex SORT solution (one that the in-house staff had the vendor create because they did not know how) is somehow acceptable because the PGM= is the sort. . .

by **Alissa Margulies** » Tue Mar 23, 2010 2:13 am

Here is simpler SyncSort for z/OS 1.3 job that will produce the requested output:

Select all

//SORT1 EXEC PGM=SORT
//SORTIN DD *
AAAAAAAAAA111111111111111
BBBBBBBBBB333333333333333
CCCCCCCCCC555555555555555
DDDDDDDDDD666666666666666
AAAAAAAAAA888888888888888
CCCCCCCCCC000000000000000
//SORTXDUP DD DISP=(NEW,PASS),DSN=&&XDUP
//SORTOF01 DD DISP=(NEW,PASS),DSN=&&OUT1
//SORTOF02 DD DSN=NODUPS
//SYSOUT DD SYSOUT=*
//SYSIN DD *
INREC OVERLAY=(81:X'F1')
SORT FIELDS=(1,10,CH,A)
DUPKEYS SUM=(81,1,ZD),XDUP
OUTFIL FILES=01,INCLUDE=(81,1,ZD,GT,1)
OUTFIL FILES=02,INCLUDE=(81,1,ZD,EQ,1)
/*
//STEP2 EXEC PGM=SORT
//SORTIN01 DD DSN=&&XDUP,DISP=SHR
//SORTIN02 DD DSN=&&OUT1,DISP=SHR
//SORTOUT DD DSN=ALLDUPS
//SYSOUT DD SYSOUT=*
//SYSIN DD *
INREC BUILD=(1,29,C'X',80:X)
MERGE FIELDS=(1,10,CH,A)
/*

by **lal** » Tue Mar 23, 2010 2:38 am

Thanks Alissa for the solution.

Dick Scherrer,
I am sorry but I should have mentioned this earlier that I wanted to to solve this problem using the new JOIN/JOINKEYS feature in Syncsort. So was expecting the solution my way even without asking for it!!! I bet I was dreaming!!! Sorry again for wasting people's precious time.

Well, this was my idea of using the SYNCSORT to solve the problem:-
1) First take the input file and split it into two files one with unique records and other with duplicate records using XSUM

Select all

//STEP010 EXEC PGM=SYNCSORT
//SORTIN DD DSN=INPUT.FILE1,DISP=SHR
//SORTOUT DD DSN=OUTPUT.UNIQUE.RECORDS,
// DISP=(,CATLG,CATLG),
// UNIT=SYSDA,
// SPACE=(CYL,(2,1),RLSE),
// DCB=(RECFM=FB,LRECL=80)
//SORTXSUM DD DSN=OUTPUT.FIRST.DUP.RECORDS,
// DISP=(,CATLG,CATLG),
// UNIT=PERMDA,
// SPACE=(CYL,(2,1),RLSE),
// DCB=(RECFM=FB,LRECL=80)
//SYSOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=(1,10,CH,A)
SUM FIELDS=NONE,XSUM

In the next step to use the new JOIN feature in Syncsort to just get all the duplicate records.

Select all

//STEP020 EXEC PGM=SYNCSORT
//SORTJNF1 DD DSN=OUTPUT.UNIQUE.RECORDS,DISP=SHR
//SORTJNF2 DD DSN=OUTPUT.FIRST.DUP.RECORDS,DISP=SHR
//SORTOUT DD DSN=OUTPUT.ALLDUPS,
// DISP=(,CATLG,CATLG),
// UNIT=PERMDA,
// SPACE=(CYL,(2,1),RLSE),
// DCB=(RECFM=FB,LRECL=80)
//SYSOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(1,10,A)
JOINKEYS FILE=F2,FIELDS=(1,10,A)
SORT FIELDS=(1,10,CH,A)
/*

But I get the following error
WER471A A REFORMAT STATEMENT IS REQUIRED TO USE THE JOIN FEATURE

This makes sense it needs REFORMAT statement since I am expecting records from both the files.

So is there a way to get all the duplicate records in OUTPUT.ALLDUPS using the JOIN/JOINKEYS feature???

Thanks,
lal

by **Alissa Margulies** » Tue Mar 23, 2010 2:46 am

This will give you all the duplicates:

Select all

//SORT1 EXEC PGM=SORT
//SORTIN DD *
AAAAAAAAAA111111111111111
BBBBBBBBBB333333333333333
CCCCCCCCCC555555555555555
DDDDDDDDDD666666666666666
AAAAAAAAAA888888888888888
CCCCCCCCCC000000000000000
//SORTOUT DD DSN=&&OUT1
//SYSOUT DD SYSOUT=*
//SYSIN DD *
INREC FIELDS=(1,10,1C'0001')
SORT FIELDS=(1,10,CH,A)
DUPKEYS SUM=(11,4,ZD)
OUTFIL OMIT=(11,4,ZD,EQ,1),OUTREC=(1,10)
/*
//SORT2 EXEC PGM=SORT
//SORTJNF1 DD *
AAAAAAAAAA111111111111111
BBBBBBBBBB333333333333333
CCCCCCCCCC555555555555555
DDDDDDDDDD666666666666666
AAAAAAAAAA888888888888888
CCCCCCCCCC000000000000000
//SORTJNF2 DD DSN=&&OUT1
//SORTOUT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(1,10,A)
JOINKEYS FILE=F2,FIELDS=(1,10,A),SORTED
REFORMAT FIELDS=(F1:1,80)
SORT FIELDS=COPY
END
/*

by **lal** » Tue Mar 23, 2010 3:14 am

Thanks Alissa for the help.

Thanks,
lal

IBM Mainframe Forum

How to do this using SYNCSORT

How to do this using SYNCSORT

Re: How to do this using SYNCSORT

Re: How to do this using SYNCSORT

Re: How to do this using SYNCSORT

Re: How to do this using SYNCSORT

Re: How to do this using SYNCSORT

Re: How to do this using SYNCSORT

Re: How to do this using SYNCSORT

Re: How to do this using SYNCSORT