Oninit cdi / Archive
Talk to an expert →

RE: SELECT QUERY between 2 different databases on the same server

Posted in 2000

Topics: General Discussion

Thiel Franky — — source: Informix-list mailing list archive (1991-1998)

Is it possible to do that if the two databases are not on the same server ? Regards, Franky -----Oorspronkelijk bericht----- Van: Martin, Wayne E. [mailto:WMartin@kmart.com] Verzonden: donderdag 15 juni 2000 15:22 Aan: 'mars1972@my-deja.com'; informix-list@iiug.org Onderwerp: RE: SELECT QUERY between 2 different databases on the same server Syntax should be: select * from database@informixserver:table_name wheredatabase@informixserver:table.column rel_op database@informixserver:table.column etc..... -----Original Message----- From: mars1972@my-deja.com [mailto:mars1972@my-deja.com] Sent: Wednesday, June 14, 2000 4:02 PM To: informix-list@iiug.org Subject: Re: SELECT QUERY between 2 differend databases on the same server Yes, if I remember right, just prepend the table names with the database name and a colon. For example: select <whatever> from sysmaster:systables, stores7:customer where... In article <3947CD4F.1205F902@ihs.com>, Richard Krenek <rkrenek@ihs.com> wrote: > Is it possible to do a query that touches 2 different tables in > different databases on the same server. Here is the SQL statment that > they want to use but they want the table doc_update to be in a different > database than doc_master. > > SQL INSERT INTO doc_update > (doc_global_id, fam_global_id, fam_num_gid, fam_sn, > source, kind, act_hist_ind, doc_key_date, pub_num, > rev_sn, sfx_sn, in_lieu_of, most_active, common_gid) > SELECT doc_global_id, fam_global_id, fam_num_gid, fam_sn, > source, kind, act_hist_ind, doc_key_date, pub_num, > rev_sn, sfx_sn, in_lieu_of, most_active, common_gid > FROM doc_master > WHERE act_hist_ind = 'A'> > Why we want to do this you ask!! Because I was asked to. We are > comparing times to do certain queries/inserts between 4 Databases > (OpenIngres/Informix/DB2/Oracle) We are using Informix 7.(forgot this > part). If we do choose Informix it will be replacing OpenIngres that has > its data spread accross 20 databases and they most likely want to copy > that structure to Informix and I need to know if that is possible (or > desirable). > > Thanks, > Richard Krenek > > -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.

↪ replying to Thiel Franky
Art S. Kagel — — source: Informix-list mailing list archive (1991-1998)

Thiel Franky wrote: > > Is it possible to do that if the two databases are not on the same server ? Sure, just remember the general SQL object-id syntax and use whatever part of that is appropriate, thus: "dbowner".database@server:"tblowner".table.column which is the full syntax. If the server is the 'current' connected server you can leave the '@server' out. If the database is NOT an ANSI mode database, or if it is and you are the table's owner, you can drop the table owner. The database name can be dropped if the table is in the 'current' database. The database owner is never required and obviously the column is not needed in a FROM clause. Oh and remember to use aliases to avoid having to type all this anywhere except the FROM clause. Art S. Kagel > Regards, > > Franky > > -----Oorspronkelijk bericht----- > Van: Martin, Wayne E. [mailto:WMartin@kmart.com] > Verzonden: donderdag 15 juni 2000 15:22 > Aan: 'mars1972@my-deja.com'; informix-list@iiug.org > Onderwerp: RE: SELECT QUERY between 2 different databases on the same > server > > Syntax should be: > > select * from database@informixserver:table_name > where> database@informixserver:table.column rel_op > database@informixserver:table.column > > etc..... > > -----Original Message----- > From: mars1972@my-deja.com [mailto:mars1972@my-deja.com] > Sent: Wednesday, June 14, 2000 4:02 PM > To: informix-list@iiug.org > Subject: Re: SELECT QUERY between 2 differend databases on the same > server > > Yes, if I remember right, just prepend the table names with the database > name and a colon. For example: > > select <whatever> from sysmaster:systables, stores7:customer where... > > In article <3947CD4F.1205F902@ihs.com>, > Richard Krenek <rkrenek@ihs.com> wrote: > > Is it possible to do a query that touches 2 different tables in > > different databases on the same server. Here is the SQL statment that > > they want to use but they want the table doc_update to be in a > different > > database than doc_master. > > > > SQL INSERT INTO doc_update > > (doc_global_id, fam_global_id, fam_num_gid, fam_sn, > > source, kind, act_hist_ind, doc_key_date, pub_num, > > rev_sn, sfx_sn, in_lieu_of, most_active, common_gid) > > SELECT doc_global_id, fam_global_id, fam_num_gid, fam_sn, > > source, kind, act_hist_ind, doc_key_date, pub_num, > > rev_sn, sfx_sn, in_lieu_of, most_active, common_gid > > FROM doc_master > > WHERE act_hist_ind = 'A'> > > > Why we want to do this you ask!! Because I was asked to. We are > > comparing times to do certain queries/inserts between 4 Databases > > (OpenIngres/Informix/DB2/Oracle) We are using Informix 7.(forgot this > > part). If we do choose Informix it will be replacing OpenIngres that > has > > its data spread accross 20 databases and they most likely want to copy > > that structure to Informix and I need to know if that is possible (or > > desirable). > > > > Thanks, > > Richard Krenek > > > > > > -- > # unrm / > ksh: unrm: not found > # man cpio > > Sent via Deja.com http://www.deja.com/ > Before you buy.

↪ replying to Art S. Kagel
ram_cnc — — source: Informix-list mailing list archive (1991-1998)

In article <394A269A.A0D8D809@bloomberg.net>, kagel@bloomberg.net wrote: > Thiel Franky wrote: > > > > Is it possible to do that if the two databases are not on the same server ? > > Sure, just remember the general SQL object-id syntax and use whatever part > of that is appropriate, thus: > > "dbowner".database@server:"tblowner".table.column > > which is the full syntax. If the server is the 'current' connected server > you can leave the '@server' out. If the database is NOT an ANSI mode > database, or if it is and you are the table's owner, you can drop the table > owner. The database name can be dropped if the table is in the 'current' > database. The database owner is never required and obviously the column is > not needed in a FROM clause. Oh and remember to use aliases to avoid > having to type all this anywhere except the FROM clause. > Alternatively you may want to look into the possibility of creating synonyms. This way you can reference it as a local table name pointing to the remote physical table. Since I did not follow these thread completely I am assuming all the database servers are Informix. You can use Informix Enterprise Gateway MAnager for different databases like Sybase, oracle and SQL server and Informix Enterprise Gateway with DRDA to establish connectivity between IBM/DB2 and Informix database servers. HTH Ram S. > Art S. Kagel > > > Regards, > > > > Franky > > > > -----Oorspronkelijk bericht----- > > Van: Martin, Wayne E. [mailto:WMartin@kmart.com] > > Verzonden: donderdag 15 juni 2000 15:22 > > Aan: 'mars1972@my-deja.com'; informix-list@iiug.org > > Onderwerp: RE: SELECT QUERY between 2 different databases on the same > > server > > > > Syntax should be: > > > > select * from database@informixserver:table_name > > where> > database@informixserver:table.column rel_op > > database@informixserver:table.column > > > > etc..... > > > > -----Original Message----- > > From: mars1972@my-deja.com [mailto:mars1972@my-deja.com] > > Sent: Wednesday, June 14, 2000 4:02 PM > > To: informix-list@iiug.org > > Subject: Re: SELECT QUERY between 2 differend databases on the same > > server > > > > Yes, if I remember right, just prepend the table names with the database > > name and a colon. For example: > > > > select <whatever> from sysmaster:systables, stores7:customer where... > > > > In article <3947CD4F.1205F902@ihs.com>, > > Richard Krenek <rkrenek@ihs.com> wrote: > > > Is it possible to do a query that touches 2 different tables in > > > different databases on the same server. Here is the SQL statment that > > > they want to use but they want the table doc_update to be in a > > different > > > database than doc_master. > > > > > > SQL INSERT INTO doc_update > > > (doc_global_id, fam_global_id, fam_num_gid, fam_sn, > > > source, kind, act_hist_ind, doc_key_date, pub_num, > > > rev_sn, sfx_sn, in_lieu_of, most_active, common_gid) > > > SELECT doc_global_id, fam_global_id, fam_num_gid, fam_sn, > > > source, kind, act_hist_ind, doc_key_date, pub_num, > > > rev_sn, sfx_sn, in_lieu_of, most_active, common_gid > > > FROM doc_master > > > WHERE act_hist_ind = 'A'> > > > > > Why we want to do this you ask!! Because I was asked to. We are > > > comparing times to do certain queries/inserts between 4 Databases > > > (OpenIngres/Informix/DB2/Oracle) We are using Informix 7.(forgot this > > > part). If we do choose Informix it will be replacing OpenIngres that > > has > > > its data spread accross 20 databases and they most likely want to copy > > > that structure to Informix and I need to know if that is possible (or > > > desirable). > > > > > > Thanks, > > > Richard Krenek > > > > > > > > > > -- > > # unrm / > > ksh: unrm: not found > > # man cpio > > > > Sent via Deja.com http://www.deja.com/ > > Before you buy. > Sent via Deja.com http://www.deja.com/ Before you buy.

↪ replying to Thiel Franky
Jan Lacluyse — — source: Informix-list mailing list archive (1991-1998)

And what to do, when you want to select Opaque-Data Types ??? Thiel Franky wrote: > Is it possible to do that if the two databases are not on the same server ? > > Regards, > > Franky > > -----Oorspronkelijk bericht----- > Van: Martin, Wayne E. [mailto:WMartin@kmart.com] > Verzonden: donderdag 15 juni 2000 15:22 > Aan: 'mars1972@my-deja.com'; informix-list@iiug.org > Onderwerp: RE: SELECT QUERY between 2 different databases on the same > server > > Syntax should be: > > select * from database@informixserver:table_name > where> database@informixserver:table.column rel_op > database@informixserver:table.column > > etc..... > > -----Original Message----- > From: mars1972@my-deja.com [mailto:mars1972@my-deja.com] > Sent: Wednesday, June 14, 2000 4:02 PM > To: informix-list@iiug.org > Subject: Re: SELECT QUERY between 2 differend databases on the same > server > > Yes, if I remember right, just prepend the table names with the database > name and a colon. For example: > > select <whatever> from sysmaster:systables, stores7:customer where... > > In article <3947CD4F.1205F902@ihs.com>, > Richard Krenek <rkrenek@ihs.com> wrote: > > Is it possible to do a query that touches 2 different tables in > > different databases on the same server. Here is the SQL statment that > > they want to use but they want the table doc_update to be in a > different > > database than doc_master. > > > > SQL INSERT INTO doc_update > > (doc_global_id, fam_global_id, fam_num_gid, fam_sn, > > source, kind, act_hist_ind, doc_key_date, pub_num, > > rev_sn, sfx_sn, in_lieu_of, most_active, common_gid) > > SELECT doc_global_id, fam_global_id, fam_num_gid, fam_sn, > > source, kind, act_hist_ind, doc_key_date, pub_num, > > rev_sn, sfx_sn, in_lieu_of, most_active, common_gid > > FROM doc_master > > WHERE act_hist_ind = 'A'> > > > Why we want to do this you ask!! Because I was asked to. We are > > comparing times to do certain queries/inserts between 4 Databases > > (OpenIngres/Informix/DB2/Oracle) We are using Informix 7.(forgot this > > part). If we do choose Informix it will be replacing OpenIngres that > has > > its data spread accross 20 databases and they most likely want to copy > > that structure to Informix and I need to know if that is possible (or > > desirable). > > > > Thanks, > > Richard Krenek > > > > > > -- > # unrm / > ksh: unrm: not found > # man cpio > > Sent via Deja.com http://www.deja.com/ > Before you buy.