accessing multiple databases

[itzgee <itzgee@PRISMNET.COM>]

I think you can set up a standard database link.  We stick to Oracle here,
but any database with standard sql installed would probably work.  Here are
my previous notes on the subject:

caveat:  I use oracle.  You might have subtle differences in the sql if you
use another product.

You have to set up a database link first.

You do not set up the link within your sqr.  I set up my links manually,
with my dba's knowledge and consent.  You can think of the database link as
a persistent object that exists inside the database, so to speak.  The link
does not make the two databases perform as one.  Links just give access to
data on other databases, really.

The standard syntax for creating a link is:

create [public or private] database link some_name_link
   connect to username identified by password
   using 'T:hostname:dbname'


If you are using Oracle SQL*Net, you can substitute
   using 'connect string'


The 'T:.....' stands for the protocol, in this case, TCP/IP.

hostname is the name of the host where the database resides.
dbname is the name of the remote database.


So, let's say you set up the following link


create public database link my_link
   connect to rstojkov identified by 18snoopy
   using 'T:my_server:my_database'


Then, when you want to access data at that location, you use the following
syntax:

select *
   from my_table@my_link
     where my_field = 'ME'

If you get tired of all that typing, you can create a synonym:

    create synonym my_self for my_table@my_link

then you can say

   select * from my_self where my_field = 'ME'

Now, a word to the wise:

    DATABASE LINKS MUST BE MAINTAINED MANUALLY!!!

    I am not a DBA, so I don't know all about this, but there are instances
where
    your links can go AWOL.  If you are migrating your database to another
server
    or if your dba does some sort of major upgrade, the database link may be
wiped
    out.

    Therefore, you need to work with your dba on this.

    You might even consider using an sqr that checks for the existence of
the link at night and warns you if it can't find it.

begin-procedure my_check

    $my_msg = 'NOT FOUND'

begin-select
my_field

    $my_msg = 'FOUND'

from my_self

end-select

    if $my_msg = 'NOT FOUND'
    let $Call_To_System = 'mail -s "Link Status"
robert.stojkovic@IO-CONSULTING.COM <'|| $my_msg
     call system using $Call_To_System #unix_status
    end-if

end-procedure


   Database links can also be slow slow slow if you try to return a lot of
rows.
    Be sure your selects are as limited as possible.   Try to nest your
selects
    if your performance is unacceptable.  Retrieve the local data, and then
call
    a procedure that retrieves the remote data.  If you do a complicated
join across
    a link, you will be like Cartman on a Big Wheel ("!?*# Can't this thing
go any faster!!!")

    You must really think out the best way to structure your links.
    If you are moving a lot of rows of data, you should set up two links,
one on each
    database, and test it to see if you "get" data faster than you "put"
data.

    Generally, I figure out which side of the equation is going to perform
the commit,
    and I try to start from that end.  Sometimes it's not really all that
much more
    efficient to do a link.

    Speaking of efficiency, if you are loading into Oracle, and it is a
straight load,
    you should try sqlloader.  There have been a lot of posts on the topic.


Gracen Duffield
Texas Department of Housing and Community Affairs
475-3839

-----Original Message-----
From: Discussion of SQR, SQRIBE Technologies's database reporting language
[mailto:SQR-USERS@list.iex.net]On Behalf Of Robert Stojkovic
Sent: Friday, September 17, 1999 11:25 AM
To: Multiple recipients of list SQR-USERS
Subject: ODBC Connection


Hello.

What would be the proper syntax to open and ODBC connection to another
database within and SQR.

Robert

Gracen Duffield
Texas Department of Housing and Community Affairs
475-3839


-----Original Message-----
From: Discussion of SQR, SQRIBE Technologies's database reporting
language [mailto:SQR-USERS@list.iex.net]On Behalf Of Jeffrey K. Bedell
Sent: Thursday, October 07, 1999 10:34 AM
To: Multiple recipients of list SQR-USERS
Subject: Accessing multiple databases


Greetings fellow SQR users -
    I have been monitoring this list serv for many months now and have
picked up many useful tidbits of information but never found the need to
make use of it.  Until now.  We are a university and therefore have
multiple platforms and environments scattered all over the place.  To
transfer information between systems we generally run an SQR that
extracts the data from one database, FTP the file over to another server
and then run another SQR which updates the second database.  What I was
wondering is, is it possible to access two separate database in the same
SQR?  That is, can I read from one database (SYBASE) and insert into a
second database (ORACLE) within the same SQR?  Anybody ever done this or
even know if it is possible?
    Thanks for any input you can offer.

- Jeff Bedell - Syracuse University