Case Insensitive in Remedy

I’m convinced that the case sensitiveness of Remedy is one of its major flaws. It can look like a minor issue, but believe me when I say that it can be the greatest source of incidences for your Remedy support team. Also for other languages that uses accents, the accent insesitiveness is a must, when sorting elements. So, changing your ARS instance to Case insensitiveness must be an objective. To have a Case Insensitive or Accent Insensitive instance of ARS is not difficult, but it isn’t straightforward. The best solution is to have the full text search enabled, but this is an extra cost in licenses. Sometimes you don’t have these licenses (or you don’t have enough for everybody), so another strategy is needed. Remedy bases all its comparisons and sorts in the database engine, so if we change the database engine, we change Remedy. That means that we must achieve that any ORDER BY or WHERE clause to be case insensitive. The solution then implies case insensitive sessions and indexes. If you control both, then you control your case sensitiveness. First of all, there is no decent option to achieve the result, so if yo want to follow my instructions, apply them in a preproduction environment before, and perform a complete functionality test. This solution is not supported by BMC and counter-side effects may appear. Also this solution is not complete. Some parts of Remedy will continue to be case sensitive, only the searches and sorts based on the database will change. Some comparisons are made at the ARS engine with cached data or they’re processed by parallel application (like the ones executed with the $PROCESS$ clause). For MS SQL Server installations it is easy to achieve the case insensitiveness, just changing the collation of the varchar columns. But for Oracle it is a bit difficult and no solution is found on the Internet (until now). So in this article I will focus on the Oracle installation.

First Step: Changing the session parameters

You must change two session parameters:

  • NLS_SORT: Controls the sorting.
  • NLS_COMP: Controls the comparisson.

In order to force Remedy to change then at each connection, you must create a session trigger in Oracle:

Oracle Session Trigger
CREATE OR REPLACE TRIGGER
    login_AI
AFTER LOGON ON SCHEMA
BEGIN
    DBMS_SESSION.SET_NLS('NLS_SORT','BINARY_AI');
    DBMS_SESSION.SET_NLS('NLS_COMP','LINGUISTIC');
END;

Once the trigger is created, you must restart all ARS servers to force reconnect and firing the triggers. And now Remedy is Accent Insensitive. If you don’t want accent sensitive but case insensitive use BINARY_CI instead of BINARY_AI. Don’t use localized sortings like XSPANISH_AI, beacuse it can result in active filters malfunctioning.

Second Step: Creating the indexes

The problem now is that all the indexes of Oracle are useless. That’s a big problem, since each time you open a form in ARS means a full scan. To avoid it you must create an accent insensitive version of all the indexes. I do not recommend to change the indexes to the accent insensitive version, but to duplicate them. This will allow you an easy rollback just disabling the session trigger in case of malfunctioning. The best way to achieve it is to:

  • Create indexes for all the primary keys of the Remedy tables. That is, the C1 column for the T and B tables, and the ENTRYID for the H and complex B tables.
  • Reproduce all the indexes listed in the SCHEMA_INDEX table. But be carefull to check the type of column, so only text fields use an accent insensitive index.

Also, use a prefix for these indexes in order to locate them in an easy way. The next code will create the list of CREATE INDEX sentences that you need:

Index creation
SELECT 'CREATE INDEX AI_I' || OBJECT_NAME || ' ON ' || OBJECT_NAME ||
' (NLSSORT(C1,''NLS_SORT=BINARY_AI''));'
FROM USER_OBJECTS WHERE OBJECT_TYPE= 'TABLE' AND (
(OBJECT_NAME LIKE 'T1%')
OR (OBJECT_NAME LIKE 'T2%')
OR (OBJECT_NAME LIKE 'T3%')
OR (OBJECT_NAME LIKE 'T4%')
OR (OBJECT_NAME LIKE 'T5%')
OR (OBJECT_NAME LIKE 'T6%')
OR (OBJECT_NAME LIKE 'T7%')
OR (OBJECT_NAME LIKE 'T8%')
OR (OBJECT_NAME LIKE 'T9%')
OR ((
(OBJECT_NAME LIKE 'B1%')
OR (OBJECT_NAME LIKE 'B2%')
OR (OBJECT_NAME LIKE 'B3%')
OR (OBJECT_NAME LIKE 'B4%')
OR (OBJECT_NAME LIKE 'B5%')
OR (OBJECT_NAME LIKE 'B6%')
OR (OBJECT_NAME LIKE 'B7%')
OR (OBJECT_NAME LIKE 'B8%')
OR (OBJECT_NAME LIKE 'B9%')
)
AND LENGTH(OBJECT_NAME)<6
)
)
UNION
SELECT 'CREATE INDEX AI_I' || OBJECT_NAME || ' ON ' || OBJECT_NAME ||
' (NLSSORT(ENTRYID,''NLS_SORT=BINARY_AI''));'
FROM USER_OBJECTS
WHERE OBJECT_TYPE= 'TABLE' AND (LENGTH(OBJECT_NAME)>5
AND(
(OBJECT_NAME LIKE 'B1%')
OR (OBJECT_NAME LIKE 'B2%')
OR (OBJECT_NAME LIKE 'B3%')
OR (OBJECT_NAME LIKE 'B4%')
OR (OBJECT_NAME LIKE 'B5%')
OR (OBJECT_NAME LIKE 'B6%')
OR (OBJECT_NAME LIKE 'B7%')
OR (OBJECT_NAME LIKE 'B8%')
OR (OBJECT_NAME LIKE 'B9%'))
OR (OBJECT_NAME LIKE 'H1%')
OR (OBJECT_NAME LIKE 'H2%')
OR (OBJECT_NAME LIKE 'H3%')
OR (OBJECT_NAME LIKE 'H4%')
OR (OBJECT_NAME LIKE 'H5%')
OR (OBJECT_NAME LIKE 'H6%')
OR (OBJECT_NAME LIKE 'H7%')
OR (OBJECT_NAME LIKE 'H8%')
OR (OBJECT_NAME LIKE 'H9%')
)
UNION
SELECT 'CREATE INDEX AI_' || SI.INDEXNAME || ' ON T'|| SI.SCHEMAID ||
' (' || DECODE(F1.DATATYPE,4,'NLSSORT(C'||SI.F1||
',''NLS_SORT=BINARY_AI'')','C'||SI.F1)
|| DECODE(SI.F2,NULL,'',',' || DECODE(F2.DATATYPE,4,
'NLSSORT(C'||SI.F2||',''NLS_SORT=BINARY_AI'')','C'||SI.F2))
|| DECODE(SI.F3,NULL,'',',' || DECODE(F3.DATATYPE,4,
'NLSSORT(C'||SI.F3||',''NLS_SORT=BINARY_AI'')','C'||SI.F3))
|| DECODE(SI.F4,NULL,'',',' || DECODE(F4.DATATYPE,4,
'NLSSORT(C'||SI.F4||',''NLS_SORT=BINARY_AI'')','C'||SI.F4))
|| DECODE(SI.F5,NULL,'',',' || DECODE(F5.DATATYPE,4,
'NLSSORT(C'||SI.F5||',''NLS_SORT=BINARY_AI'')','C'||SI.F5))
|| DECODE(SI.F6,NULL,'',',' || DECODE(F6.DATATYPE,4,
'NLSSORT(C'||SI.F6||',''NLS_SORT=BINARY_AI'')','C'||SI.F6))
|| DECODE(SI.F7,NULL,'',',' || DECODE(F7.DATATYPE,4,
'NLSSORT(C'||SI.F7||',''NLS_SORT=BINARY_AI'')','C'||SI.F7))
|| DECODE(SI.F8,NULL,'',',' || DECODE(F8.DATATYPE,4,
'NLSSORT(C'||SI.F8||',''NLS_SORT=BINARY_AI'')','C'||SI.F8))
|| DECODE(SI.F9,NULL,'',',' || DECODE(F9.DATATYPE,4,
'NLSSORT(C'||SI.F9||',''NLS_SORT=BINARY_AI'')','C'||SI.F9))
|| DECODE(SI.F10,NULL,'',',' || DECODE(F10.DATATYPE,
4,'NLSSORT(C'||SI.F10||',''NLS_SORT=BINARY_AI'')','C'||SI.F10))
|| DECODE(SI.F11,NULL,'',',' || DECODE(F11.DATATYPE,
4,'NLSSORT(C'||SI.F11||',''NLS_SORT=BINARY_AI'')','C'||SI.F11))
|| DECODE(SI.F12,NULL,'',',' || DECODE(F12.DATATYPE,
4,'NLSSORT(C'||SI.F12||',''NLS_SORT=BINARY_AI'')','C'||SI.F12))
|| DECODE(SI.F13,NULL,'',',' || DECODE(F13.DATATYPE,
4,'NLSSORT(C'||SI.F13||',''NLS_SORT=BINARY_AI'')','C'||SI.F13))
|| DECODE(SI.F14,NULL,'',',' || DECODE(F14.DATATYPE,
4,'NLSSORT(C'||SI.F14||',''NLS_SORT=BINARY_AI'')','C'||SI.F14))
||');' FROM SCHEMA_INDEX SI
LEFT JOIN FIELD F1 ON
((SI.SCHEMAID = F1.SCHEMAID) AND (SI.F1 = F1.FIELDID))
LEFT JOIN FIELD F2 ON
((SI.SCHEMAID = F2.SCHEMAID) AND (SI.F2 = F2.FIELDID))
LEFT JOIN FIELD F3 ON
((SI.SCHEMAID = F3.SCHEMAID) AND (SI.F3 = F3.FIELDID))
LEFT JOIN FIELD F4 ON
((SI.SCHEMAID = F4.SCHEMAID) AND (SI.F4 = F4.FIELDID))
LEFT JOIN FIELD F5 ON
((SI.SCHEMAID = F5.SCHEMAID) AND (SI.F5 = F5.FIELDID))
LEFT JOIN FIELD F6 ON
((SI.SCHEMAID = F6.SCHEMAID) AND (SI.F6 = F6.FIELDID))
LEFT JOIN FIELD F7 ON
((SI.SCHEMAID = F7.SCHEMAID) AND (SI.F7 = F7.FIELDID))
LEFT JOIN FIELD F8 ON
((SI.SCHEMAID = F8.SCHEMAID) AND (SI.F8 = F8.FIELDID))
LEFT JOIN FIELD F9 ON
((SI.SCHEMAID = F9.SCHEMAID) AND (SI.F9 = F9.FIELDID))
LEFT JOIN FIELD F10 ON
((SI.SCHEMAID = F10.SCHEMAID) AND (SI.F10 = F10.FIELDID))
LEFT JOIN FIELD F11 ON
((SI.SCHEMAID = F11.SCHEMAID) AND (SI.F11 = F11.FIELDID))
LEFT JOIN FIELD F12 ON
((SI.SCHEMAID = F12.SCHEMAID) AND (SI.F12 = F12.FIELDID))
LEFT JOIN FIELD F13 ON
((SI.SCHEMAID = F13.SCHEMAID) AND (SI.F13 = F13.FIELDID))
LEFT JOIN FIELD F14 ON
((SI.SCHEMAID = F14.SCHEMAID) AND (SI.F14 = F14.FIELDID))
WHERE F1.DATATYPE = 4
OR F2.DATATYPE = 4
OR F3.DATATYPE = 4
OR F4.DATATYPE = 4
OR F5.DATATYPE = 4
OR F6.DATATYPE = 4
OR F7.DATATYPE = 4
OR F8.DATATYPE = 4
OR F9.DATATYPE = 4
OR F10.DATATYPE = 4
OR F11.DATATYPE = 4
OR F12.DATATYPE = 4
OR F13.DATATYPE = 4
OR F14.DATATYPE = 4
;

Execute the code as a script, and then execute the output.

Maintaining the indexes

Now, your Remedy instance is Accent Insensitive. But the indexes created will degrade over time because:

  • Every time you create a new index in a form, it will be case sensitive. No case insensitive version will be created.
  • Every time you create a form, you are creating indexes, they also will be case sensitive.
  • Oracle case insensitive indexes can corrupt over time.

In order to avoid it, it is a best practice to erase all your indexes and recreate them. Do it periodically. To erase all indexes, you can use this script:

Index deletion
SELECT 'DROP INDEX ' || OBJECT_NAME || ';'
FROM USER_OBJECTS WHERE OBJECT_TYPE = 'INDEX'
AND OBJECT_NAME LIKE 'AI%';
Posted on by Jose Huerta in Database, Featured

About Jose Huerta

Jose is a project manager and service manager from Mallorca (Spain). The last years, he has been closely related to Remedy at almost all levels: deployment, customization, consulting, administration and service management. His work doesn't end with BMC products, but he's continually exploring other aspects of IT management, as the leader of development projects and IT services.

8 Responses to Case Insensitive in Remedy

  1. ToRaX

    Thanks a lot for providing this useful script!

    One correction:
    Where is says
    AND LENGTH(OBJECT_NAME)5
    it should be
    AND LENGTH(OBJECT_NAME) <= 5

    Comment:
    In my environment, the script did not create statements for creating indexes for IH… and IB… tables. Is this on purpose?

    • Jose Huerta

      Thanks for the typo. The error is bigger than it seems. Now it is corrected. But comparison operators (>,<) have produced an strange behavior (as they were interpreted as HTML tags) and some part of the script disapeared. That’s the reason for not creating indexes on B and H tables. Try this new version.

      If you experience any other problem comment it!

  2. ToRaX

    Thanks for the new version of the SQL statement! With this, I was able to create more than 1000 previously missing indexes.

    For verification purposes, I have now created a simple query that checks all indexes (I%) if they have a prefixed version. This query returns approx. 200 indexes. I checked some of them, they are in fact missing a prefixed index.

    Can you observe the same on your system?

    The query you could use:
    select object_name as missing_index from user_objects where object_type=’INDEX’ and object_name like ‘I%’
    and ‘AI_’||object_name not in (
    select object_name from user_objects where object_type=’INDEX’ and object_name like ‘AI_%’)

    • Jose Huerta

      Yes It’s normal. If an index is composed only by non-text fields (dates or numbers, for instance), then the index is not recreated as accent insensitive.

      That’s the objective of the last where statement, to ensure at least one text field.

      If you can find one index not created that contains text fields, then it is a missing index and we can improve the script.

  3. ToRaX

    Ok, I see. Thanks for the explanation!

  4. el.perolin

    Thanks! Tested on my BMC ITsm instance and it works fine!

  5. ToRaX

    Hi again,

    it’s been a while… and we’re still using case-insensitive searches with Oracle.

    I just came across the following:
    The system tables that hold workflow objects (e.g. actlink) do have OOTB indexes as well. When using the system with case-insensitive settings, wouldn’t we have to re-create the appropriate indexes also for these tables (e.g. index ACTLINK_IND on table ACTLINK)?

  6. ToRaX

    Jose, did you have the chance to have a look at my finding?
    Do you agree that additional manual indexes are necessary?

Add a Comment