SELECT QUERY between 2 differend databases on the same server

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

Try INSERT INTO db1:doc_update ( your field list)SELECT (your field list with appropriate data-types) FROM db2:doc_master WHERE ... Richard Krenek <rkrenek@ihs.com> wrote in message news:3947CD4F.1205F902@ihs.com... > 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 >

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.

Richard Krenek 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. Sure. In fact, the databases may be on the same instance, or on 2 different instances on the same server or on 2 different servers. However, the logging of the databases must be identical. insert into table ... select ... from db:table; -- other database on same instance insert into table select ... from db@ifx_server:table; -- other database in different instance(generic) > 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). This is fantastic. Are you actually going to compare the performance of all 4 database engines? Talk to C.D.I about your ONCONFIG, disk layout, etc. We'll make sure your Informix instance is the best it can be. All the best Rudy

Richard Krenek 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. Sure. In fact, the databases may be on the same instance, or on 2 different instances on the same server or on 2 different servers. However, the logging of the databases must be identical. insert into table ... select ... from db:table; -- other database on same instance insert into table select ... from db@ifx_server:table; -- other database in different instance(generic) > 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). This is fantastic. Are you actually going to compare the performance of all 4 database engines? Talk to C.D.I about your ONCONFIG, disk layout, etc. We'll make sure your Informix instance is the best it can be. All the best Rudy