Re: Help requested: Multiple explicit connections for cross-db Join's... ?!

Matt Krom wrote:
>
> Couldn't find this in the rtfm FAQ... you may know the answer if you
> have used ProC for awhile, and/or use SQLNet and/or work with multiple
> databases...
>
> I'm writing an application that needs to maintain open connections to
> two separate databases (conceivably on different machines), and perform
> a 'join' between them. In the Oracle docs, I've come across two
> different ways to do this, both with problems:
>
> 1) do an EXEC SQL CREATE LINK DB1 CONNECT 'name' USING 'password' AT
> 'host'... and an EXEC SQL CREATE SYNONYM table1 FOR table1_at_DB1... then
> simply doing a join using two SYNONYM's.
>
> problem: As I understand it, the db LINKs open a new connection for
> each query at run-time; I need constant connections (to reduce
> overhead). Am I misunderstanding?
>
> 2) do two of EXEC SQL CONNECT :username IDENTIFIED BY :password
> AT DB_NAME USING :db_string... then use EXEC SQL AT DB_NAME
> SELECT...
>
> Problem: As I see it, there's no way to join across two databases; you
> have to specify a single database in the EXEC SQL AT statement.
>
> Any help, asap, is greatly appreciated! Thank you.
>
>
> Matthew Krom
> krom_at_media.mit.edu
> Cambridge, Massachusetts
>
> http://krom.www.media.mit.edu/~krom/

My understanding is that one you initiate a connection using a database link, that LINK stays open until you perform 'alter session close database link <dblink>'.

In the Oracle7 Server Sql manual ...
alter session close database link dblink - closes the database link dblink, eliminating your session's connection to the remote database.

You may encounter some performance limitations if joining many rows between two different nodes across the network. In such a case you may want to consider simple snapshots to provide a local copy of the remote tables.

Just a thought Received on Mon Sep 30 1996 - 00:00:00 CEST