SAND CDBMS SQL Reference Guide
SAND CDBMS SQL Database Objects and Language Elements

Databases

In SAND CDBMS SQL, a database is a collection of schemas that can be a self-contained entity with respect to data access: it defines an outer boundary of the "world" of data that can be addressed from a database user session. Typically, databases are independent of each other, such that SQL statements refer only to objects located in the database to which the user is currently connected.

SAND CDBMS also supports a "federated" database system, in which tables in multiple running databases can be queried from within the current user session.

Federated Databases

The federated database system is a way to query tables from multiple SAND databases via a connection to a single database. Providing a simple, unified interface for distributed data means that the data in several logically separate databases never have to be physically combined in a single database. As far as end users of the federated database system are concerned, they are transparently accessing tables in the local database, even if the data comes from remote databases that might be running on different machines, with different hardware resources and different operating systems.

There are two ways to query remote tables through the local database:

1. Create a local "linked" table that will act as a pointer to the remote one
2. Specify a dynamic pass-through query in the FROM clause of a SELECT statement.

Linked Tables

One way to make a remote table accessible in the local database involves creating a local table that is linked to the remote one. The linked table is defined with a CREATE TABLE...WITH CONNECTION statement that specifies the following information:

• the local table that will act as a link to the remote one (this "linked" table will be created, using the remote table's structure as a template)
• an existing connection that will be used to access the remote database (connection objects are created with the CREATE CONNECTION command)
• the remote table that will be linked to the local one
  -or-
  a SELECT statement ("pass-through" query) that references one or more linked tables associated with tables in the remote database (the local table that is created will use the structure of the query results table)

After the linked table is created and a connection is established with the remote database, users can query the remote table(s) simply by referencing the linked table name in SELECT statements. Records from the remote database will be fetched "over the wire" and included in the result set. Most types of queries can be executed on remote tables in this manner, including complex queries that involve inner/outer joins with other remote or local tables. However, there are a few query restrictions. 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.

Note that a linked/remote table cannot be changed structurally with an ALTER TABLE command, nor can its data contents be altered via INSERT/UPDATE/DELETE statements.

At the other end, federated access will not disturb users and clients of remote databases: they can connect and operate on the databases as usual. In addition, no special software or configuration is required for the remote databases in a federated system. And since remote tables and data cannot be changed by local users, each remote database is effectively read-only.

Dynamic Pass-through Queries

The other way to access tables in remote databases is to specify a special kind of subquery, called a dynamic pass-through query, in the FROM clause of a local SELECT statement. The dynamic pass-through query is sent to the remote database, where it is executed, and the results set is returned to the local query. If the pass-through query generates an error condition on the remote database side, the error message is sent back to the local query.

Note that the dynamic pass-through query is not checked for syntax or semantics before it is directed to the remote database.