IDS Forum

Re: Require Info about Create Table

Posted By: Art S. Kagel (Oninit)
Date: Thursday, 10 April 2008, at 11:10 a.m.

In Response To: Re: Require Info about Create Table (VIKAS HIVARKAR)

VIKAS HIVARKAR wrote:
> The synonym idea is great Mr Kagel !
>
> I never got a real chance to work with synonyms .Pls tell me how does it
> works.
>
> This is what i did.
> create synonym "informix".archive for db1:"informix".archive;
> and got a error saying that the db2 is not in logging mode so i changed its
>

Correct, that goes back to my point that you cannot perform distributed
queries between databases with different logging modes.

> loggin mode to Buffered as that of db1 & again executed the above create
> statement .
> the synonym was created and select count(*) from archive on db2 showed 10000
> rows.
> but when i did dbschema -d db2 -t archive there was no schema in the output.
>

Because archive is not a table in db2 it is only a synonym for a remote
table. If you run dbschema -d db2 on the whole database, synonyms are
printed out, just not when you run it at the table level.

> correct me if i am wrong on this . synonym created only points to the real
> table on db1 and no physical table is created on db2.
>

Correct. It is just an entry in the syssynonyms table.

> Now if this is the case then please tell me what will happen if there are DML
> operations performed on the actuall archive table in db1 .
> Does any changed done to archive table on db1 will be seen on its synonym ie
> archive table on db2.if yes then its great for my cause.
>
> Am i correct on this ,if not please expalin me how does synonyms work .
>

Yes, users in db2 who query db2:archive - the synonym - will immediately
see any changes in db1:archive - the real table.

> Also what if the 2 databases are on different machines can we still use these
> sysnonym option ( considering the fact that these two databases have entry of
> each other in sqlhosts and /etc/services).
>
>
You can define a synonym on a table in a database on a remote instance.
No problem . The syntax is almost identical, you just have to specify
the servername which you do not have to do if db1 & db2 are colocated on
the same instance:

create synonym "informix".archive for
db1@some_instance_name:"informix".archive;

Art S. Kagel
Oninit

Messages In This Thread

• Require Info about Create Table
  VIKAS HIVARKAR -- Thursday, 10 April 2008, at 8:47 a.m.
  • RE: Require Info about Create Table
    Mark Tyrer -- Thursday, 10 April 2008, at 9:05 a.m.
  • Re: Require Info about Create Table
    Art S. Kagel (Oninit) -- Thursday, 10 April 2008, at 9:30 a.m.
    • Re: Require Info about Create Table
      VIKAS HIVARKAR -- Thursday, 10 April 2008, at 10:46 a.m.
      • Re: Require Info about Create Table
        Art S. Kagel (Oninit) -- Thursday, 10 April 2008, at 11:10 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.