I've got a system that has a DB2 iSeries database. We are rewriting a portion of the system and implementing it using SQL Server and Entity Framework.
Originally, I was lead to believe that there wasn't much of a tie-in between the tables we are moving to SQL Server and the DB2 ones. However, I'm finding a few spots where there are foreign-key references across them.
Are there any standard ways of handling this kind of scenario, where we need to maintain foreign-key references across databases? I can think of a few options:
- Duplicate some of the data from SQL Server to DB2 on iSeries, enough to satisfy the foreign key constraints. I hate this idea not only because of the duplication of data but also because if we only duplicated the necessary data it would only exist to satisfy a constraint, but we couldn't do anything useful on it like JOIN to it.
- Remove the foreign key constraints from the iSeries and completely unlink the two databases. This has obvious issues, but thought I would throw it out there in case there are any benefits that I'm not seeing.
- Use some kind of database link to keep the two in sync. I know not how this would work, how it would perform, or even if it is possible to have foreign key constraints across a link. Perhaps this would need to be done with a trigger as I imagine a regular foreign key reference would not work in this case?
Does anybody see any other options, and what would be the best approach?
I don't believe there is anything on the DB2 side that would ever need to read/write anything to the SQL Server side, or vice versa, so we could keep the databases accesses from the application separate. I believe it is just to keep the foreign key references.