Page 1 of 1

Reformat/Create Records using SYNCSORT

PostPosted: Sun May 16, 2010 8:26 pm
by Quikplay
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:
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


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


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.

Re: Reformat/Create Records using SYNCSORT

PostPosted: Sun May 16, 2010 8:49 pm
by dick scherrer
Hello and welcome to the forum,

My first suggestion won't be one you want. . . :)

If Syncsort is to be consider as a programming replacement, you really need to get your system up to the current release. 1.2 is now rather old - thus not having the new features/capability . . .

On the good side, there are examples of many solutions posted in the forum (both in this Syncsort part of the forum as well as the DFSORT part of the forum). We also have participation from Syncsort development/support (and from DFSORT).

Re: Reformat/Create Records using SYNCSORT

PostPosted: Sun May 16, 2010 8:59 pm
by Quikplay
Thanks for your feedback. Admittedly, this is an odd approach to reformulating data, but it's the challenge I've been handed and I'm limited to working with the versions of SyncSort that our Systems Group is kind enough to install. I'm part of one of many applications that operate on our mainframe and therefore have no baring on what software versions that get installed.

Still - any approach suggestions would be appreciated. I've scanned the forums and haven't come across much to help, so I'm hoping there are a few subject matter experts that can apply out-of-the-box thinking to this issue.

Thanks,
John