The CREATE TABLE...WITH CONNECTION command creates a linked table that will allow users to query an associated table in a remote database.
Required Privileges
To create a linked table, the user authorization must own or possess OWNER privileges on the schema where the table will be created, or possess DBA privileges.
Syntax
table name
The table name argument is an identifier that names the linked table
uniquely within the schema: it cannot match any other table or view name within
the current schema. Table names can be up to 128 characters long. It is strongly
recommended that none of the SAND CDBMS
SQL keywords be used as a table name, as this may cause problems when
referencing the table in certain SQL statements.
The table may be qualified by the schema to which it will belong: prefix the table name with the name of the appropriate schema followed by a period ( . ), that is, schema-name.table-name.
connection name
The connection name argument refers to an existing connection object
(created previously with the CREATE CONNECTION
command). This connection object encompasses a CONNECTION section in the nucleus.ini
file, the name of a remote database started on the host and port number
specified by the nucleus.ini CONNECTION section, and a user name/password
that can gain entry to the remote database.
remote table name
The remote table name identifies which table (or view) in the remote
database will be associated with the linked table being created.
The remote table may be qualified by the schema to which it belongs, by prefixing the table name with the name of the appropriate schema followed by a period ( . ), that is, remote-schema-name.remote-table-name. If the schema is omitted, the remote table is assumed to exist either in the default schema of the user authorization defined in the connection object, or else in the remote database's PUBLIC schema.
The user authorization named in the connection object must possess at least SELECT privileges on the remote table, otherwise the CREATE TABLE...WITH CONNECTION command will fail with an "insufficient privileges" error.
<SELECT statement>
As an alternative to naming a table in the remote database, a SELECT statement
(also known as a "pass-through" query in this situation) can be
specified to limit the data fetched from the database. Instead of referencing
a remote table directly, the SELECT statement references a previously defined
linked table. This option should be used if it is known in advance that only
a subset of the remote table data is required for local querying, since this
method can be more efficient in terms of the amount of time required for user
queries and the amount of data that has to be fetched over the network.
One further distinction between the pass-through and remote table name options is that the pass-through query can reference more than one table. A pass-through query can, for instance, join multiple remote tables through their local linked table counterparts. That is, each table referenced in the SELECT statement must be an existing linked table that points to a table in the same remote database. The new linked table created locally through the CREATE TABLE...WITH CONNECTION command will have a structure compatible with the query results table.
Note that some SELECT clauses are not supported for the pass-through option: ORDER BY, WITH SAMPLE OF, and FETCH FIRST ONLY cannot be included. As well, pass-through queries cannot be nested.
Description
The linked table brought into existence with the CREATE TABLE...WITH CONNECTION command is essentially a pointer to a table, or set of joined tables, in a remote database: it is "linked" to the remote table. (Note that a remote "table" in this context can also be a remote view.)
The linked table is created with a structure that is compatible with the associated remote table. The linked table does not completely mirror the remote one, as constraints and collation information are not copied over. The linked table itself is "empty" of records and cannot be filled up with data by a user. The linked table, and by extension the remote table, is read-only to users.
The connection object named in the CREATE TABLE...WITH CONNECTION statement must have been defined previously with the CREATE CONNECTION command. This connection object provides all the information required to connect to the database containing the specified remote table, including connection name, database name, host computer, port number, and a user authorization/password. Note that the user authorization must have at least SELECT privileges on the remote table. Also note that the remote database must have been started on the same connection name, on the given host and port number, when the CREATE TABLE...WITH CONNECTION command is executed.
The choice between the remote table name and pass-through query options in the FOR clause can be based on the expected types of user queries. If there is a need to handle many ad hoc queries that collectively involve all columns of the remote table, the remote table name option is the appropriate one. However, if only a subset of the remote table data needs to be available for local querying, the pass-through query option can take advantage of filtering to provide generally faster, more efficient query results.
The pass-through option can also be used to join remote tables. Since the pass-through query itself–and not any particular remote table–defines the structure of the linked table, multiple remote tables (through their local linked table equivalents) can be referenced in the SELECT statement. On the other hand, a view can be defined on the remote database side that performs the same join operation, and that view can be specified in the FOR clause of the CREATE TABLE...WITH CONNECTION statement.
A linked table is queried in the same manner as a standard table. To a user querying the linked table, there is not much indication that it is anything other than a standard table, even though, behind the scenes, data is being fetched from a remote database rather than the local one.
However, there are a few restrictions on remote querying. The following elements cannot be included in SELECT statements executed against linked tables:
- subqueries
- OLAP functions
- some types of CASE statements
- comparisons against literal TIMESTAMP values
- SELECT NULL.
A linked table can be subsequently removed using the standard DROP TABLE command.
Examples
The following example creates a linked table called linktab1 in schema s1, which will be associated with the remote table rmtab1 (in remote schema work2), using the connection object rmconn:
CREATE TABLE s1.linktab1 WITH CONNECTION rmconn FOR work2.rmtab1;
After the linked table is created, the remote table rmtab1 can be queried from the local database through the linked table linktab1.
The next example creates a linked table called linktab3 with the same connection object, but using a pass-through query to define the linked table. Since the pass-through query joins a pair of remote tables (rmtab1, rmtab2), local linked tables associated with those remote tables will have to be referenced in the SELECT statement. The linked table for rmtab1 (that is, linktab1) was created in the previous example. A linked table for rmtab2 will also have to be created before the CREATE TABLE...WITH CONNECTION...SELECT statement is executed:
CREATE TABLE s1.linktab2 WITH CONNECTION rmconn FOR work2.rmtab2;
CREATE TABLE s1.linktab3 WITH CONNECTION rmconn FOR
SELECT linktab1.dept AS Dept, linktab2.salary AS Salary
FROM linktab1, linktab2
WHERE linktab1.eid = linktab2.eid;
The pass-through query joins two tables in the remote database (rmtab1 and rmtab2) through their local linked tables (linktab1 and linktab2, respectively) . The linked table generated by this command (linktab3) will have two columns, Dept (based on the linktab1.dept column) and Salary (based on the linktab2.salary column). A straight SELECT * query against this new linked table will return those records from rmtab1.dept and rmtab2.salary that are joined on the common eid field. Of course any query on the linked table can also be refined further and/or joined with other, local tables.
Note that executing the pass-through query by itself:
SELECT linktab1.dept AS Dept, linktab2.salary AS Salary
FROM linktab1, linktab2
WHERE linktab1.eid = linktab2.eid;
will give the same results as the following query on linktab3:
SELECT * FROM linktab3;
The difference is that with the pass-through query, under the covers, all of the records from both rmtab1 and rmtab2 are fetched from the remote database, and then the filtering condition is applied locally; whereas with the linktab3 query, the join is performed on the remote database side and only those records allowed by the selection criteria are returned to the local database. From the perspective of the local database, the latter option is clearly a more efficient use of resources.