Data definition statements

HCL OneDB™ supports a configuration parameter, SQL_LOGICAL_CHAR, whose setting can simplify the use of certain Data Definition Language (DDL) statements of SQL when you declare character data types in locales that support multibyte code sets.

If the instance has SQL_LOGICAL_CHAR set to enable logical character semantics in declarations of character data types, the maximum number of bytes that are required to store a single character of the code set of the locale can affect these SQL data definition statements:
  • ALTER TABLE
  • CREATE TABLE

The SQL_LOGICAL_CHAR setting can also affect the DEFINE statement of SPL when it declares character variables.

The SQL_LOGICAL_CHAR feature addresses a potential problem for data management applications that are developed in a single-byte locale, such as the default locale, but that are later deployed in a multibyte locale. By default, numeric size specifications in declarations of character data types are interpreted in units of bytes. A character column that can store strings of up to 10 bytes, for example, can store no more than two logical characters that each requires four bytes of storage. A table schema that was designed for a single-byte locale might lead to data truncation in operations on character strings in multibyte characters.

The setting of the SQL_LOGICAL_CHAR configuration parameter, however, can change the behavior of the SQL parser, so that size specifications in character data type declarations are interpreted in units of logical characters, rather than as bytes. The maximum declared size is multiplied by a numeric factor, as specified by the setting of this parameter.

The following table shows the valid settings and their effects:
Value Effect
OFF or 1 No expansion of declared sizes
2 Use 2 as the expansion factor for declared sizes.
3 Use 3 as the expansion factor for declared sizes.
4 Use 4 as the expansion factor for declared sizes.
ON Use M as the expansion factor, where M is the maximum storage length in bytes that any logical character requires in the code set of the current database. Depending on the code set associated with the DB_LOCALE setting, M has a positive integer range from 1 (in single-byte locales) up to 4.
When the SQL_LOGICAL_CHAR configuration parameter is set to a value greater than 1, it instructs the SQL parser to interpret explicit and implicit size declarations as logical characters, rather than as bytes, in declarations of SPL variables and in CREATE TABLE and ALTER TABLE statements that define columns of the following data types:
  • CHAR and CHARACTER
  • CHARACTER VARYING and VARCHAR
  • LVARCHAR
  • NCHAR
  • NVARCHAR
  • DISTINCT types whose base types are built in character data types.
  • DISTINCT types whose base types are the previously listed data types.
  • ROW data type fields of any of the previously listed data types.
  • Elements of the previously listed data types within LIST, MULTISET, and SET collection objects.

The SQL_LOGICAL_CHAR setting has no effect, however, on TEXT or CLOB objects, nor on user-defined data types (UDTs) that store character strings.

Enabling logical character semantics for the database locale guarantees that sufficient storage is available for the data type to store the specified number of logical characters. The resulting size in bytes of a character column in a database table or of an SPL character variable is the product of the declared size of the data type multiplied by the SQL_LOGICAL_CHAR value, if this size is 2, 3, or 4, or (if SQL_LOGICAL_CHAR is set to "ON" or "on") by the maximum number of bytes of storage that the largest logical character in the code set of the database locale requires.

For example, if the integer expansion factor is 4, then a CHAR(10) data type specification requests 40 bytes of storage, creating a CHAR(40) data type in standard SQL notation, despite the CHAR(10) declaration.

For NVARCHAR and VARCHAR data types, the declared reserved size, which specifies the minimum storage, is not affected by this feature. For example, with the same integer setting is 4, then a VARCHAR(10,5) data type specification, with 4 as the expansion factor, requests a maximum of 40 bytes of storage with 5 of these bytes reserved, creating a VARCHAR(40, 5) data type in standard SQL notation, despite the VARCHAR(10,5) declaration. (The reserve size parameters of VARCHAR and NVARCHAR 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 five multibyte characters is 5 bytes, so that declared size remains unchanged.)

When a valid SQL_LOGICAL_CHAR setting greater than 1 is in effect, a VARCHAR or NVARCHAR declaration with no size specification is interpreted as one logical character, and the resulting data type occupies the same number of bytes of storage as the SQL_LOGICAL_CHAR setting.

For LVARCHAR column declarations with no size specified, the default size is interpreted as 2048 logical characters. When LVARCHAR is used in I/O operations on opaque data types, however, the limit on the maximum size is determined by the operation system, and the SQL_LOGICAL_CHAR setting is ignored.

If a client session connects to a database in which the SQL_LOGICAL_CHAR configuration parameter was enabled at the time of database creation, this setting takes effect at connection time. The SQL_LOGICAL_CHAR setting for a database cannot be changed, and persists until the database is dropped, even if the HCL OneDB instance that manages the database is stopped and restarted with a new SQL_LOGICAL_CHAR setting.

Whether the SQL_LOGICAL_CHAR configuration parameter is set to enable or disable the expansion of declared storage sizes, its setting specifies how data type declarations are interpreted for all sessions of the HCL OneDB instance.

For embedded languages such as ESQL/C, character data type declarations are expanded when they are passed to HCL OneDB by the client application.