Connectivity between databases

I'm currently in the market for a Unix RDBMS, and the selected software needs to be able to communicate with other people's databases which will probably be from other vendors (Oracle and Sybase for certain). I was looking for insight as to how this would work, and whether people had any experience of how well the different database products work with other SQL databases. I want to hear from users--I've already talked to the vendors.

What we would be doing would be to compare our databases against other people's databases. The database would contain information about our scientific observations, and we would want to correlate our observations with the observations of other instruments--e.g. when were we pointed at the same target, etc. The users would *not* be storing or updating any data in the remote database--just looking at it. In fact, I don't really expect the user's to be writing to the local databases either, except for temporary tables used to store the results of queries.

However, the database software will only represent a small fraction of the activities that will be going on at our site (say 5-10%), so we don't want to put a lot of manpower into putting together a sophisticated distributed database. We have also looked at transaction managers, and have decided that those would be inappropriate since they require that everything can be expressed in well-understood and predefined transactions--we want to keep a lot of flexibility open in the system.

It seems that there are several ways to go about it:

1. The local database software could spawn a shell script which starts a session on the remote database which extracts the requested data and writes it to one or more flat ASCII files. These ASCII files are then imported to temporary tables in the local database and manipulated locally. This probably would be sufficient for our needs. Also, if we did this we could probably use any database package, since (I believe) any of them can spawn OS commands, and import/export ASCII text files. Does anybody have any experience working this way, and can comment on how well it works?
2. We could use gateway products from one vendor to another. I believe this requires buying additional software for at least one of the machines. I don't know how well these gateways work, and expect that the gateways from one vendor are different from those from another vendor. I'd be interested in hearing about people's experiences with gateways from Oracle, Ingres, Sybase, Informix, etc., and whether they think one is better than another.
3. We could use 3rd party front-ends which have their own gateways. I don't know much about this either--up to now I've only been studying the problem of the back end.

Thank you in advance,

Bill Thompson Received on Tue Nov 17 1992 - 22:09:00 CET