I have two files VB 490 (File B) & VB 500 (File A). We use SYNCSORT here (hope I am not posting in the wrong forum).
Topic moved to proper part of the forum.
File A - File with Current data (Col1, Col2, Col3 ... VALID_FROM, VALID_TO) - VALID_FROM & VALID_TO have null values.
File B - File with History + Current data (Col1, Col2, Col3 ... LOAD_DATE - Date when data is loaded) - This file also has duplicates
Challenge: File A should end up having current data PLUS History data only if there's any change in any column for an existing current record.
Also, VALID_FROM date in File A gets the value of LOAD_DATE and VALID_TO remains NULL. However, if there is any change that took place
to the second row - remember, any change to any column - new row will be inserted into File A and VALID_TO (of old row) gets LOAD_DATE - 1 associated to the new row
LOAD_DATE should be inserted to VALID_FROM of the new row.
For example:
Now - Current
File A:
Col1 Col2 Col3 Col4 VALID_FROM VALID_TO
1 a A ASDF
2 b B LKJH
3 c C MNBV
File B:
Col1 Col2 Col3 Col4 LOAD_DATE
1 a A ASDF 01/01/2009
1 a A ASDD 01/15/2009
2 b B LKJH 01/01/2009
2 b B LKJH 01/15/2009
3 c C MNBV 01/01/2009
3 c C CERT 01/15/2009
Later - Future
File A:
Col1 Col2 Col3 Col4 VALID_FROM VALID_TO
1 a A ASDF 01/01/0001 01/14/2009
1 a A ASDD 01/15/2009 NULL
2 b B LKJH 01/01/0001 NULL
3 c C MNBV 01/01/0001 01/14/2009
3 c C CERT 01/15/2009 NULL
This could be tough to get done in just one sort step. If it is tough, then I would request someone to help me arrive at an intermediate solution.
File B => I want to compare Col1 to Col4, if there is no difference, I want to write to a new file along with the LOAD_DATE (which I don't want to compare).
Perhaps, I will think of a different approach from that point. Atleast, that will get rid of duplicates and keep my LOAD_DATE.
Please let me know if you think I should provide any more information.
This is how actual data looks:
File A:
1aAASDF
2bBLKJH
3cCMNBV
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
Hello,
Quote:
This could be tough to get done in just one sort step. If it is tough, then I would request someone to help me arrive at an intermediate solution.
Sorry, but this makes no sense in any number of sort steps. . .
Even if someone posts a "solution" it will not be complete as i suspect the posted requirement is not complete. On well-run systems code of this type also produces an audit trail - i see none.
When some "magic bullet" sort control shows up, it is most likely that no one on your system will understand how it works - making it impossible to maintain. There is also the high likelihood that additional processing requirements (when discovered) will be beyond the "programming capability" of the utility.
I would expect management to be quite perturbed to be told that the quick fix (i.e. this "sort" requirement) that needs an enhancement or fix will take days or weeks because a real program needs to be created from scratch.
If you didn't comprehend the problem correctly, then it has to be my mistake as I may not have stated it in the right way.
The original requirement is in DB2, but unfortunately I can use either DB2 or SYNCSORT. Getting this done completely in an SQL query looks to be impossible. Even if we decide to break it down and deal with pieces independently, that could bring down DB2 to its knees.
That's when we decided probably SORT could be a better option.
We have been storing only current data, and now business asked us to provide them with history data too. For the past 6 months, we have been storing data in a DB2 table (LOAD RESUME), which effectively means data is being added/appended to existing data - whether or not it is changed. It has been decided that now is the time to 'merge' and get rid of redundant data and in the process 'adjust' those dates.
I believe that I gave you all the required information. If you are interested in terms of the example again, I will be glad to explain in the best possible way.
Please let me know in any case.
PS: OP was in DFSORT/ICETOOL, but now it has been moved to JCL. Do you have any idea why?
