Reformat/Create Records using SYNCSORT
Posted: Sun May 16, 2010 8:26 pm
All,
I'm using SyncSort v1.2 on a z/390 mainframe, and have been given a fairly complex requirement to try to accomplish using SyncSort. I have 3 input files consisting of a Parent and two Child records, and each input record (all 3 inputs) contain 9's compliment date fields. I've successfully converted these to gregorian dates, and successfully created the joins necessary to create data in the following format:
First and foremost - this could fairly easily be accomplished with a program, but the challenge is to try to accomplish the following using the SyncSort utility. This seems like a silly requirement, but it does have further reaching implications for future projects based on sone odd data we have to work with. What I need to be able to do is to create a set of records for a Parent Code that spans from a low-date (0001-01-01) through to a high-date (9999-12-31), and for each record show either the applicable child values, or a blank where a value would not exist. Ultimately, I would end with a data file that contains (for this one joined record) that looks like the following. The ultimate goal is to use this data as lookups for extremely large datasets, where a final sort step would write out only records based on the Parent Code join, but when the input record date field is between Effective and Expiration date only.
I'm looking for suggestions on direction, or perhaps coding examples that I can use as a kick-start to meeting this requirement. Any suggestions would be appreciated.
Thanks.
I'm using SyncSort v1.2 on a z/390 mainframe, and have been given a fairly complex requirement to try to accomplish using SyncSort. I have 3 input files consisting of a Parent and two Child records, and each input record (all 3 inputs) contain 9's compliment date fields. I've successfully converted these to gregorian dates, and successfully created the joins necessary to create data in the following format:
Parent Record:-----------------
PrntCd: Eff_Dt: Chd1: Chd2:
0000001 1999-01-01 C1A C2M
0000002 2001-03-15 C1B C2N
0000002 2006-12-31 C1C C2T
0000003 2007-04-01 C1H C2T
0000003 2009-06-30 C1B C2N
0000004 2004-06-01 C1K C2P
...
Child 1 Records:
Chld1Cd Eff_Dt Value:
C1A 2002-01-01 DOG
C1B 2003-06-01 CAT
C1C 1998-08-15 LEMUR
C1H 2004-05-07 PARROT
C1K 2003-06-01 LIZZARD
...
Child 2 Records:
Chld2Cd Eff_Dt Value:
C2M 2005-10-15 DOG2
C2N 2002-04-01 CAT2
C2P 1995-10-15 LEMUR2
C2T 2006-06-01 PARROT2
...
When joined together based on the codes driven by the Parent record, one of the resulting data records looks like:
Parent Record:----------------- Child 1 Record:----------- Child 2 Record:-----------
PrntCd: Eff_Dt: Chd1: Chd2: Chld1Cd Eff_Dt Value: Chld2Cd Eff_Dt Value:
0000001 1999-01-01 C1A C2M C1A 2002-01-01 DOG C2M 2005-10-15 DOG2
PrntCd: Eff_Dt: Chd1: Chd2:
0000001 1999-01-01 C1A C2M
0000002 2001-03-15 C1B C2N
0000002 2006-12-31 C1C C2T
0000003 2007-04-01 C1H C2T
0000003 2009-06-30 C1B C2N
0000004 2004-06-01 C1K C2P
...
Child 1 Records:
Chld1Cd Eff_Dt Value:
C1A 2002-01-01 DOG
C1B 2003-06-01 CAT
C1C 1998-08-15 LEMUR
C1H 2004-05-07 PARROT
C1K 2003-06-01 LIZZARD
...
Child 2 Records:
Chld2Cd Eff_Dt Value:
C2M 2005-10-15 DOG2
C2N 2002-04-01 CAT2
C2P 1995-10-15 LEMUR2
C2T 2006-06-01 PARROT2
...
When joined together based on the codes driven by the Parent record, one of the resulting data records looks like:
Parent Record:----------------- Child 1 Record:----------- Child 2 Record:-----------
PrntCd: Eff_Dt: Chd1: Chd2: Chld1Cd Eff_Dt Value: Chld2Cd Eff_Dt Value:
0000001 1999-01-01 C1A C2M C1A 2002-01-01 DOG C2M 2005-10-15 DOG2
First and foremost - this could fairly easily be accomplished with a program, but the challenge is to try to accomplish the following using the SyncSort utility. This seems like a silly requirement, but it does have further reaching implications for future projects based on sone odd data we have to work with. What I need to be able to do is to create a set of records for a Parent Code that spans from a low-date (0001-01-01) through to a high-date (9999-12-31), and for each record show either the applicable child values, or a blank where a value would not exist. Ultimately, I would end with a data file that contains (for this one joined record) that looks like the following. The ultimate goal is to use this data as lookups for extremely large datasets, where a final sort step would write out only records based on the Parent Code join, but when the input record date field is between Effective and Expiration date only.
PrntCd: Eff_Dt: Exp_Dt: Chd1: Value: Chd2: Value:
0000001 0001-01-01 1998-12-31 [blank] [blank] [blank] [blank]
0000001 1999-01-01 2001-12-31 C1A [blank] C2M [blank]
0000001 2002-01-01 2005-10-14 C1A DOG C2M [blank]
0000001 2005-10-15 9999-12-31 C1A DOG C2M DOG2
0000001 0001-01-01 1998-12-31 [blank] [blank] [blank] [blank]
0000001 1999-01-01 2001-12-31 C1A [blank] C2M [blank]
0000001 2002-01-01 2005-10-14 C1A DOG C2M [blank]
0000001 2005-10-15 9999-12-31 C1A DOG C2M DOG2
I'm looking for suggestions on direction, or perhaps coding examples that I can use as a kick-start to meeting this requirement. Any suggestions would be appreciated.
Thanks.