IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Sync logic between VSAM files and DB2 tables


IBM Mainframe Forums -> COBOL Programming
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
murali.andaluri

New User


Joined: 07 Jun 2013
Posts: 23
Location: USA

PostPosted: Tue Aug 25, 2020 1:01 pm
Reply with quote

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
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai

PostPosted: Tue Aug 25, 2020 2:39 pm
Reply with quote

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
View user's profile Send private message
Garry Carroll

Senior Member


Joined: 08 May 2006
Posts: 1113
Location: Dublin, Ireland

PostPosted: Tue Aug 25, 2020 4:28 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2790
Location: NYC,USA

PostPosted: Tue Aug 25, 2020 8:16 pm
Reply with quote

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
View user's profile Send private message
murali.andaluri

New User


Joined: 07 Jun 2013
Posts: 23
Location: USA

PostPosted: Tue Aug 25, 2020 9:29 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2790
Location: NYC,USA

PostPosted: Tue Aug 25, 2020 10:07 pm
Reply with quote

which solution though? let unload utility and idcams worry about millions rows. try it.
Back to top
View user's profile Send private message
sergeyken
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1206

PostPosted: Tue Aug 25, 2020 11:38 pm
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10801
Location: italy

PostPosted: Wed Aug 26, 2020 12:39 am
Reply with quote

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
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai

PostPosted: Wed Aug 26, 2020 9:30 am
Reply with quote

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
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai

PostPosted: Wed Aug 26, 2020 9:34 am
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> COBOL Programming

 


Similar Topics
Topic Forum Replies
No new posts How to: PK does not exist in several ... DB2 5
No new posts Read two files CLIST & REXX 4
No new posts Map VSAM Dataset to a Table/Tablespace DB2 5
No new posts Compare 2 files replace data in 1st f... DFSORT/ICETOOL 4
No new posts Merging 2 files but ignore duplicate... DFSORT/ICETOOL 1
Search our Forums:

Back to Top