Re: Create synonym on Informix SE 7.22 on Unix

JL Mark's response reminded me of a trick I used to do in SE 2.x (I think) way back when to simulate synonyms. Dont know if it will work on 7.x SE (dont have access to my home box where I have it installed), but here it is... Let us say our requirement is to create a synonym for a table TB in database B from database A. Our synonym is called TA. 1) Create a table TA on database A with the identical structure as table TB on database B. 2) In systables, there would be an entry dirpath or some such which contains the full path name to the ISAM file which represents the table TA in database A. Not sure if it is dirpath (was a long time ago), but here is the SQL to run on database B to find out the location of table TB... select dirpath from systables where tabname = "TB";3) On database TA, update the dirpath entry in systables to point to the ISAM file representing table B.TB. Thus on database A, run this SQL... update systables set dirpath = "/value/you/found/in/previous/sql" where tabname = "TA" I know this used to work since I had a data mart (didn't call it by that name in those days, though :-)) loaded off an OLTP database which had synonyms to the OLTP database from the data mart. And that was before I knew thats system catalog tables were never to be touched :-). HTH Sujit "Mark D. Stock" <mdstock@mydas.freeserve.co.uk> on 03/09/2000 12:55:28 PM To: JL <jlr@voila.fr> cc: informix-list@iiug.org Subject: Re: Create synonym on Informix SE 7.22 on Unix JL wrote: > > Hi, > > I've got two databases on the same database server and I need to create a > synonym between my two databases but when I want to create it, I ever have > an error : 554 syntax disallowed in this database server. > > I've tried many differents syntax but it doesn't work and the > documentation doesn't help me at all. > > Thanks to give me ideas if you can. Well guess what? This syntax isn't supported in SE. You can only create synonyms to local tables. You can create synonyms to remote tables in OnLine or IDS. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /| | http://www.informix.com http://www.informixhandbook.com |///// / //| | http://www.iiug.org +-----------------------------------+//// / ///| | |What year 2000 bug? year 2000 bug? |/// / ////| | |year 2000 bug? year 2000 bug? year |// / /////| | |2000 bug? year 2000 bug? year 1900 |/ ////////| +----------------------+-----------------------------------+-----------+

Sujit.Pal@bankofamerica.com wrote: > JL > > Mark's response reminded me of a trick I used to do in SE 2.x (I think) way back > when to simulate synonyms. Dont know if it will work on 7.x SE (dont have access > to my home box where I have it installed), but here it is... > > Let us say our requirement is to create a synonym for a table TB in database B > from database A. Our synonym is called TA. > > 1) Create a table TA on database A with the identical structure as table TB on > database B. > 2) In systables, there would be an entry dirpath or some such which contains the > full path name to the ISAM file which represents the table TA in database A. Not > sure if it is dirpath (was a long time ago), but here is the SQL to run on > database B to find out the location of table TB... > select dirpath from systables where tabname = "TB";> 3) On database TA, update the dirpath entry in systables to point to the ISAM > file representing table B.TB. Thus on database A, run this SQL... > update systables set dirpath = "/value/you/found/in/previous/sql" > where tabname = "TA"> > I know this used to work since I had a data mart (didn't call it by that name in > those days, though :-)) loaded off an OLTP database which had synonyms to the > OLTP database from the data mart. And that was before I knew thats system > catalog tables were never to be touched :-). You can achieve the same result without messing with the system catalog, per se, using links, hard or symbolic. Messing with the SE catalog is less error prone than messing with the OnLine catalog. The OnLine catalog often contains data which is also stored somewhere else, out in SysMaster land. Changing it in the system catalog doesn't affect the SysMaster information, so you run into troubles. With SE, the system catalog is all there is... > "Mark D. Stock" <mdstock@mydas.freeserve.co.uk> on 03/09/2000 12:55:28 PM > > To: JL <jlr@voila.fr> > cc: informix-list@iiug.org > Subject: Re: Create synonym on Informix SE 7.22 on Unix > > JL wrote: > > I've got two databases on the same database server and I need to create a > > synonym between my two databases but when I want to create it, I ever have > > an error : 554 syntax disallowed in this database server. > > > > I've tried many differents syntax but it doesn't work and the > > documentation doesn't help me at all. > > > > Thanks to give me ideas if you can. > > Well guess what? This syntax isn't supported in SE. You can only create > synonyms to local tables. You can create synonyms to remote tables in > OnLine or IDS. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.95 -- see http://www.perl.com/CPAN #include <disclaimer.h>