Selective SYNCSORT where count more than two

amitava · Active User Joined: 30 Oct 2005 Posts: 186 Location: India

Hi All,
I want to sort my input file(say - A) on the column 1-5 and 29-9. Column 29-9 is repeatative in the output file i.e. in output file it can come for more than one occurance. I want to move those records of the input fie whose 29-9 column occurance are same for more than 2 records. Am I clear to my question? E.g -

My Input File
===========================================

dick scherrer · Posted: Mon Jun 18, 2007 6:23 am

Hello,

Please post which sort product your organization uses.

If you are not sure, the jes output for a sort step will identify which sort product you are using.

amitava · Active User Joined: 30 Oct 2005 Posts: 186 Location: India

Hi Dick,
Sorry I forgot to mention that my mainframe version has SYNCSORT to use.

dick scherrer · Posted: Tue Jun 19, 2007 1:45 am

Hello Amitava,

I believe you will be ahead to proceed with program code. This should take only a small amount of code to accomplish.

There may be a way to do this with Syncsort/Synctool, but i've not been able to find an example that does what you want.

William Thompson · Posted: Tue Jun 19, 2007 2:04 am

dick scherrer · Posted: Tue Jun 19, 2007 2:50 am

Hi Bill,

The output should only contain records that have a key that is a duplicate to at least two other records - not merely a single duplicate. The input is in order by this "key". (if i understand correctly. . .).

In the posted sample data, only one key fits the rule - 001622738.

Johnson_Jiang · Posted: Tue Jun 19, 2007 7:42 am

I think maybe you just want to pick out all those record which the count of 29-9 is more than two rows, right? Maybe no need to use program to do it, just use SORT and refer to the three steps:

1. Use SORT/Copy option to add one constant field, i.e. '00001', at the end of each record.

2. Use SORT/Sum option to sum all the records, the sum fields should be 1-5 and 29-9. After this step, you can pick out all of those records which the count of 29-9 is more than 2.

3. Use Sort/Include option to get rid of those unsuitable records.

Sample JCL:

Step1 :

//SORTIN DD DSN....
//SORTOUT DD DSN=&&TEMP01,DISP=(NEW,PASS)... <- File length should be extended to contain the field '000001'.
//SYSIN DD *
SORT FIELDS=COPY
OUTREC=(1,length of inputfile,C'000001'),CONVERT

Step2:

//SORTIN DD DSN=&&TEMP01,DISP=(OLD,PASS)
//SORTOUT DD DSN=&&TEMP02,DISP=(NEW,PASS)....
//SYSIN DD *
SORT FIELDS=(1,5,CH,A,29,9,CH,A)
SUM FIELDS=(22,6,ZD) <- Here sum by the new field
OUTREC FIELDS=(1, length of temp01)

Step3:

//SORTIN DD DSN=&&TEMP02,DISP=(OLD,PASS)
//SORTOUT DD DSN=<Your output file here>,DISP=(NEW,CATLG)....
//SYSIN DD *
SORT FIELDS=(1,5,CH,A,29,9,CH,A)
INCLUDE=(xx,xx,CH,NE,C'000001') <- Here get rid of those single record
OUTREC FIELDS=(1, length of your input file)

dick scherrer · Posted: Tue Jun 19, 2007 8:27 am

Hello,

Yes, i believe something like that could work (i don't believe what is posted will quite make it - try it on your system and let us know), but it is more "programmer" work than a simple program would be . . . It is also 3 steps that would have to be documented (if this was something that would be promoted to production) rather than one.

What if additional business rules are determined?

One other caution is that if the original input had 100 million records and 20 million were selected as >2 duplicates, that is 3 passes of those 20 million "keepers" and 2 passes of the 100 million (my math may be off there, but you get the idea

). A simple program would do this in one pass saving considerable machine resources.

amitava · Active User Joined: 30 Oct 2005 Posts: 186 Location: India

Hi All,
Thanx a lot for your help and support. Dick and William-I am creating the input file obviously thru a COBOL program and this program has become so much complicated having the business logic and so; I prefered to do the reformat thru JCL. I am getting what you want to specify Dick, but really I think it will be very tough to incorporate the changes in the existing program! And including a new program for doing the reformat only I think it will be an extra burden to the system!

What do you say Dick?

Johnson - Thanks a lot for your approach! I have to run it in my system and will let you about this if any mishap happens!!!

dick scherrer · Posted: Tue Jun 19, 2007 2:01 pm

Hello,

You have not posted any of your business rules or described any of the complexity, so i cannot make much of a reply.

Unless there is something i do not understand, where is the difficulty in only writing the output when there are more than 2 records for a given key?

amitava · Active User Joined: 30 Oct 2005 Posts: 186 Location: India

Hi Dick,
Please don't mind but as it is (IBMMAINFRAME) is a common forum I cannot post my organization's business details required for the program we have already coded or the JCL step we want to incorporate in our existing JCL.
Hey I know (Not to disclose the business processing) - this is something big people always talk about! But frankly speaking (as of mine), it will be very tough to explain you all the business details thru a mail only!

At least please don't make such a rigid response! Ha ha ha - just jocking!!!

dick scherrer · Posted: Tue Jun 19, 2007 8:04 pm

Hello,

If the requirement definition is well understood, it can be explained without giving away any "company secrets". Also, i don't believe we need "all the details" just enough info to understand what is so complex.

For giggles, look around in some of the topics and you will find this