|
View previous topic :: View next topic
|
| Author |
Message |
murali.andaluri
New User
Joined: 07 Jun 2013
Posts: 23
Location: USA
|
|
|
|
Hi,
I working on new requirement. I want best ideas from you.
Requirement: We have two processes. First process is updating DB2 tables. Once tables are updating we are syncing this Db2 data into VSAM files through 2nd process on daily basis. But somehow VSAM files are not 100% synch with Db2 tables data.
So my task is to create a framework in such a way for any given table, it should compare table data and VSAM file data. Then all non synch data from VSAM file should be synced to DB2 table data.
Table and VSAM data is huge (in millions).
Can anyone suggest best ideas to achieve this?
Thanks in advance. |
|
| Back to top |
|
 |
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Hi,
I have worked on a similar requirement what we followed is
1. Unload DB2 table, extract VSAM file into flat files. Here you will need to take care of transformations if any you guys are doing between VSAM and DB2
2. Sort the flat files on a similar key and feed both the files to ccobol file matching program
3. the cobol program will create 6 files Missing in Db2, Extra in Db2, not matching in Db2, missing in VSAM, Extra in VSAM, not natching in VSAM
4, Using these files you will need to update, insert or delete the records from DB2 tables
5. For this Inset/Delete/Update we had different process of using dynamic SQL
I tried to accommodate the logic as much as I can, but its a very vast design so please let us know if any specific questions
Thanks,
Chandan |
|
| Back to top |
|
|
Garry Carroll
Senior Member
Joined: 08 May 2006
Posts: 1205
Location: Dublin, Ireland
|
|
|
|
| Quote: |
| 3. the cobol program will create 6 files Missing in Db2, Extra in Db2, not matching in Db2, missing in VSAM, Extra in VSAM, not natching in VSAM |
Six files seems excessive. Three datasets should be all that's needed and these can be obtained without writing a Colol program but by usng the JOINKEYS control cards provided by your SORT product. This can identify records that are only in the first of two input datasets, only in the second of the two input datasets and those that are in both input datasets.
The records only in the first input can be added to the second to synchronise and/or the records only in the second input can be applied to the first. The third output, records that are in both, requires no action.
Garry. . |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
How about you find why they aren't in sync when they suppose to and fix that part instead of patch work later?
Second, You can write a simple job which will Unload DB2, reformat to VSAM structure and do a REPRO. This will replace and make exact same as DB2 instead of spending time comparing and then inserting only missing once. |
|
| Back to top |
|
|
murali.andaluri
New User
Joined: 07 Jun 2013
Posts: 23
Location: USA
|
|
|
|
Hi All,
Thanks for your inputs.
I was also thinking similar sort of solution, but problem is tables contains millions of rows. So, handling millions of records through flat files are too difficult.
So, I am thinking about alternative solution. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| which solution though? let unload utility and idcams worry about millions rows. try it. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2144
Location: USA
|
|
|
|
| murali.andaluri wrote: |
Hi All,
Thanks for your inputs.
I was also thinking similar sort of solution, but problem is tables contains millions of rows. So, handling millions of records through flat files are too difficult.
So, I am thinking about alternative solution. |
There are no such things as miracles.(C)
If you really need to verify and to synchronize the tables of billions of rows, then you have to handle those billions of rows, and there is no workaround.
Using professionally optimized tools, like HPU utility, standard SORT utilities, REPRO function of IDCAMS, etc. would really help in dealing with huge amounts of data. |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10888
Location: italy
|
|
|
|
| Quote: |
| But somehow VSAM files are not 100% synch with Db2 tables data. |
why not try to understand and fix the somehow first
rather than setting up an expensive FIX framework ?
anyway I do not see any relation to COBOL programming |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
| Garry Carroll wrote: |
| Quote: |
| 3. the cobol program will create 6 files Missing in Db2, Extra in Db2, not matching in Db2, missing in VSAM, Extra in VSAM, not natching in VSAM |
Six files seems excessive. Three datasets should be all that's needed and these can be obtained without writing a Colol program but by usng the JOINKEYS control cards provided by your SORT product. This can identify records that are only in the first of two input datasets, only in the second of the two input datasets and those that are in both input datasets.
The records only in the first input can be added to the second to synchronise and/or the records only in the second input can be applied to the first. The third output, records that are in both, requires no action.
Garry. . |
Yes I agree we created those 6 files for some reporting purpose, and about using join keys those were useful for missing and extra but to get field level mismatches for around 200 fields SORT Join did not seems to be feasible and the reporting was easier in cobol |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
| Rohit Umarjikar wrote: |
How about you find why they aren't in sync when they suppose to and fix that part instead of patch work later?
Second, You can write a simple job which will Unload DB2, reformat to VSAM structure and do a REPRO. This will replace and make exact same as DB2 instead of spending time comparing and then inserting only missing once. |
Totally agree with you Rohit..
For our case there were many subsytems interacting with main system.. Our ultimate aim was to replace all VSAM files with DB2, so we had this re-synchronize logic for time being till we replace VSAM file with DB2 in actual programs..
I just told one piece of our entire architecture as it was suiting the OP's requirement |
|
| Back to top |
|
|
|
|
