Maintenance Statement

1. Status Checking

1.1 Viewing the Connected Model

Description: Returns the current SQL dialect mode (Tree or Table).

Syntax:

showCurrentSqlDialectStatement
    : SHOW CURRENT_SQL_DIALECT
    ;

Example:

IoTDB> SHOW CURRENT_SQL_DIALECT;

Result：

+-----------------+
|CurrentSqlDialect|
+-----------------+
|             TREE|
+-----------------+

1.2 Viewing the Cluster Version

Description: Returns the current cluster version.

Syntax:

showVersionStatement
    : SHOW VERSION
    ;

Example:

IoTDB> SHOW VERSION;

Result:

+-------+---------+
|Version|BuildInfo|
+-------+---------+
|2.0.1.2|  1ca4008|
+-------+---------+

1.3 Viewing Cluster Key Parameters

Description: Returns key parameters of the current cluster.

Syntax:

showVariablesStatement
    : SHOW VARIABLES
    ;

Key Parameters:

  1. ClusterName: The name of the current cluster.
  2. DataReplicationFactor: Number of data replicas per DataRegion.
  3. SchemaReplicationFactor: Number of schema replicas per SchemaRegion.
  4. DataRegionConsensusProtocolClass: Consensus protocol class for DataRegions.
  5. SchemaRegionConsensusProtocolClass: Consensus protocol class for SchemaRegions.
  6. ConfigNodeConsensusProtocolClass: Consensus protocol class for ConfigNodes.
  7. TimePartitionOrigin: The starting timestamp of database time partitions.
  8. TimePartitionInterval: The interval of database time partitions (in milliseconds).
  9. ReadConsistencyLevel: The consistency level for read operations.
  10. SchemaRegionPerDataNode: Number of SchemaRegions per DataNode.
  11. DataRegionPerDataNode: Number of DataRegions per DataNode.
  12. SeriesSlotNum: Number of SeriesSlots per DataRegion.
  13. SeriesSlotExecutorClass: Implementation class for SeriesSlots.
  14. DiskSpaceWarningThreshold: Disk space warning threshold (in percentage).
  15. TimestampPrecision: Timestamp precision.

Example:

IoTDB> SHOW VARIABLES;

Result:

+----------------------------------+-----------------------------------------------------------------+
|                          Variable|                                                            Value|
+----------------------------------+-----------------------------------------------------------------+
|                       ClusterName|                                                   defaultCluster|
|             DataReplicationFactor|                                                                1|
|           SchemaReplicationFactor|                                                                1|
|  DataRegionConsensusProtocolClass|                      org.apache.iotdb.consensus.iot.IoTConsensus|
|SchemaRegionConsensusProtocolClass|                  org.apache.iotdb.consensus.ratis.RatisConsensus|
|  ConfigNodeConsensusProtocolClass|                  org.apache.iotdb.consensus.ratis.RatisConsensus|
|               TimePartitionOrigin|                                                                0|
|             TimePartitionInterval|                                                        604800000|
|              ReadConsistencyLevel|                                                           strong|
|           SchemaRegionPerDataNode|                                                                1|
|             DataRegionPerDataNode|                                                                0|
|                     SeriesSlotNum|                                                             1000|
|           SeriesSlotExecutorClass|org.apache.iotdb.commons.partition.executor.hash.BKDRHashExecutor|
|         DiskSpaceWarningThreshold|                                                             0.05|
|                TimestampPrecision|                                                               ms|
+----------------------------------+-----------------------------------------------------------------+

1.4 Viewing the Current Timestamp of Database

Description: Returns the current timestamp of the database.

Syntax:

showCurrentTimestampStatement
    : SHOW CURRENT_TIMESTAMP
    ;

Example:

IoTDB> SHOW CURRENT_TIMESTAMP;

Result:

+-----------------------------+
|             CurrentTimestamp|
+-----------------------------+
|2025-02-17T11:11:52.987+08:00|
+-----------------------------+

1.5 Viewing Executing Queries

Description: Displays information about all currently executing queries.

Syntax:

showQueriesStatement
    : SHOW (QUERIES | QUERY PROCESSLIST)
        (WHERE where=booleanExpression)?
        (ORDER BY sortItem (',' sortItem)*)?
        limitOffsetClause
    ;

Parameters:

  1. WHERE Clause: Filters the result set based on specified conditions.
  2. ORDER BY Clause: Sorts the result set based on specified columns.
  3. limitOffsetClause: Limits the number of rows returned.
    1. Format: LIMIT <offset>, <row_count>.

Columns in QUERIES Table:

  • time: Timestamp when the query started.
  • queryid: Unique ID of the query.
  • datanodeid: ID of the DataNode executing the query.
  • elapsedtime: Time elapsed since the query started (in seconds).
  • statement: The SQL statement being executed.

Example:

IoTDB> SHOW QUERIES WHERE elapsedtime > 0.003

Result:

+-----------------------------+-----------------------+----------+-----------+--------------------------------------+
|                         Time|                QueryId|DataNodeId|ElapsedTime|                             Statement|
+-----------------------------+-----------------------+----------+-----------+--------------------------------------+
|2025-05-09T15:16:01.293+08:00|20250509_071601_00015_1|         1|      0.006|SHOW QUERIES WHERE elapsedtime > 0.003|
+-----------------------------+-----------------------+----------+-----------+--------------------------------------+

1.6 Viewing Region Information

Description: Displays regions' information of the current cluster.

Syntax:

showRegionsStatement
    : SHOW REGIONS
    ;

Example:

IoTDB> SHOW REGIONS

Result:

+--------+------------+-------+-------------+-------------+-----------+----------+----------+-------+---------------+------+-----------------------+----------+
|RegionId|        Type| Status|     Database|SeriesSlotNum|TimeSlotNum|DataNodeId|RpcAddress|RpcPort|InternalAddress|  Role|             CreateTime|TsFileSize|
+--------+------------+-------+-------------+-------------+-----------+----------+----------+-------+---------------+------+-----------------------+----------+
|       9|SchemaRegion|Running|root.__system|           21|          0|         1|   0.0.0.0|   6667|      127.0.0.1|Leader|2025-08-01T17:37:01.555|          |
|      10|  DataRegion|Running|root.__system|           21|         21|         1|   0.0.0.0|   6667|      127.0.0.1|Leader|2025-08-01T17:37:01.556|   8.27 KB|
|      65|SchemaRegion|Running|      root.ln|            1|          0|         1|   0.0.0.0|   6667|      127.0.0.1|Leader|2025-08-25T14:46:50.113|          |
|      66|  DataRegion|Running|      root.ln|            1|          1|         1|   0.0.0.0|   6667|      127.0.0.1|Leader|2025-08-25T14:46:50.425|     524 B|
+--------+------------+-------+-------------+-------------+-----------+----------+----------+-------+---------------+------+-----------------------+----------+

1.7 Viewing Available Nodes

Description: Returns the RPC addresses and ports of all available DataNodes in the current cluster. Note: A DataNode is considered “available” if it is not in the REMOVING state.

This feature is supported starting from v2.0.8.

Syntax:

showAvailableUrlsStatement
    : SHOW AVAILABLE URLS
    ;

Example:

IoTDB> SHOW AVAILABLE URLS

Result:

+----------+-------+
|RpcAddress|RpcPort|
+----------+-------+
|   0.0.0.0|   6667|
+----------+-------+

1.8 View Service Information

Description: Returns service information (MQTT service, REST service) on all active DataNodes (in RUNNING or READ-ONLY state) in the current cluster.

This feature is supported starting from v2.0.8.

Syntax:

showServicesStatement
    : SHOW SERVICES <ON dataNodeId>
    ;

Examples:

IoTDB> SHOW SERVICES
IoTDB> SHOW SERVICES ON 1

Execution result:

+--------------+-------------+---------+
| Service Name | DataNode ID | State   |
+--------------+-------------+---------+
| MQTT         | 1           | STOPPED |
| REST         | 1           | RUNNING |
+--------------+-------------+---------+

1.9 View Disk Space Usage

Description: Returns the disk space usage of the specified pattern, including the size of ChunkGroups and the size of Metadata.

Note: Statistics are based on the actual size of data in TsFiles; therefore, deletions made via mods are not considered.

Supported since version 2.0.9-beta

Syntax:

showDiskUsageStatement
    : SHOW DISK_USAGE FROM pathPattern 
    whereClause?
    orderByClause?
    rowPaginationClause?
    ;
pathPattern
    : ROOT (DOT nodeName)*
    ;

Explanation: The pattern is used to match devices, must start with ROOT, and intermediate nodes in the path support * or **.

Result Set

Column NameColumn TypeDescription
DatabasestringDatabase name
DataNodeIdint32DataNode node ID
RegionIdint32Region ID
TimePartitionint64Time partition ID
SizeInBytesint64Disk space occupied (in bytes)

Example:

SHOW DISK_USAGE FROM root.ln.**;

Execution Result:

+--------+----------+--------+-------------+-----------+
|Database|DataNodeId|RegionId|TimePartition|SizeInBytes|
+--------+----------+--------+-------------+-----------+
| root.ln|         1|      13|         2932|        203|
+--------+----------+--------+-------------+-----------+

2. Status Setting

2.1 Setting the Connected Model

Description: Sets the current SQL dialect mode to Tree or Table which can be used in both tree and table modes.

Syntax:

SET SQL_DIALECT = (TABLE | TREE);

Example:

IoTDB> SET SQL_DIALECT=TREE;
IoTDB> SHOW CURRENT_SQL_DIALECT;

Result:

+-----------------+
|CurrentSqlDialect|
+-----------------+
|            TREE|
+-----------------+

2.2 Updating Configuration Items

Description: Updates configuration items. Changes take effect immediately without restarting if the items support hot modification.

Syntax:

setConfigurationStatement
    : SET CONFIGURATION propertyAssignments (ON INTEGER_VALUE)?
    ;

propertyAssignments
    : property (',' property)*
    ;
    
property
    : identifier EQ propertyValue
    ;

propertyValue
    : DEFAULT
    | expression
    ;

Parameters:

  1. propertyAssignments: A list of properties to update.
    1. Format: property (',' property)*.
    2. Values:
      • DEFAULT: Resets the configuration to its default value.
      • expression: A specific value (must be a string).
  2. ON INTEGER_VALUE (Optional): Specifies the node ID to update.
    1. If not specified or set to a negative value, updates all ConfigNodes and DataNodes.

Example:

IoTDB> SET CONFIGURATION ‘disk_space_warning_threshold’='0.05',‘heartbeat_interval_in_ms’='1000' ON 1;

2.3 Loading Manually Modified Configuration Files

Description: Loads manually modified configuration files and hot-loads the changes. Configuration items that support hot modification take effect immediately.

Syntax:

loadConfigurationStatement
    : LOAD CONFIGURATION localOrClusterMode?
    ;
    
localOrClusterMode
    : (ON (LOCAL | CLUSTER))
    ;

Parameters:

  1. localOrClusterMode (Optional):
    1. Specifies the scope of configuration loading.
    2. Default: CLUSTER.
    3. Values:
      • LOCAL: Loads configuration only on the DataNode directly connected to the client.
      • CLUSTER: Loads configuration on all DataNodes in the cluster.

Example:

IoTDB> LOAD CONFIGURATION ON LOCAL;

2.4 Setting the System Status

Description: Sets the system status to either READONLY or RUNNING.

Syntax:

setSystemStatusStatement
    : SET SYSTEM TO (READONLY | RUNNING) localOrClusterMode?
    ;    
    
localOrClusterMode
    : (ON (LOCAL | CLUSTER))
    ;

Parameters:

  1. RUNNING | READONLY:
    1. RUNNING: Sets the system to running mode, allowing both read and write operations.
    2. READONLY: Sets the system to read-only mode, allowing only read operations and prohibiting writes.
  2. localOrClusterMode (Optional):
    1. LOCAL: Applies the status change only to the DataNode directly connected to the client.
    2. CLUSTER: Applies the status change to all DataNodes in the cluster.
    3. Default: ON CLUSTER.

Example:

IoTDB> SET SYSTEM TO READONLY ON CLUSTER;

3. Data Management

3.1 Flushing Data from Memory to Disk

Description: Flushes data from the memory table to disk.

Syntax:

flushStatement
    : FLUSH identifier? (',' identifier)* booleanValue? localOrClusterMode?
    ;

booleanValue
    : TRUE | FALSE
    ;

localOrClusterMode
    : (ON (LOCAL | CLUSTER))
    ;

Parameters:

  1. identifier (Optional):
    1. Specifies the name of the path to flush.
    2. If not specified, all paths are flushed.
    3. Multiple Paths: Multiple path names can be specified, separated by commas (e.g., FLUSH root.ln, root.lnm.**).
  2. booleanValue (Optional):
    1. Specifies the type of data to flush.
    2. TRUE: Flushes only the sequential memory table.
    3. FALSE: Flushes only the unsequential MemTable.
    4. Default: Flushes both sequential and unsequential memory tables.
  3. localOrClusterMode (Optional):
    1. ON LOCAL: Flushes only the memory tables on the DataNode directly connected to the client.
    2. ON CLUSTER: Flushes memory tables on all DataNodes in the cluster.
    3. Default: ON CLUSTER.

Example:

IoTDB> FLUSH root.ln TRUE ON LOCAL;

4. Data Repair

4.1 Starting Background Scan and Repair of TsFiles

Description: Starts a background task to scan and repair TsFiles, fixing issues such as timestamp disorder within data files.

Syntax:

startRepairDataStatement
    : START REPAIR DATA localOrClusterMode?
    ;
    
localOrClusterMode
    : (ON (LOCAL | CLUSTER))
    ;

Parameters:

  1. localOrClusterMode（Optional）:
    1. ON LOCAL: Executes the repair task only on the DataNode directly connected to the client.
    2. ON CLUSTER: Executes the repair task on all DataNodes in the cluster.
    3. Default: ON CLUSTER.

Example:

IoTDB> START REPAIR DATA ON CLUSTER;

4.2 Pausing Background TsFile Repair Task

Description: Pauses the background repair task. The paused task can be resumed by executing the START REPAIR DATA command again.

Syntax:

stopRepairDataStatement
    : STOP REPAIR DATA localOrClusterMode?
    ;
    
localOrClusterMode
    : (ON (LOCAL | CLUSTER))
    ;

Parameters:

  1. localOrClusterMode (Optional):
    1. ON LOCAL: Executes the pause command only on the DataNode directly connected to the client.
    2. ON CLUSTER: Executes the pause command on all DataNodes in the cluster.
    3. Default: ON CLUSTER.

Example:

IoTDB> STOP REPAIR DATA ON CLUSTER;

5. Query Termination

5.1 Terminating Queries

Description: Terminates one or more running queries.

Syntax:

killQueryStatement
    : KILL (QUERY queryId=string | ALL QUERIES)
    ;

Parameters:

  1. QUERY queryId: Specifies the ID of the query to terminate.
  • To obtain the queryId, use the SHOW QUERIES command.
  1. ALL QUERIES: Terminates all currently running queries.

Example:

Terminate a specific query:

IoTDB> KILL QUERY 20250108_101015_00000_1;

Terminate all queries:

IoTDB> KILL ALL QUERIES;

6. Query Debugging

6.1 DEBUG SQL

Definition: Add the DEBUG keyword at the beginning of an SQL query statement. During execution, debug logs will be output, including underlying file scan information involved in the query.

Supported since V2.0.9-beta

Syntax:

debugSQLStatement
    : DEBUG ? query
    ;

Description:

  • Log output directory: logs/log_datanode_query_debug.log

Example:

  1. Execute the following SQL for a DEBUG query
DEBUG SELECT * FROM root.ln.**;
  1. Check the log content in log_datanode_query_debug.log to view the file scan information involved in the query.
2026-03-24 10:06:18,755 [Query-Worker-Thread-3$20260324_020618_00052_1.1.0.0] INFO  o.a.i.d.s.b.TimeSeriesMetadataCache:159 - Cache miss: root.ln.wf01.wt01.temperature in file: /home/iotdb/timechodb/data/datanode/data/sequence/root.ln/13/2932/1773824951611-1-0-0.tsfile 
2026-03-24 10:06:18,757 [Query-Worker-Thread-3$20260324_020618_00052_1.1.0.0] INFO  o.a.i.d.s.b.TimeSeriesMetadataCache:160 - Device: root.ln.wf01.wt01, all sensors: [temperature] 
2026-03-24 10:06:18,758 [Query-Worker-Thread-3$20260324_020618_00052_1.1.0.0] INFO  o.a.i.d.s.b.BloomFilterCache:110 - get bloomFilter from cache where filePath is: /home/iotdb/timechodb/data/datanode/data/sequence/root.ln/13/2932/1773824951611-1-0-0.tsfile 
2026-03-24 10:06:18,759 [Query-Worker-Thread-3$20260324_020618_00052_1.1.0.0] INFO  o.a.i.d.s.b.TimeSeriesMetadataCache:227 - Get timeseries: root.ln.wf01.wt01.temperature metadata in file: /home/iotdb/timechodb/data/datanode/data/sequence/root.ln/13/2932/1773824951611-1-0-0.tsfile from cache: TimeseriesMetadata{timeSeriesMetadataType=0, chunkMetaDataListDataSize=8, measurementId='temperature', dataType=DOUBLE, statistics=startTime: 1773824951259 endTime: 1773824951259 count: 1 [minValue:12.9,maxValue:12.9,firstValue:12.9,lastValue:12.9,sumValue:12.9], modified=false, isSeq=true, chunkMetadataList=[measurementId: temperature, datatype: DOUBLE, version: 0, Statistics: startTime: 1773824951259 endTime: 1773824951259 count: 1 [minValue:12.9,maxValue:12.9,firstValue:12.9,lastValue:12.9,sumValue:12.9], deleteIntervalList: null]}. 
2026-03-24 10:06:18,759 [Query-Worker-Thread-3$20260324_020618_00052_1.1.0.0] INFO  o.a.i.d.s.d.r.r.c.m.DiskChunkMetadataLoader:97 - Modifications size is 0 for file Path: /home/iotdb/timechodb/data/datanode/data/sequence/root.ln/13/2932/1773824951611-1-0-0.tsfile  
2026-03-24 10:06:18,759 [Query-Worker-Thread-3$20260324_020618_00052_1.1.0.0] INFO  o.a.i.d.s.d.r.r.c.m.DiskChunkMetadataLoader:109 - After modification Chunk meta data list is:  
2026-03-24 10:06:18,759 [Query-Worker-Thread-3$20260324_020618_00052_1.1.0.0] INFO  o.a.i.d.s.d.r.r.c.m.DiskChunkMetadataLoader:110 - measurementId: temperature, datatype: DOUBLE, version: 0, Statistics: startTime: 1773824951259 endTime: 1773824951259 count: 1 [minValue:12.9,maxValue:12.9,firstValue:12.9,lastValue:12.9,sumValue:12.9], deleteIntervalList: null 
2026-03-24 10:06:18,760 [Query-Worker-Thread-3$20260324_020618_00052_1.1.0.0] INFO  o.a.i.d.s.b.ChunkCache:167 - get chunk from cache whose key is: ChunkCacheKey{filePath='/home/iotdb/timechodb/data/datanode/data/sequence/root.ln/13/2932/1773824951611-1-0-0.tsfile', regionId=13, timePartitionId=2932, tsFileVersion=1, compactionVersion=0, offsetOfChunkHeader=27} 
2026-03-24 10:06:18,761 [pool-69-IoTDB-ClientRPC-Processor-1$20260324_020618_00052_1] INFO  o.a.i.d.q.p.Coordinator:902 - debug select * from root.ln.**