Character Data Types

Built-in Character Types

The database server also uses LVARCHAR to represent the external format of opaque data types. In I/O operations of the database server, LVARCHAR data values have no upper limit on their size, apart from file size restrictions or limits of your operating system or hardware resources.

Logical Character Semantics in Character Type Declarations

HCL OneDB™ supports a configuration parameter, SQL_LOGICAL_CHAR, whose setting can instruct the SQL parser to interpret the maximum size of character columns in data type declarations of the CREATE TABLE or ALTER TABLE statements as logical characters, rather than in units of bytes.

When a database is created, the current SQL_LOGICAL_CHAR setting for the database server is recorded in the systables table of the system catalog. The feature has no effect on tables that are subsequently created or altered in the database if the setting is OFF or 1.

In a database where the SQL_LOGICAL_CHAR setting is ON or is a digit between 2, 3, or 4, however, the SQL parser interprets explicit and implicit size declarations as logical characters in declarations of SPL variables and declarations of columns in database tables for the following character types:

• CHAR and CHARACTER
• CHARACTER VARYING and VARCHAR
• LVARCHAR
• NCHAR
• NVARCHAR
• DISTINCT types of the data types listed above
• DISTINCT types of those DISTINCT types
• ROW data type fields of the types listed above .
• LIST, MULTISET, and SET elements of the types listed above.

This feature has no effect on the maximum storage size limits for the character types listed in the previous table. For databases that use a multibyte locale, however, it can reduce the risk of data truncation when a string is inserted into a character column or assigned to a character variable.

For example, if 4 is the SQL_LOGICAL_CHAR setting for the database, then a VARCHAR(10, 5) specification is interpreted as requesting a maximum of 40 bytes of storage, with 5 of these bytes reserved, creating a VARCHAR(40, 5) data type in standard SQL notation, rather than what was specified in the declaration.

The reserve size parameters of VARCHAR and NVARCHAR data types are not affected by the SQL_LOGICAL_CHAR setting, because the minimum size of a multibyte character is 1 byte. In this example, the minimum size of 5 multibyte characters is 5 bytes, a size that remains unchanged.

See the description of the SQL_LOGICAL_CHAR configuration parameter in the HCL OneDB Administrator's Reference for more information about the effect of the SQL_LOGICAL_CHAR setting in databases whose DB_LOCALE specifies a multibyte locale. For additional information about multibyte locales and logical characters, see the HCL OneDB GLS User's Guide.

IDSSECURITYLABEL

HCL OneDB also supports the IDSSECURITYLABEL data type for systems that implement label-based access control (LBAC). This built-in data type can be formally classified as a character type, because it is defined as a DISTINCT OF VARCHAR(128) data type, but only users who hold the DBSECADM role can declare this data type in DDL operations. It supports the LBAC security feature, rather than functioning as a general-purpose character type.

Data Type Promotion

For some string-manipulation operations of HCL OneDB, the five built-in character data types listed above support data type promotion, in order to reduce the risk of string operations failing because a returned string is too large to be stored in an NVARCHAR or VARCHAR column or program variable. See the topic "Return Types from CONCAT and String Manipulation Functions" in HCL OneDB Guide to SQL: Syntax for details of data type promotion among HCL OneDB character types.

National Language Support

The NCHAR and NVARCHAR types are sometimes called National Language Support data types because of their support for localized collation. Because columns of type VARCHAR or NVARCHAR have no default size, you must specify a size (no greater than 255) in their declaration. For VARCHAR or NVARCHAR columns on which an index is defined, the maximum size is 254 bytes.

NLSCASE lNSENSITIVE Databases

In databases created with the NLSCASE INSENSITIVE keyword option, operations on data strings of the NCHAR or NVARCHAR types makes no distinction between uppercase and lowercase variants of the same letter. Rows are stored in NCHAR or NVARCHAR columns in the letter case in which characters were loaded, but data strings that consist of the same letters in the same sequence are evaluated as duplicates, even if the case of some letters is not identical. For example, the three NCHAR strings "ABC" and "AbC" and "abC" are treated as duplicates. Other built-in character types, including CHAR, LVARCHAR, and VARCHAR, follow the default case-sensitive rules, so that the same three strings in a CHAR column evaluate to distinct values.

Databases with the NLSCASE INSENSITIVE property also ignore the letter case of DISTINCT data types whose base types are NCHAR or NVARCHAR, as well as NCHAR or NVARCHAR fields in named or unnamed ROW types, and NCHAR or NVARCHAR elements of COLLECTION data types, including LIST, SET, or MULTISET.

In a database that is insensitive to the letter case of NCHAR or NVARCHAR values, string manipulation operations might produce unexpected results, if they implicitly cast CHAR, LVARCHAR, or VARCHAR operands or arguments to NCHAR or NVARCHAR data types. In some contexts, the operation can return a duplicate string, despite letter case variations that the database server would not have treated as duplicates for the original data types.