This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.
Remedy AR System Server
BMC Remedy AR System Server 8.1, 9.x
Enabling case-insensitive searches with an AR System server 8.1, 9.x with Oracle
Enabling case-insensitive searches with an AR System server 8.1 or 9.x with Oracle
By default, the Oracle database is case-sensitive. This topic discusses how you can enable case-insensitive searching for fixed-length text fields in BMC Remedy AR System 8.1 or 9.x using Oracle.
This topic includes:
- BMC supports using ARSystem in an Oracle case-insensitive environment when searching limited-length text fields.
- This topic describes one method of configuring this environment. there may be a different method to set Oracle into case-insensitive. This is a task for the DBA.
- There is some work that needs to be performed on the Oracle side in order complete the Case-Insensitivity setup. This involves the creation of Linguistic indexes wherever a character index exists.
- As a courtesy, BMC has provided information and an unsupported utility to help customers with the creation of these Linguistic Indexes.
- BMC recommends that you make these changes in a non-production environment and monitor the environment for potential problems before deploying this solution in a production environment.
- The database administrator should also monitor the usage pattern of newly created functional (linguistic) indexes.
- For any issues with the Oracle database after these changes, work directly with Oracle Support as it is outside of the BMC Remedy AR System scope.
For optimal usage of database indexes for case-insensitive searches, your environment must meet the following configuration requirements:
· The Oracle parameter cursor_sharing needs to be set as specified in our documentation depending the exact version level.
Please check the following link to get this information: Tuning the Oracle server - Cursor sharing
This is set at the database and in the ar.cfg/ar.conf file with the Oracle-Cursor-Sharing parameter..
Configuring the AR System server for case-insensitive search
To enable a case-insensitive search for fixed-length text fields in BMC Remedy AR System on Oracle, complete the following steps. (For a successful implementation, follow all of the steps in the order described.)
ALTER SESSION SET NLS_SORT=BINARY_CI
A defect in 9.1 SP2 caused the NLS_SORT setting to change to BINARY_AI, which is Case Insensitive and Accent Insensitive. This will impact performance as the existing functional indexes are configured with BINARY_CI and will not be used. This has been fixed in 9.1 SP2 Patch 1 - this version reverts to using BINARY_CI.
By default, the AR System server creates regular indexes when you add an index to a form. To support case-insensitive searches on Oracle databases, for all indexes based on character fields functional indexes are required instead of regular indexes. Setting this parameter will cause the AR System server to create functional indexes for the form when a new index is added to a form. This parameter helps to avoid performance degradation that can result from not using database indexes.
These configuration changes cause the AR System server to configure the Oracle session to support case-insensitive searching.
This is applicable to all searches, including those generated by workflow (active links, filters, escalations, and so on).
3. Recreate indexes in the Oracle database as functional indexes using the attached li_<date>.sql PL/SQL script.
(see “Converting AR System server indexes into functional indexes” below)
Converting AR System server indexes into functional indexes
While running in case-insensitive mode, Oracle does not use the regular indexes that the AR System server creates. Instead, Oracle uses functional indexes (also called linguistic indexes). You can create a functional index based on a specific language or on a multilingual option. This ensures that the indexed entries are sorted in the correct order for the specific language or language set.
You can find this unsupported script attached to this document.
This script needs to be run as SYSDBA. After connecting to sqlplus as SYSDBA, execute the following command:
SQL> alter session set current_schema = <Remedy Schema Owner Name, e.g. ARADMIN>
Execute the li_<date>.sql script. The script will prompt you for the schema owner name and tablespace name.
These are likely upper case such as ARADMIN and ARSYSTEM.
To run the scripts:
1. The first step is to run the drop_i.sql script. This will drop all the current indexes. Make sure you have a Database backup. These indexes need to be deleted so that the new Linguistic Indexes do not conflict.
2. Next, execute the create_li.sql script to create new Linguistic indexes for every existing regular index. Spool the output to a text file. This script could take up to 30-60 minutes to complete.
SQL> spool c:\temp\createli.txt
SQL> spool off
If this occurs, you will need to identify the data that is in violation, remove or repair the duplicate data and manually rebuild the specific index by re-executing the SQL statement.
You can try identifying the duplicate data using the following example SQL statement:
If the table name in the error message is 'T' and some number, you can use the numeric portion of the table name in the following SQL statement to identify the name of the form.
You can ignore errors referring to Primary Key indexes such as, "ORA-02429: cannot drop index used for enforcement of unique/primary key". Some of the Data-Dictionary tables have PKs using regular indexes. These indexes cannot be dropped since they are defining the PK. When you run the 'create_li.sql' script, it will create a new Linguistic Index and the original 'regular' index will remain. This is normal and does not present a problem.
Case-insensitive search for unlimited-length text and attachments fields
The AR System server stores unlimited-length text fields and attachment fields as CLOBs in the Oracle database, which does not allow case-insensitive text search on CLOB fields. Use AR System server Full Text Search (FTS) for case-insensitive search capability on unlimited-length text fields and attachment fields.
Use the underlying database capability to provide case-insensitive search for limited-length text fields in the AR System server. If a field is marked for FTS indexing, a database search is not performed for that field. Instead, the search request uses the FTS search engine.
For the fields that are marked for FTS indexing solely for providing case-insensitive search in the AR System server, set the Index For FTS field property to None. This option disables FTS indexing for the field and routes any searches on the field to the database.
Case-insensitive searches for non-ARserver clients
If you use another tool to perform searches on the Remedy database, such as SQLPlus, Toad, or SQL Developer, you must set the 2 session parameters from that session to have a Case-Insensitive session. Alternatively, you can create a login trigger that sets these automatically:
ALTER SESSION SET NLS_SORT=BINARY_CI
It turned out to be hitting a known Oracle Bug 27416997 (check Metalink Doc ID 2390584.1).You will need Oracle credentials to access this Bug, you may reach out to your DBA). The Bug has been resolved in Oracle 19.1.
To work around this issue, set below parameters (as per above Oracle solution doc)
alter system set "_optimizer_generate_transitive_pred"=FALSE scope=both
See this article for more information