Evan,
my company provide a federated solution to this with impressive performance.
For this reason we have a deal with SAP as an embedded technology to do
federated access to ERP & other data within Netweaver.

It would be very simple to install and federate - we have a graphical data
modeling tool to make it easy. I'd expect to be able to show results within
a few minutes of installation, which takes about 40 minutes. You may not
need it but we not only create the views but allow you to write to the
federated view too - something that DB2 does not allow at all.

In particular we will delegate query functions to the place where they are
most efficiently execute and you will not have to make ANY changes to ANY of
your queries, tables or databases.

You can see the plan in our product too & will quickly see the level of
extra expertise we have in this area which is our speciality.

I'd be interested to hear more & speak of our customer's experiences.

Regards,
David Penney
[url]www.metamatrix.com[/url]


"Evan Smith" <esmith2112hotmail.com> wrote in message
news:23658335.0305141132.636bab70posting.google.c om...
> I don't seem to find much in the docs about federated DB design. I'm
> hoping someone can point me in the right direction.
>
> I have a scenario where we have a live production database and an
> archive database that has the same structure as the production
> database. On a regular basis rows are moved from production to
> archive.
>
> Most queries can happily exist attached either to the production or
> the archive database, but we have cases where a query needs to get a
> consolidated report looking at both sets of data. Originally, we put
> the burden on the app to consolidate the results, but it was getting
> too complex. Instead we created a federated database where federated
> views were created for all tables that had data on both sides like
> this:
>
> CREATE FEDERATED VIEW MYSCHEMA.MYTABLE AS
> SELECT * FROM HISTORY.MYTABLE
> UNION ALL
> SELECT * FROM PRODUCTION.MYTABLE
>
> All of the mechanics of this work fine. Servers, nicknames, etc. all
> exist and are accessible. Queries submitted against the federated view
> do return results, however the performance is abysmal. I knew we would
> take some performance hit, but what normally takes seconds on either
> standalone database, now takes over 20 minutes on the federated
> database.
>
> I looked at the plans for some of the queries on the federated DB and
> not surprisingly, they're pretty ugly. Does anyone know of any tips,
> tricks, strategies to do a better design? Are there any links buried
> deep on the IBM site that give a good background on the subject
> matter?
>
> Thanks in advance,
> Evan