Hello,

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.

Have fun. Looks like an interesting assignment.

Reading the IBM® DB2 Universal Database™ - Federated Systems Guide - Version 8, I can read that "Federated views that are created from more than one nicknamed data source object are read-only views."

Does this limitation applies also to version 9 of db2?

Thank you so much for your feedback