3

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.

Improve this question
7
  • 2
    I don't think there is a good solution to maintain referential integrity with heterogeneous distributed databases at the DBMS layer. I'm not sure I understand what you mean that no read/write access is needed from/to DB2 and SQL Server. Doesn't the logical foreign key relationship imply there is read/write access needed, or are you implying this is done in the application layer?
    – Dan Guzman
    Commented Jul 8, 2015 at 12:24
  • @DanGuzman I mean I think it is all done in the application.I don't think we have any explicit joins or anything like that that would need to read or write from one DB to the other. There might not be a good solution, but which could be considered the least evil?
    – ChrisC
    Commented Jul 8, 2015 at 12:32
  • Have you considered using Entity Framework with DB2 for i?
    – WarrenT
    Commented Jul 9, 2015 at 6:14
  • @WarrenT yes, it is something I'm thinking about for some other stuff, but ultimately it doesn't really solve this problem. Writing to one or the other would work fine, but I would imagine that I would still have to have different contexts for the different DBs, and the foreign key issue would still be there. Unless I am missing something?
    – ChrisC
    Commented Jul 9, 2015 at 11:40
  • I was thinking perhaps everything could be stored on DB2, but that might not be a feasible direction if you have other things that may still need to be on SQL Server. But there should be a way to sync some tables, with some manageable amount of lag perhaps.
    – WarrenT
    Commented Jul 9, 2015 at 13:55

1 Answer 1

Reset to default
1

If the foreign key constraints don't point to any other table inside of originating database (ie, if SQL Server doesn't point to SQL Server, etc.), then I would drop those foreign keys. They aren't useful.

If they do point to other tables, then perhaps you do have to copy what data is needed just to satisfy the constraint. (Ugh). Either that or re-consider that relationship and possibly drop the foreign key constraint again.

Another thing to look into (at least on DB2 for i) would be informational constraints. I know DB2 LUW had them. They were non-enforced constraints. They helped the optimizer when choosing query plans, but were not enforced as an actual constraint (you could violate them). Of course, a side effect of those is that sometimes you could miss data you wanted due to the informational constraint.

And not sure if SQL Server has something similar or not. I am still learning that platform.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.