Create DB2 view across 2 databases

Question

I have an application that needs to query two different DB2 databases for the exact same data. Is there any way to create a view that takes my query, executes it against both databases, combines the results, and send them back to my application?

Answer 1

Yes, sort of. We had to do something similar a few years back because one of our customers split their data across two DB2 instances but still wanted single queries that would get at them both (the reporting tool we were using could only connect to one instance).

From memory, it's a matter of:

• turning on federation support (needed for instance-to-instance communication).
• creating a wrapper with create wrapper so one DB2 instance knows how to connect to another.
• registering the other server with create server.
• using create user mapping to set up mapping of credentials between the two instances.
• creating an alias in the local instance for the remote table with create nickname.

From there, you would just create your view as something like:

select * from localtable union all select * from nickname;

and you should have rows from both tables.