SQL Reference
Data Definition Language (DDL)...

Cluster and Node Management

Cluster and Node Management commands allow administrators to manage storage clusters, create and manage storage spaces, create and manage nodes, and apply configuration changes to nodes, clusters, or the overall system.

CLUSTER

CREATE CLUSTER

CREATE CLUSTER creates a new cluster in the current database. The cluster name must be distinct from the name of any existing tables in the database.

To create a cluster, the logged-in user must be a system-level user.

For more information on creating clusters, see Ocient Application Configuration.

For Foundation (LTS) clusters, if the system is configured with a single-cluster configuration, when you add a second Foundation cluster, the system automatically changes to a multi-cluster configuration. This change is irreversible and requires a full system restart to fully take effect.

Syntax

SQL


Parameter

Data Type

Description

cluster_name

string

The name of the new cluster to create.

Define Clusters ( <create_definition> )

You must include these configuration settings when you create a new cluster.

Configuration

Data Type

Description

TYPE

string

The supported value is:



foundation - Processes query operators and handles storage on the system.

PARTICIPANTS

string

A list of one or more node names that are included in the new cluster.



The number of participant nodes in a cluster must be equal to or greater than the storage-space width. For more information, see CREATE STORAGESPACE.



Adding more than the minimum number of participants to a cluster overprovisions the cluster and allows replacing or dropping participant nodes.



Nodes cannot be assigned as participants to multiple clusters.

STORAGESPACE

string

The name of the storage space for the cluster.

Examples

This example creates an LTS cluster named lts_cluster0 using the storage space named my_storage_space with three participant nodes.

SQL


DROP CLUSTER

DROP CLUSTER removes an existing cluster from the system.

To remove a cluster, you must be a system-level user.

Syntax

SQL


Parameter

Data Type

Description

cluster_name

string

The name of the specified cluster to remove.

Example

This example removes an existing cluster called lts_cluster0.

SQL


ALTER CLUSTER

Add Nodes To An Existing Cluster

ALTER CLUSTER ADD PARTICIPANTS adds the specified nodes to an existing cluster in the system. This operation can overprovision the storage space if necessary.

Syntax

SQL


Parameter

Data Type

Description

cluster_name

string

The name of the specified cluster to alter.

node_name1, [,...]

string

The name of one or more nodes to add to the cluster.

Example

This example adds the node node3 to a cluster named example_cluster, which already contains node1 and node2. Following this operation, example_cluster consists of three nodes.

SQL


Remove Nodes from an Existing Cluster

ALTER CLUSTER DROP PARTICIPANTS drops nodes from an existing cluster.

This command fails if the cluster type is LTS. Dropping the specified nodes brings the number of participants below the total width of the storage space associated with that cluster.

Syntax

SQL


Parameter

Data Type

Description

cluster_name

string

The name of the specified cluster to alter.

node_name1, [,...]

string

The name of one or more nodes to drop from the cluster.

Example

This example drops participant node node3 from a cluster named example_cluster which contains node1, node2, and node3. Following this operation, the participants in example_cluster consist of the nodes node1 and node2.

SQL


Cluster Scope Configuration

ALTER CLUSTER ALTER CONFIG SET sets a configuration override for the configuration at the cluster scope. For an explanation of how configuration overrides work, see Inspect the Current Configuration.

If the specified path to the configuration setting does not exist, the system considers it as a new configuration and appends it to the collection of existing configurations. To reset a configuration override for a specific key, use the RESET keyword.

Syntax

SQL


Parameter

Data Type

Description

cluster_name

string

The name of the specified cluster to alter.

string_definition

string

The name of a configuration parameter to alter.



Contact Ocient®support for details about altering configuration parameters.

string_value

string

The new value to set for the specified configuration parameter.

Examples

This example alters the base configuration for cluster cluster_name and sets the certificate for all nodes to a certificate named cert_name.crt.

SQL


This example resets the certificate configuration for cluster cluster_name.

SQL


This example alters the configuration at the cluster scope for cluster cluster_name and service role lts.

SQL


Cluster Scope Log Level Configuration

ALTER CLUSTER cluster_name ALTER LOG LEVEL SET sets the log level for a particular logger for all nodes in the provided cluster. For an explanation of how configuration overrides work, see Inspect the Current Configuration.

Syntax

SQL


Parameter

Data Type

Description

cluster_name

string

The name of the specified cluster to alter.

Logger Parameters (<logger_parameters>)

Configuration

Data Type

Description

logger_name

string

Use ALL for the root logging configuration.



Otherwise, the logger_name parameter can be any string specifying a logger name for all nodes in the provided cluster.

logger_level

string

The level of severity to be assigned to the logger_name



logger_level must be one of the following options:

'EDEBUG' | 'DEBUG' | 'VERBOSE' | 'INFO' | 'WARN' | 'ERROR'

See the Error Monitoring page for more details on log levels.



To reset a logger to the default log level (INFO), use the RESET keyword.

Examples

The following example alters the log level at the cluster scope. This sets the query log level for all nodes in the cluster to DEBUG.

SQL


This example resets the log level for the query logger for the entire cluster.

SQL


Rename Cluster

Rename an existing cluster in the system.

Syntax

SQL


Parameter

Data Type

Description

old_cluster_name

string

The name of the specified cluster to alter.

new_cluster_name

string

The new name to assign to the cluster.

Example

This example renames an existing cluster called lts-cluster-0 to lts-cluster-1.

SQL


STORAGESPACE

CREATE STORAGESPACE

CREATE STORAGESPACE creates a new storage space. The storage space name must be distinct from the name of any existing storage spaces in the system.

To create a storage space, you must possess the CREATE STORAGESPACE privilege for the current system.

It is recommended that the total width on a production system be smaller than the number of Foundation (LTS) Nodes in the cluster so that loading can continue even in the event of a node outage.

Syntax

SQL


Parameter

Data Type

Description

storage_space_name

string

The name of the new storage space.



A storage space name must begin with a letter followed by letters, numbers, and underscores.

width_integer

integer

The total number of nodes to use in each segment group as it is written to disk.



Width cannot be greater than the number of nodes in the storage cluster.

pw_integer

integer

The number of parity coding bits to use for each segment group. This number must be less than width.



The specified storage space can still complete queries even if nodes are disabled as long as the number of disabled nodes does not exceed the parity_width.



For example, a storage space with a width = 5 and a parity_width = 2 can have up to two nodes become disabled, and the system still uses the three remaining nodes to execute queries. However, if a third node goes down, the system can no longer process query operations.

Example

This example creates a new storage space called ocient.

SQL


DROP STORAGESPACE

DROP STORAGESPACE removes an existing storage space, along with all associated tables and views. The command will fail if one or more storage clusters are linked to the storage space.

To remove a storage space, you must possess the DROP STORAGESPACE privilege for the storage space. Note that this action cannot be undone.

Syntax

SQL


Parameter

Data Type

Description

storage_space_name

string

The name of a storage space to drop.

You can drop multiple storage spaces by specifying additional storage space names and separating each with commas.

Example

Remove an existing storage space named ocient_ss.

SQL


Remove multiple storage spaces.

SQL


ALTER STORAGESPACE

ALTER STORAGESPACE RENAME renames an existing storage space.

To rename a storage space, the logged-in user must be a system-level user.

Syntax

SQL


Parameter

Data Type

Description

old_storage_space_name

string

The name of a storage space to rename.

new_storage_space

string

The new name for the specified storage space.

Example

To rename an existing storage space called teststoragespace to storage-space-1.

SQL


NODE

DROP NODE

DROP NODE removes an existing node from the system.

A node cannot be removed from the system if it is still part of a cluster or if it is one of the two remaining Administration Nodes.

Syntax

SQL


Parameter

Data Type

Description

node_name

string

The name of a node to drop.

You can drop multiple nodes by specifying additional node names and separating each with commas.

Example

Remove a node named example_node.

SQL


Remove multiple nodes.

SQL


ALTER NODE

ALTER NODE RENAME TO

ALTER NODE RENAME renames an existing node.

To rename a node, you must be a system-level user.

Renaming a node can cause a mismatch between the name of the node and the hostname of the node. Furthermore, you have to update users and scripts to use the new name of the node. It is recommended that this command is used only to undo situations like this that can arise naturally (for example, if a node has to be replaced with a differently-named node).

Syntax

SQL


Parameter

Data Type

Description

old_node_name

string

The name of a node to rename.

new_node_name

string

The new name for the specified node.

Example

This example renames an existing node named sql0 to sql1.

SQL


ALTER NODE ADD ROLE

ALTER NODE ADD ROLE adds a role to a node. Each node can have one or more roles that it performs.

To add a role, you must be a system-level user.

Syntax

SQL


Parameter

Data Type

Description

node_name

string

The name of a node to alter.

Node Roles (<node_role>)

The following table explains the supported roles for nodes:

Role

Description

Notes

streamloader

The streamloader role allows the node to continually import a large amount of data into the database.

When adding the streamloader role, the node must be restarted in order for the change to take effect.

sql

The sql role accepts a SQL statement, parses the statement, and compiles the statement.

You can remove this role only if there is at least one other SQL Node present.

operatorvm

The operatorvm role manages database query plans and executes queries.

Added or removed automatically together with the sql or lts role.

lts

The nodes with the lts role store data on disk in segments in a column-oriented fashion. The lts role is associated with the Foundation Nodes on the system.

Can only be added or removed by adding or removing the node as a participant in an LTS cluster.

admin

The admin role accepts the administrative protocol and updates system metadata.

Can only be removed if there are at least two other Administrative Nodes.

health

A node with the health role is responsible for maintaining performance-related counters and statistics.

Present initially on every node. Cannot be removed.

Example

To add SQL role to the node sql1:

SQL


ALTER NODE REMOVE ROLE

ALTER NODE REMOVE ROLE removes a role from a node. For a list of roles, see Node Roles.

Some node roles cannot be removed using an ALTER NODE SQL statement, including health, lts, and operatorvm.

To remove a role, you must be a system-level user.

The node must be restarted in order for this change to take effect.

If you remove a SQL role from a SQL Node, the database removes the node from the connectivity pool. You must restart all SQL Nodes so that the other SQL nodes understand that the node is no longer a SQL Node.

When you remove the admin role, you must also remove the /var/opt/ocient/metadataStorage.raft file.

Syntax

SQL


Parameter

Data Type

Description

node_name

string

The name of a node to alter.

Example

This example removes the sql role from a node.

SQL


ALTER NODE ALTER CONFIG SET

ALTER NODE ALTER CONFIG SET sets a configuration override for the configuration at the node scope. For an explanation of how configuration overrides work, see Inspect the Current Configuration.

If the specified path to the configuration setting does not exist, it will be considered a new configuration and appended to the collection of existing configurations. To reset a configuration override for a specific key, use the RESET keyword.

Syntax

SQL


Parameter

Data Type

Description

node_name

string

The name of the specified node to alter.

string_definition

string

The name of a configuration parameter to alter.



Contact Ocient Support for details about altering configuration parameters.

string_value

string

The new value to be set for the specified configuration parameter.

Examples

This example sets the certificate name for this node to cert_name.crt.

SQL


This example resets the certificate configuration for the node node2.

SQL


This example alters the configuration at the node scope for node node2 for service role lts.

SQL


ALTER NODE ALTER LOG LEVEL SET

ALTER NODE node_name ALTER LOG LEVEL SET sets the log level for a particular logger on the specified node. For an explanation of how configuration overrides work, see Inspect the Current Configuration.

The logger name specified can be any string specifying a logger name, or ALL for the root logging configuration. See the Log Configuration for more details.

To reset a logger to the default log level (INFO), use the RESET keyword.

Syntax

SQL


Parameter

Data Type

Description

node_name

string

The name of the specified node to alter.

<logger_parameters>

string

Use ALL for the root logging configuration.



Otherwise, the logger_name parameter can be any string specifying a logger name assigned to a severity level. See the Log Configuration page for more details.



To reset a logger to the default log level (INFO), use the RESET keyword.

Examples

This example alters the log level at the node scope. The example sets the query log level for this particular node to DEBUG.

SQL


This example resets log level for the query logger on the specified node.

SQL


ALTER NODE ALTER METRIC LEVEL

Alters the reporting level of various internal metrics on a specific node. For more information on internal metrics, see Statistics Monitoring.

To set metrics reporting on all nodes across the system, see ALTER SYSTEM ALTER METRIC LEVEL.

Syntax

SQL


Parameter

Data Type

Description

node_name

string

The name of a node to alter.

Set Metric Levels <set_options>

The SET SQL statement assigns one or more specified metrics to the specified reporting level.

To set levels for a range of statistics, you can use wildcard characters with the LIKE keyword. Alternatively, you can use regular expressions with the REGEX keyword.

Parameter

Data Type

Description

metric_to_alter

string

The names of one or more metrics to alter for logging. Enclose any metric names in single quotes.

The supported logging level values for metrics are INFO and DEBUG. Either keyword must be in single quotes.

Reset Metric Levels <reset_options>

The RESET SQL statement reverts one or more specified metrics to the default log level.

You can use wildcard characters with the LIKE keyword. Alternatively, you can use regular expressions with the REGEX keyword.

Use the ALL keyword to revert all metrics to the default log level.

Parameter

Data Type

Description

metric_to_reset

string

The names of one or more metrics to reset by reverting their log level to the default.

To revert all metrics, use the ALL keyword.

Examples

In this example, the ALTER NODE SQL statement sets the resultCache.queries metric on the node oc1 to the DEBUG level.

SQL


This example uses the LIKE wildcard keyword to capture any metrics prefixed by localStorageService.device.smart. and assign them to the INFO level.

SQL


Similar to the previous example, this SQL statement uses regular expressions to capture any metrics prefixed by localStorageService.device.smart..

SQL


This example alters the level of multiple metrics.

SQL


This example resets the same metrics.

SQL


ALTER NODE SET ADDRESS

ALTER NODE node_name SET ADDRESS ip_address changes the internal IP address for any node. You must restart the system after you change the IP address. If you change the IP address of an Administrator Node, then you must update the bootstrap.conf file for every node with the new IP address. For details about this file, see Ocient System Bootstrapping.

Syntax

SQL


Parameter

Data Type

Description

node_name

string

The name of a node to alter.

ip_address

string

Internal IP address for a node.

Example

This example changes the IP address to 111.11.11.111 for the node named my_node.

SQL


CONNECTIVITY POOL

CREATE CONNECTIVITY_POOL

CREATE CONNECTIVITY_POOL creates a connectivity pool from participant SQL Nodes for a client connection. Connectivity pools enable you to change the IP address of the client connection. Client redirects happen only within connectivity pools. For details about connectivity pools and managing them, see Manage the Network Configuration of an Ocient System.

All SQL Nodes must be part of at least one connectivity pool. SQL Nodes only listen using the values set for the listen_ip and listen_port parameters.

The database uses the source_ip, source_port, and priority parameters when multiple connectivity pools exist that a client can use for the connection. In this case, the database uses the connectivity pool with the highest priority.

The database commits all changes made from these SQL statements after you restart all the SQL Nodes.

Syntax

SQL


Parameter

Data Type

Description

pool_name

string

The name of the connectivity pool.

source_ip

string

The source IP address, which is the IP address in CIDR notation of the client that connects to the SQL Node.

source_port

integer

Optional.

The source port number, which is the port number of the client. This parameter defaults to none, which means any port number.

priority

integer

The priority of the connection. A higher number indicates a higher priority.

node_name

string

The name of the SQL Node.

listen_ip

string

The IP address for listening.

listen_port

integer

The port number for listening.

advertised_ip

string

The IP address to return to the client.

advertised_port

integer

Optional.

The port number to return to the client. If you specify NULL for this parameter, then the database uses the value of the listen_port parameter.

Examples

Create the connectivity pool named test_pool with source address 1.2.3.4/32 and priority 1 for one SQL Node named sql0. The node has the IP address 111.1.1.1 and port number 4050 for listening. Specify the local IP address to return to the client.

SQL


Create the connectivity pool named test_pool with source address 1.2.3.4/32, port number 44, and priority 1 for one SQL Node named sql0. The node has the IP address 111.1.1.1 and port number 4050 for listening. Specify the local IP address and port number 4050 to return to the client.

SQL


Create the connectivity pool named test_pool with source address 1.2.3.4/32, port number 44, priority 2, and three participant SQL Nodes: sql0, sql1, and sql2. The nodes have the IP address 111.1.1.1 and port number 4050 for listening. Specify the local IP address and port number 4050 to return to the client.

SQL


DROP CONNECTIVITY_POOL

DROP CONNECTIVITY_POOL removes the specified connectivity pool.

All nodes must belong to a connectivity pool. The execution of this SQL statement does not permit the removal of a pool such that a node becomes an orphan.

Syntax

SQL


Parameter

Data Type

Description

pool_name

string

The name of the connectivity pool to remove.

Example

Remove the connectivity pool named test_pool.

SQL


ALTER CONNECTIVITY_POOL

ALTER CONNECTIVITY_POOL SET

ALTER CONNECTIVITY_POOL SET sets the metadata of a connectivity pool that includes the source IP address, source port, priority, and participants.

Syntax

SQL


For parameter definitions, see CREATE CONNECTIVITY POOL.

To directly edit the LISTEN_ADDRESS, LISTEN_PORT, ADVERTISED_ADDRESS, or ADVERTISED_PORT parameters, you must either remove the participants and add them back with new values for these parameters using the ALTER CONNECTIVITY_POOL DROP PARTICIPANTS and ALTER CONNECTIVITY_POOL ADD PARTICIPANTS statements respectively, or use the ALTER CONNECTIVITY_POOL SET PARTICIPANTS statement.

Examples

This example sets the priority to 2 of the connectivity pool named test_pool.

SQL


This example sets the participants of the connectivity pool named test_pool to the list of participants with SQL Nodes sql1 and sql2. When you execute this statement, the database resets the prior list of node participants to the new list. The nodes have the IP address 111.1.1.1 and port number 4050 for listening. Specify the local IP address and port number 4050 to return to the client.

SQL


View the node identifiers of the participants of the test_pool connectivity pool.

SQL


ALTER CONNECTIVITY_POOL RENAME TO

ALTER CONNECTIVITY_POOL RENAME TO renames the existing connectivity pool.

Syntax

SQL


Parameter

Data Type

Description

pool_name

string

The name of the existing connectivity pool.

new_pool_name

string

The new name of the connectivity pool.

Examples

Rename the test_pool connectivity pool to new_test_pool.

SQL


ALTER CONNECTIVITY_POOL ADD PARTICIPANTS

ALTER CONNECTIVITY_POOL ADD PARTICIPANTS adds one or more nodes to the participant list of an existing connectivity pool.

Syntax

SQL


Examples

Add one node sql5 to the participant list of the connectivity pool test_pool. The node has the IP address 111.1.1.1 and port number 4050 for listening. Specify the local IP address and port number 4050 to return to the client.

SQL


Add two nodes sql6 and sql7 to the participants list of the connectivity pool test_pool. The nodes have the IP address 111.1.1.1 and port number 4050 for listening. Specify the local IP address and port number 4050 to return to the client.

SQL


ALTER CONNECTIVITY_POOL DROP PARTICIPANTS

ALTER CONNECTIVITY_POOL DROP PARTICIPANTS removes one or more nodes from the participant list of an existing connectivity pool. If you remove the last node of a connectivity pool, then the Ocient System removes the connectivity pool automatically.

All nodes must belong to a connectivity pool. The execution of this SQL statement does not permit the removal of a node such that it becomes an orphan.

Syntax

SQL


Examples

Remove one node sql5 from the connectivity pool test_pool.

SQL


Remove nodes sql6 and sql7 from the connectivity pool test_pool.

SQL


SYSTEM

ALTER SYSTEM ALTER CONFIG SET

ALTER SYSTEM ALTER CONFIG SET sets a configuration override for the configuration at the system scope. For an explanation of how configuration overrides work, see Inspect the Current Configuration.

If the specified path to the configuration setting does not exist, it is considered a new configuration and appended to the collection of existing configurations. To reset a configuration override for a specific key, use the RESET keyword.

By default, this configuration override is set in the base node configuration. To apply the override to a specific service role, simply prefix the key with the name of the service role.

Syntax

SQL


Parameter

Data Type

Description

string_definition

string

The name of a configuration parameter to alter.



Contact Ocient Support for details about altering configuration parameters.

string_value

string

The new value to be set for the specified configuration parameter.

Examples

This example alters the base configuration at the system scope. The examples sets the certificate name for each node in the entire system to cert_name.crt.

SQL


This example resets certificate configuration for the entire system.

SQL


This example alters the configuration at the system scope for service role lts.

SQL


System Scope Log Level Configuration

ALTER SYSTEM ALTER LOG LEVEL SET sets the log level for a particular logger for all nodes in the system. For an explanation of how configuration overrides work, see Inspect the Current Configuration.

The logger name specified can be any string specifying a logger name, or ALL for the root logging config. See the Log Configuration for more details.

Syntax

SQL


Parameter

Data Type

Description

<logger_parameters>

string

Use ALL for the root logging config.



Otherwise, the logger_name parameter can be any string specifying a logger name assigned to a severity level. See the Log Configuration page for more details.



To reset a logger to the default log level (INFO), use the RESET keyword.

Example

This example alters the log level at the system scope.

SQL


ALTER SYSTEM RENAME TO

ALTER SYSTEM RENAME TO sets the system name. The system name appears in the sys.system_information system catalog table. You can also see the current system name by using the CURRENT_SYSTEM function.

Syntax

SQL


Parameter

Data Type

Description

system_name

string

The name of the Ocient system.

Example

SQL


ALTER SYSTEM ALTER METRIC LEVEL

Alters the reporting level of various internal metrics across the system, including all nodes. For more information on internal metrics, see Statistics Monitoring.

To set metrics reporting for individual nodes, see ALTER NODE ALTER METRIC LEVEL.

Syntax

SQL


Set Metric Levels <set_options>

The SET SQL statement assigns one or more specified metrics to the specified reporting level. You can assign metrics to INFO or DEBUG levels.

To set levels for a range of statistics, you can use wildcard characters with the LIKE keyword. Alternatively, you can use regular expressions with the REGEX keyword.

Parameter

Data Type

Description

metric_to_alter

string

The names of one or more metrics to alter for logging. Enclose any metric names in single quotes.

The supported logging levels for metrics are INFO and DEBUG. Either keyword must be in single quotes. 

Reset Metric Levels <reset_options>

The RESET SQL statement reverts one or more specified metrics to the default reporting level.

You can use wildcard characters with the LIKE keyword. Alternatively, you can use regular expressions with the REGEX keyword.

Use the ALL keyword to revert all metrics to the default level.

Parameter

Data Type

Description

metric_to_reset

string

The names of one or more metrics to reset by reverting their level to the default.

To revert all metrics, use the ALL keyword.

Examples

In this example, the ALTER SYSTEM SQL statement sets the resultCache.queries metric to the DEBUG level.

SQL


This example uses the LIKE wildcard to capture any metrics prefixed by localStorageService.device.smart. and assign them to the INFO level.

SQL


Similar to the previous example, this SQL statement uses regular expressions to capture any metrics prefixed by localStorageService.device.smart..

SQL


This example alters the level of multiple metrics.

SQL


This example resets the same metrics.

SQL


Related Links