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

Version 5
    Share:|

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    Remedy AR System Server


    COMPONENT:

    AR System


    APPLIES TO:

    Remedy AR System Server v.18.05



    QUESTION:

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


    ANSWER:

    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. 
    Note, the DBA must convert all out of the box indexes with new NLS_SORT=BINARY_AI linguistic indexes.


    Article Number:

    000154764


    Article Type:

    FAQ/Procedural



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles