Skip to main content
Skip table of contents

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:

SQL
SYSADMIN_VDB.createVdb("name" => 'string_name');;

Example

SQL
CALL "SYSADMIN_VDB.createVdb"("name" => 'newVDB');;

SYSADMIN_VDB.dropVdb

This procedure removes a virtual database by ID:

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

Please be aware that this operation cannot be undone, and a dropped virtual database is gone for good.

Example

SQL
CALL "SYSADMIN_VDB.dropVdb"("id" => 10);;

SYSADMIN_VDB.generateCloudAgentApiKey

This procedure generates a new CLOUD_AGENT_CLIENT_API_KEY option value.

SQL
SYSADMIN_VDB.generateCloudAgentApiKey(OUT newApiKey string RESULT)

Example

SQL
CALL "SYSADMIN_VDB.generateCloudAgentApiKey"() ;;

SYSADMIN_VDB.getCloudAgentConfiguration

The procedure retrieves the contents of all configuration files from all connected agent instances. 

SQL
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

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

SQL
SYSADMIN_VDB.getCloudAgentConnectionStatus(IN datasource string, OUT connected boolean NOT NULL)
Parameter nameParameter typeMandatoryDescription
datasource 
stringNULLThis parameter specifies the data source for which the connection status needs to be checked.

Examples

SQL
SELECT * FROM "SYSADMIN_VDB.getCloudAgentConnectionStatus"() ;; 

SELECT * FROM "SYSADMIN_VDB.getCloudAgentConnectionStatus"(
    "datasource" => 'some_datasource'
) ;;

SYSADMIN_VDB.getJavaProp

This procedure gets the translator-related client secret:

SQL
SYSADMIN_VDB.getJavaProp(IN key1 string NOT NULL, OUT value1 string RESULT)

Example

SQL
CALL "SYSADMIN_VDB.getJavaProp"("key1" => 'user.language');;

SYSADMIN_VDB.setJavaProp

This procedure sets the translator-related client secret:

SQL
SYSADMIN_VDB.setJavaProp(IN key1 string NOT NULL, IN value1 string)

Example

SQL
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 nameParameter typeMandatoryDescription
namestringNOT NULLUnique name of an SSH tunnel
localHoststringNULLHost of a client's machine (default: localhost)
localPortintegerNOT NULLPort of a client's machine
remoteHoststringNOT NULLHost of a remote machine
remotePortintegerNOT NULLPort of a remote machine
hoststringNOT NULLUsername and host used for connecting to a remote machine via SSH protocol (format: username@host)
sshKeyPairIdintegerNULLID of a key-pair in the SYSADMIN_VDB.SSHCredentials table
sshPortintegerNULLPort used by SSH server on a remote machine (default: 22)
sshPropertiesstringNULLSSH 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
passwordstringNULLPassword used for simple authentication on the SSH server
passPhrasestringNULLPassword phrase used in case of a secured private key and key authentication on the SSH server
privateKeyPathstringNULLPrivate key path or the private key itself as a string in case of key authentication on the SSH server

proxy

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)
proxyPortinteger NULL Port used by the proxy (if this setting is not set via importSSHTunnel, proxy configuration set via System Properties  will be considered)
proxyUserstring NULL User at the proxy (if this setting is not set via importSSHTunnel, proxy configuration set via System Properties  will be considered)
proxyPasswordstring 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 nameParameter typeMandatoryDescription
namestringNOT NULLUnique 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 nameParameter typeMandatoryDescription
namestringNOT NULLUnique name of an SSH tunnel
localHoststringNULLHost of a client's machine (default: localhost)
localPortintegerNOT NULLPort of a client's machine
remoteHoststringNOT NULLHost of a remote machine
remotePortintegerNOT NULLPort of a remote machine
hoststringNOT NULLUsername and host used for connecting to a remote machine via SSH protocol (format: username@host)
portForwardingType stringNULLType 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
sshKeyPairIdintegerNULLID of a key-pair in the SYSADMIN_VDB.SSHCredentials table
sshPortintegerNULLPort used by SSH Server on a remote machine (default: 22)
encryptedSshPropertiesstringNULLSSH 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
encryptedPasswordstringNULLEncrypted password used for simple authentication on the SSH server
encryptedPassPhrasestringNULLEncrypted password phrase used in case of secured private key and key authentication on the SSH server
encryptedPrivateKeystringNULLEncrypted private key in case of key authentication on the SSH server

proxy

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)
proxyUserstring NULL User at the proxy (if this setting is not set via importSSHTunnel, proxy configuration set via System Properties  will be considered)

encryptedProxyPassword

string NULL Encrypted password for the proxy server
sshPropertiesstringNULLSSH 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
passwordstringNULLPassword used for a simple authentication on the SSH server
passPhrasestringNULLPassword phrase used in case of a secured private key and key authentication on the SSH server
privateKeystringNULLPrivate key in case of key authentication on the SSH server

proxyPassword

stringNULLPassword for the proxy server

SYSADMIN_VDB.createSSHKeyPair

This procedure creates a key pair. It takes the following parameters:

Parameter nameParameter typeMandatoryDescription
namestringNULLUnique name of a key pair
publicKeystringNOT NULLPublic key
idintegerNOT NULLID of the key pair

SYSADMIN_VDB.renameSSHKeyPair

This procedure renames a key pair. It takes the following parameters:

Parameter nameParameter typeMandatoryDescription
namestringNOT NULLUnique name of a key pair
publicKeystringNOT NULLPublic key
idintegerNOT NULLID of the key pair

SYSADMIN_VDB.removeSSHKeyPair 

This procedure removes a key pair. It takes the following parameters:

Parameter nameParameter typeMandatoryDescription
idintegerNOT NULLID of the key pair

SYSADMIN_VDB.importSSHKeyPair 

This procedure creates a key pair. It takes the following parameters:

Parameter nameParameter typeMandatoryDescription
namestringNULLUnique name of a key pair
publicKeystringNULLPublic key
privateKeystringNULLPrivate key
encryptedPrivateKeystringNULLEncrypted private key
idintegerNOT NULLID 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.

SQL
SYSADMIN_VDB.updateCloudAgentConfiguration(IN file string NOT NULL, IN configuration string)
Parameter nameParameter typeMandatoryDescription
file 
stringNOT NULLThe name of the cloud agent properties file to be created, updated, or deleted across all agents
configurationstringNULLSpecifies 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

SQL
--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'
);;
JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.