I'd like to have early feedback of a new architecture that I'm thinking for our customers that has db2 dbms.
I have many copies of the same db schema around the globe; suppose that each belongs to a particular entity.
Suppose, just for example, that their names are remote_schema1, remote_schema2 and remote_schema3.
I have one central site that, via cooperation, mantains a copy of every remote db (i.e. local_schema1, local_schema2 and +local_schema3+). These are local in the sense that they are inside a LAN, not on the same workstation.
I would like to create a new one, say federated_schema.
A client that connects to federated_schema sees the data of both local_schema1, local_schema2 and local_schema3.
So if I made a query to federated_schema.tableA, that query retrieves the data from both local_schema1.tableA, local_schema2.tableA and local_schema3.tableA.
First question: is this schenario feasible with db2 build-in federation capabilities?
After that, I could have a problem: it is absolutely possibile that local_schema1.tableA and local_schema2.tableA has a record with the same key. Suppose, for example, that it is a auto-generated number,
So a record with primary key 1000 refers to two different records, one from local_schema1 and one from local_schema2.
I'm thinking to augment the primary key of federated_schema.tableA with an "origin" column.
So if a record belongs to local_schema1.tableA, its primary key is (1000, 'schema1'), if it belongs to local_schema2.tableA, its primary key is (1000, 'schema2') and so on
Second question: does this solution sound good or do you propose a better one for differentiating these records?
Third question: if I make a change to record whos primary key is (1000, 'schema1'), can I "send back" that change to the table local_schema1.tableA?
Thanks for any feedback and useful information you can give to me
Just create a user-view where you "union all" the federated tables. Your proposal about the key-column is good, but do not call that a primary-key because PK's are for tables, not for views. The "send back" issue can be solved with an "instead of" trigger on the view. Code you logic there.