Subject Re: [IB-Architect] Select across multiple database and XA compatability....
Author Dmitry Kuzmenko
Hello, Paul!

Paul Reeves wrote:

> interest and the time to implement a feature. As with software development
> generally a lot is down to someone wanting something enough to actually pay
> for it, either with their time or their money. Which leads me to the question
> - What is the business case for this feature? Can a similar result be achieved
> in another way?

I saw different variants of what people want calling it "heterogeneous joins":

1. 2 or more databases with common reference tables.
This can be emulated duplicating reference tables across different databases
by replication. Reference table edits must be performed in one (main) database only

2. big database with operational and archive information, that must be qieried
together. The best solution is to separate operational and archive data, make
backup and restore each of them separately.
This can be made by movind operational data from time to time to archive database.
But, this does not allow to make queries on both databases.

3. backup and restore of different (big) tables. Reference tables are changed rarely, so
they do not need to move into backup each time. Maybe there 2 solutions: a) do
"partial" backup for different tables, b) allow to specify what objects can
be stored in each secondary DB file, and allow backup of each secondary file
Now can't be emulated, since backup does copy of all data in database
(and restore it all, too). The only way again is replication, where different
tables are gathered into secondary databases - these databases must be "backed-up"
instead of main database.

Also there are two kinds of heterogeneous joins - first are performed on databases on the
same server, second - on different servers. Second is much harder for network and engine.
First is simplier since databases are being operated by one server, but it looks simplier only
for SuperServer, not for Classic (wich operates with only one database at a time).

That's all I have to say, you can document this if somebody will ask for such features.
Each point can be extended and it will be easy to understand (at least)
how it can be implemented. :-)

p.s. you see, that most task can be done with replication. But it is not a panacea.
Replication makes database a bit complicated - new triggers, new servers, performance
slowdown, and lot of other things looking like problems at the first time.
I do not insist somebody to do all I wrote in FB immediately :-), but there must be
good understanding of production system architectures and their needs.

Dmitry Kuzmenko, Epsylon Technologies.