Joining from Ansi databases to Informix databases

Hey gurus, We are running in house code using an Informix standard database setup. We are purchasing a vendor product that runs using an ANSI database. We are putting both databases in the same config, and would like to join across these two but we get these errors: select m1.loc_mid from sysutils@prdi04:msm_location m1, msm_loc m2 wherem1.loc_mid=m2.loc_mid; # ^ # 571: Cannot reference an external non-ANSI database. # create synonym m1 for sysutils@prdi04:"informix".msm_location;# ^ # 571: Cannot reference an external non-ANSI database. # Any suggestions? kate

Kate_Tomchik@HomeDepot.COM wrote: > > Hey gurus, > We are running in house code using an Informix standard database setup. We are > purchasing a vendor product that runs using an ANSI database. We are putting > both databases in the same config, and would like to join across these two but > we get these errors: > > select m1.loc_mid from sysutils@prdi04:msm_location m1, msm_loc m2 where> m1.loc_mid=m2.loc_mid; > # ^ > # 571: Cannot reference an external non-ANSI database. > # > create synonym m1 for sysutils@prdi04:"informix".msm_location;> # ^ > # 571: Cannot reference an external non-ANSI database. > # > > Any suggestions? Either change the logging mode of one of the databases or give up. To join two tables in two databases, the databases *must* have absolutely identical logging modes (eg both buffered logging, or both unbuffered logging, or both unlogged, or both MODE ANSI). No mixture is allowed. SysMaster is an exception to this; you can access it from any type of database. I doubt if it will provide you the answer you're looking for (though I suppose, in theory, if you arrange for one application to use sysmaster, the other might be able to access it because the data is in sysmaster -- but pukesville and it won't be supported!). -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"

You cannot join between databases with incompatible logging modes. Compatible combinations are: ANSI MODE <-> ANSI MODE UNBUFFERED LOG <-> UNBUFFERED LOG UNBUFFERED LOG <-> BUFFERED LOG BUFFERED LOG <-> BUFFERED LOG NON-LOGGED <-> NON-LOGGED The ONLY solution, other than to change the logging mode of one database or the other, is to maintain multiple connections, one to each database opened WITH CONCURRENT TRANSACTIONS, and switch between them performing the join in your software. Art S. Kagel Kate_Tomchik@HomeDepot.COM wrote: > > Hey gurus, > We are running in house code using an Informix standard database setup. We are > purchasing a vendor product that runs using an ANSI database. We are putting > both databases in the same config, and would like to join across these two but > we get these errors: > > select m1.loc_mid from sysutils@prdi04:msm_location m1, msm_loc m2 where> m1.loc_mid=m2.loc_mid; > # ^ > # 571: Cannot reference an external non-ANSI database. > # > create synonym m1 for sysutils@prdi04:"informix".msm_location;> # ^ > # 571: Cannot reference an external non-ANSI database. > # > > Any suggestions? > > kate