Remedy - Server - How to configure NLS_SORT BINARY_AI (Accent Insensitive) linguistic search with Oracle database and AR System v.18.05 and higher

Knowledge Article

Remedy - Server - How to configure NLS_SORT BINARY_AI (Accent Insensitive) linguistic search with Oracle database and AR System v.18.05 and higher
How to configure NLS_SORT BINARY_AI (Accent Insensitive) linguistic search with Oracle database and AR System v.18.05 and higher
Remedy AR System Server
AR System
Remedy AR System Server v.18.05 and higher
Remedy AR System Server
AR System
Remedy AR System Server v.18.05 and higher
How to configure NLS_SORT BINARY_AI (Accent Insensitive) linguistic search with Oracle database and AR System v.18.05.
 
This knowledge article may contain information that does not apply to version 21.05 or later which runs in a container environment. Please refer to Article Number 000385088 for more information about troubleshooting BMC products in containers.

Follow the below steps:

1- Go to the Applications links menu > AR System Administration > AR System Administration Console

          User-added image


2- Select System > General > Server Information

          User-added image



3- Select the Database tab.

          User-added image



4- In the Database Configuration file enter the following:

         DB-Options: Oracle {
         name:NLS_SORT
         value:BINARY_AI
         }

          User-added image

          Press Ok.
          Then press Apply and Ok to close the Server Information form.
          

5- In the Centralized Configuration form component "com.bmc.arsys.server", pick sub component for corresponding AR node.
     Then Add:
          Db-Case-Insensitive: T
          Db-Functional-Index: T
Each node in the server group must have these values.

          User-added image


          User-added image


6- Restart AR System. 


7- Validate results with Java Driver or main Driver, use glsql command with "select * from nls_session_parameters".
     Check the following article for the full set of commands:
     Remedy - Server - How to verify NLS SESSION PARAMETERS values using the Driver utility

     The expected output will show:
         ValueList 8:
         Number of Items : 2
            Type: CHAR - NLS_SORT
            Type: CHAR - BINARY_AI


8- Validate by enabling API, SQL logs, create any index on Developer Studio.
     Review SQL logs and confirm the NLS_SORT parameter in clause (example below):
     ........
     <SQL > <TID: 0000000319> <RPC ID: 0000003763> <Queue: Admin> <Client-RPC: 390600   > <USER: Demo> <Overlay-Group: 1>
     /* Tue Jun 19 2018 14:48:43.8940 */ CREATE INDEX I504_1_2 ON T504 ((nlssort(C2, 'NLS_SORT=BINARY_AI')))
     ........

The DBA can also confirm if sessions are using NLS_SORT=BINARY_AI and if we do, Oracle should pick up BINARY_AI indexes. 
The DBA must convert all out of the box indexes with new NLS_SORT=BINARY_AI linguistic indexes.



Note:

Can Create functional indexes after running the script attached in below article.

https://communities.bmc.com/docs/DOC-94486

In the resulting script, replace BINARY_CI with BINARY_AI and run the scripts in the environment.
Remedy AR System Server
AR System
Remedy AR System Server v.18.05 and higher
Attachment(s):