Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Can I store a value and based on which modify another value

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
dinesh_deadman
Warnings : 1

New User


Joined: 10 Aug 2007
Posts: 38
Location: Chicago

PostPosted: Wed Jan 06, 2010 3:44 am    Post subject: Can I store a value and based on which modify another value
Reply with quote

Hello:

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

File B:
1aAASDF01/01/2009
1aAASDD01/15/2009
2bBLKJH01/01/2009
2bBLKJH01/15/2009
3cCMNBV01/01/2009
3cCCERT01/15/2009

Result Set:
1aAASDF01/01/000101/14/2009
1aAASDD01/15/2009NULL
2bBLKJH01/01/0001NULL
3cCMNBV01/01/000101/14/2009
3cCCERT01/15/2009NULL

-Dinesh
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jan 06, 2010 4:01 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dinesh_deadman
Warnings : 1

New User


Joined: 10 Aug 2007
Posts: 38
Location: Chicago

PostPosted: Wed Jan 06, 2010 8:36 am    Post subject: Reply to: Can I store a value and based on which modify ano
Reply with quote

Hello Dick,

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?

-Dinesh
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Wed Jan 06, 2010 10:45 am    Post subject:
Reply with quote

Quote:
PS: OP was in DFSORT/ICETOOL, but now it has been moved to JCL. Do you have any idea why?
Because you are using Syncsort, not DFSORT.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Group Data based on a key Arun Raj DFSORT/ICETOOL 7 Thu Apr 27, 2017 11:29 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts Inserting records based on conditions vickey_dw DFSORT/ICETOOL 9 Wed Feb 22, 2017 1:33 pm
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us