Siebel: Sensitizing Case Insensitive Queries

There was a time in the not too distant past - whenever users ask for certain fields to be case insensitive, developers had an answer - "look it's not like we can't or won't do it - it is just that the performance takes a beating". Now things look a lot easier.

The Problem

Siebel applications are case sensitive by default. Although we would certainly like to see them as another Google search - enterprise search was never really a Siebel forte (hold, I did not come to Siebel 8.0 improvements and Oracle Secure Search!). Now, searching for accounts or contacts would require the users to have a general idea or adhere to a framework of naming convention. An "ABC Company" here and "Abc Company" there, albeit a typo, result in frustration for the users. If you can have your whole application case insensitive you just turn on a switch and you lead a peaceful life. But unfortunately, not many of us are blessed with small databases or user-base and the kind of queries that requires users to take coffee-breaks are not encouraged.

Tackling case-insensitive queries assumed monstrous proportions in some implementations. Users have to get their work done. So there were a few work-arounds:

• Force case sensitive queries for desired fields by prefixing the queries with "~". Might have user-training problems, users are not happy with the extra typing (you have to really search for the damn character). Again, this might lead to the coffee-break problem, although in a much smaller scale
• Force a single case (like all caps) for certain fields like account or contact names. Again, not a beautiful solution, requires lot of user discipline

Then, there were developers - they have to maintain a certain level of user-satisfaction in the interest of their jobs and so were more work-arounds:

• Tell users to use ~ in their queries. Create indexes (like functional indexes) to manage performance loads
• Make selected fields case insensitive and manage performance through indexes
• Force case on fields and enforce user discipline
  
• And another interesting solution, which is now mainstream - enabling case-insensitivity through a redirection approach (you have to go through the entire article)

How Siebel Helps?

Enter CIAI (Case Insensitive and Accent Insensitive) wizard in Siebel 8.0. In there, we have a more streamlined way of dealing with enabling case-insensitive queries for specific fields.

What you do, as a developer, is pretty straight-forward:

• Select columns of interest
• Invoke CIAI wizard
• Select all indexes that need tuning
• Let wizard do it's thing

What you do, as a user, is more simple:

• You use the enabled fields to query - no case, no nonsense
  

What CIAI wizard does is not so simple:

• Change default insensitivity property to "DB Case & Accent" for the specified columns
  
• Optionally, create child columns that are same as parent (or previoulsy specified) columns except for the force-case part. In the background, all values are stored in one case
• Optionally, delete index including parent columns and recreate indexes for the child columns
  

Now, the object manager reroutes the queries on specified columns to the case-insensitive columns that in-turn use the case-insensitive indexes to return results that are case-insensitive. I bet you don't want to hear the term case-insensitive again (there you go!).

We can run CIAI wizard multiple times and even turn off the changes made in prior executions. Note that there are a bunch of prerequisites. More important ones - only certain table types like Data (Public/Private/Intersection), Extension etc. are supported and only char, varchar and CLOB data types can be configured for CIAI queries.

Interested to learn more? - Head to "Configuring Siebel Business Applications" in Bookshelf.