Portal | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Sync logic between VSAM files and DB2 tables

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
View previous topic :: :: View next topic  
Author Message
murali.andaluri

New User


Joined: 07 Jun 2013
Posts: 16
Location: USA

PostPosted: Tue Aug 25, 2020 1:01 pm    Post subject: Sync logic between VSAM files and DB2 tables
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    Post subject:
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: 1088
Location: Dublin, Ireland

PostPosted: Tue Aug 25, 2020 4:28 pm    Post subject:
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: 2467
Location: NY,USA

PostPosted: Tue Aug 25, 2020 8:16 pm    Post subject:
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: 16
Location: USA

PostPosted: Tue Aug 25, 2020 9:29 pm    Post subject: Reply to: Sync logic between VSAM files and DB2 tables
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: 2467
Location: NY,USA

PostPosted: Tue Aug 25, 2020 10:07 pm    Post subject:
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

Active Member


Joined: 29 Apr 2008
Posts: 825
Location: Maryland

PostPosted: Tue Aug 25, 2020 11:38 pm    Post subject: Re: Reply to: Sync logic between VSAM files and DB2 tables
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

Senior Member


Joined: 14 Mar 2007
Posts: 10700
Location: italy

PostPosted: Wed Aug 26, 2020 12:39 am    Post subject: Reply to: Sync logic between VSAM files and DB2 tables
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    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> COBOL Programming All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Undesired result while joining files. Khupe SYNCSORT 3 Tue Sep 22, 2020 8:05 pm
No new posts VSAM Alter Command - Cylinder Extent ... Need2Know JCL & VSAM 3 Tue Sep 22, 2020 3:02 pm
No new posts RPLDDDD field for extended VSAM - ESDS amitsjcntl JCL & VSAM 0 Tue Sep 22, 2020 1:45 pm
No new posts Dynamically read input files using BP... Srinivas B COBOL Programming 20 Sat Sep 05, 2020 1:48 pm
No new posts DFDSS msg ADR485E when restoring VSAM... Alan Playford JCL & VSAM 5 Sat Aug 29, 2020 9:33 pm

Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us