SQL_LOGICAL_CHAR configuration parameter

Use the SQL_LOGICAL_CHAR configuration parameter to enable or disable the expansion of size specifications in declarations of built-in character data types.

onconfig.std value
SQL_LOGICAL_CHAR OFF ( = interpret size specifications in units of bytes )
values
OFF = No expansion of declared sizes.

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 length in bytes that any logical character requires in the code set of the current database. Depending on the DB_LOCALE setting, M has an integer range from 1 (in single-byte locales) up to 4.

takes effect
After you edit your onconfig file and restart the database server.
When you reset the value dynamically in your onconfig file by running the onmode -wf command.
When you reset the value in memory by running the onmode -wm command.

Usage

For applications that are developed in single-byte locales, but deployed in multibyte locales, this feature can reduce the risk of multibyte logical characters being truncated during data entry operations.

In a multibyte code set, such as UTF-8 or the multibyte code sets for some East Asian languages, a single logical character can require more than one byte of storage. The setting of this parameter can instruct the SQL parser to apply logical-character semantics to declarations of these built-in character data types:
  • BSON
  • CHAR
  • CHARACTER
  • CHARACTER VARYING
  • JSON
  • LVARCHAR
  • NCHAR
  • NVARCHAR
  • VARCHAR
  • DISTINCT types that declare any of these data types as their base types
  • ROW types (named and unnamed) that include fields of these data types
  • Collection types (LIST, MULTISET, or SET) that include these types as elements.

The setting that you specify for this parameter must be one of the following values:

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.

Automatic Resizing of the Expansion Factor

When SQL_LOGICAL_CHAR is set to a valid digit, and the current session creates a database, HCL OneDB compares the SQL_LOGICAL_CHAR value with the maximum number of bytes that any logical character will use for the code set of the database.

If the SQL_LOGICAL_CHAR setting is greater than that maximum number of bytes, the database uses the maximum value for the locale as the new expansion factor, overriding what the configuration file specifies. The SQL_LOGICAL_CHAR setting in the configuration file remains unchanged, and continues to act as the default expansion factor for other user databases.

Similarly, if the SQL_LOGICAL_CHAR value for a session is automatically reset to a digit, as described above, but the same session subsequently connects to another database whose locale uses a code set in which a logical character requires a larger storage size than the current expansion factor, HCL OneDB uses the maximum number of bytes for the new code set as the new expansion factor while the user session is connected to that database, rather than using the current setting of SQL_LOGICAL_CHAR.

Automatic resetting of the expansion factor to match the largest logical character size in the code set that DB_LOCALE specifies at connection time also occurs when SQL_LOGICAL_CHAR is set to ON, but the effects of the ON setting are not identical to the database server behavior when SQL_LOGICAL_CHAR is set to a digit (1, 2, 3, or 4) in two ways:
  • The expansion factor can be automatically reset to a smaller value if ON is the SQL_LOGICAL_CHAR setting.
  • There is no difference between SQL_LOGICAL_CHAR = 4 and SQL_LOGICAL_CHAR = ON.

You must set SQL_LOGICAL_CHAR to ON, rather than to a digit, if you want a smaller expansion factor when the current session connects to a database whose largest logical character in the DB_LOCALE code set requires a smaller number of bytes than the current SQL_LOGICAL_CHAR setting. The effective expansion factor will always be less than or equal to the maximum character size for a locale.