Re: views on external databases

}From: bdixon@pcss.com (Bill Dixon) }Subject: views on external databases }Date: Mon, 18 Jul 1994 20:52:55 GMT }X-Informix-List-Id: <news.7660> } }We have Informix SE (v5.?) running on an IBM RS/6000. We have the }need to do something that we think is possible in Informix OnLine, }but is not possible in SE (we tried). Whenever we call Informix to }see if one of their products will do something, we always seem to get }the standard marketing answer, "of course it will, it will do }anything you want it to". (My appologies to non-marketing employees }of Informix :-) Could someone out there with access to OnLine please }confirm this for us? } }On page 11-25 of our SQL Tutorial manual, it says "INFORMIX-OnLine }permits you to base a view on tables and views in external }databases." Here is what we want to do, put as simply as possible: } }One database is called "main". It contains two tables, "customer" & }"state". The customer table has one record per customer. The state }table has one record per state (Alabama, Alaska, etc). } }Then there needs to be a second database, called "branch". This }database will also have a table called "customer", using the same }schema as the main database. However, since the states don't change }(short of a civil war), there is no need to duplicate that data. }Therefore, the branch database needs to contain a view, called }"state", on the state table in the main database. } }The sql statements we are using to try to set up the view on the branch }database are: } }> create view state_tbl ( }> state_code, }> state_name ) }> as select }> m.state_code, }> m.state_name }> from main:state_tbl m } }With SE I receive: }> Error 554: Syntax disallowed in this database server. Correct: (unless someone's change the rules while I wasn't looking) you cannot do distributed database access with SE; you can do remote database access using I-Net, but not access multiple databases simultaneously. (This ignores single machine tricks played with links, symbolic or otherwise.) }If this works the way we think it should, it should make no }difference which database a set of sql statements is run on, it }should run the same, without modification. I would do this (and do do this) with a synonym, rather than a view. I have a database called WTS which references another database called BUG, and WTS 'contains' all the BUG tables using synonyms such as: CREATE SYNONYM 'wtsdba'.bug FOR bug@pts:bug; CREATE SYNONYM 'wtsdba'.bug_family FOR bug@pts:bug_family; CREATE SYNONYM 'wtsdba'.bug_family_owner FOR bug@pts:bug_family_owner; However, there is no reason why a view wouldn't also work. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>