Administer > Database Administration > Case-sensitivity > Oracle case-sensitivity > Validate case-insensitive unique indexes

Validate case-insensitive unique indexes

During the initial system load, Service Manager created a set of case-insensitive indexes for each table, based on the keys in that table. Service Manager logs these indexes first time reads a table when the sqldebug:1 parameter is in the sm.ini file. You can review the settings created for each table by viewing the sm.log file.

The case-insensitive unique indexes should be created as Oracle function based indexes where the Column Expression is NLSSORT("<field name>",'nls_sort=''BINARY_CI''').

To verify whether the case-insensitive unique indexes are being with the correct column expression:

  1. Set sqldebug:1 in the sm.inifile, located in the <SM_install_location>\Server\RUN directory.
  2. Start Service Manager.
  3. Check the sm.log file, located in the <SM_install_location>\Server\logs directory.

The following entry in the log file indicates that the Oracle instance is set to case insensitive, and that you were able to connect to it successfully.

RTE I Oracle server settings for language, territory and character set: AMERICAN_AMERICA.AL32UTF8 (AL16UTF16)
RTE I OCI Client settings for language, territory and character set: AMERICAN_AMERICA.AL32UTF8 (UTF16)
....
RTE I Oracle instance setting for NLS_SORT is set to BINARY_CI
RTE I Oracle instance setting for NLS_COMP is set to LINGUISTIC 
...
RTE I Oracle session is set up in CASE INSENSITIVE mode

The following information in the log file indicates that the Dbdict table has an index, DBDICTM1C989DE64, with a key called "NAME", which is case-insensitive.

RTE D Table Name: DBDICTM1                                                                                                                   
RTE D Schema Name                    Index Name                     Type Column Name                    Column Expression                                                                                                 
RTE D ------------------------------ ------------------------------ ---- ------------------------------ ----------------------------------------                                                                          
RTE D SMDB                           DBDICTM1C989DE64               U    SYS_NC00003$                   NLSSORT("NAME",'nls_sort=''BINARY_CI''')                                                                          
RTE D ------------------------------ ------------------------------ ---- ------------------------------ ----------------------------------------