> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ocient.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Cluster and Node Management
export const Ocient = "Ocient®";
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 details about creating clusters, see [Ocient Application Configuration](/ocient-application-configuration).
For Foundation 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 SQL theme={null}
CREATE CLUSTER [ IF NOT EXISTS ] cluster_name
::=
TYPE [=] foundation
| PARTICIPANTS [=] (node_name1, node_name2, ...)
| STORAGESPACE [=] storage_space_name
```
| **Parameter** | **Data** **Type** | **Description** |
| -------------- | ----------------- | -------------------------------------- |
| `cluster_name` | string | The name of the new cluster to create. |
#### Define Clusters ( `` )
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 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 details, see [CREATE STORAGESPACE](#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 a Foundation cluster named `lts_cluster0` using the storage space named `my_storage_space` with three participant nodes.
```sql SQL theme={null}
CREATE CLUSTER lts_cluster0
TYPE=foundation
PARTICIPANTS=(node1,node2,node3)
STORAGESPACE=my_storage_space;
```
### DROP CLUSTER
`DROP CLUSTER` removes an existing cluster from the system.
To remove a cluster, you must be a system-level user.
**Syntax**
```sql SQL theme={null}
DROP CLUSTER [ IF EXISTS ] cluster_name
```
| **Parameter** | **Data** **Type** | **Description** |
| -------------- | ----------------- | -------------------------------------------- |
| `cluster_name` | string | The name of the specified cluster to remove. |
**Example**
This example removes an existing cluster named `lts_cluster0`.
```sql SQL theme={null}
DROP CLUSTER lts_cluster0;
```
### ALTER CLUSTER
#### ALTER CLUSTER ADD PARTICIPANTS
`ALTER CLUSTER ADD PARTICIPANTS` adds the specified nodes to an existing cluster in the system. This operation can overprovision the storage space if necessary.
For the system to recognize newly added nodes, you must restart the cluster. To do this, you can follow the steps in [Add Foundation Nodes](/expand-and-rebalance-system#add-foundation-nodes).
**Syntax**
```sql SQL theme={null}
ALTER CLUSTER [ IF EXISTS ] cluster_name
ADD PARTICIPANTS [=] ( node_name1 [,...] );
```
| **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 SQL theme={null}
ALTER CLUSTER example_cluster ADD PARTICIPANTS (node3);
```
#### ALTER CLUSTER DROP PARTICIPANTS
`ALTER CLUSTER DROP PARTICIPANTS` drops nodes from an existing cluster.
This SQL statement fails for a Foundation cluster. Dropping the specified nodes brings the number of participants below the total width of the storage space associated with that cluster.
**Syntax**
```sql SQL theme={null}
ALTER CLUSTER [ IF EXISTS ] cluster_name
DROP PARTICIPANTS [=] ( node_name1 [,...] );
```
| **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 SQL theme={null}
ALTER CLUSTER example_cluster DROP PARTICIPANTS (node3);
```
#### ALTER CLUSTER ADD STORAGESPACE
`ALTER CLUSTER ADD STORAGESPACE` links an existing storage space to a storage cluster, enabling tables in that storage space to use the Foundation Nodes of that cluster. A storage space must be associated with at least one storage cluster before you can create tables in the space.
**Required Privileges**
To add a storage space to a cluster, you must have system-level privileges.
**Syntax**
```sql SQL theme={null}
ALTER CLUSTER [ IF EXISTS ] cluster_name ADD STORAGESPACE storage_space_name
```
| Parameter | Type | Description |
| -------------------- | ------ | -------------------------------------------------------------------- |
| `cluster_name` | string | The name of the storage cluster to modify. |
| `storage_space_name` | string | The name of an existing storage space to associate with the cluster. |
**Example**
This example adds the storage space `analytics_space` to the cluster `lts_cluster_0`.
```sql SQL theme={null}
ALTER CLUSTER lts_cluster_0 ADD STORAGESPACE analytics_space;
```
#### ALTER CLUSTER REMOVE STORAGESPACE
`ALTER CLUSTER REMOVE STORAGESPACE` detaches a storage space from a storage cluster. This detaching prevents the creation of new tables in that storage space within the cluster.
The statement fails if:
* Tables still exist in the storage space within the cluster. Remove all associated tables before detaching the storage space.
* The storage space is currently set as the system default storage space. Reset the default by using [ALTER SYSTEM SET DEFAULT STORAGESPACE](#alter-system-set-default-storagespace) before detaching the storage space.
* You are attempting to detach the core system storage space: `"systemStorageSpace"`.
**Required Privileges**
To detach a storage space from a cluster, you must have system-level privileges.
**Syntax**
```sql SQL theme={null}
ALTER CLUSTER [ IF EXISTS ] cluster_name REMOVE STORAGESPACE storage_space_name
```
| Parameter | Type | Description |
| -------------------- | ------ | --------------------------------------------------------- |
| `cluster_name` | string | The name of the storage cluster to modify. |
| `storage_space_name` | string | The name of the storage space to detach from the cluster. |
**Example**
This example detaches the storage space `analytics_space` from the cluster `lts_cluster_0`.
```sql SQL theme={null}
ALTER CLUSTER lts_cluster_0 REMOVE STORAGESPACE analytics_space;
```
#### ALTER CLUSTER ALTER CONFIG SET
`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](/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 SQL theme={null}
-- To set a new configuration override:
ALTER CLUSTER [ IF EXISTS ] cluster_name
ALTER CONFIG SET string_definition [=] string_value [,...]
-- To reset a configuration override:
ALTER CLUSTER [ IF EXISTS ] clusterName
ALTER CONFIG RESET [ string_definition [,...] ]
```
| **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 SQL theme={null}
ALTER CLUSTER cluster_name
ALTER CONFIG SET 'certificateStore.cert' = 'cert_name.crt';
```
This example resets the certificate configuration for cluster `cluster_name`.
```sql SQL theme={null}
ALTER CLUSTER cluster_name
ALTER CONFIG RESET 'certificateStore.cert';
```
This example alters the configuration at the cluster scope for cluster `cluster_name` and service role `lts`.
```sql SQL theme={null}
ALTER CLUSTER cluster_name
ALTER CONFIG SET 'lts.numLevels' = '3';
```
#### ALTER CLUSTER ALTER LOG LEVEL SET
`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](/inspect-the-current-configuration).
Syntax
```sql SQL theme={null}
-- To set a new configuration override:
ALTER CLUSTER cluster_name
ALTER LOG LEVEL SET [ [,...] ]
::=
{ 'ALL' | logger_name [=] logging_level }
-- To reset a configuration override:
ALTER CLUSTER cluster_name ALTER LOG LEVEL RESET [logger_name [, ...] ]
```
| **Parameter** | **Data** **Type** | **Description** |
| -------------- | ----------------- | ------------------------------------------- |
| `cluster_name` | string | The name of the specified cluster to alter. |
**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 [Log Monitoring](/log-monitoring) page for more details on log levels.
To reset a logger to the default log level (`INFO`), use the `RESET` keyword. |
**Examples**
This example alters the log level at the cluster scope. This sets the query log level for all nodes in the cluster to `DEBUG`.
```sql SQL theme={null}
ALTER CLUSTER cluster_name
ALTER LOG LEVEL SET 'query' = 'DEBUG';
```
This example resets the log level for the query logger for the entire cluster.
```sql SQL theme={null}
ALTER CLUSTER cluster_name
ALTER LOG LEVEL RESET 'query';
```
#### ALTER CLUSTER RENAME TO
Rename an existing cluster in the system.
**Syntax**
```sql SQL theme={null}
ALTER CLUSTER old_cluster_name RENAME TO new_cluster_name
```
| **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 named `lts-cluster-0` to `lts-cluster-1`.
```sql SQL theme={null}
ALTER CLUSTER "lts-cluster-0" RENAME TO "lts-cluster-1";
```
## STORAGESPACE
### CREATE STORAGESPACE
`CREATE STORAGESPACE` creates a new storage space. The name of the storage space 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 of a production system be smaller than the number of Foundation Nodes in the cluster so that loading can continue even in the event of a node outage.
**Syntax**
```sql SQL theme={null}
CREATE STORAGESPACE [ IF NOT EXISTS ] storage_space_name
[ WIDTH [=] width_integer], [ PARITY_WIDTH[=] pw_integer ]
```
| **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 named `ocient`.
```sql SQL theme={null}
CREATE STORAGESPACE ocient WIDTH = 10, PARITY_WIDTH = 2;
```
### DROP STORAGESPACE
`DROP STORAGESPACE` removes an existing storage space, along with all associated tables and views. The SQL statement fails 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 SQL theme={null}
DROP STORAGESPACE [ IF EXISTS ] storage_space_name [, ...]
```
| **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 SQL theme={null}
DROP STORAGESPACE ocient_ss;
```
Remove multiple storage spaces.
```sql SQL theme={null}
DROP STORAGESPACE ocient_ss1, ocient_ss2;
```
### 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 SQL theme={null}
ALTER STORAGESPACE [ IF EXISTS ] old_storage_space_name RENAME TO new_storage_space_name
```
| **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 named `teststoragespace` to `storage-space-1`.
```sql SQL theme={null}
ALTER STORAGESPACE "teststoragespace" RENAME TO "storage-space-1";
```
## 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 one of the two remaining Administration Nodes.
**Syntax**
```sql SQL theme={null}
DROP NODE [ IF EXISTS ] node_name [, ...]
```
| **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 SQL theme={null}
DROP NODE example_node;
```
Remove multiple nodes.
```sql SQL theme={null}
DROP NODE example_node1, example_node2;
```
### 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. Use this statement only to reverse these situations that can arise naturally (for example, you must replace a node with a node that has a different name).
If you have a DNS configuration for the node, you must reconfigure the node address after renaming the node by using the [ALTER NODE SET ADDRESS](#alter-node-set-address) SQL statement.
**Syntax**
```sql SQL theme={null}
ALTER NODE [ IF EXISTS ] old_node_name RENAME TO new_node_name
```
| **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 SQL theme={null}
ALTER NODE sql0 RENAME TO sql1;
```
#### 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 SQL theme={null}
ALTER NODE [ IF EXISTS ] node_name ADD ROLE [ IF NOT EXISTS ]
::=
{ sql | streamloader | operatorvm | admin | health }
```
| **Parameter** | **Data** **Type** | **Description** |
| ------------- | ----------------- | ---------------------------- |
| `node_name` | string | The name of a node to alter. |
#### **Node Roles (**``**)**
This 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 a Foundation 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**
Add SQL role to the node `sql1`.
```sql SQL theme={null}
ALTER NODE sql1 ADD ROLE sql;
```
#### ALTER NODE REMOVE ROLE
`ALTER NODE REMOVE ROLE` removes a role from a node. For a list of roles, see [Node Roles](#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 SQL theme={null}
ALTER NODE [ IF EXISTS ] node_name REMOVE ROLE [ IF EXISTS ]
{ sql | streamloader | admin }
```
| **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 SQL theme={null}
ALTER NODE my_node REMOVE ROLE 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](/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 SQL theme={null}
-- To set a new configuration override:
ALTER NODE [ IF EXISTS ] node_name
ALTER CONFIG SET string_definition [=] string_value [,...]
-- To reset a configuration override:
ALTER NODE [ IF EXISTS ] node_name ALTER CONFIG RESET string_definition [,...]
```
| **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 SQL theme={null}
ALTER NODE node2 ALTER CONFIG SET 'certificateStore.cert' = 'cert_name.crt';
```
This example resets the certificate configuration for the node `node2`.
```sql SQL theme={null}
ALTER NODE node2 ALTER CONFIG RESET 'certificateStore.cert';
```
This example alters the configuration at the node scope for node `node2` for service role `lts`.
```sql SQL theme={null}
ALTER NODE node2 ALTER CONFIG SET 'lts.numLevels' = '3';
```
#### 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](/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](/log-monitoring#log-configuration) for more details.
To reset a logger to the default log level (INFO), use the `RESET` keyword.
**Syntax**
```sql SQL theme={null}
-- To set a new configuration override:
ALTER NODE [ IF EXISTS ] node_name ALTER LOG LEVEL SET { [,...] }
::=
'ALL' | logger_name [=] { 'EDEBUG' | 'DEBUG' | 'VERBOSE' | 'INFO' | 'WARN' | 'ERROR' }
-- To reset a configuration override:
ALTER NODE [ IF EXISTS ] node_name ALTER LOG LEVEL RESET [ logger_name [,...] ]
```
| **Parameter** | **Data** **Type** | **Description** |
| --------------------- | ----------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `node_name` | string | The name of the specified node to alter. |
| `` | 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](/log-monitoring) 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 SQL theme={null}
ALTER NODE node_name ALTER LOG LEVEL SET 'query' = 'DEBUG';
```
This example resets log level for the query logger on the specified node.
```sql SQL theme={null}
ALTER NODE node_name ALTER LOG LEVEL RESET 'query';
```
#### ALTER NODE ALTER METRIC LEVEL
Alters the reporting level of various internal metrics on a specific node. For details about internal metrics, see [Statistics Monitoring](/statistics-monitoring).
To set metrics reporting on all nodes across the system, see [ALTER SYSTEM ALTER METRIC LEVEL](#alter-system-alter-metric-level).
**Syntax**
```sql SQL theme={null}
ALTER NODE [ IF EXISTS ] node_name ALTER METRIC LEVEL
{ SET | RESET }
::=
[ LIKE | REGEX ] metric_to_alter [ ,... ] [=] { 'INFO' | 'DEBUG' }
::=
{ [ LIKE | REGEX ] metric_to_reset [ ,... ] | ALL }
```
| **Parameter** | **Data** **Type** | **Description** |
| ------------- | ----------------- | ---------------------------- |
| `node_name` | string | The name of a node to alter. |
#### Set Metric Levels ``
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 ``
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 SQL theme={null}
ALTER NODE oc1 ALTER METRIC LEVEL
SET 'resultCache.queries' = 'DEBUG';
```
This example uses the `LIKE` wildcard keyword to capture any metrics prefixed by `localStorageService.device.smart.` and assign them to the `INFO` level.
```sql SQL theme={null}
ALTER NODE oc1 ALTER METRIC LEVEL
SET LIKE 'localStorageService.device.smart.%' 'INFO';
```
Similar to the previous example, this SQL statement uses regular expressions to capture any metrics prefixed by `localStorageService.device.smart.`.
```sql SQL theme={null}
ALTER NODE oc1 ALTER METRIC LEVEL
SET REGEX 'localStorageService.device.smart.*' 'INFO';
```
This example alters the level of multiple metrics.
```sql SQL theme={null}
ALTER NODE oc1 ALTER METRIC LEVEL
SET 'resultCache.queries' = 'DEBUG',
'resultCache.data' = 'INFO',
LIKE 'localStorageService.device.smart.%' 'INFO';
```
This example resets the same metrics.
```sql SQL theme={null}
ALTER NODE oc1 ALTER METRIC LEVEL
RESET 'resultCache.queries',
'resultCache.data',
LIKE 'localStorageService.device.smart.%';
```
#### ALTER NODE SET ADDRESS
`ALTER NODE node_name SET ADDRESS ip_address` changes the internal IP address or hostname for any node. You must restart the system after you change the address. If you change the address of an Administrator Node, then you must update the `bootstrap.conf` file for every node with the new address.
For details about this file, see [Ocient System Bootstrapping](/ocient-system-bootstrapping).
**Syntax**
```sql SQL theme={null}
ALTER NODE [ IF EXISTS ] node_name SET ADDRESS node_address
```
| **Parameter** | **Data** **Type** | **Description** |
| -------------- | ----------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `node_name` | string | The name of a node to alter. |
| `node_address` | string | The address for a node.
This address can be an internal IP address (e.g., `111.11.11.111`) or a DNS hostname with the node address matching the node name (e.g., `sql1`). |
**Examples**
This example changes the IP address to `111.11.11.111` for the node named `my_node`.
```sql SQL theme={null}
ALTER NODE my_node SET ADDRESS '111.11.11.111';
```
This example changes the SQL Node `sql1` to use the DNS address `sql1`.
```sql SQL theme={null}
ALTER NODE sql1 SET ADDRESS 'sql1';
```
## 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](/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.
For information on all connectivity pools on the system, query the system catalog tables for [Connectivity Pools](/system-catalog#connectivity-pools). To see the connectivity pool assignment for each node, query the system catalog table for [connectivity pool participants](/system-catalog#sys-connectivity_pool_participants).
**Syntax**
```sql SQL theme={null}
CREATE CONNECTIVITY_POOL [IF NOT EXISTS] pool_name
::=
SOURCE_ADDRESS [=] source_ip
| [ SOURCE_PORT [=] source_port ]
| PRIORITY [=] priority
| [ SSO INTEGRATION [=] sso_name ]
| PARTICIPANTS (
(NODE [=] node_name
LISTEN_ADDRESS [=] listen_ip
LISTEN_PORT [=] listen_port
ADVERTISED_ADDRESS [=] advertised_ip
[ ADVERTISED_PORT [=] advertised_port ]
[ OPENAPI_PORT [=] openapi_port ), ...)
```
| **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 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. |
| `sso_name` | string | The name of an SSO integration to use for the connectivity pool. For details, see [SSO INTEGRATION](#sso-integration). |
| `node_name` | string | The name of the SQL Node. |
| `listen_ip` | string | The IP address for listening.
ℹ️ Connectivity pools do not support dynamic IP addresses. If the IP address of your SQL Node changes, you must manually update it by using an [ALTER CONNECTIVITY\_POOL ALTER PARTICIPANT](#alter-connectivity_pool-alter-participant) SQL statement. |
| `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. |
| `openapi_port` | integer | The specified port for HTTP Query API-compliant specifications and endpoints. |
**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 SQL theme={null}
CREATE CONNECTIVITY_POOL IF NOT EXISTS test_pool
SOURCE_ADDRESS '1.2.3.4/32'
PRIORITY 1
PARTICIPANTS (
(NODE sql0
LISTEN_ADDRESS '111.1.1.1'
LISTEN_PORT 4050
ADVERTISED_ADDRESS 'localhost'));
```
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 SQL theme={null}
CREATE CONNECTIVITY_POOL IF NOT EXISTS test_pool
SOURCE_ADDRESS '1.2.3.4/32'
SOURCE_PORT 44
PRIORITY 1
PARTICIPANTS (
(NODE sql0
LISTEN_ADDRESS '111.1.1.1'
LISTEN_PORT 4050
ADVERTISED_ADDRESS 'localhost'
ADVERTISED_PORT 4050));
```
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 SQL theme={null}
CREATE CONNECTIVITY_POOL IF NOT EXISTS test_pool
SOURCE_ADDRESS '1.2.3.4/32'
SOURCE_PORT 44
PRIORITY 2
PARTICIPANTS (
(NODE sql0
LISTEN_ADDRESS '111.1.1.1'
LISTEN_PORT 4050
ADVERTISED_ADDRESS 'localhost'
ADVERTISED_PORT 4050),
(NODE sql1
LISTEN_ADDRESS '111.1.1.1'
LISTEN_PORT 4050
ADVERTISED_ADDRESS 'localhost'
ADVERTISED_PORT 4050),
(NODE sql2
LISTEN_ADDRESS '111.1.1.1'
LISTEN_PORT 4050
ADVERTISED_ADDRESS 'localhost'
ADVERTISED_PORT 4050));
```
### 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 SQL theme={null}
DROP CONNECTIVITY_POOL [ IF EXISTS ] pool_name
```
| **Parameter** | **Data** **Type** | **Description** |
| ------------- | ----------------- | -------------------------------------------- |
| `pool_name` | string | The name of the connectivity pool to remove. |
**Example**
Remove the connectivity pool named `test_pool`.
```sql SQL theme={null}
DROP CONNECTIVITY_POOL test_pool;
```
### 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 number, priority, and node participants.
**Syntax**
```sql SQL theme={null}
ALTER CONNECTIVITY_POOL [ IF EXISTS ] pool_name SET
::=
SOURCE_ADDRESS [=] source_ip |
SOURCE_PORT [=] source_port |
PRIORITY [=] priority |
PARTICIPANTS(
(NODE [=] node_name
LISTEN_ADDRESS [=] listen_ip
LISTEN_PORT [=] listen_port
ADVERTISED_ADDRESS [=] advertised_ip
[ADVERTISED_PORT [=] advertised_port] ), ...)
```
For parameter definitions, see [CREATE CONNECTIVITY POOL](#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 SQL theme={null}
ALTER CONNECTIVITY_POOL test_pool SET PRIORITY = 2;
```
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 SQL theme={null}
ALTER CONNECTIVITY_POOL test_pool
SET PARTICIPANTS(
(NODE sql1
LISTEN_ADDRESS '111.1.1.1'
LISTEN_PORT 4050
ADVERTISED_ADDRESS 'localhost'
ADVERTISED_PORT 4050),
(NODE sql2
LISTEN_ADDRESS '111.1.1.1'
LISTEN_PORT 4050
ADVERTISED_ADDRESS 'localhost'
ADVERTISED_PORT 4050));
```
View the node identifiers of the participants of the `test_pool` connectivity pool by querying the node identifier `node_id` in the `sys.connectivity_pool_participants` and `sys.connectivity_pools` system catalog tables.
```sql SQL theme={null}
SELECT cpp.node_id
FROM sys.connectivity_pool_participants cpp
INNER JOIN sys.connectivity_pools cp
ON cpp.id = cp.id
WHERE cp.name = 'test_pool';
```
#### ALTER CONNECTIVITY\_POOL RENAME TO
`ALTER CONNECTIVITY_POOL RENAME TO` SQL statement renames the existing connectivity pool.
**Syntax**
```sql SQL theme={null}
ALTER CONNECTIVITY_POOL [ IF EXISTS ] pool_name RENAME TO new_pool_name
```
| **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 SQL theme={null}
ALTER CONNECTIVITY_POOL test_pool RENAME TO new_test_pool;
```
#### ALTER CONNECTIVITY\_POOL ADD PARTICIPANTS
`ALTER CONNECTIVITY_POOL ADD PARTICIPANTS` adds one or more nodes to the participant list of an existing connectivity pool.
For information on all connectivity pools on the system, query the system catalog tables for [Connectivity Pools](/system-catalog). To see the connectivity pool assignment for each node, query the system catalog table for [connectivity pool participants](/system-catalog).
**Syntax**
```sql SQL theme={null}
ALTER CONNECTIVITY_POOL [ IF EXISTS ] pool_name
ADD PARTICIPANTS(
(NODE [=] node_name
LISTEN_ADDRESS [=] listen_ip
LISTEN_PORT [=] listen_port
ADVERTISED_ADDRESS [=] advertised_ip
[ADVERTISED_PORT [=] advertised_port] ), ...)
```
**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 SQL theme={null}
ALTER CONNECTIVITY_POOL test_pool
ADD PARTICIPANTS(
NODE sql5
LISTEN_ADDRESS '111.1.1.1'
LISTEN_PORT 4050
ADVERTISED_ADDRESS 'localhost'
ADVERTISED_PORT 4050);
```
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 SQL theme={null}
ALTER CONNECTIVITY_POOL test_pool
ADD PARTICIPANTS(
(NODE sql6
LISTEN_ADDRESS '111.1.1.1'
LISTEN_PORT 4050
ADVERTISED_ADDRESS 'localhost'
ADVERTISED_PORT 4050),
(NODE sql7
LISTEN_ADDRESS '111.1.1.1'
LISTEN_PORT 4050
ADVERTISED_ADDRESS 'localhost'
ADVERTISED_PORT 4050));
```
#### ALTER CONNECTIVITY\_POOL ALTER PARTICIPANT
`ALTER CONNECTIVITY_POOL ALTER PARTICIPANT` modifies the configurations of one SQL Node assigned to the existing connectivity pool.
This SQL statement can alter only one configuration parameter of a participant node at a time.
For information on all connectivity pools on the system, query the system catalog tables for [Connectivity Pools](/system-catalog). To see the connectivity pool assignment for each node, query the system catalog table for [connectivity pool participants](/system-catalog).
**Syntax**
```sql SQL theme={null}
ALTER CONNECTIVITY_POOL [ IF EXISTS ] pool_name
ALTER PARTICIPANT node_name
SET
::=
{ LISTEN_ADDRESS [=] listen_ip
| LISTEN_PORT [=] listen_port
| ADVERTISED_ADDRESS [=] advertised_ip
| ADVERTISED_PORT [=] advertised_port
| OPENAPI_PORT [=] openapi_port }
```
| **Parameter** | **Data** **Type** | **Description** |
| ----------------- | ----------------- | ------------------------------------------------------------------------------------------------------------------------------------------------- |
| `pool_name` | string | The name of the connectivity pool. |
| `node_name` | string | The name of the SQL Node to alter. |
| `listen_ip` | string | The IP address for listening. |
| `listen_port` | numeric | The port number for listening. |
| `advertised_ip` | string | The IP address to return to the client. |
| `advertised_port` | numeric | 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. |
| `openapi_port` | numeric | The specified port for HTTP Query API-compliant specifications and endpoints. |
**Example**
Modify the SQL Node `sql0` to use the new `listen_port` value `4051`.
```sql SQL theme={null}
ALTER CONNECTIVITY_POOL cp_test
ALTER PARTICIPANT sql0
SET listen_port 4051;
```
#### 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, the Ocient System automatically removes the connectivity pool.
For information on all connectivity pools on the system, query the system catalog tables for [Connectivity Pools](/system-catalog). To see the connectivity pool assignment for each node, query the system catalog table for [connectivity pool participants](/system-catalog).
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 SQL theme={null}
ALTER CONNECTIVITY_POOL [ IF EXISTS ] pool_name
DROP PARTICIPANTS (node_name, ...)
```
**Examples**
Remove one node `sql5` from the connectivity pool `test_pool`.
```sql SQL theme={null}
ALTER CONNECTIVITY_POOL IF EXISTS test_pool DROP PARTICIPANTS sql5;
```
Remove nodes `sql6` and `sql7` from the connectivity pool `test_pool`.
```sql SQL theme={null}
ALTER CONNECTIVITY_POOL test_pool DROP PARTICIPANTS (sql6, sql7);
```
#### ALTER CONNECTIVITY\_POOL SET SSO INTEGRATION
`ALTER CONNECTIVITY_POOL SET SSO INTEGRATION` assigns a connectivity pool to use a specific SSO integration.
Connectivity pools look for the specified SSO integration name for each database you are trying to connect to. If the database does not support that SSO integration name, it uses the default SSO.
**Syntax**
```sql SQL theme={null}
ALTER CONNECTIVITY_POOL [ IF EXISTS ] pool_name
SET SSO INTEGRATION sso_name
```
| **Parameter** | **Data** **Type** | **Description** |
| ------------- | ----------------- | ------------------------------------------- |
| `pool_name` | string | The name of the existing connectivity pool. |
| `sso_name` | string | The new name of the SSO integration. |
**Example**
```sql SQL theme={null}
ALTER CONNECTIVITY_POOL cp_test SET SSO INTEGRATION sso_test;
```
#### ALTER CONNECTIVITY\_POOL REMOVE SSO INTEGRATION
`ALTER CONNECTIVITY_POOL REMOVE SSO INTEGRATION` removes an assigned SSO integration.
**Syntax**
```sql SQL theme={null}
ALTER CONNECTIVITY_POOL [ IF EXISTS ] pool_name
REMOVE SSO INTEGRATION sso_name
```
| **Parameter** | **Data** **Type** | **Description** |
| ------------- | ----------------- | ------------------------------------------- |
| `pool_name` | string | The name of the existing connectivity pool. |
| `sso_name` | string | The name of the existing SSO integration. |
**Example**
This example removes the SSO integration `sso_test` from the connectivity pool `cp_test`.
```sql SQL theme={null}
ALTER CONNECTIVITY_POOL cp_test REMOVE SSO INTEGRATION sso_test;
```
## SSO INTEGRATION
### CREATE SSO INTEGRATION
Creates a new SSO integration protocol, which can connect to databases as a database integration (see [ALTER DATABASE SET SSO INTEGRATION](/databases#alter-database-set-sso-integration) ) or connect using a connectivity pool (see [ALTER CONNECTIVITY\_POOL SET SSO INTEGRATION](#alter-connectivity_pool-set-sso-integration)).
**Syntax**
```sql SQL theme={null}
CREATE SSO INTEGRATION [IF NOT EXISTS ] sso_name
PROTOCOL sso_protocol [, ... ]
::=
-- literal or string value
property_name = value |
-- list of values
property_name = [ value [, ...] ] |
-- map of values
property_name = { key = value [, ...] }
```
| **Parameter** | **Type** | **Description** |
| --------------- | -------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `sso_name` | string | The identifier of the SSO integration to create. |
| `sso_protocol` | string | Supported values are: `oidc`, `openid`, and `openid_connect`.
All these values are aliases of each other and use the OAuth 2.0 Authorization. |
| `property_name` | string | One or more properties to include in the SSO integration. Each SSO property must be specified either as a literal, list, or map. See the SSO Properties table for the requirements for each supported SSO parameter.
SSO properties also have these rules:
You can place any string key or value between double quotations (e.g., `"default_group"`).
String values that contain characters other than `[a-zA-z] \| [0-9] \| '_' ` require double quotations (e.g., `"this.is.a.complex-$tring"`).
Using a NULL value clears the existing configuration of a list or map property (e.g., `user_claim_ids = NULL`). |
#### SSO Properties
| **Name** | **Required** | **Value Type** | **Default Value** | **Description** |
| -------------------------------- | ------------ | ------------------------------------------- | -------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `issuer` | Yes | string | None | The [complete URL](https://openid.net/specs/openid-connect-core-1_0.html#Terminology) for the OAuth 2.0 and OpenID Connect Authorization Server. This property value is the expected `\"iss\"` claim in access tokens validated by the database. |
| `client_id` | Yes | string | None | The [client identifier](https://openid.net/specs/openid-connect-core-1_0.html#Terminology) as registered with the OpenID Provider. |
| `client_secret` | Yes | string | None | The [client secret](https://openid.net/specs/openid-connect-core-1_0.html#IDTokenValidation) as registered with the OpenID Provider. This property is required for some SSO workflows. |
| `default_group` | Yes | string | None | The group users are assigned if no group is specified in the `group_claim_mappings` property for the OpenID Provider. |
| `redirect_host` | No | string | Depends on the connector (pyocient or JDBC). | Specifies the host name during SSO redirection. |
| `redirect_ssl` | No | Boolean | Depends on the connector (pyocient or JDBC). | Set to `true` to enable SSL callback during SSO redirection (i.e., redirect uses `https`).
Set to `false` to disable (i.e., redirect uses `http`). |
| `disabled` | No | Boolean | `false` | Set to `true` to disable the OIDC integration for maintenance temporarily.
⚠️ If you set this property to `true`, all authentication requests using this connection fail. |
| `enable_id_token_authentication` | No | Boolean | `false` | Set this property to `true` if the identifier token also contains the authorization token.
In most circumstances, this option is necessary only for machine-to-machine connections without user interaction, such as a server using a script to connect to an Ocient System. |
| `user_id_claims` | No | list of strings (e.g., value \[, ...] | \["email"] | Set the identifier token claims used to identify users in audit trails. If you do not set a value, the system uses the `"email"` claim if it is present, otherwise it uses `["iss", "sub"]`. |
| `additional_scopes` | No | list of strings (e.g., value \[, ...] | \[] | Specifies additional scopes to request when executing the Authorization Code Flow. |
| `additional_audiences` | No | list of strings (e.g., value \[, ...] | \[] | Specifies additional audiences to accept when validating tokens. This property is useful for authorization servers without the token exchange capability.
Each additional audience is a case-sensitive URL from a provider, similar to the `issuer` value. |
| `groups_claim_ids` | No | list of strings (e.g., value \[, ...] | \[] | Specifies the token claims that can be used to map the user to a Database group. If you specify the `groups_claim_mappings` property, you must also specify the `groups_claim_ids` property. |
| `groups_claim_mappings` | No | map of strings (e.g., key = value \[, ...]) | {} | Specifies mappings from the Provider group to the Database group. |
| `roles_claim_ids` | No | list of strings (e.g., value \[, ...] | \[] | Specifies the token claims that can be used to map the user to a Database role. If you specify the `roles_claim_mappings` property, you must also specify the `roles_claim_ids` property. |
| `roles_claim_mappings` | No | map of strings (e.g., key = value \[, ...]) | {} | Specifies mappings from the Provider role to the Database role. |
| `allowed_groups` | No | list of strings (e.g., value \[, ...] | \[] | Specifies a list of external identity provider groups that are permitted to authenticate through this SSO integration. If you specify the `allowed_groups` property, only users who are members of the specified identify provider groups can access the Ocient System.
If this property is empty or you do not specify it, then group-based filtering is disabled, and the system allows all authenticated users from the provider unless they are explicitly blocked. Group names must match exactly as they appear in the claims of the identify provider group. |
| `allowed_roles` | No | list of strings (e.g., value \[, ...] | \[] | Defines a list of external identity provider roles that are authorized to access the Ocient System through this SSO integration. Only users assigned to the specified roles in the external provider can authenticate.
If this property is empty or you do not specify it, role-based access control is disabled for this integration. Role names must correspond exactly to the role claims provided by the identity provider in the authentication response. |
| `blocked_groups` | No | list of strings (e.g., value \[, ...] | \[] | Specifies a list of external identity provider groups that are explicitly denied access through this SSO integration. The system blocks users who are members of any specified group in the external identity provider from authenticating, regardless of other permissions they have. Setting this property takes precedence over the `allowed_groups` property if a user belongs to both an allowed group and a blocked group. Group names must match exactly as they appear in the claims of the identify provider group. |
| `blocked_roles` | No | list of strings (e.g., value \[, ...] | \[] | Defines a list of external identity provider roles that are explicitly prohibited from accessing the Ocient System through this SSO integration. The system denies access to users assigned to any of the specified roles, overriding any other access permissions. Setting this property takes precedence over the
`allowed_roles` property in cases where a user is in both an allowed role and a blocked role. Role names must correspond exactly to the role claims of the identity provider group. |
| `allow_offline_access` | No | Boolean | `false` | When the `allow_offline_access` property is `true`, Ocient requests offline access from the OpenID Connect identity provider by including `access_type=offline` and `prompt=consent` in the authorization URL. |
**Example**
This example creates an SSO integration protocol with the specified OpenID issuer `"https://accounts.google.com"`, client identifier, and default group. Each of these parameters is required to create a new SSO integration. The example assumes preset values for the client identifier and default group.
```sql SQL theme={null}
CREATE SSO INTEGRATION sso_test PROTOCOL oidc
issuer = "https://accounts.google.com",
client_id = example_database_app_id,
default_group = example_database_group;
```
### DROP SSO INTEGRATION
Drops an SSO integration protocol.
**Syntax**
```sql SQL theme={null}
DROP SSO INTEGRATION [ IF EXISTS ] sso_name
```
| **Parameter** | **Type** | **Description** |
| ------------- | -------- | ---------------------------------------------- |
| `sso_name` | string | The identifier of the SSO integration to drop. |
**Example**
This SQL statement drops the SSO integration protocol `sso_test`.
```sql SQL theme={null}
DROP SSO INTEGRATION sso_test;
```
### ALTER SSO INTEGRATION
Alters an SSO integration protocol by renaming it or modifying its properties.
```sql SQL theme={null}
ALTER SSO INTEGRATION sso_name [ IF EXISTS ] {
RENAME TO new_name |
SET [, ... ]
}
::=
-- literal or string value
property_name = value |
-- list of values
property_name = [ value [, ...] ] |
-- map of values
property_name = { key = value [, ...] }
```
| **Parameter** | **Type** | **Description** |
| --------------- | -------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `sso_name` | string | The identifier of the SSO integration to alter. |
| `new_name` | string | The new name of the SSO integration. |
| `property_name` | string | One or more properties to include in the SSO integration. Each SSO property must be specified either as a literal, list, or map. See the [SSO Properties](#sso-properties) table to see the requirements for each supported SSO parameter.
SSO properties also have these rules:
You can place any string key or value between double quotations (e.g., `"default_group"`).
String values that contain characters other than `[a-zA-z] \| [0-9] \| '_' ` require double quotations (e.g., `"this.is.a.complex-$tring"`).
Using a NULL value clears the existing configuration of a list or map property (e.g., `user_claim_ids = NULL`). |
**Examples**
**Alter SSO Integration Properties**
This example alters the SSO integration to use different properties, changing the OpenID client identifier and the default group to `"group2"`. The example assumes a preset value for the client identifier.
```sql SQL theme={null}
ALTER SSO INTEGRATION sso_test SET
client_id = different_database_app_id,
default_group = "group2";
```
**Rename an SSO Integration**
This example renames the SSO integration to `sso_test_2`.
```sql SQL theme={null}
ALTER SSO INTEGRATION sso_test RENAME TO sso_test_2;
```
## SYSTEM
### ALTER SYSTEM ALTER CONFIG SET
`ALTER SYSTEM ALTER CONFIG SET` sets an override for the configuration at the system scope. For an explanation of how configuration overrides work, see [Inspect the Current Configuration](/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 parameter, use the `RESET` keyword.
This configuration override is set in the base node configuration by default. To apply the override to a specific service role, prefix the key with the service role name.
**Syntax**
```sql SQL theme={null}
-- To set a new configuration override:
ALTER SYSTEM
ALTER CONFIG SET parameter_name [=] parameter_value [,...]
-- To reset a configuration override:
ALTER SYSTEM
ALTER CONFIG RESET [ parameter_name [,...] ]
```
| **Parameter** | **Data** **Type** | **Description** |
| ----------------- | ----------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `parameter_name` | string | The name of a configuration parameter to alter. This parameter should be a string literal (i.e., enclosed in single quotes).
Contact Ocient Support for details about altering configuration parameters. |
| `parameter_value` | any | The new value to be set for the specified configuration parameter.
The data type of this value depends on the configuration parameter. This can be a string literal that can be cast to the data type of the configuration parameter. For example, the string literal `'true'` is equivalent to the Boolean value `true`. |
**Examples**
This example alters the base configuration of the system scope. The example sets the certificate name for each node in the entire system to `cert_name.crt`.
```sql SQL theme={null}
ALTER SYSTEM ALTER CONFIG SET 'certificateStore.cert' = 'cert_name.crt';
```
This example resets the certificate configuration for the entire system.
```sql SQL theme={null}
ALTER SYSTEM ALTER CONFIG RESET 'certificateStore.cert';
```
This example alters the configuration for the service role `sql`. The `purgeCacheFrequency` setting determines the frequency in seconds to purge the result-set cache of stale values.
```sql SQL theme={null}
ALTER SYSTEM ALTER CONFIG SET 'sql.purgeCacheFrequency' = 600;
```
#### ALTER SYSTEM ALTER LOG LEVEL
`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](/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](/log-monitoring) for more details.
**Syntax**
```sql SQL theme={null}
-- To set a new configuration override:
ALTER SYSTEM ALTER LOG LEVEL SET { [,...] }
::=
'ALL' | logger_name [=] { 'EDEBUG' | 'DEBUG' | 'VERBOSE' | 'INFO' | 'WARN' | 'ERROR' }
-- To reset a configuration override:
ALTER SYSTEM ALTER LOG LEVEL RESET [logger_name [, ...]]
```
| **Parameter** | **Data** **Type** | **Description** |
| --------------------- | ----------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `` | 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](/log-monitoring) 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 SQL theme={null}
ALTER SYSTEM ALTER LOG LEVEL SET 'ALL' = 'DEBUG';
```
### 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](/other-functions-and-expressions#current_system) function.
**Syntax**
```sql SQL theme={null}
ALTER SYSTEM RENAME TO system_name
```
| **Parameter** | **Data** **Type** | **Description** |
| ------------- | ----------------- | ------------------------------ |
| `system_name` | string | The name of the Ocient System. |
**Example**
```sql SQL theme={null}
ALTER SYSTEM RENAME TO "productionSystem";
```
### ALTER SYSTEM ALTER METRIC LEVEL
Alters the reporting level of various internal metrics across the system, including all nodes. For details about internal metrics, see [Statistics Monitoring](/statistics-monitoring).
To set metrics reporting for individual nodes, see [ALTER NODE ALTER METRIC LEVEL](#alter-node-alter-metric-level).
**Syntax**
```sql SQL theme={null}
ALTER SYSTEM ALTER METRIC LEVEL
{ SET | RESET }
::=
[ LIKE | REGEX ] metric_to_alter [ ,... ] [=] { 'INFO' | 'DEBUG' }
::=
{ [ LIKE | REGEX ] metric_to_reset [ ,... ] | ALL }
```
#### Set Metric Levels ``
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 ``
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 SQL theme={null}
ALTER SYSTEM ALTER METRIC LEVEL
SET 'resultCache.queries' = 'DEBUG';
```
This example uses the `LIKE` wildcard to capture any metrics prefixed by `localStorageService.device.smart.` and assign them to the `INFO` level.
```sql SQL theme={null}
ALTER SYSTEM ALTER METRIC LEVEL
SET LIKE 'localStorageService.device.smart.%' 'INFO';
```
Similar to the previous example, this SQL statement uses regular expressions to capture any metrics prefixed by `localStorageService.device.smart.`.
```sql SQL theme={null}
ALTER SYSTEM ALTER METRIC LEVEL
SET REGEX 'localStorageService.device.smart.*' 'INFO';
```
This example alters the level of multiple metrics.
```sql SQL theme={null}
ALTER SYSTEM ALTER METRIC LEVEL
SET 'resultCache.queries' = 'DEBUG',
'resultCache.data' = 'INFO',
LIKE 'localStorageService.device.smart.%' 'INFO';
```
This example resets the same metrics.
```sql SQL theme={null}
ALTER SYSTEM ALTER METRIC LEVEL
RESET 'resultCache.queries',
'resultCache.data',
LIKE 'localStorageService.device.smart.%';
```
### ALTER SYSTEM ALTER SECURITY
Set security settings using the `ALTER SYSTEM ALTER SECURITY` SQL statement. Replace `` with the security setting and `` with the value.
You can inspect the current values of system configuration settings using the `sys.config` and `sys.node_config` system catalog tables.
**Syntax**
```sql SQL theme={null}
ALTER SYSTEM ALTER SECURITY [=]
```
| **Parameter** | **Data** **Type** | **Description** |
| ------------------ | ----------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `security_setting` | string | The security setting with values:
\* `password_minimum_length`
\* `password_complexity_level`
\* `password_no_repeat_count`
\* `password_lifetime_days`
\* `password_invalid_attempt_limit`
For details about these values, see [Database Password Security Settings](/database-password-security-settings). |
| `value` | numeric | An integer to represent one of the security settings. For details about this value, see [Database Password Security Settings](/database-password-security-settings). |
**Examples**
**Set a Security Setting for All Databases**
Set the password lifetime to 30 days for the entire system.
```sql SQL theme={null}
ALTER SYSTEM ALTER SECURITY password_lifetime_days = 30;
```
**Set Multiple Security Settings**
Set multiple security settings in a single SQL statement. In this case, set the password lifetime to 30 days and the minimum password character length to 12. As with other similar SQL statements, the `=` character is optional.
```sql SQL theme={null}
ALTER SYSTEM ALTER SECURITY
password_lifetime_days 30,
password_minimum_length 12;
```
### ALTER SYSTEM SET DEFAULT STORAGESPACE
Sets the specified storage space as the default for creating new tables.
`CREATE TABLE` SQL statements automatically use the default storage space unless you specify a different storage space (see [Table Options](/tables#create-option-\)). After you create a table, you cannot alter its assigned storage space. Statements such as `DROP STORAGESPACE` and `ALTER CLUSTER REMOVE STORAGESPACE` throw an error if they attempt to drop the default storage space of the system.
To remove the default storage space, use the optional `RESET` keyword. The `RESET` keyword alters the system to have no default storage space. Until you set a storage space as the new default, `CREATE TABLE` statements throw an error if they do not specify a storage space for the new table.
**Required Privileges**
To use this SQL statement, you must have the `ALTER` privilege for the system. For details about privileges, see [Data Control Language (DCL) Statement Reference](/data-control-language-dcl-statement-reference).
**Syntax**
```sql SQL theme={null}
ALTER SYSTEM { SET | RESET } DEFAULT STORAGESPACE [ storage_space_name ]
```
| **Parameter** | **Data** **Type** | **Description** |
| -------------------- | ----------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `storage_space_name` | string | The name of a storage space to set as the system default. This parameter is required when you use the `SET` keyword to define a default storage space.
Do not include this parameter when you use the `RESET` keyword to remove the default storage space. |
**Examples**
**Set a Default Storage Space**
This example sets `ss_0` as the system default storage space.
```sql SQL theme={null}
ALTER SYSTEM SET DEFAULT STORAGESPACE ss_0;
```
**Remove the Default Storage Space**
This example removes any default storage space.
```sql SQL theme={null}
ALTER SYSTEM RESET DEFAULT STORAGESPACE;
```
## Related Links
[Other Functions and Expressions](/other-functions-and-expressions)
[CREATE TABLE SQL Statement Examples](/create-table-sql-statement-examples)
[Database Password Security Settings](/database-password-security-settings)