Restrictions on SET COLLATION

Although SET COLLATION enables you to change the collating order of the database server dynamically within a session, you should be aware of several limitations on the scope of what the SET COLLATION statement can accomplish.

  • Only collation performed by the database server is affected. Client processes that sort data are not affected by SET COLLATION.
  • Only the current session is affected. Other sessions are not affected directly by your SET COLLATION statements, but the database server will use their creation-time collating order to set any database objects that you create after SET COLLATION has run successfully.
  • Changing the collating order does not change the code set. The database server always uses the code set specified by DB_LOCALE.
  • Only NCHAR and NVARCHAR values are sorted in locale-specific order.

Processing characters from dissimilar code sets

Because SET COLLATION changes only the collating order, rather than the current locale or code set, you generally cannot use this statement to insert character data from different locales, such as French and Japanese, into the same database.Because SET COLLATION changes only the collating order, rather than the current locale or code set, you generally cannot use this statement to insert character data from different locales into the same database. You must instead use a locale that supports Unicode if the database needs to store characters from two or more languages that require inherently different code sets or code pages. For applications and for other client applications that use the HCL OneDB™ GLS library, databases with locales that support UTF-8 character encoding can store characters that correspond to code points from dissimilar character sets of more than one natural language, but only if all of the following conditions are satisfied:
  • The GL_USEGLU environment variable was a set to 1 when the database server instance was started.
  • The DB_LOCALE environment variable was set to a valid Unicode locale when the database was created.
  • The CLIENT_LOCALE environment variable is set to valid Unicode locale that the DB_LOCALE setting of the database server supports.

For HCL OneDB to use the International Components for Unicode (ICU) 4.8.14.2.1 libraries to support versions of Unicode up to 6.0,5.1, the GL_USEGLU environment variable must be set to a value of 1 (one) in the server environment before the server is started. This setting initializes conversion routines that enable Unicode collation and SQL operations in databases that use UTF-8 character encoding, including the Chinese GB18030-2000 code set. This conversion applies only to databases that were created with GL_USEGLU=1 already set.

Attention: The GL_USEGLU environment variable has no effect, however, on JDBC client applications, including those of the HCL OneDB JSON compatibility wire protocol listener. To support JDBC applications correctly in Unicode locales, there is no requirement that GL_USEGLU be set to 1 in the client or in the server environments.