jensen@skinfaxe.diku.dk (J|rgen Jensen) (09/23/90)
Dated the 31st of Aug 1990 I posted a query concerning the feasibility / advisability of sharing a table between several individual Informix-databases. bochner@endor.harvard.edu (Harry Bochner) posted a follow-up, commenting on the general question: Whether one central database with individualisations incorporated is to be preferred over mulitiple individual databases. He concluded that it depends on what sort of personalization you're talking about, but in the common case one central database would be the best choice, when faced with the need to implement general enhancements. In this particular situation I'm talking about here, the individualised databases are really personally developed applications -- "spread-sheet-like" so to speak. But they do have to do look-ups in one or two master-tables that are common to the applications. Until now this has been accomplished by periodically re-loading these tables into each database from ascii-files dumped from a mainframe DBMS. As the Informix-version in question is C-ISAM-based I begat the (wild?) idea that it should somehow be possible to share read-access to the master-tables by including the path-name to the table's index- and data-files in the sys-info of each individualised database. That was the basis for my query. clh@tfic.bc.ca (Chris Hermansen) was able to tell me that the statement I had been thinking about was an extension to CREATE TABLE as in: create table hooey (i integer, f float) in "/tmp/snarf.dbs" This syntax was certified to be valid at least for Informix ISQL 2.10.xx running on Sun SS1s and 3/60s. And it was indeed the statement that had been lurking in the back of my mind. Chris also pointed out, however, that it wouldn't solve my problem of getting the *other* databases to know about the table. it won't work to do the "create table in ..." again, one must suppose. I hadn't thought it out this far :-( The only solution in this direction seems to be to manipulate the sys-tables in the other databases. Experiments ought to show whether or not the sys-info would have to be updated on a continuing basis, in which case the whole thing probably would not be worth the trouble. But I seem to remember that in the Informix-version I'm talking about here, that "number of rows" and similar dynamic sys-info isn't actually *used* for look-ups. It would be somewhat dirty to depend on this, of course.) As I am writing this it strikes me that I / we have't been thinking about multi-user access problems yet. When the master-tables are being updated, what would happen to an individualised database in the middle of a look-up on what it thinks is its own table? The master-tables are fairly stable, of course, but ... I had better back out of this before it's too late (-. . . . . . . . . . . . . . . . . . . . . . . . . . . j e n s e n (jensen@diku.dk)