Summary of data types

HCL OneDB™ supports the most common set of built-in data types. Additionally, an extended set of data types are supported on the database server.

You can use both built-in data types (which are system-defined) and extended data types (which you can define) in the following ways:

Use them to create columns within database tables.
Declare them as arguments and as returned types of routines.
Use them as base types from which to create DISTINCT data types.
Cast them to other data types.
Declare and access host variables of these types in SPL and ESQL/C.

You assign data types to columns with the CREATE TABLE statement and change them with the ALTER TABLE statement. When you change an existing column data type, all data is converted to the new data type, if possible.

For information about the ALTER TABLE and CREATE TABLE statements, on SQL statements that create specific data types, that create and drop casts, and on other data type topics, see the HCL OneDB Guide to SQL: Syntax.

For information about how to create and use complex data types supported by HCL OneDB, see the HCL OneDB Database Design and Implementation Guide. For information about how to create user-defined data types, see HCL OneDB User-Defined Routines and Data Types Developer's Guide.

Some data types can be used in distributed SQL operations, while others can be used only in SQL operations within the same database.

Built-in data types supported in local and distributed SQL operations

The following table lists all of the built-in SQL data types that HCL OneDB supports. These built-in SQL data types are valid in all HCL OneDB SQL transactions, including data-manipulation language (DML) operations of these types:

Operations on objects in the local database
Cross-database operations on objects in databases of the local server instance
Cross-server operations on objects in databases of two or more database server instances

Table 1. Data types supported in all operations
Data type	Explanation
BIGINT data type	Stores 8-byte integer values from -(2⁶³ -1) to 2⁶³ -1
BIGSERIAL data type	Stores sequential, 8-byte integers from 1 to 2⁶³ -1
BSON and JSON built-in opaque data types	The BSON data type is the binary representation of a JSON data type format for serializing JSON documents. The JSON data type is a plain text format for entering and displaying structured data.
BYTE data type	Stores any kind of binary data, up to 2³¹ bytes in length
CHAR(n) data type	Stores character strings; collation is in code-set order
CHARACTER(n) data type	Is a synonym for CHAR
CHARACTER VARYING(m,r) data type	Stores character strings of varying length (ANSI-compliant); collation is in code-set order
DATE data type	Stores calendar dates
DATETIME data type	Stores calendar date combined with time of day
DEC data type	Is a synonym for DECIMAL
DECIMAL	Stores floating-point numbers with definable precision; if database is ANSI-compliant, the scale is zero
DECIMAL (p,s) Fixed Point	Stores fixed-point numbers of defined scale and precision
DOUBLE PRECISION data types	Synonym for FLOAT
FLOAT(n)	Stores double-precision floating-point numbers corresponding to the double data type in C
INT data type	Is a synonym for INTEGER
INT8	Stores 8-byte integer values from -(2⁶³ -1) to 2⁶³ -1
INTEGER data type	Stores whole numbers from -2,147,483,647 to +2,147,483,647
INTERVAL data type	Stores a span of time (or level of effort) in units of years and months.
INTERVAL data type	Stores a span of time in a contiguous set of units of days, hours, minutes, seconds, and fractions of a second
MONEY(p,s) data type	Stores currency amounts
NCHAR(n) data type	Same as CHAR, but can support localized collation
NUMERIC(p,s) data type	Synonym for DECIMAL(p,s)
NVARCHAR(m,r) data type	Same as VARCHAR, but can support localized collation
REAL data type	Is a synonym for SMALLFLOAT
SERIAL(n) data type	Stores sequential integers ( > 0) in positive range of INT
SERIAL8(n) data type	Stores sequential integers ( > 0) in positive range of INT8
SMALLFLOAT	Stores single-precision floating-point numbers corresponding to the float data type of the C language
SMALLINT data type	Stores whole numbers from -32,767 to +32,767
TEXT data type	Stores any kind of text data, up to 2³¹ bytes in length
VARCHAR(m,r) data type	Stores character strings of varying length (up to 255 bytes); collation is in code-set order

In cross-server MERGE operations, the source table (but not the target table) can be in a database of a remote HCL OneDB server.

For the character data types (CHAR, CHAR VARYING, LVARCHAR, NCHAR, NVARCHAR, and VARCHAR), a data string can include letters, digits, punctuation, whitespace, diacritical marks, ligatures, and other printable symbols from the code set of the database locale. For UTF-8 and for code sets of some East Asian locales, multibyte characters are supported within data strings.

Built-in data types supported only in local database SQL operations

The following table lists the data types that HCL OneDB supports only for use in SQL operations in a local database.

Table 2. Data types supported in a local database
Data type	Explanation
BLOB data type	Stores binary data in random-access chunks
The binary18 data type	Stores 18 byte binary-encoded strings
The binaryvar data type	Stores binary-encoded strings with a maximum length of 255 bytes
BOOLEAN data type	Stores Boolean values true and false
CLOB data type	Stores text data in random-access chunks
DISTINCT data types	Stores data in a user-defined type that has the same format as a source type on which it is based, but its casts and functions can differ from those on the source type
Calendar data type	Stores a calendar for a TimeSeries data type
CalendarPattern data type	Stores the structure of the calendar pattern for a Calendar data type
IDSSECURITYLABEL data type	Stores LBAC security label objects.
LIST(e) data type	Stores a sequentially ordered collection of elements, all of the same data type, e; allows duplicate values
The lld_locator data type	Stores a large object identifier
The lld_lob data type	Stores the location of a smart large object and specifies whether the object contains binary or character data
LVARCHAR(m) data type	Stores variable-length strings of up to 32,739 bytes
MULTISET(e) data type	Stores a non-ordered collection of values, with elements all of the same data type, e; allows duplicate values.
The node data type for querying hierarchical data	Stores a combination of integers and decimal points that represents hierarchical relationships, of variable length up to 256 characters
OPAQUE data types	Stores a user-defined data type whose internal structure is inaccessible to the database server
ROW data type, Named	Stores a named ROW type
ROW data type, Unnamed	Stores an unnamed ROW type
SET(e) data type	Stores a non-ordered collection of elements, all of the same data type, e; does not allow duplicate values
ST_LineString data type	Stores a one-dimensional object as a sequence of points defining a linear interpolated path
ST_MultiLineString data type	Stores a collection of ST_LineString data types
ST_MultiPoint data type	Stores a collection of ST_Point data types
ST_MultiPolygon data type	Stores a collection of ST_Polygon data types
ST_Point data type	Stores a zero-dimensional geometry that occupies a single location in coordinate space
ST_Polygon data type	Stores a two-dimensional surface stored as a sequence of points defining its exterior bounding ring and 0 or more interior rings
TimeSeries data type	Stores a collection of row subtypes

These extended data types of HCL OneDB are individually described in other topics. These data types are valid in local operations on databases where the data types are defined.

Extended data types in cross-database distributed SQL transactions

Distributed operations on other databases of the same HCL OneDB instance can access BOOLEAN, BLOB, CLOB, and LVARCHAR data types, which are implemented as built-in opaque types. Such operations can also access DISTINCT types whose base types are built-in types, and user-defined types (UDTs), if the UDTs and DISTINCT types are explicitly cast to built-in types, and if all of the UDTs, casts, and DISTINCT types are defined in all the participating databases.

You cannot, however, reference the following extended data types in cross-database transactions that access multiple databases of the local HCL OneDB instance:

UDTs that are not cast to built-in data types
DISTINCT types that are not cast to built-in data types
Collection data types
Named or unnamed ROW data types

Extended data types in cross-server distributed SQL transactions

Distributed SQL transactions and function calls that access databases of other HCL OneDB instances cannot return values of complex or smart large object data types, nor of most distinct or built-in opaque data types. Among the extended data types, only the following can be accessed in cross-server SQL operations:

Any non-opaque built-in data type
BOOLEAN
DISTINCT of non-opaque built-in types
DISTINCT of BOOLEAN
DISTINCT of LVARCHAR
DISTINCT of any of the DISTINCT types listed above
IDSSECURITYLABEL
LVARCHAR

A cross-server distributed SQL transaction can support DISTINCT data types only if they are cast explicitly to built-in types, and all of the DISTINCT types, their data type hierarchies, and their casts are defined exactly the same way in each database that participates in the distributed operation. For queries or other DML operations in cross-server UDRs that use the data types in the preceding list as parameters or as returned data types, the UDR must also have the same definition in every participating database.

The built-in DISTINCT data type IDSSECURITYLABEL, which stores security label objects, can be accessed in cross-server and cross-database operations on protected data by users who hold sufficient security credentials. Like local operations on protected data, distributed queries that access remote tables protected by a security policy can return only the qualifying rows that IDSLBACRULES allow, after the database server has compared the security label that secures the data with the security credentials of the user who issues the query.