SYSADMIN_VDB Procedures
The procedures described on this page enable you to manage virtual databases and SSH tunnels.
SYSADMIN_VDB.createVdb
This procedure creates a virtual database:
SYSADMIN_VDB.createVdb("name" => 'string_name');;
Example
CALL "SYSADMIN_VDB.createVdb"("name" => 'newVDB');;
SYSADMIN_VDB.dropVdb
This procedure removes a virtual database by ID:
SYSADMIN_VDB.dropVdb("id" => biginteger_id);;
To view the ID of the virtual database you need to delete, you can use the SYSADMIN_VDB.VirtualDatabases
table.
Example
CALL "SYSADMIN_VDB.dropVdb"("id" => 10);;
SYSADMIN_VDB.generateCloudAgentApiKey
This procedure generates a new CLOUD_AGENT_CLIENT_API_KEY
option value.
SYSADMIN_VDB.generateCloudAgentApiKey(OUT newApiKey string RESULT)
Example
CALL "SYSADMIN_VDB.generateCloudAgentApiKey"() ;;
SYSADMIN_VDB.getCloudAgentConfiguration
The procedure retrieves the contents of all configuration files from all connected agent instances.
SYSADMIN_VDB.getCloudAgentConfiguration(OUT clientId string NOT NULL, OUT file string NOT NULL, OUT configuration string)
clientId
here is a randomly generated UUID assigned to an agent instance upon startup, provided one does not already exist. It is stored in the client_id
file within the agent's configuration directory. The clientId
serves as a unique identifier for an instance, enabling it to be differentiated from other instances, particularly in environments where multiple agents are deployed in a cluster.
Example
SELECT * FROM SYSADMIN_VDB.getCloudAgentConfiguration() ;;
SYSADMIN_VDB.getCloudAgentConnectionStatus
This procedure is designed to assess the connection status of a cloud agent for a specific data source or the server in general if NULL is passed as a datasource
value.
SYSADMIN_VDB.getCloudAgentConnectionStatus(IN datasource string, OUT connected boolean NOT NULL)
Parameter name | Parameter type | Mandatory | Description |
---|---|---|---|
datasource | string | NULL | This parameter specifies the data source for which the connection status needs to be checked. |
Examples
SELECT * FROM "SYSADMIN_VDB.getCloudAgentConnectionStatus"() ;;
SELECT * FROM "SYSADMIN_VDB.getCloudAgentConnectionStatus"(
"datasource" => 'some_datasource'
) ;;
SYSADMIN_VDB.getJavaProp
This procedure gets the translator-related client secret:
SYSADMIN_VDB.getJavaProp(IN key1 string NOT NULL, OUT value1 string RESULT)
Example
CALL "SYSADMIN_VDB.getJavaProp"("key1" => 'user.language');;
SYSADMIN_VDB.setJavaProp
This procedure sets the translator-related client secret:
SYSADMIN_VDB.setJavaProp(IN key1 string NOT NULL, IN value1 string)
Example
CALL "SYSADMIN_VDB.setJavaProp"("key1" => 'user.language', "value1" => 'en');;
SYSADMIN_VDB.createSSHTunnel
This procedure creates an SSH tunnel. It takes the following parameters:
To view the full table, click the expand button in its top right corner
Parameter name | Parameter type | Mandatory | Description |
---|---|---|---|
name | string | NOT NULL | Unique name of an SSH tunnel |
localHost | string | NULL | Host of a client's machine (default: localhost) |
localPort | integer | NOT NULL | Port of a client's machine |
remoteHost | string | NOT NULL | Host of a remote machine |
remotePort | integer | NOT NULL | Port of a remote machine |
host | string | NOT NULL | Username and host used for connecting to a remote machine via SSH protocol (format: username@host ) |
sshKeyPairId | integer | NULL | ID of a key-pair in the SYSADMIN_VDB.SSHCredentials table |
sshPort | integer | NULL | Port used by SSH server on a remote machine (default: 22 ) |
sshProperties | string | NULL | SSH comma-separated properties in CSV-like form: property1=<value1>,property2=<value2> . Used to provide a timeout, cypher parameters, key parameters like used algorithms, a possibility to provide classes for custom algorithms, and so on |
password | string | NULL | Password used for simple authentication on the SSH server |
passPhrase | string | NULL | Password phrase used in case of a secured private key and key authentication on the SSH server |
privateKeyPath | string | NULL | Private key path or the private key itself as a string in case of key authentication on the SSH server |
| string |
NULL
| Possible values: NONE , HTTP , SOCKS4 , SOCKS5 (if this setting is not set via importSSHTunnel , proxy configuration set via System Properties will be considered) |
proxyHost
| string |
NULL
| Host address of the proxy (if this setting is not set via importSSHTunnel , proxy configuration set via System Properties will be considered) |
proxyPort | integer |
NULL
| Port used by the proxy (if this setting is not set via importSSHTunnel , proxy configuration set via System Properties will be considered) |
proxyUser | string |
NULL
| User at the proxy (if this setting is not set via importSSHTunnel , proxy configuration set via System Properties will be considered) |
proxyPassword | string |
NULL
| Password for the proxy server (if this setting is not set via importSSHTunnel , proxy configuration set via System Properties will be considered) |
SYSADMIN_VDB.removeSSHTunnel
This procedure removes an SSH tunnel. It takes the following parameter:
Parameter name | Parameter type | Mandatory | Description |
---|---|---|---|
name | string | NOT NULL | Unique name of the SSH tunnel intended for removal |
SYSADMIN_VDB.importSSHTunnel
This procedure imports or refreshes an SSH tunnel. It takes the following parameters:
To view the full table, click the expand button in its top right corner
Parameter name | Parameter type | Mandatory | Description |
---|---|---|---|
name | string | NOT NULL | Unique name of an SSH tunnel |
localHost | string | NULL | Host of a client's machine (default: localhost) |
localPort | integer | NOT NULL | Port of a client's machine |
remoteHost | string | NOT NULL | Host of a remote machine |
remotePort | integer | NOT NULL | Port of a remote machine |
host | string | NOT NULL | Username and host used for connecting to a remote machine via SSH protocol (format: username@host ) |
portForwardingType
| string | NULL | Type of SSH port forwarding (L by default). As the Data Virtuality Server supports only local port forwarding at the moment, the field can have only the L value |
sshKeyPairId | integer | NULL | ID of a key-pair in the SYSADMIN_VDB.SSHCredentials table |
sshPort | integer | NULL | Port used by SSH Server on a remote machine (default: 22 ) |
encryptedSshProperties | string | NULL | SSH comma-separated properties in CSV-like form: property1=<value1>,property2=<value2> . Used to provide a timeout, cypher parameters, key parameters like used algorithms, a possibility to provide classes for custom algorithms, and so on. It can consist of sensitive data, and this is why the value should be encrypted |
encryptedPassword | string | NULL | Encrypted password used for simple authentication on the SSH server |
encryptedPassPhrase | string | NULL | Encrypted password phrase used in case of secured private key and key authentication on the SSH server |
encryptedPrivateKey | string | NULL | Encrypted private key in case of key authentication on the SSH server |
| string |
NULL
| Possible values: NONE , HTTP , SOCKS4 , SOCKS5 (if this setting is not set via importSSHTunnel, proxy configuration set via System Properties will be considered) |
proxyHost
| string |
NULL
| Address of the proxy (if this setting is not set via importSSHTunnel , proxy configuration set via System Properties will be considered) |
proxyPort
| string |
NULL
| Port used by proxy (if this setting is not set via importSSHTunnel , proxy configuration set via System Properties will be considered) |
proxyUser | string |
NULL
| User at the proxy (if this setting is not set via importSSHTunnel , proxy configuration set via System Properties will be considered) |
| string |
NULL
| Encrypted password for the proxy server |
sshProperties | string | NULL | SSH comma-separated properties in CSV-like form: property1=<value1>,property2=<value2> . Used to provide a timeout, cypher parameters, key parameters like used algorithms, a possibility to provide classes for custom algorithms, and so on |
password | string | NULL | Password used for a simple authentication on the SSH server |
passPhrase | string | NULL | Password phrase used in case of a secured private key and key authentication on the SSH server |
privateKey | string | NULL | Private key in case of key authentication on the SSH server |
| string | NULL | Password for the proxy server |
SYSADMIN_VDB.createSSHKeyPair
This procedure creates a key pair. It takes the following parameters:
Parameter name | Parameter type | Mandatory | Description |
---|---|---|---|
name | string | NULL | Unique name of a key pair |
publicKey | string | NOT NULL | Public key |
id | integer | NOT NULL | ID of the key pair |
SYSADMIN_VDB.renameSSHKeyPair
This procedure renames a key pair. It takes the following parameters:
Parameter name | Parameter type | Mandatory | Description |
---|---|---|---|
name | string | NOT NULL | Unique name of a key pair |
publicKey | string | NOT NULL | Public key |
id | integer | NOT NULL | ID of the key pair |
SYSADMIN_VDB.removeSSHKeyPair
This procedure removes a key pair. It takes the following parameters:
Parameter name | Parameter type | Mandatory | Description |
---|---|---|---|
id | integer | NOT NULL | ID of the key pair |
SYSADMIN_VDB.importSSHKeyPair
This procedure creates a key pair. It takes the following parameters:
Parameter name | Parameter type | Mandatory | Description |
---|---|---|---|
name | string | NULL | Unique name of a key pair |
publicKey | string | NULL | Public key |
privateKey | string | NULL | Private key |
encryptedPrivateKey | string | NULL | Encrypted private key |
id | integer | NOT NULL | ID of the key pair |
SYSADMIN_VDB.updateCloudAgentConfiguration
This procedure creates or updates the configuration in the specified file across all connected agent instances. If the configuration
parameter is NULL
or empty, the specified configuration file is deleted. Should the validation of the new configuration on an agent succeed, it is applied immediately upon download. If there is a configuration error (such as an invalid data source definition, etc.), the agent will still connect to the server provided the SaaS URL and API key are correct.
SYSADMIN_VDB.updateCloudAgentConfiguration(IN file string NOT NULL, IN configuration string)
Parameter name | Parameter type | Mandatory | Description |
---|---|---|---|
| string |
| The name of the cloud agent properties file to be created, updated, or deleted across all agents |
configuration | string | NULL | Specifies the agent configuration to be written to the designated properties file. If the argument is set to NULL or omitted, the properties file will be deleted |
Examples
--Update the configuration stored in "agent_A.properties" file of some cloud agent.
CALL SYSADMIN_VDB.updateCloudAgentConfiguration (
file => 'agent_A.properties',
configuration => 'dvsaas.ws.url=ws://localhost:8080/cloud-agent
client.api.key=<CLOUD_AGENT_API_KEY>'
);;
--Delete the "agent_A.properties" configuration file.
CALL SYSADMIN_VDB.updateCloudAgentConfiguration (
file => 'agent_A.properties'
);;