Distributed queries

Posted in 2003

ELENA GROVER — Tue, 20 May 2003 21:26:48 — source: IIUG Forums & Mailing Lists

Hi everybody, I have the next question: We have two databases one with logging the other one without. I need to make a report using data from these two databases, according to Informix Guide to SQL "a database with logging can retrieve only from databases with logging. What can I do? I know that I can change the buffering option but it is not a solution for us. Any suggestion would be very appreciated.

↪ replying to ELENA GROVER

rkusenet — Tue, 20 May 2003 21:43:19 — source: IIUG Forums & Mailing Lists

----- Original Message ----- From: "ELENA GROVER" <sistemasjalisco6@grupozeta.com> To: <ids@iiug.org> Sent: Tuesday, May 20, 2003 17:26 Subject: Distributed queries [1183] > Hi everybody, I have the next question: > > We have two databases one with logging the other one without. I need to make a report using data from these two >databases, according to Informix Guide to SQL "a database with logging can retrieve only from databases with logging. >What can I do? > > I know that I can change the buffering option but it is not a solution for us. Any suggestion would be very >appreciated. If you plan to use one SQL to access two different databases, then they both must be of same type, either logged or unlogged. Depending on the report tool, u can pull out data from one database into an ascii file, switch to the other database, load it to a temp table, and then proceed with your query. It is messy, but you have no other option.

↪ replying to ELENA GROVER

ART KAGEL, BLOOMBERG/ 65E 55TH — Wed, 21 May 2003 13:53:48 — source: IIUG Forums & Mailing Lists

The only way is to establish two named connections one to each database and switch databases with SET CONNECTION TO <conn_name>. The only gotcha is to establish each connection with the WITH CONCURRENT TRANSACTIONS clause so that open cursors and transactions do not close and rollback when you switch to the other connection. My dbcopy utility uses this technique to copy data between databases with incompatible logging modes. As for performance it actually tends to be a bit faster than alternating between cursors open to two separate databases from within a connection to one of them. Art S. Kagel ----- Original Message ----- From: Elena Grover <sistemasjalisco6@grupozeta.com> At: 5/20 17:58 > Hi everybody, I have the next question: > > We have two databases one with logging the other one without. I need to make a > report using data from these two databases, according to Informix Guide to SQL > "a database with logging can retrieve only from databases with logging . What > can I do? > > I know that I can change the buffering option but it is not a solution for us. > Any suggestion would be very appreciated.