Accessing Remote Tables

A remote table is a table on a database server other than the current server. The general syntax for accessing a table on another server is:

database@server:[owner.]table

Here, a table can be a table name, view name or synonym. You have the option of specifying the table owner. For the complete syntax options, see the documentation of the Database and Database Object segments in the IBM Informix Guide to SQL: Syntax.

The following example shows a query that accesses a remote table:

DATABASE locdb; SELECT l.name, r.assignment FROM rdb@rsys:rtab r, 
loctab l WHERE l.empid = r.empid;

This query accesses the name and empid columns from a the local table loctab, and the assignment and empid columns from the remote table rtab. The data is joined using empid as the join column.

The following example shows a query that accesses data on a remote table and inserts it into a local table:

DATABASE locdb; INSERT INTO loctab SELECT * FROM rdb@rsys:rtab;

This query selects all data from the remote table rtab, and inserts it into the local table loctab.

The following example creates a view in the local database using the empid and priority columns from the remote database rdb.

DATABASE locdb; CREATE VIEW myview (empid, empprty) 
AS SELECT empid, priority FROM rdb@rsys:rtab;

Table Permissions

Permissions for accessing table in other databases and remote tables are controlled at the table location. When accessing a remote server, the connection is made using the login name and password of the user executing the query. To access remote data, the user must have the appropriate permissions on the remote table.

When processing distributed queries, the database server ignores the active role on the current local database when accessing a remote object. On the remote server, the default role applied to each remote database is used. If a default role is not defined, the user's privilege define the access permissions for the objects in each remote database.

Qualifying Table References

References to tables may be qualified with the current database and server name. If no qualification is specified, the current database and server context is implied. For example, if the current database is locdb and the current server is currsys, the following references to loctab are equivalent:

locdb@currsys:loctab 
locdb:loctab
loctab