The 'G' Moment: How to Build an ANSI-Compliant GBase 8s Database Environment

Published on 2025-07-07

When using the GBase 8s database, creating an ANSI-compliant database is an essential feature that helps developers better conform to the ANSI/ISO SQL standards. This article details how to create an ANSI-compliant database and explores the characteristics of different modes and their use cases.

 

Database Creation Modes

When creating a database in GBase 8s, you can choose from different modes, each with specific purposes and characteristics:

No-Logging Mode: This mode offers better performance but does not support logical recovery operations.

Buffered Logging Mode: Supports logical recovery while avoiding frequent I/O operations, making it suitable for production systems.

Unbuffered Logging Mode: Involves frequent I/O operations but results in minimal data loss in the event of a database crash.

ANSI Mode: A special type of unbuffered logging mode that complies with additional ANSI rules.

 

Database Creation Syntax

The basic syntax for creating a database is as follows:

CREATE DATABASE database [IN dbspace] [WITH LOG | WITH BUFFERED LOG | WITH LOG MODE ANSI] [NLSCASE SENSITIVE | NLSCASE INSENSITIVE];

Parameter Descriptions

  • database: The name of the new database, which must be unique on the database server.

  • dbspace: The dbspace where the database stores its data. The default is rootdbs.  

  • WITH LOG: Enables logging.

  • WITH BUFFERED LOG: Enables buffered logging.

  • WITH LOG MODE ANSI: Creates an ANSI-compliant database.

  • NLSCASE SENSITIVE: Creates a case-sensitive database.

  • NLSCASE INSENSITIVE: Creates a case-insensitive database.

 

Usage Notes

Current Database: The database specified by CREATE DATABASE becomes the current database.

Permissions: If the DBCREATE_PERMISSION configuration parameter is not set, any user can create a database. If set, only the specified users can create databases. The gbasedbt user always has the permission to create databases.

 

Creating Databases in Different Modes

1. No-Logging Mode

CREATE DATABASE db_no;

In this mode, the database cannot use transactions or statements that require transaction logging, such as BEGIN WORK, COMMIT WORK, ROLLBACK WORK, etc.

2. Buffered Logging Mode

CREATE DATABASE db_buf WITH BUFFERED LOG;

Using buffered logging improves logging performance but may incur the risk of not being able to recover the last few transactions after a failure.

3. Unbuffered Logging Mode

CREATE DATABASE db_unbuf WITH LOG;

When creating a database on the secondary server of a high-availability cluster, the WITH LOG option must be used.

4. ANSI-Compliant Database

CREATE DATABASE db_ansi WITH LOG MODE ANSI;

An ANSI-compliant database has the following characteristics:

  • All SQL statements are automatically enclosed in transactions.

  • Uses unbuffered logging.

  • Enforces owner naming; you must use the owner name when referencing objects such as tables and views.

  • The default isolation level is REPEATABLE READ.

  • The default privileges on objects differ from those in non-ANSI-compliant databases.

  • All DECIMAL data types are fixed-point values.

5. Case-Sensitive Database

CREATE DATABASE db_sens1 WITH LOG;CREATE DATABASE db_sens2 WITH LOG NLSCASE SENSITIVE;

In a case-sensitive database, the Boolean condition 'M' MATCHES 'm' evaluates to false.

6. Case-Insensitive Database

CREATE DATABASE db_insens1 WITH LOG NLSCASE INSENSITIVE;

In a case-insensitive database, string operations on NCHAR and NVARCHAR columns ignore letter case.

 

Querying Database Properties

You can query the sysmaster:sysdatabases table to obtain database property information:

SELECT * FROM sysmaster:sysdatabases WHERE name = 'db_insens2';


Property Description

In GBase 8s, creating an ANSI-compliant database helps developers better adhere to the ANSI/ISO SQL standards. Through this article, you will understand the characteristics of different database modes and their use cases. We hope this article helps you create and manage databases more efficiently.