DB2 z/OS - Replication Between DB2 z/OS and MS SQL Server (via Microsoft Dynamics)

Bill Gallagher

DB2 z/OS - Replication Between DB2 z/OS and MS SQL Server (via Microsoft Dynamics)
This may be a unique requirement, but I thought I'd throw it out here for thoughts and on the unlikely chance that somebody else has done (or tried to do) this.

We are running DB2 11 on z/OS.

We have a legacy application that we will soon be rewriting and replacing with a new application. The old application is a client server app, approximately 20 years old, with the backend database residing on the mainframe in DB2 11. The new application will be built using a Microsoft Dynamics framework, with the database residing in SQL Server 2016.

The rewrite is going to be done incrementally, one application component at a time. As one component is rewritten and becomes operational in the new application, the corresponding legacy component will be deactivated in the old application. So throughout the course of the project, both applications will be up and running. Over the course of time, as components are rewritten and migrated from old to new, the new application will become more heavily used and the old application less heavily used, until the completion of the project. At that point, the old application will be completely shut down and decommissioned.

The challenge that we are facing is that there will be some data that will need to exist in both places at once (DB2 and MS Dynamics/SQL). For the most part, one system or the other will be the "book of record", or "source of truth" for some particular pieces of data. As it changes in the "book of record" database it may need to be synchronized to the other database, perhaps for some downstream processing that has not yet been rewritten/migrated. But there may also be some unique instances where some data entities could be updated on either side, and then must be synced up on the other.

As if that's not challenging enough, the biggest wrench that's being thrown into the machinery here is the use of Microsoft Dynamics. The data (residing in SQL Server) is abstracted by Dynamics to the point where it's not practical to try to access the data natively in SQL Server. In fact, direct access in terms of insert/update/delete will likely "break" something with Dynamics. All data access will need to be done through an API in Dynamics, which will then access (read or update) the data in the database. This makes the bi-directional synchronization of the two databases even more difficult, because we can't just go "DB2 <-> SQL Server", where both databases are built from traditional third normal form data models (which the legacy DB2 database is), but we have to synch between a 3NF database and an abstracted Dynamics entity that we can only access via an API.

It's been suggested that Microsoft's SSIS product can connect to both DB2 z/OS and to a Dynamics entity via a Dynamics supplied API. I'm not quite sure if and how that would work, if SSIS can be used to perform bi-directional data movement (i.e. DB2 to Dynamics API, and Dynamics API to DB2).

I feel that I've been rambling on here.

Any thoughts from anybody on the list? Anybody have any experience trying to do something like this?

Thanks.

Bill Gallagher
DB2 Database Administrator
State of Connecticut


Daniel Luksetich

DB2 z/OS - Replication Between DB2 z/OS and MS SQL Server (via Microsoft Dynamics)
(in response to Bill Gallagher)


How interesting. I literally began working with SSIS just a few hours
ago. I haven't been able to get it working properly with Db2 11 for
z/OS, but it seems to be working well with Db2 11.1 for Windows. I was
able to move a Db2 table to SQL Server in no time at all. I can read Db2
11 for z/OS tables, but can't place them in a data flow. Also, while
SSIS seems to recognize LOBs in Db2 11.1 for Windows it does not seem to
understand LOBs in Db2 11 for z/OS.

I think it is worth the time and effort to explore the possibility of
using SSIS.

Good luck!

Dan Luksetich

On 11.08.2017 11:25, GALLAGHER, WILLIAM wrote:

> This may be a unique requirement, but I thought I'd throw it out here for thoughts and on the unlikely chance that somebody else has done (or tried to do) this.
>
> We are running DB2 11 on z/OS.
>
> We have a legacy application that we will soon be rewriting and replacing with a new application. The old application is a client server app, approximately 20 years old, with the backend database residing on the mainframe in DB2 11. The new application will be built using a Microsoft Dynamics framework, with the database residing in SQL Server 2016.
>
> The rewrite is going to be done incrementally, one application component at a time. As one component is rewritten and becomes operational in the new application, the corresponding legacy component will be deactivated in the old application. So throughout the course of the project, both applications will be up and running. Over the course of time, as components are rewritten and migrated from old to new, the new application will become more heavily used and the old application less heavily used, until the completion of the project. At that point, the old application will be completely shut down and decommissioned.
>
> The challenge that we are facing is that there will be some data that will need to exist in both places at once (DB2 and MS Dynamics/SQL). For the most part, one system or the other will be the "book of record", or "source of truth" for some particular pieces of data. As it changes in the "book of record" database it may need to be synchronized to the other database, perhaps for some downstream processing that has not yet been rewritten/migrated. But there may also be some unique instances where some data entities could be updated on either side, and then must be synced up on the other.
>
> As if that's not challenging enough, the biggest wrench that's being thrown into the machinery here is the use of Microsoft Dynamics. The data (residing in SQL Server) is abstracted by Dynamics to the point where it's not practical to try to access the data natively in SQL Server. In fact, direct access in terms of insert/update/delete will likely "break" something with Dynamics. All data access will need to be done through an API in Dynamics, which will then access (read or update) the data in the database. This makes the bi-directional synchronization of the two databases even more difficult, because we can't just go "DB2 <-> SQL Server", where both databases are built from traditional third normal form data models (which the legacy DB2 database is), but we have to synch between a 3NF database and an abstracted Dynamics entity that we can only access via an API.
>
> It's been suggested that Microsoft's SSIS product can connect to both DB2 z/OS and to a Dynamics entity via a Dynamics supplied API. I'm not quite sure if and how that would work, if SSIS can be used to perform bi-directional data movement (i.e. DB2 to Dynamics API, and Dynamics API to DB2).
>
> I feel that I've been rambling on here.
>
> Any thoughts from anybody on the list? Anybody have any experience trying to do something like this?
>
> Thanks.
>
> Bill Gallagher
>
> DB2 Database Administrator
>
> State of Connecticut
>
> -----End Original Message-----


Links:
------
[1] http://www.idug.org/p/fo/st/?post=183675&amp;anc=p183675#p183675
[2] http://www.idug.org/p/fo/si/?topic=19
[3] http://www.idug.org/p/us/to/
[4] http://www.ESAIGroup.com/idug
[5] http://www.idug.org/p/cm/ld/fid=2