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.

==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:

===Create Server===
{{{{<esc><create server link statement> ::=
	create server <server link name> <metadata>

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

===Alter Server===
{{{{<esc><alter server link statement> ::=
	alter server <server link name> <alter metadata>
</esc>}}}}

===Drop Server===
{{{{<esc><drop server statement> ::=
	drop server <server name>
</esc>}}}}

==Server Link Configuration==
Each linked server has the following configuration properties:

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

===InstanceName===
The name of the instance to connect to. If no instance name is specified, {{Dataphor}} is assumed.

===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.

===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.

===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.

==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:

===CreateServerLinkUser===
{{{{<nowiki>CreateServerLinkUser(AUserID : String, AServerLinkName : System.Name, AServerUserID : String, AServerPassword : String)</nowiki>}}}}
Creates a new server link user for the given server link.

===CreateServerLinkUserWithEncryptedPassword===
{{{{<nowiki>CreateServerLinkUserWithEncryptedPassword(AUserID : String, AServerLinkName : System.Name, AServerUserID : String, AEncryptedServerPassword : String)</nowiki>}}}}
Creates a new server link user for the given server link using an encrypted password string.

===SetServerLinkUserID===
{{{{<nowiki>SetServerLinkUserID(AUserID : String, AServerLinkName : System.Name, AServerUserID : String)</nowiki>}}}}
Sets the UserID that will be used to connect to the given server link for the given Dataphor user.

===SetServerLinkUserPassword===
{{{{<nowiki>SetServerLinkUserPassword(AUserID : String, AServerLinkName : System.Name, APassword : String)</nowiki>}}}}
Sets the Password that will be used to connect to the given server link for the given Dataphor user.

===ChangeServerLinkUserPassword===
{{{{<nowiki>ChangeServerLinkUserPassword(AServerLinkName : System.Name, AOldPassword : String, APassword : String)</nowiki>}}}}
Allows the current user to change their password associated with the given server link.

===DropServerLinkUser===
{{{{<nowiki>DropServerLinkUser(AUserID : String, AServerLinkName : System.Name)</nowiki>}}}}
Drops the server link user for the given server link for the given Dataphor user.

===ServerLinkUserExists===
{{{{<nowiki>ServerLinkUserExists(AUserID : String, AServerLinkName : System.Name) : Boolean</nowiki>}}}}
Returns true if the given server link has a server link user configured for the given Dataphor user, false otherwise.

==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:
#If the server link specifies any configuration settings for the native session, a {{NativeSessionInfo}} structure with only those settings is used.
#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.
#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:
#If the server link has a server link user for the current Dataphor user, that information is used.
#If the server link has a default user (a server link user with an empty user ID), that information is used.
#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.

==Cross-Instance Query==
The following operators are available for performing queries on remote sessions:

===ExecuteOn===
{{{{<nowiki>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)</nowiki>}}}}

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.


===EvaluateOn===
{{{{<nowiki>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</nowiki>}}}}

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 };}}}}

===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:

{{{{<esc><expression> on <server link name></esc>}}}}

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

{{{{select Devices on Test;}}}}

==Examples==

===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.

===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" };}}}}

===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.

===Server Link With Default User===
The following code snippet will create a server link and associate a default user:

{{{{<nowiki>create server Test;
CreateServerLinkUser('', 'Test', 'Admin', '');</nowiki>}}}}

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

===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.
Dataphor.org

Navigation

Cross-Instance Query
