Cross-Instance Query

In order to enable one Dataphor Server instance to query data in another instance, Dataphor now supports the definition of a server link. Each server link specifies a Dataphor instance that can be used to perform cross-instance queries.

Edit

Maintaining Server Links

To support the configuration of a server link, a new catalog object called a ServerLink has been introduced. The following new statements have been introduced to manage server links:

Edit

Create Server

<create server link statement> ::=
	create server <server link name> <metadata>

<server link name> ::=
	<qualified identifier>
Creates a server link named <server link name>. The metadata is used to set the various settings of the server link as described in the section Server Link Configuration.

Edit

Alter Server

<alter server link statement> ::=
	alter server <server link name> <alter metadata>

Edit

Drop Server

<drop server statement> ::=
	drop server <server name>

Edit

Server Link Configuration

Each linked server has the following configuration properties:

Edit

HostName

The name of the computer on which the instance is running. If no host name is specified, localhost is assumed.

Edit

InstanceName

The name of the instance to connect to. If no instance name is specified, Dataphor is assumed.

Edit

OverridePortNumber

An optional port number to use to connect. If specified, this port number will be used rather than querying the listener for the port number of the instance.

Edit

UseSessionInfo

This setting determines whether or not the session information from the currently running session will be used to construct the native session info for the connection.

Edit

Native Session Information

In addition to the above settings, each Server Link can specify the native session information to be used to connect. For more information on the settings available for a native session, see the NativeSessionInfo structure in the Native CLI documentation.

Edit

Server Link Users

By default, the Server Link will use the current session's user credentials to connect. However, each server link can be configured to use different credentials for each Dataphor user. This is accomplished using the following D4 operators:

Edit

CreateServerLinkUser

CreateServerLinkUser(AUserID : String, AServerLinkName : System.Name, AServerUserID : String, AServerPassword : String)
Creates a new server link user for the given server link.

Edit

CreateServerLinkUserWithEncryptedPassword

CreateServerLinkUserWithEncryptedPassword(AUserID : String, AServerLinkName : System.Name, AServerUserID : String, AEncryptedServerPassword : String)
Creates a new server link user for the given server link using an encrypted password string.

Edit

SetServerLinkUserID

SetServerLinkUserID(AUserID : String, AServerLinkName : System.Name, AServerUserID : String)
Sets the UserID that will be used to connect to the given server link for the given Dataphor user.

Edit

SetServerLinkUserPassword

SetServerLinkUserPassword(AUserID : String, AServerLinkName : System.Name, APassword : String)
Sets the Password that will be used to connect to the given server link for the given Dataphor user.

Edit

ChangeServerLinkUserPassword

ChangeServerLinkUserPassword(AServerLinkName : System.Name, AOldPassword : String, APassword : String)
Allows the current user to change their password associated with the given server link.

Edit

DropServerLinkUser

DropServerLinkUser(AUserID : String, AServerLinkName : System.Name)
Drops the server link user for the given server link for the given Dataphor user.

Edit

ServerLinkUserExists

ServerLinkUserExists(AUserID : String, AServerLinkName : System.Name) : Boolean
Returns true if the given server link has a server link user configured for the given Dataphor user, false otherwise.

Edit

Cross-Instance Connections

A connection to server link is called a remote session. When the Dataphor server needs to establish a remote session to a given server link, the following logic is used to build the session information used to connect to the server:
  1. If the server link specifies any configuration settings for the native session, a NativeSessionInfo structure with only those settings is used.
  2. If the server link specifies that session info should be used (UseSessionInfo="True"), then a NativeSessionInfo structure based on the current session's session information is used.
  3. Otherwise, a NativeSessionInfo structure is built based on the currently running process.

Once the basic session info has been determined, the credentials are determined using the following logic:
  1. If the server link has a server link user for the current Dataphor user, that information is used.
  2. If the server link has a default user (a server link user with an empty user ID), that information is used.
  3. Otherwise, the credentials of the current user are used.

When a remote session is established on a given process, transactions are started on the connection to bring the transaction nesting level up to the current transaction nesting level for the process.

Transaction management operations performed on the process are coordinated with any currently active remote sessions.

When the process stops, any active remote sessions are closed.

Edit

Cross-Instance Query

The following operators are available for performing queries on remote sessions:

Edit

ExecuteOn

ExecuteOn(const AServerLinkName : Name, const AStatement : String)
ExecuteOn(const AServerLinkName : Name, const AStatement : String, const AInParams : row)
ExecuteOn(const AServerLinkName : Name, const AStatement : String, const AInParams : row, var AOutParams : row)

Executes the given statement on the given server link.

If the current process does not have a connection to the server link, one is established as described in the Remote Sessions discussion.

Edit

EvaluateOn

EvaluateOn(const AServerLinkName : Name, const AExpression : String) : generic
EvaluateOn(const AServerLinkName : Name, const AExpression : String, const AInParams : row) : generic
EvaluateOn(const AServerLinkName : Name, const AExpression : String, const AInParams : row, var AOutParams : row) : generic

Evaluates the given expression on the given server link.

If the current process does not have a connection to the server link, one is established as described in the Remote Sessions discussion.

Note that because the return value of these operators is generic, a cast must be used to retrieve the actual value. For example, the following code snippet returns the names of the libraries available on a server link named Test:

select EvaluateOn('Test', 'select Libraries { Name }') as table { Name : Name };

Edit

On Expression

In addition to these operators, a new clause has been introduced to the language allowing for cross-instance queries to be expressed without the need to dynamically construct the expression to be executed remotely. The on clause has the following syntax:

<expression> on <server link name>

For example, the following query can be used to retrieve a list of devices from the Test server link:

select Devices on Test;

Edit

Examples

Edit

Default Server Link

The following code snippet creates a default server link named Test:

create server Test;

This link will connect to the Dataphor instance on localhost using session information constructed from the currently running process, and the current user's credentials.

Edit

Basic Server Link

The following code snippet will create a server link named Test that connects to the Test instance on MYNOCK:

create server Test tags { HostName = "MYNOCK", InstanceName = "Test" };

Edit

Server Link With Session Info

The following code snippet will create a server link and specify the session information to use:

create server Test tags { DefaultUseDTC = "True", DefaultMaxStackDepth = "256" };

Note that becasue the session info is specified here, no session settings will be inferred from the current session, even if UseSessionInfo is set to true on the server link.

Edit

Server Link With Default User

The following code snippet will create a server link and associate a default user:

create server Test;
CreateServerLinkUser('', 'Test', 'Admin', '');

When a remote session is established to this server, the Admin user will be used, regardless of the user that establishes the connection.

Edit

Server Link With User Credentials

The following code snippet will create a server link and associate credentials for a specific-user:

create server Test;
CreateServerLinkUser('FETT', 'Test', 'Admin', '');

When the user FETT establishes a connection to this server, the Admin user will be used.