IBM Mainframe Forum

by **ibmmf4u** » Fri Aug 10, 2012 6:27 pm

Hi Bill,

Thanks a lot. I went by your instructions and got stuck at

OUTFIL, define another GROUP (same technique) and PUSH the ID to another position,

Pasted below is the piece of code and the output obtained at each scenario.

Source code:-

INREC IFTHEN=(WHEN=INIT,
OVERLAY=(41:SEQNUM,8,ZD,
RESTART=(1,8))),

SORT FIELDS=COPY

Output:-

00001234 TEST1 TESTING 00000001
00001234 DESCRIPTION 00000002
00001234 TESTDUPL 00000003
00003456 TYPE2 TESTER2 00000001
00003456 DESC1 00000002
00003456 DESC2 00000003
00003456 DESC3 00000004
00004567 ERROR1 ERROR DESC1 00000001
00004567 DESC2 00000002
00004567 DESC3 00000003
00001234 TEST1 TESTER1 00000001
00001234 EXAMPLE1 00000002
00003456 TYPE2 TESTER2 00000001
00003456 EXAMPLE2 00000002
00004567 ERROR1 DESC LINE1 00000001
00004567 DESC LINE2 00000002
00001234 TEST1 TESTSAMP 00000001
00001234 SAMPLE1 00000002

Source code:-

Select all

INREC IFTHEN=(WHEN=INIT,
OVERLAY=(41:SEQNUM,8,ZD,
RESTART=(1,8))),

IFTHEN=(WHEN=GROUP,
BEGIN=(41,8,CH,EQ,C'00000001'),
PUSH=(50:ID=8))

SORT FIELDS=COPY

Output:-

Select all

00001234 TEST1 TESTING 00000001 00000001
00001234 DESCRIPTION 00000002 00000001
00001234 TESTDUPL 00000003 00000001
00003456 TYPE2 TESTER2 00000001 00000002
00003456 DESC1 00000002 00000002
00003456 DESC2 00000003 00000002
00003456 DESC3 00000004 00000002
00004567 ERROR1 ERROR DESC1 00000001 00000003
00004567 DESC2 00000002 00000003
00004567 DESC3 00000003 00000003
00001234 TEST1 TESTER1 00000001 00000004
00001234 EXAMPLE1 00000002 00000004
00003456 TYPE2 TESTER2 00000001 00000005
00003456 EXAMPLE2 00000002 00000005
00004567 ERROR1 DESC LINE1 00000001 00000006
00004567 DESC LINE2 00000002 00000006
00001234 TEST1 TESTSAMP 00000001 00000007
00001234 SAMPLE1 00000002 00000007

The third scenario on outrec i got bit confused i used the same technique in grouping and moved the existing id to new positions but when i looked at the output file bot the id's of the files were similar.

Pasted below is the piece of code which i used in the third scenario.

Source code:-

Select all

INREC IFTHEN=(WHEN=INIT,
OVERLAY=(41:SEQNUM,8,ZD,
RESTART=(1,8))),

IFTHEN=(WHEN=GROUP,
BEGIN=(41,8,CH,EQ,C'00000001'),
PUSH=(50:ID=8))

SORT FIELDS=COPY

OUTREC IFTHEN=(WHEN=GROUP,
BEGIN=(41,8,CH,EQ,C'00000001'),
PUSH=(60:ID=8,70:50,8))

Output:-

Select all

00001234 TEST1 TESTING 00000001 00000001 00000001 00000001
00001234 DESCRIPTION 00000002 00000001 00000001 00000001
00001234 TESTDUPL 00000003 00000001 00000001 00000001
00003456 TYPE2 TESTER2 00000001 00000002 00000002 00000002
00003456 DESC1 00000002 00000002 00000002 00000002
00003456 DESC2 00000003 00000002 00000002 00000002
00003456 DESC3 00000004 00000002 00000002 00000002
00004567 ERROR1 ERROR DESC1 00000001 00000003 00000003 00000003
00004567 DESC2 00000002 00000003 00000003 00000003
00004567 DESC3 00000003 00000003 00000003 00000003
00001234 TEST1 TESTER1 00000001 00000004 00000004 00000004
00001234 EXAMPLE1 00000002 00000004 00000004 00000004
00003456 TYPE2 TESTER2 00000001 00000005 00000005 00000005
00003456 EXAMPLE2 00000002 00000005 00000005 00000005
00004567 ERROR1 DESC LINE1 00000001 00000006 00000006 00000006
00004567 DESC LINE2 00000002 00000006 00000006 00000006
00001234 TEST1 TESTSAMP 00000001 00000007 00000007 00000007
00001234 SAMPLE1 00000002 00000007 00000007 00000007

I am not sure if i did code it exactly. Sorry for troubling you a lot.

Request you to kindly help me in achieving the same.

Thanks in advance!!!

by **BillyBoyo** » Sat Aug 11, 2012 4:10 pm

The idea of running the code in "pieces" is so that you can see how the data "develops".

You also need an idea of what you want to achieve.

You know the final position (your expected output).

My description of the final stage (OUTFIL with INCLUDE/OMIT for ID equal to the first ID) should allow you to construct the stage before the final one, remembering that if you are going to exclude duplicates, then the items which are the same key need to be "next" to each other.

Select all

00001234 TEST1 TESTING 00000001 00000001 00000001
00001234 DESCRIPTION 00000002 00000001 00000001
00001234 TESTDUPL 00000003 00000001 00000001
00001234 TEST1 TESTER1 00000001 00000004 00000001
00001234 EXAMPLE1 00000002 00000004 00000001
00001234 TEST1 TESTSAMP 00000001 00000007 00000001
00001234 SAMPLE1 00000002 00000007 00000001

The 2nd-to-last colum is the ID from INREC. The last column is the ID from OUTREC, once all the 1234s have been sorted together, so the final column, generated by the GROUP on the restarted seqence (a new one) gives the first ID for the key, and applies it to all the records of that key. Then, if the last two columns are equal, you have yourself the first group, only, and the rest can be ignored.

Once you understand how to get to the result, it is just a question of implementation. The time on this is worth spending, because the technique (envisioning how the input data can become your expected results) is what you'll do with every Sort solution. Sometimes it'll be trivial, and you won't notice. Other times it'll take more effort.

by **ibmmf4u** » Sun Aug 12, 2012 3:00 pm

Hi Bill,

Thanks for guiding, enlightening and helping me in each phase of achieving the requirement.

Thanks a lot once again. I tried the below piece of code per your instructions and it's working fine.

Source code:-

Select all

//SORTIN DD *
00001234 TEST1 TESTING
00001234 DESCRIPTION
00001234 TESTDUPL
00003456 TYPE2 TESTER2
00003456 DESC1
00003456 DESC2
00003456 DESC3
00004567 ERROR1 ERROR DESC1
00004567 DESC2
00004567 DESC3
00001234 TEST1 TESTER1
00001234 EXAMPLE1
00003456 TYPE2 TESTER2
00003456 EXAMPLE2
00004567 ERROR1 DESC LINE1
00004567 DESC LINE2
00001234 TEST1 TESTSAMP
00001234 SAMPLE1
//SYSIN DD *
INREC IFTHEN=(WHEN=INIT,
OVERLAY=(41:SEQNUM,8,ZD,
RESTART=(1,8))),

IFTHEN=(WHEN=GROUP,
BEGIN=(41,8,CH,EQ,C'00000001'),
PUSH=(50:ID=8))

SORT FIELDS=(1,8,CH,A)

OUTREC IFTHEN=(WHEN=INIT,
OVERLAY=(61:SEQNUM,8,ZD,
RESTART=(1,8))),

IFTHEN=(WHEN=GROUP,
BEGIN=(61,8,CH,EQ,C'00000001'),
PUSH=(70:ID=8))

OUTFIL INCLUDE=(50,8,CH,EQ,70,8,CH),
BUILD=(1,40,40X)
//*

Output:-

Select all

00001234 TEST1 TESTING
00001234 DESCRIPTION
00001234 TESTDUPL
00003456 TYPE2 TESTER2
00003456 DESC1
00003456 DESC2
00003456 DESC3
00004567 ERROR1 ERROR DESC1
00004567 DESC2
00004567 DESC3

However it has some constraints , if the input file contains 4 key records and its duplicates it can't able to handle it.(In reality the file contains many records and occurrence of its duplicates varies) Its just writing out the first three records eliminating the duplicates.

Below mentioned is an example of the input file:-

Input file:-

Select all

00001234 TEST1 TESTING
00001234 DESCRIPTION
00001234 TESTDUPL
00003456 TYPE2 TESTER2
00003456 DESC1
00003456 DESC2
00003456 DESC3
00004567 ERROR1 ERROR DESC1
00004567 DESC2
00004567 DESC3
00001234 TEST1 TESTER1
00001234 EXAMPLE1
00006789 TESTNU4 TESTN DESC4
00006789 TESTN2
00006789 TESTN3
00003456 TYPE2 TESTER2
00003456 EXAMPLE2
00004567 ERROR1 DESC LINE1
00004567 DESC LINE2
00006789 TESTNU4 TESTN DESC32
00006789 TESTN DUPLINE
00001234 TEST1 TESTSAMP
00001234 SAMPLE1

Please kindly help me in achieving the same.

Thanks in advance!!!!!!!

by **BillyBoyo** » Mon Aug 13, 2012 6:29 pm

Can you show the output from your step with that input. I don't have Syncsort, so can't run your example myself.

by **ibmmf4u** » Mon Aug 13, 2012 7:56 pm

Sure Bill.

Pasted below is the output obtained in each phase for the latest input file which you asked for.

Source code:-

Select all

//SORTIN DD *
00001234 TEST1 TESTING
00001234 DESCRIPTION
00001234 TESTDUPL
00003456 TYPE2 TESTER2
00003456 DESC1
00003456 DESC2
00003456 DESC3
00004567 ERROR1 ERROR DESC1
00004567 DESC2
00004567 DESC3
00001234 TEST1 TESTER1
00001234 EXAMPLE1
00006789 TESTNU4 TESTN DESC4
00006789 TESTN2
00006789 TESTN3
00003456 TYPE2 TESTER2
00003456 EXAMPLE2
00004567 ERROR1 DESC LINE1
00004567 DESC LINE2
00006789 TESTNU4 TESTN DESC32
00006789 TESTN DUPLINE
00001234 TEST1 TESTSAMP
00001234 SAMPLE1
//SYSIN DD *
INREC IFTHEN=(WHEN=INIT,
OVERLAY=(41:SEQNUM,8,ZD,
RESTART=(1,8)))

SORT FIELDS=COPY
//*

Output:-

Select all

00001234 TEST1 TESTING 00000001
00001234 DESCRIPTION 00000002
00001234 TESTDUPL 00000003
00003456 TYPE2 TESTER2 00000001
00003456 DESC1 00000002
00003456 DESC2 00000003
00003456 DESC3 00000004
00004567 ERROR1 ERROR DESC1 00000001
00004567 DESC2 00000002
00004567 DESC3 00000003
00001234 TEST1 TESTER1 00000001
00001234 EXAMPLE1 00000002
00006789 TESTNU4 TESTN DESC4 00000001
00006789 TESTN2 00000002
00006789 TESTN3 00000003
00003456 TYPE2 TESTER2 00000001
00003456 EXAMPLE2 00000002
00004567 ERROR1 DESC LINE1 00000001
00004567 DESC LINE2 00000002
00006789 TESTNU4 TESTN DESC32 00000001
00006789 TESTN DUPLINE 00000002
00001234 TEST1 TESTSAMP 00000001
00001234 SAMPLE1 00000002

Source code:-

Select all

//SYSIN DD *
INREC IFTHEN=(WHEN=INIT,
OVERLAY=(41:SEQNUM,8,ZD,
RESTART=(1,8))),

IFTHEN=(WHEN=GROUP,
BEGIN=(41,8,CH,EQ,C'00000001'),
PUSH=(50:ID=8))

SORT FIELDS=(1,8,CH,A)
//*

Output:-

Select all

00001234 TEST1 TESTING 00000001 00000001
00001234 DESCRIPTION 00000002 00000001
00001234 TESTDUPL 00000003 00000001
00001234 TEST1 TESTER1 00000001 00000004
00001234 EXAMPLE1 00000002 00000004
00001234 TEST1 TESTSAMP 00000001 00000009
00001234 SAMPLE1 00000002 00000009
00003456 TYPE2 TESTER2 00000001 00000002
00003456 DESC1 00000002 00000002
00003456 DESC2 00000003 00000002
00003456 DESC3 00000004 00000002
00003456 TYPE2 TESTER2 00000001 00000006
00003456 EXAMPLE2 00000002 00000006
00004567 ERROR1 ERROR DESC1 00000001 00000003
00004567 DESC2 00000002 00000003
00004567 DESC3 00000003 00000003
00004567 ERROR1 DESC LINE1 00000001 00000007
00004567 DESC LINE2 00000002 00000007
00006789 TESTNU4 TESTN DESC4 00000001 00000005
00006789 TESTN2 00000002 00000005
00006789 TESTN3 00000003 00000005
00006789 TESTNU4 TESTN DESC32 00000001 00000008
00006789 TESTN DUPLINE 00000002 00000008

Source code:-

Select all

//SYSIN DD *
INREC IFTHEN=(WHEN=INIT,
OVERLAY=(41:SEQNUM,8,ZD,
RESTART=(1,8))),

IFTHEN=(WHEN=GROUP,
BEGIN=(41,8,CH,EQ,C'00000001'),
PUSH=(50:ID=8))

SORT FIELDS=(1,8,CH,A)

OUTREC IFTHEN=(WHEN=INIT,
OVERLAY=(61:SEQNUM,8,ZD,
RESTART=(1,8)))
//*

Output:-

Select all

00001234 TEST1 TESTING 00000001 00000001 00000001
00001234 DESCRIPTION 00000002 00000001 00000002
00001234 TESTDUPL 00000003 00000001 00000003
00001234 TEST1 TESTER1 00000001 00000004 00000004
00001234 EXAMPLE1 00000002 00000004 00000005
00001234 TEST1 TESTSAMP 00000001 00000009 00000006
00001234 SAMPLE1 00000002 00000009 00000007
00003456 TYPE2 TESTER2 00000001 00000002 00000001
00003456 DESC1 00000002 00000002 00000002
00003456 DESC2 00000003 00000002 00000003
00003456 DESC3 00000004 00000002 00000004
00003456 TYPE2 TESTER2 00000001 00000006 00000005
00003456 EXAMPLE2 00000002 00000006 00000006
00004567 ERROR1 ERROR DESC1 00000001 00000003 00000001
00004567 DESC2 00000002 00000003 00000002
00004567 DESC3 00000003 00000003 00000003
00004567 ERROR1 DESC LINE1 00000001 00000007 00000004
00004567 DESC LINE2 00000002 00000007 00000005
00006789 TESTNU4 TESTN DESC4 00000001 00000005 00000001
00006789 TESTN2 00000002 00000005 00000002
00006789 TESTN3 00000003 00000005 00000003
00006789 TESTNU4 TESTN DESC32 00000001 00000008 00000004
00006789 TESTN DUPLINE 00000002 00000008 00000005

Source code:-

Select all

//SYSIN DD *
INREC IFTHEN=(WHEN=INIT,
OVERLAY=(41:SEQNUM,8,ZD,
RESTART=(1,8))),

IFTHEN=(WHEN=GROUP,
BEGIN=(41,8,CH,EQ,C'00000001'),
PUSH=(50:ID=8))

SORT FIELDS=(1,8,CH,A)

OUTREC IFTHEN=(WHEN=INIT,
OVERLAY=(61:SEQNUM,8,ZD,
RESTART=(1,8))),

IFTHEN=(WHEN=GROUP,
BEGIN=(61,8,CH,EQ,C'00000001'),
PUSH=(70:ID=8))
//*

Output:-

Select all

00001234 TEST1 TESTING 00000001 00000001 00000001 00000001
00001234 DESCRIPTION 00000002 00000001 00000002 00000001
00001234 TESTDUPL 00000003 00000001 00000003 00000001
00001234 TEST1 TESTER1 00000001 00000004 00000004 00000001
00001234 EXAMPLE1 00000002 00000004 00000005 00000001
00001234 TEST1 TESTSAMP 00000001 00000009 00000006 00000001
00001234 SAMPLE1 00000002 00000009 00000007 00000001
00003456 TYPE2 TESTER2 00000001 00000002 00000001 00000002
00003456 DESC1 00000002 00000002 00000002 00000002
00003456 DESC2 00000003 00000002 00000003 00000002
00003456 DESC3 00000004 00000002 00000004 00000002
00003456 TYPE2 TESTER2 00000001 00000006 00000005 00000002
00003456 EXAMPLE2 00000002 00000006 00000006 00000002
00004567 ERROR1 ERROR DESC1 00000001 00000003 00000001 00000003
00004567 DESC2 00000002 00000003 00000002 00000003
00004567 DESC3 00000003 00000003 00000003 00000003
00004567 ERROR1 DESC LINE1 00000001 00000007 00000004 00000003
00004567 DESC LINE2 00000002 00000007 00000005 00000003
00006789 TESTNU4 TESTN DESC4 00000001 00000005 00000001 00000004
00006789 TESTN2 00000002 00000005 00000002 00000004
00006789 TESTN3 00000003 00000005 00000003 00000004
00006789 TESTNU4 TESTN DESC32 00000001 00000008 00000004 00000004
00006789 TESTN DUPLINE 00000002 00000008 00000005 00000004

If you look at the above output the ID generated for TESTNU4 in 50th position and 70th postion are different hence it's not getting pulled over in the output file.

Source code:-

Select all

//SYSIN DD *
INREC IFTHEN=(WHEN=INIT,
OVERLAY=(41:SEQNUM,8,ZD,
RESTART=(1,8))),

IFTHEN=(WHEN=GROUP,
BEGIN=(41,8,CH,EQ,C'00000001'),
PUSH=(50:ID=8))

SORT FIELDS=(1,8,CH,A)

OUTREC IFTHEN=(WHEN=INIT,
OVERLAY=(61:SEQNUM,8,ZD,
RESTART=(1,8))),

IFTHEN=(WHEN=GROUP,
BEGIN=(61,8,CH,EQ,C'00000001'),
PUSH=(70:ID=8))

OUTFIL INCLUDE=(50,8,CH,EQ,70,8,CH),
BUILD=(1,40,40X)
//*

Final output file obtained:-

Select all

00001234 TEST1 TESTING
00001234 DESCRIPTION
00001234 TESTDUPL
00003456 TYPE2 TESTER2
00003456 DESC1
00003456 DESC2
00003456 DESC3
00004567 ERROR1 ERROR DESC1
00004567 DESC2
00004567 DESC3

Where in ideally the output file should be as follows (It should also be containing the TESTNU4 records and its description as well .

Output file:-

Select all

00001234 TEST1 TESTING
00001234 DESCRIPTION
00001234 TESTDUPL
00003456 TYPE2 TESTER2
00003456 DESC1
00003456 DESC2
00003456 DESC3
00004567 ERROR1 ERROR DESC1
00004567 DESC2
00004567 DESC3
00006789 TESTNU4 TESTN DESC4
00006789 TESTN2
00006789 TESTN3

Bill can you please help me in how to achieve this.

Also have a one more quick question over here, is there any way on how to eliminate the duplicate records by sorting them at first based on the keyfields and later eliminating the duplicates.

Below mentoined is an example of the same sorted input file.
Inputfile:-

Select all

00001234 TEST1 TESTING
00001234 DESCRIPTION
00001234 TESTDUPL
00001234 TEST1 TESTER1
00001234 EXAMPLE1
00001234 TEST1 TESTSAMP
00001234 SAMPLE1
00003456 TYPE2 TESTER2
00003456 DESC1
00003456 DESC2
00003456 DESC3
00003456 TYPE2 TESTER2
00003456 EXAMPLE2
00004567 ERROR1 ERROR DESC1
00004567 DESC2
00004567 DESC3
00004567 ERROR1 DESC LINE1
00004567 DESC LINE2
00006789 TESTNU4 TESTN DESC4
00006789 TESTN2
00006789 TESTN3
00006789 TESTNU4 TESTN DESC32
00006789 TESTN DUPLINE

It should produce the same output file after eliminating the duplicate records.
Output file:-

Select all

00001234 TEST1 TESTING
00001234 DESCRIPTION
00001234 TESTDUPL
00003456 TYPE2 TESTER2
00003456 DESC1
00003456 DESC2
00003456 DESC3
00004567 ERROR1 ERROR DESC1
00004567 DESC2
00004567 DESC3
00006789 TESTNU4 TESTN DESC4
00006789 TESTN2
00006789 TESTN3

Please kindly help me in achieving the same.

Thanks in advance!!!

by **BillyBoyo** » Mon Aug 13, 2012 10:43 pm

Select all

//SYSIN DD *
INREC IFTHEN=(WHEN=INIT,
OVERLAY=(41:SEQNUM,8,ZD,
RESTART=(1,8))),

IFTHEN=(WHEN=GROUP,
BEGIN=(41,8,CH,EQ,C'00000001'),
PUSH=(50:ID=8))

SORT FIELDS=(1,8,CH,A)

OUTREC IFTHEN=(WHEN=INIT,
OVERLAY=(61:SEQNUM,8,ZD,
RESTART=(1,8))),

IFTHEN=(WHEN=GROUP,
BEGIN=(61,8,CH,EQ,C'00000001'),
PUSH=(70:ID=8))
//*

The problem is with the final IFTHEN in this piece. Here the idea was not to put a new ID, but to PUSH the first ID of the sorted group of records, so that all the records belonging to that first group could be identified.

The above code works "by coincidence" when the ID generated after sorting matches the ID before sorting for that particular key.

I'm not sure I understand your follow-up question

Also have a one more quick question over here, is there any way on how to eliminate the duplicate records by sorting them at first based on the keyfields and later eliminating the duplicates.

This is what you are doing. You are sorting the file on the key, then discarding all groups (based on being a group before sorted) except for the first.

If you want to be able to get the records back into the original order, you need to add a sequence number for the unsorted file, after you have completed processing, sort on the sequence number and write the records including only the original data.

IBM Mainframe Forum

Removing duplicates!!!

Re: Removing duplicates!!!

Re: Removing duplicates!!!

Re: Removing duplicates!!!

Re: Removing duplicates!!!

Re: Removing duplicates!!!

Re: Removing duplicates!!!