DB2 federated architecture

[ivenuti]

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.

Desiderata:A client that connects to federated_schema sees the data of both local_schema1, local_schema2 and local_schema3.I'd like that also updates and deletes can be done agaist local_schemaN (origin of the record). No matter if I can't do inserts.



My first solution uses a serie of nickname, one for each table in local_schemaN:

CREATE NICKNAME local_federated.tableA_1 FOR remote1.schema1.tableA
CREATE NICKNAME local_federated.tableA_2 FOR remote2.schema2.tableA
CREATE NICKNAME local_federated.tableA_3 FOR remote3.schema3.tableA
...
 

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 key becomes (1000, 'schema1'), if it belongs to local_schema2.tableA, its key becomes (1000, 'schema2') and so on

Here is a code that I'd like to write for this scenario:

CREATE VIEW my.tableA(k, c1, c2, c3) AS
        SELECT '1', c1, c2, c3 FROM  local_federated.tableA_1
    UNION ALL
        SELECT '2', c1, c2, c3 FROM  local_federated.tableA_2
    UNION ALL
        SELECT '3', c1, c2, c3 FROM  local_federated.tableA_3
 

Unfortunatly, reading the documentation, I can see that "Federated views that are created from more than one nicknamed data source object are read-only views." (see pag. 141 of this doc, for example).
Also the triggers can't be done with such federeted view; isn't it?

Any idea how to go over this limit? (maybe another design of my db could be fine!!)

Thanks for any feedback and useful information you can give to me