DEV Community: Michael

Managing the FoundationDB Cluster in GCDW: Start/Stop, Scale, Replace, and Upgrade

Michael — Thu, 30 Jul 2026 12:50:00 +0000

FoundationDB serves as the metadata backbone for GCDW (GBase Cloud Data Warehouse). Keeping it healthy is essential for the entire gbase database platform. This guide covers the core operational tasks — service control, cluster file handling, node expansion, safe removal, hardware replacement, and version upgrades — using a 3‑node FDB cluster as the example.

Service Lifecycle and Auto‑Start

FoundationDB uses fdbmonitor to supervise fdbserver and backup_agent, while systemd supervises fdbmonitor. All commands must be run on each node individually.

# Start
systemctl start foundationdb

# Stop
systemctl stop foundationdb

# Check status (Active: active (running) indicates normal)
systemctl status foundationdb

# Disable auto‑start on boot
systemctl disable foundationdb

# Enable auto‑start on boot
systemctl enable foundationdb

Cluster File Management

The cluster connection string is stored in /etc/foundationdb/fdb.cluster and is identical on every node. Its format is description:ID@IP:PORT,IP:PORT,....

Specify a custom cluster file for clients: fdbcli -C /path/to/fdb.cluster
In APIs: Python's fdb.open(cluster_file=...) or Java's FDB.open(path) accept a custom path.
Environment variable: Set FDB_CLUSTER_FILE (lower priority than the ‑C flag, higher than the default location).
Retrieve current cluster info from a connected session:

  fdb> get \xFF\xFF/cluster_file_path    # file path
  fdb> get \xFF\xFF/connection_string    # connection string

Expanding the Cluster: Adding Nodes

Install the FoundationDB packages on the new node.
Copy the existing /etc/foundationdb/fdb.cluster to the same path on the new node.
Restart the FDB service:

   systemctl restart foundationdb

The new node will automatically join and start serving data.

Shrinking the Cluster: Removing Nodes

Verify redundancy: Ensure the remaining nodes still meet the redundancy mode (e.g., double requires at least 2 machines).
Reconfigure coordinators (if the node being removed is a coordinator):

   fdb> coordinators 10.0.2.81:4500 10.0.2.82:4500

Exclude the node to trigger data migration:

   fdb> exclude 10.0.2.83:4500

This operation can take a while. Pressing Ctrl+C only stops the progress display; the data movement continues in the background.
Run fdb> exclude to see the current exclusion list.
1. Stop and remove the service:

   systemctl stop foundationdb
   systemctl disable foundationdb
   yum remove foundationdb-server foundationdb-client

Rollback: To cancel an exclusion, run fdb> include all.

Replacing a Machine

A replacement is simply a combination of expansion and shrinkage: add the new node, wait for data rebalancing, then exclude and remove the old node.

Uninstallation and Upgrades

Uninstall: Remove the RPM packages directly.

  rpm -e foundationdb-clients foundationdb-server

Upgrade: Run the RPM upgrade on every node. All nodes must be upgraded to the same version.

  rpm -Uvh foundationdb-clients-7.2.3-1.el7.x86_64.rpm foundationdb-server-7.2.3-1.el7.x86_64.rpm

Key Takeaways

Mastering FoundationDB's start/stop, cluster file, and scaling procedures is essential for maintaining the high availability of your GCDW metadata layer. Always verify fdb.cluster permissions, and double‑check your redundancy policy before changing coordinators or running exclude. With these practices, your gbase database infrastructure will remain solid and predictable.

Monitoring FoundationDB in GCDW: A Deep Dive into the status Command

Michael — Thu, 30 Jul 2026 12:20:00 +0000

GCDW (GBase Cloud Data Warehouse) relies on FoundationDB (FDB) for metadata storage. The fdbcli tool's status command provides a comprehensive snapshot of cluster health, redundancy, load, and resource consumption. This guide explains every key section of the status and status details output for your gbase database infrastructure.

At a Glance: What to Check First

Run status inside fdb> and focus on these vital signs:

Redundancy mode – single/double/triple; defines your replica count.
Fault Tolerance – maximum nodes that can fail without data loss. 0 means any single node failure loses data.
Replication health – must be Healthy.
Memory availability – per‑process available RAM; a warning appears below 4 GB.
Conflict rate – high values indicate transaction contention.

Key Output Sections

Section	Metric	Meaning
Configuration	Redundancy mode	`double` = 2 replicas; `triple` = 3 replicas
	Storage engine	`ssd-2` (large datasets) or `memory-2` (small, fast)
	Coordinators	Odd number recommended (e.g., 3)
Cluster	FoundationDB processes	Total FDB server processes; one per CPU core, ≥4 GB RAM each
	Fault Tolerance	Number of machines that can fail; 0 = no redundancy
Data	Replication health	Must be `Healthy`
	Moving data	Non‑zero during scaling or recovery
	Disk space used	Total disk consumed by the cluster
Workload	Read / Write rate	Current throughput in Hz
	Transactions started / committed	Transactional throughput
	Conflict rate	Rate of transaction conflicts

Switching the Storage Engine

You can change the storage engine online without downtime:

fdb> configure ssd         # switch to SSD engine
fdb> configure memory-3    # switch to memory engine

SSD engine: Persists data on disk. Best for large datasets. Space reclamation after deletes is delayed.
Memory engine: Keeps data in memory, logs to disk. Best for small, fast workloads.

Per‑Process Details with status details

status details exposes resource usage for every FDB server process:

Process performance details:
  10.0.2.81:4500 (  4% cpu;  5% machine; 0.000 Gbps;  1% disk IO; 0.5 GB / 2.7 GB RAM )
  10.0.2.82:4500 (  6% cpu;  2% machine; 0.000 Gbps;  1% disk IO; 0.4 GB / 2.8 GB RAM )

Each line shows:

cpu – single‑core CPU usage of the process.
machine – overall CPU utilisation of the host.
Gbps – combined network throughput.
disk IO – disk busy percentage.
RAM – physical memory used by the process / total available per process on that host.

Coordination server reachability is also listed, helping you quickly spot network partitions or down nodes.

Operational Best Practices

Keep Fault Tolerance ≥ 1 in production. Prefer double redundancy for safety.
Never ignore memory warnings – insufficient RAM leads to OOM kills and performance collapse.
Monitor Conflict rate routinely; a persistent spike usually indicates a hot key or poor data model design.
During scaling or node replacements, track Moving data to know when rebalancing completes.

Mastering the status command gives you full visibility into the FoundationDB layer of your GCDW gbase database, keeping the metadata backbone healthy and performant.

GBase 8a Load vs. Export Syntax Comparison

Michael — Thu, 30 Jul 2026 11:50:00 +0000

GBase 8a provides robust data loading (LOAD DATA INFILE) and data export (SELECT INTO OUTFILE) capabilities. While both share many syntactic elements, they also have distinct features. This article compares the core parameters side by side, with practical export examples for your gbase database.

Core Parameter Comparison

Attribute	Load	Export	Notes
Basic Syntax	`LOAD DATA INFILE`	`SELECT INTO OUTFILE`
File	`file_list`	`file_name`	Load supports multiple files; export only a single file name
Character Set	`CHARACTER SET`	`CHARACTER SET`
Data Format	`DATA_FORMAT`		Load‑only, e.g., `DATA_FORMAT 6` for JSON
Data Format with Line Separator	`HAVING LINES SEPARATOR`
NULL Value	`NULL_VALUE`	`NULL_VALUE`	Load: ≤15 chars; Export: ≤32 chars, default `\N`
Field Delimiter	`TERMINATED BY`	`TERMINATED BY`	Load: ≤15 chars; Export: ≤10 chars
Field Enclosure	`ENCLOSED BY`	`[OPTIONALLY] ENCLOSED BY`	Single character; `OPTIONALLY` only for character types
Preserve Blanks	`PRESERVE [LEADING \	TRAILING] BLANKS`
Autofill	`AUTOFILL`
Fixed Length	`LENGTH`	`LENGTH`	Fixed‑width mode
Column Mapping	`TABLE_FIELDS`
Line Terminator	`LINES TERMINATED BY`	`LINES TERMINATED BY`	Max 10 chars
Max Bad Records	`MAX_BAD_RECORDS`
Date/Time Formats	`DATETIME FORMAT`, `DATE FORMAT`, `TIMESTAMP FORMAT`, `TIME FORMAT`		Load; export formats specified in `TABLE_FIELDS`
Error Logging	`TRACE`, `TRACE_PATH`
No File Split	`NOSPLIT`
Parallelism	`PARALLEL`, `MAX_DATA_PROCESSORS`, `MIN_CHUNK_SIZE`
Skip Missing Files	`SKIP_BAD_FILE`
Set Column Values	`SET`
Skip Header Lines	`IGNORE NUM LINES`
File Compression	`FILE_FORMAT`		UNDEFINED, UNCOMPRESSED, GZIP, SNAPPY, LZO
Escape Character		`ESCAPED BY`	Export; default `\`
BLOB Handling		`BLOBMODE`	Export: `TYPE_TEXT`, `TYPE_BASE64`, `TYPE_URL`
Line Prefix		`LINES STARTING BY`	Export
Double‑Enclosure Escaping		`DOUBLE_ENCLOSED BY`	Export
Export Destination		`OUTFILEMODE BY`	`LOCAL` or `HDFS`
Write Mode		`WRITEMODE BY`	`NORMAL` or `OVERWRITES`
Parallel File Count		`FILECOUNT`	HDFS export only
Max File Size		`FILESIZE`	Export; supports k/K/m/M/g/G suffix
Header Row		`WITH HEAD`	Optional for local exports

Export Examples

Add a Prefix to Each Line

SELECT * FROM test INTO OUTFILE '/home/gbase/test.txt'
FIELDS TERMINATED BY '|'
DOUBLE_ENCLOSED BY ','
ESCAPED BY '\\'
WRITEMODE BY OVERWRITES
LINES STARTING BY '----';

This prepends ---- to every output line, useful for embedding metadata.

Split Large Exports by File Size

When FILESIZE is specified, the export automatically splits into multiple files. The first file keeps the original name, and subsequent files get suffixes like _p1, _p2, etc. This is particularly useful for large datasets in a gbase database environment where downstream processing expects manageable file sizes.

Temporarily Changing Session‑Level Parameters in GBase 8a

Michael — Thu, 30 Jul 2026 11:01:50 +0000

Some parameters in GBase 8a can be modified at the session level, affecting only the current connection. When using connection pools, it's a good practice to save the original value, apply the temporary change, and restore it afterwards to avoid impacting subsequent queries. Here's the safe workflow for a gbase database.

Steps

Save the current value – Use SELECT @@parameter_name and store it in a user variable.
Change the parameter – Use SET parameter_name = new_value.
Restore the parameter – After the specific query completes, SET parameter_name = @saved_variable.

Complete Example

-- 1. Inspect and save the current value
SELECT @@estimate_func_max_len;
-- Returns 0
SET @bak = @@estimate_func_max_len;

-- 2. Temporarily change the parameter
SET estimate_func_max_len = 32700;
SHOW VARIABLES LIKE 'esti%';
-- Shows Value = 32700

-- 3. Restore the original value
SET estimate_func_max_len = @bak;
SHOW VARIABLES LIKE 'esti%';
-- Shows Value = 0

This pattern lets you tune parameters for specific queries without permanently altering the session environment, keeping your gbase database connections clean and predictable.

Loading JSON Data Files in GBase 8a

Michael — Tue, 28 Jul 2026 15:40:00 +0000

Starting from version 9.5.3.28, GBase 8a supports loading JSON files directly into tables. By specifying DATA_FORMAT 6 in the LOAD DATA INFILE statement, each JSON object is automatically mapped to table columns. This article covers common JSON loading patterns and important caveats for your gbase database.

Basic Syntax

LOAD DATA INFILE 'file_path' INTO TABLE table_name DATA_FORMAT 6;

Loading Patterns

Single JSON Object (One Row)

{"id":"1","name":"First"}

Loads one row. Column names in the JSON must match the table columns.

One JSON Object Per Line

{"id":"1","name":"First"}
{"id":"2","name":"second no comer between line"}

Each line is treated as an independent record. Trailing commas at the end of lines are optional and safely ignored.

Multiple JSON Objects on a Single Line

Compact form (no separator): Objects placed directly next to each other are parsed correctly.

{"id":"1","name":"First"}{"id":"2","name":"second"} → two rows.

Comma separated (without array): A comma between objects causes only the first object to load; subsequent objects are skipped.
JSON Array: Wrap objects in [...] and separate with commas. All objects in the array are loaded.

[{"id":"1","name":"First"},{"id":"2","name":"second"}] → two rows.

Mixed‑Format Files

You can combine lines with single objects, compact objects, and arrays in the same file. The parser handles each line independently.

Important Limitations

No nested JSON values: A column value cannot be another JSON object or array. The structure must be flat.
File splitting restrictions: If the JSON data is compact (no delimiters between objects) or the values themselves contain the line terminator, the file cannot be split across multiple loader processes. In such cases the entire file is processed as a single unit, which bypasses the per‑line length limit but requires the entire content to be syntactically perfect.
Literal constants: JSON keywords true, false, and null must be written in lowercase and must not be enclosed in double quotes. Any deviation will cause a parsing error.

JSON loading in a gbase database makes ingesting semi‑structured data straightforward. By following these patterns, you can seamlessly integrate JSON log streams, IoT data, or API responses into your GBASE analytical pipelines without external preprocessing.

GBase 8a initcap: Capitalizing the First Letter of Every Word

Michael — Tue, 28 Jul 2026 14:50:00 +0000

The initcap function in GBase 8a capitalizes the first letter of each word in a string and converts the rest to lowercase. It's a handy tool for normalising names, titles, and other text fields in a gbase database.

Syntax

INITCAP(expr)

expr: a string expression — a column, a literal, or a function result.
Letters are determined by the Unicode Letter category, covering English, pinyin, Chinese characters, Japanese kana, and Western European letters.
Words are delimited by spaces, tabs, newlines, full‑width spaces, and punctuation marks (commas, hyphens, underscores, etc.).

Examples

Basic English Text

gbase> SELECT INITCAP('hello wORLD');
+------------------------+
| INITCAP('hello wORLD') |
+------------------------+
| Hello World            |
+------------------------+

Full‑Width Pinyin

Full‑width letters are treated the same way.

gbase> SELECT INITCAP('ａｂｃ');
+----------------------+
| INITCAP('ａｂｃ')    |
+----------------------+
| Ａｂｃ               |
+----------------------+

Various Delimiters

initcap recognises a wide range of separators, so words split by punctuation, hyphens, underscores, or full‑width spaces are each capitalised independently.

gbase> SELECT INITCAP('hello,wORLD');
+------------------------+
| INITCAP('hello,wORLD') |
+------------------------+
| Hello,World            |
+------------------------+

gbase> SELECT INITCAP('hello-wORLD');
+------------------------+
| INITCAP('hello-wORLD') |
+------------------------+
| Hello-World            |
+------------------------+

gbase> SELECT INITCAP('hello_wORLD');
+------------------------+
| INITCAP('hello_wORLD') |
+------------------------+
| Hello_World            |
+------------------------+

initcap is a simple yet powerful string formatting function in GBase 8a. Use it whenever you need to present text in a consistent, capitalised format directly within your SQL queries.

GBase 8a date_trunc: Truncating Date/Time to a Specified Precision

Michael — Tue, 28 Jul 2026 14:20:00 +0000

The date_trunc function in GBase 8a truncates a date/time value to the specified precision. Everything below that precision is set to its minimal value (e.g., truncating to month resets the day, hour, minute, and second to the first day of the month at 00:00:00). This is extremely useful for time‑series aggregation and data alignment in a gbase database.

Syntax

date_trunc(type, date)

type: the precision to truncate to, such as year, quarter, month, week, day, hour, minute, second, century, decade, millennium, milliseconds, microseconds. Both quoted and unquoted forms are accepted.
date: a datetime expression (usually DATETIME).

Common Truncation Types

type	Truncation Effect	Example Output for `'2023-04-21 11:12:13.56789'`
year	Resets to Jan 1 00:00:00	`2023-01-01 00:00:00`
quarter	First day of the quarter 00:00:00	`2023-04-01 00:00:00`
month	First day of the month 00:00:00	`2023-04-01 00:00:00`
week	Monday of the week 00:00:00	`2023-04-17 00:00:00`
day	Time set to 00:00:00	`2023-04-21 00:00:00`
hour	Minutes and seconds set to zero	`2023-04-21 11:00:00`
minute	Seconds set to zero	`2023-04-21 11:12:00`
second	Sub‑seconds set to zero	`2023-04-21 11:12:13`
century	First day of the century	`2001-01-01 00:00:00`
decade	First day of the decade	`2020-01-01 00:00:00`
millennium	First day of the millennium	`2001-01-01 00:00:00`
milliseconds	Microseconds set to zero	`2023-04-21 11:12:13.567000`
microseconds	Sub‑microseconds set to zero	`2023-04-21 11:12:13.567890`

Note: week respects the default_week_format parameter; a date at the very beginning of a year may belong to the last week of the previous year.

Examples

-- Truncate to year
SELECT date_trunc(year, '2023-04-21 11:12:13.56789');
-- Result: 2023-01-01 00:00:00

-- Truncate to quarter
SELECT date_trunc(quarter, '2023-04-21 11:12:13.56789');
-- Result: 2023-04-01 00:00:00

-- Truncate to month
SELECT date_trunc(month, '2023-04-21 11:12:13.56789');
-- Result: 2023-04-01 00:00:00

-- Truncate to week (Monday of the week)
SELECT date_trunc(week, '2023-04-21 11:12:13.56789');
-- Result: 2023-04-17 00:00:00

-- A date at the start of the year may fall into the previous year's week
SELECT date_trunc(week, '2023-01-01 11:12:13.56789');
-- Result: 2022-12-26 00:00:00

-- Truncate to day
SELECT date_trunc(day, '2023-04-21 11:12:13.56789');
-- Result: 2023-04-21 00:00:00

-- Truncate to hour
SELECT date_trunc(hour, '2023-04-21 11:12:13.56789');
-- Result: 2023-04-21 11:00:00

-- Truncate to minute
SELECT date_trunc(minute, '2023-04-21 11:12:13.56789');
-- Result: 2023-04-21 11:12:00

-- Truncate to second
SELECT date_trunc(second, '2023-04-21 11:12:13.56789');
-- Result: 2023-04-21 11:12:13

-- Truncate to century
SELECT date_trunc(century, '2023-04-21 11:12:13.56789');
-- Result: 2001-01-01 00:00:00

-- Truncate to decade
SELECT date_trunc(decade, '2023-04-21 11:12:13.56789');
-- Result: 2020-01-01 00:00:00

-- Truncate to millennium
SELECT date_trunc(millennium, '2023-04-21 11:12:13.56789');
-- Result: 2001-01-01 00:00:00

-- Truncate to millisecond
SELECT date_trunc(milliseconds, '2023-04-21 11:12:13.56789');
-- Result: 2023-04-21 11:12:13.567000

-- Truncate to microsecond
SELECT date_trunc(microseconds, '2023-04-21 11:12:13.56789');
-- Result: 2023-04-21 11:12:13.567890

date_trunc is an essential function for time‑based analytics in a gbase database. It simplifies aggregating data by fixed intervals and aligning timestamps across different sources. Combine it with date_part or extract for even more control over your time‑series queries in GBASE's MPP cluster.

GBase 8a Mirror Cluster: Using Fewer High‑Spec Nodes for Backup

Michael — Tue, 28 Jul 2026 13:50:00 +0000

In a GBase 8a mirror cluster, the standard recommendation is to keep the primary and backup clusters topologically identical. However, for real‑time backup scenarios, you can use a smaller number of high‑capacity nodes for the backup cluster. This guide demonstrates a working configuration with a 3‑node cluster in a gbase database, where the primary cluster has 2 nodes and the backup cluster has only 1 node.

Test Environment

Three nodes are split into two virtual clusters:

VC1: 2 nodes (101, 102), each with 1 primary shard (p1 d0), giving a total of 2 primary shards.
VC2: 1 node (115), configured with 2 primary shards (p2 d0), also giving a total of 2 primary shards.

The prerequisites for mirroring are met because:

Both VCs have the same number of primary shards (2).
The hashmaps are identical (VC2 initialises its nodedatamap from VC1).

Creating the Mirror Database

Initialise the hashmaps and set up the mirror relationship:

-- Initialise VC1
USE VC vc1;
INITNODEDATAMAP;

-- Initialise VC2 from VC1 to ensure identical hashmaps
USE VC vc2;
INITNODEDATAMAP FROM vc1;

-- Create the database on both VCs
CREATE DATABASE vc1.testdb;
CREATE DATABASE vc2.testdb;

-- Set VC1.testdb to mirror to VC2
ALTER DATABASE vc1.testdb SET DEFAULT MIRROR = vc2;

-- Verify the mirror relationship
SHOW MIRROR DATABASES;

The output confirms that testdb now has MIRROR_VC = vc2, indicating the mirror is active.

Verifying Mirror Replication

Create a table and insert data on VC1, then check that it appears on VC2:

USE VC vc1;
USE testdb;

CREATE TABLE t1(id INT, name VARCHAR(100));
INSERT INTO t1 VALUES(1, 'From vc1');

-- Query on VC1
SELECT * FROM vc1.testdb.t1;
-- Output: | 1 | From vc1 |

-- Query on VC2 — data is automatically synchronised
SELECT * FROM vc2.testdb.t1;
-- Output: | 1 | From vc1 |

Key Prerequisites for Mirroring

Equal number of primary shards: Both VCs must have the same total count of primary segments.
Identical hashmaps: VC2 must be initialised with initnodedatamap from vc1, ensuring that the nodedatamap table has the same data_distribution_id count and content on both sides.

You can verify hashmap consistency with this query:

SELECT DISTINCT(res.r), COUNT(res.r)
FROM (
    SELECT COUNT(*) r
    FROM gbase.nodedatamap t
    WHERE t.data_distribution_id IN (1, 2)
    GROUP BY t.hashkey, t.nodeid
) res
GROUP BY res.r;

A correct setup returns a single row with count(res.r) = 65536, confirming the hashmaps match.

This configuration proves that a gbase database mirror cluster can operate with an asymmetric topology — fewer backup nodes — as long as the shard count and hashmap alignment rules are strictly followed. This approach saves hardware while still providing real‑time data protection.

Exporting Fixed‑Length Data Files in GBase 8a

Michael — Tue, 28 Jul 2026 13:20:00 +0000

Fixed‑length (or "positional") exports eliminate the need for field delimiters, which is especially useful when your data contains characters that are hard to escape. In a gbase database, you can export query results as fixed‑width records using the SELECT INTO OUTFILE command with specific options.

Syntax for Fixed‑Length Exports

There are three ways to produce a fixed‑length file:

Explicit field lengths – SELECT ... INTO OUTFILE '...' FIELDS length(len1, len2, ...)
Empty terminators and enclosures – SELECT ... INTO OUTFILE '...' FIELDS TERMINATED BY '' ENCLOSED BY '' ESCAPED BY ''
Empty terminators only – SELECT ... INTO OUTFILE '...' FIELDS TERMINATED BY '' ESCAPED BY ''

Important: You cannot mix the length option with TERMINATED BY or ENCLOSED BY – doing so will cause a syntax error.

Method 1: Explicit Field Lengths

Use FIELDS length() to define the exact byte width for each column. Each data type has a minimum width that must be respected.

Data Type	Minimum Length (bytes)
TINYINT	4
SMALLINT	6
INT	11
BIGINT	21
DECIMAL(P,S)	P + 2 (if S > 0) or P + 1 (if S = 0)
VARCHAR(N) (UTF8)	N × 3
VARCHAR(N) (UTF8MB4)	N × 4

Example:

-- Export table t1, where id is INT and f1 is VARCHAR(10) in UTF8
SELECT * FROM t1 INTO OUTFILE '/home/gbase/t1.zxq'
FIELDS length '11,30' WRITEMODE BY OVERWRITES;

If the specified length is smaller than the actual byte size of the data, the content will be truncated. For instance, a VARCHAR(10) column in UTF8 needs at least 30 bytes; providing only 2 bytes would output just the first 2 bytes.

Method 2 & 3: Empty Delimiters and Escape Characters

If you don't want to calculate lengths manually, simply set the field terminator, enclosure, and escape character to empty strings. The engine then outputs each column at its maximum defined width automatically.

-- Option A: Clear all three
SELECT * FROM t1 INTO OUTFILE '/home/gbase/t1.zxq'
FIELDS TERMINATED BY '' ENCLOSED BY '' ESCAPED BY ''
WRITEMODE BY OVERWRITES;

-- Option B: Clear only terminator and escape
SELECT * FROM t1 INTO OUTFILE '/home/gbase/t1.zxq'
FIELDS TERMINATED BY '' ESCAPED BY ''
WRITEMODE BY OVERWRITES;

Both options produce fixed‑length output and are functionally identical. They are more convenient than specifying explicit lengths, but they don't allow you to customise column widths.

Fixed‑length exports are a reliable way to handle complex data exchange scenarios. Choose the method that best balances flexibility and simplicity for your specific gbase database workload.

Does GBase 8a V95 SetSysEnv.py Require the root User?

Michael — Tue, 28 Jul 2026 12:48:59 +0000

In GBase 8a V8, installation, upgrades, and service management all required root privileges. Starting with V95, all database services run under a non‑root dbauser (typically gbase). However, certain environment setup operations, such as running SetSysEnv.py, still need root‑level permissions to adjust system limits. This does not mean you must log in as root or know the root password — you can use sudo instead.

Running SetSysEnv.py with sudo

The following example shows the gbase user successfully executing SetSysEnv.py via sudo:

[gbase@gbase_rh7_015 ~]$ sudo ./SetSysEnv.py --dbaUser=gbase

Checking the log confirms the script ran with root privileges and applied the necessary system settings:

[gbase@gbase_rh7_015 ~]$ tail /tmp/SetSysEnv.log
2023-04-24 09:37:31,733-root-INFO Set system max memory size to end.
2023-04-24 09:37:31,770-root-INFO Set system virtual memory...
2023-04-24 09:37:31,810-root-INFO exec cmd:su - gbase -c "ulimit -v",and return value:unlimited
2023-04-24 09:37:31,811-root-INFO Modify system env end.

As long as the gbase user has sudo privileges, SetSysEnv.py can complete its tasks without ever needing the root password. This is the recommended approach in environments where direct root access is restricted.

In a gbase database V95 deployment, remember that daily operations run as gbase, but one‑time environment setup still requires a privilege escalation step — and sudo is the safe and standard way to do it.

GBase 8a date_part and extract: Extracting Date/Time Components

Michael — Mon, 27 Jul 2026 15:45:00 +0000

GBase 8a provides two functions for extracting specific parts of a date or time value: date_part and extract. They are equivalent in functionality — date_part has a simpler syntax, while extract follows the Oracle‑compatible style. Both support a wide range of extraction types, from year and month down to microseconds.

Syntax

-- date_part: type can be quoted or unquoted
date_part(type, date)

-- extract
extract(type FROM date)

type: the component to extract, e.g. year, month, day.
date: a datetime expression (usually DATETIME; DATE values are auto‑cast).

Quick Reference of Common Types

type	Description	Example Output
year	Year	2023
month	Month	4
day	Day	23
hour	Hour	11
minute	Minute	12
second	Second	13
quarter	Quarter (1–4)	2
week	Week of year	17
dow	Day of week (Sunday=0)	0
isodow	ISO day of week (Monday=1, Sunday=7)	7
doy	Day of year	113
epoch	Unix timestamp (with microseconds)	1682219533.56789
year_month	Year and month	202304
day_hour	Day and hour	2311
hour_minute	Hour and minute	1112
minute_second	Minute and second	1213
millisecond	Millisecond (integer)	567
microsecond	Microsecond (integer)	567890
century	Century	21
decade	Decade (year ÷ 10)	202
millennium	Millennium	3
milliseconds	Seconds + milliseconds (decimal)	13567.89
microseconds	Seconds + microseconds (integer)	13567890

Typical Examples

All examples use date_part; extract produces identical results.

-- Year
SELECT date_part(year, '2023-04-23 11:12:13.56789');      -- 2023

-- Month
SELECT date_part(month, '2023-04-23 11:12:13.56789');     -- 4

-- Day
SELECT date_part(day, '2023-04-23 11:12:13.56789');       -- 23

-- Hour
SELECT date_part(hour, '2023-04-23 11:12:13.56789');      -- 11

-- Minute
SELECT date_part(minute, '2023-04-23 11:12:13.56789');    -- 12

-- Second
SELECT date_part(second, '2023-04-23 11:12:13.56789');    -- 13

-- Millisecond (truncates the fractional seconds)
SELECT date_part(millisecond, '2023-04-23 11:12:13.56789'); -- 567

-- Microsecond
SELECT date_part(microsecond, '2023-04-23 11:12:13.56789'); -- 567890

-- Year and month as an integer (202304)
SELECT date_part(year_month, '2023-04-23 11:12:13.56789');

-- Day and hour as an integer (2311)
SELECT date_part(day_hour, '2023-04-23 11:12:13.56789');

-- Hour and minute as an integer (1112)
SELECT date_part(hour_minute, '2023-04-23 11:12:13.56789');

-- Minute and second as an integer (1213)
SELECT date_part(minute_second, '2023-04-23 11:12:13.56789');

-- Epoch with microseconds (extract only)
SELECT extract(epoch FROM '2023-04-23 11:12:13.56789');    -- 1682219533.56789

-- Milliseconds as decimal (13.56789 seconds)
SELECT date_part(milliseconds, '2023-04-23 11:12:13.56789'); -- 13567.89

-- Microseconds as integer (13.56789 seconds → 13567890 µs)
SELECT date_part(microseconds, '2023-04-23 11:12:13.56789'); -- 13567890

-- ISO year vs. calendar year
SELECT date_part(isoyear, '2023-01-01 11:12:13');          -- 2022
SELECT date_part(year, '2023-01-01 11:12:13');             -- 2023

With these two functions, extracting any component from a date or time value becomes straightforward. They are essential for grouping, filtering, and reporting in a gbase database.

GBase 8a sha2: Built‑in SHA‑224, SHA‑256, SHA‑384, and SHA‑512 Hashing

Michael — Mon, 27 Jul 2026 15:15:00 +0000

The sha2 function in GBase 8a provides native support for multiple SHA‑2 hash algorithms. You can compute cryptographic hashes directly in SQL, which is useful for data integrity verification and secure storage in a gbase database.

Syntax

sha2(str, mode)

str: The input string. Supports Chinese, English, digits, and all characters allowed by the VARCHAR type.
mode: The hash algorithm selector. Only four values are accepted:
- 224 → SHA‑224
- 256 → SHA‑256
- 384 → SHA‑384
- 512 → SHA‑512

Any other value returns NULL. The input string should use UTF‑8 encoding.

mode	Algorithm	Return Length
224	SHA‑224	varchar(56)
256	SHA‑256	varchar(64)
384	SHA‑384	varchar(96)
512	SHA‑512	varchar(128)

Examples

-- SHA-224
gbase> SELECT sha2('GBase 8a', 224);
+----------------------------------------------------------+
| sha2('GBase 8a',224)                                     |
+----------------------------------------------------------+
| 7e3272023ef3e8edcbeb1daf2244529d806229d481753d45bd5bb524 |
+----------------------------------------------------------+

-- SHA-256
gbase> SELECT sha2('GBase 8a', 256);
+------------------------------------------------------------------+
| sha2('GBase 8a',256)                                             |
+------------------------------------------------------------------+
| 37e28220f6937e7dd7afcf6511e35a72d835dc50bd8c363fec8054485933313b |
+------------------------------------------------------------------+

-- SHA-384
gbase> SELECT sha2('GBase 8a', 384);
+--------------------------------------------------------------------------------------------------+
| sha2('GBase 8a',384)                                                                             |
+--------------------------------------------------------------------------------------------------+
| a1ea79415dc17396399a97d1e07c0553c775519be2f031333c9ef05d724341e4f2ed09cda8b890c595c1add5e8df2348 |
+--------------------------------------------------------------------------------------------------+

-- SHA-512
gbase> SELECT sha2('GBase 8a', 512);
+----------------------------------------------------------------------------------------------------------------------------------+
| sha2('GBase 8a',512)                                                                                                             |
+----------------------------------------------------------------------------------------------------------------------------------+
| 66668c66c50b095b772fc69cc903881a6107187cf33d4d9b43016c3b7260115e99175b677c2d603675ac7edcc0cf5ec7436c905d1b126237ab5e7949e48b62ed |
+----------------------------------------------------------------------------------------------------------------------------------+

-- Invalid mode
gbase> SELECT sha2('GBase 8a', 123);
+----------------------+
| sha2('GBase 8a',123) |
+----------------------+
| NULL                 |
+----------------------+

The sha2 function brings multiple SHA‑2 variants directly into your SQL toolkit. Whether you're hashing passwords or verifying row‑level integrity, you can do it inline without leaving the gbase database engine.