Technology
Stefan Damian, , , , Leave a Comment on Enabling case-insensitive searches with an AR System server 8.1 or 9.x with Oracle
, , , ,

Enabling case-insensitive searches with an AR System server 8.1 or 9.x with Oracle

Note: This is a guide provided by Support as a courtesy. There may be better or different ways to set Oracle to case-insensitive and it is up to the DBA Team to perform this Oracle task.
 
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:

         Pre-requisites for using database indexes for case-insensitive search on Oracle
         Configuring the AR System server for case-insensitive search
         Converting AR System server indexes into functional indexes
         Considerations for case-insensitive searches
 
Note:
– 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.
 
Pre-requisites for using database indexes for case-insensitive search on Oracle
For optimal usage of database indexes for case-insensitive searches, your environment must meet the following configuration requirements:
· A minimum client version of Oracle 11.2.
· 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.)
 
1.  Configure the AR System server as follows:
a.  Add the following configuration to the AR System server configuration file (ar.cfg or ar.conf):
         Db-Case-Insensitive: T
This causes special session parameters (NLS_SORT and NLS_COMP) to be set to support case-insensitive searches and invalidate existing database indexes. 
       ALTER SESSION SET NLS_COMP=LINGUISTIC
       ALTER SESSION SET NLS_SORT=BINARY_CI
 
IMPORTANT NOTE!!!
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. 
 
b. Add the following configuration to the AR System server configuration file (ar.cfg or ar.conf):
        Db-Functional-Index: T

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.

 
2. Restart the AR System server to ensure that the configuration changes take effect.
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 PL/SQL script.
(see “Converting AR System server indexes into functional indexes” below)

4. Test

Perform functionality and performance testing to ensure proper operation.   There should be a behavior difference since upper and lower case characters will now be treated the same whereas in the past they were treated as different.

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.

To ensure satisfactory performance when using case-insensitive searching in the AR System server, you must create functional indexes in the BMC Remedy AR System database.
BMC provides an unsupportedPL/SQL script to manage these existing indexes and to convert them from regular to functional indexes.

 
IMPORTANT NOTE!!!
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 OraCaseTransformation.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.

 
This script will create 4 new scripts based on the current existing indexes.
  a) drop_i.sql             — Drops existing Normal indexes                                                               
  b) drop_li.sql             — Drops Linguistic indexes if reversion to Case Sensitivity is needed         
  c) recreate_ni.sql     — Recreates Normal indexes if reversion to Case Sensitivity is needed         
  d) create_li.sql       — Creates Linguistic indexes                                                   
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.

 For example:
SQL> spool c:\temp\createli.txt
SQL> @create_li.sql
SQL> spool off
Note: check the spool file for errors from this script. 
You may encounter “ORA-01450: maximum key length (xxxx) exceeded” errors when creating some Linguistic Indexes. This is because the total key length of the columns in the index exceeds the (xxxx) value above. That value is 6398 for a database block size of 8K. Your best option then may be to create those indexes in a tablespace of larger block size.
 
You may also encounter “ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found”
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.   
 
For example:  If the spooled output shows the following
 
 
CREATE unique index I253_8_1_LI on T253(NLSSORT(C8,’NLS_SORT=BINARY_CI’)) PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE ARSYSTEM
ERROR at line 1:
 
 
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
You can try identifying the duplicate data using the following example SQL statement:
 
 
SQL> select UPPER(C8),count(*) from T253 group by UPPER(C8) having count(*) > 1 order by 1;
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.
SQL> select name from arschema where schemaId=253

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.

 
3.  At this point you can test to ensure that these indexes exist and work properly.
 
Considerations for case-insensitive searches
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.
Case-insensitive search for limited-length-text 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_COMP=LINGUISTIC
ALTER SESSION SET NLS_SORT=BINARY_CI
 
NOTE:  It was found that if AR System was being run in Case Insensitive mode on Oracle 12c or above, certain use cases would not return expected rows. In this example, “Site” drop down menu on the CTM:People form had returned 72 rows (as expected) when Remedy was in Case Sensitive mode, however NO RECORDS were listed when it was ran in Case Insensitive mode.
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

Leave a Reply