Specifying NLSCASE case sensitivity
You can explicitly create a case-sensitive or case insensitive database.
CREATE DATABASE employees IN dbspaceYee WITH BUFFERED LOG;
CREATE DATABASE stores IN dbsp1 WITH LOG NLSCASE SENSITIVE;
Because
case sensitivity is enabled by default, the following statement has
the same effect: CREATE DATABASE stores IN dbsp1 WITH LOG;
In a database that is case sensitive, for a table in which column col3 is of type NCHAR or NVARCHAR, for example, the Boolean condition col3 MATCHES 'SAM' evaluates as false for rows where 'Sam' is the value in col3. In contrast, in a different database created with the NLSCASE INSENSITIVE keyword option, the same Boolean condition col3 MATCHES 'SAM' evaluates as true for the same rows of the same table. As this example implies, for tables that include NCHAR or NVARCHAR columns in which some row values differ only in letter case, query results depend on the NLSCASE setting of the database.
All informix databases are case sensitive for operations on character strings of the built-in CHAR, LVARCHAR, and VARCHAR data types. If you create a case-sensitive database, whether by default, or explicitly with the NLSCASE SENSITIVE keywords, that database also treats strings of the National Language Support data types NCHAR and NVARCHAR as case-sensitive, if the database locale supports letter case.
Creating a database that is not case sensitive
In some applications, the letter case of character strings can be disregarded. Data entry procedures, for example, might accept the strings 'M' and 'm' as logically equivalent within a record. For large data sets, applying conditional logic to convert both case variants to a single value can result in slower performance than storing the records in an NCHAR or NVARCHAR column of a case-insensitive database, in which the strings 'M' and 'm' encode the same case-insensitive value. Here the condition 'M' MATCHES 'm' evaluates as true for NCHAR or NVARCHAR columns.
Every database created with the NLSCASE INSENSITIVE property stores uppercase and lowercase NCHAR and NVARCHAR letters exactly as they are loaded into their tables; any unmodified record that a query returns has its original lettercase. In all operations on NCHAR and NVARCHAR values, however, such as sorting, grouping, or identifying duplicate rows, the database server ignores any letter case variants, and treats, for example, the strings 'Mi' and 'mI' as the same value. Information about the case of letters not discarded, but it is also not used when the database server processes NLS data types.
When you include the NLSCASE INSENSITIVE keywords as the last specification of the CREATE DATABASE statement, the database server creates a database that always processes the following types of character strings without regard to letter case:
- Strings stored in columns of the NLS data types NCHAR and NVARCHAR
- Strings stored as a DISTINCT data type whose base type is NCHAR or NVARCHAR
- Strings stored as elements of these data types within a collection data type
- Strings stored in fields of the above data types in a named or unnamed ROW data type
- Strings stored as SPL variables of these data types
- Strings implicitly or explicitly cast to these data types
- Strings returned by functions as output parameters of these data types.
CREATE DATABASE stores IN dbsp2 WITH BUFFERED LOG NLSCASE INSENSITIVE;
To perform case-sensitive operations on a string of the NCHAR or NVARCHAR data type in a case-insensitive database, you must first explicitly cast the string to a CHAR, LVARCHAR, or VARCHAR data type, and then perform the case-sensitive operation. (See, however, the topic Return Types from CONCAT and String Functions, which identifies contexts in which the database server automatically casts the result of built-in string-manipulation functions and string operators to NCHAR or NVARCHAR data types.)
Examples of NLSCASE INSENSITIVE queries
In a case-insensitive database, when a query calls an aggregate function or includes the GROUP BY clause for an NCHAR or NVARCHAR column, the database server treats letter-case variants in the data as duplicate column values, as in the following program fragment.
CREATE DATABASE casedb WITH LOG NLSCASE INSENSITIVE;
CREATE TABLE foo (cc CHAR(5), nc NCHAR(5));
INSERT INTO foo VALUES ('IBM', 'iBM');
INSERT INTO foo VALUES ('ibm', 'ibM');
INSERT INTO foo VALUES ('ibm', 'ibM');
INSERT INTO foo VALUES ('Ibm', 'Ibm');
SELECT COUNT(nc) FROM foo
GROUP BY nc;
SELECT COUNT(nc) FROM foo
WHERE nc = 'ibm' GROUP BY nc;
In both of the queries above, the COUNT aggregate function returns 4, the total number of rows that the INSERT statements loaded into foo. Because column nc is an NLS data type, all of the rows satisfy the nc = 'ibm' condition in the WHERE clause, despite variations in letter case among the nc values.
SELECT nc FROM foo GROUP BY nc;
the
output can be any of the string values from the INSERT statements
(namely 'IBM', 'iBM', 'ibm', 'ibM',
or 'Ibm'), depending on the order in which the server
processes or scans the rows.SELECT DISTINCT nc FROM foo;
This
returns a single row, because from the NLSCASE INSENSITIVE perspective,
all of the rows have the same value, despite the variations in letter
case. As in the previous example, the first row retrieved from among
the inserted rows will be returned by the query. SELECT COUNT(DISTINCT nc) FROM foo;
This
returns a count of 1, again because in this case-insensitive
database, all of the rows in table foo evaluate as duplicates.Restrictions on NLSCASE INSENSITIVE databases
- They support distributed cross-database and cross-server queries only with databases that also have the NLSCASE INSENSITIVE property.
- Case-sensitive databases cannot connect to NLSCASE INSENSITIVE
databases. Attempts to do so fail with this error:
-26801 Cannot reference an external database that is not case sensitive.
- NLSCASE INSENSITIVE databases cannot connect to case-sensitive
databases. Attempts to do so fail with this error:
The only exception is that the NLSCASE setting does not prevent connections to the case-sensitive system databases, such as sysmaster, sysadmin, sysutils, sysusers, and syscdr, of the same Informix database server instance. The results of operations that access a system database depends on the NLSCASE setting of the other database.-26802 Cannot reference an external database that is case sensitive.
- The onload and onunload utilities do not support databases that have the NLSCASE INSENSITIVE property.
- In an Enterprise Replication cluster, no error or warning is issued if you specify a replication pair in whose databases differ in their NLSCASE property. To reduce the risk of data inconsistencies, replicate case-sensitive databases only with case-sensitive databases. and replicate NLSCASE INSENSITIVE databases only with NLSCASE INSENSITIVE databases.