HP 3000 Manuals HP 3000 Manuals
Connecting to Remote ORACLE Tables
Access SQL/iX supports SQL*NET, the ORACLE communications component that
allows Information Access users to access remote ORACLE tables. You can
set up connections to remote ORACLE tables using a database link or an
alias.
Using a Database Link
If you are using Access SQL/iX version A.05.22 or later, you can use a
database link for connecting to remote tables. You define a database
link (DBlink) in a local database using the SQL*Plus command CREATE
DATABASE LINK. The DBlink contains enough information to uniquely
identify a pathway from the local database to the remote database that
contains the tables. The DBlink contains the following information:
* network protocol
* nodename
* dbname
* username/password
* protocol options
Here is an example of a DBlink:
CREATE DATABASE LINK SOMEDATA
CONNECT TO HENRI IDENTIFIED BY LEE
USING 'T:MYSYSTEM:A';
where:
SOMEDATA is the DBlink name.
HENRI is the user name.
LEE is the user password.
T:MYSYSTEM:A is the database string. The T is the network prefix
for TCP/IP; MYSYSTEM is the node name; A is the ORACLE SID.
Additional information about DBlinks is in Chapter 4 of the SQL*NET for
the HP 3000/900 manual.
Once the DBlink is defined, you must add the following entry to the
user's IASQL file, which allows the user to access the tables on the
remote database:
SETVAR ORACLE_DB_LINKS 1
In Information Access, the user creates a connection to the database
containing the DBlink using the Create Connection Definition window.
When the user displays the Tables list, the tables from the local
database along with the tables from the remote database are shown.
The advantage of using this method is that the Information Access user
does not have to know how the connection was made to the remote tables.
Also, the user can access multiple remote tables via SQL*NET at the same
time and can join local tables with remote tables. However, the user
cannot choose from which of the remote databases to get tables.
Information Access gets tables from each DBlink. If there are 10 DBlinks
defined, then Information Access gets tables from all 10 remote
databases.
Using Synonyms
You can specify a synonym for the remote table for purposes of security
and convenience. Synonyms allow the user to hide the owner or database
name of a remote table or to specify a shortened version of a name. For
example, suppose the owner and link name for a remote table is a long
character string:
JOHNDOE.CUSTOMER_HISTORY@KERMIT.
You could define a short synonym for the string such as JCH, that the
user specifies in SELECTs.
For more information about defining and using synonyms, see the ORACLE
RDBMS Database Administrator's Guide.
Using an Alias
If you are using Access SQL/iX version A.05.20 or later, you can use an
alias for connecting to remote tables. In the SQL*NET alias file, you
define an alias that represents the remote database containing the
tables. There is a public alias file that all users have access to, and
a private alias file for each user. Either alias file can be used,
depending on who should have access to the remote tables. The alias file
contains the following information:
* alias name
* network prefix for TCP/IP
* host name
* system ID
Instructions for defining an alias are in Chapter 4 of the SQL*NET TCP/IP
User's Guide.
After the alias definition is defined, the user creates a connection to
any database in Information Access using the Create Connection Definition
window. The alias name that represents the remote database is appended
to the user name in the form:
USER@ALIAS
When the user displays the Tables list, the tables from the one remote
database are shown.
The advantage of using this method is the user can choose which remote
database to access. However, the user cannot query tables from multiple
remote databases at the same time and cannot join local tables with
remote tables.