Access remote tables

A remote table is a table on a database server other than the current server. You can connect from your current server to a remote server.

At any time, there can be only one active connection from the local server to a remote server. HCL OneDB™ does not support multiple active connections between the same two database servers using different server aliases. Thus, if you use different server aliases to connect to the same remote server, the initial connection is reused.

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 HCL OneDB 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 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;