Siebel Innovation Pack 2015: Running the Siebel Case Insensitivity Wizard

Siebel Database Upgrade Guide > Upgrading the Siebel Database >

Running the Siebel Case Insensitivity Wizard

Environments: Development and production test.

Platforms: Windows, UNIX, IBM z/OS.

For Siebel CRM 8.1, query features are enhanced to provide indexes that directly support case and accent insensitive (CIAI) queries on eligible text columns. The Case Insensitivity Wizard configures specified columns for CIAI queries by defining CIAI columns and CIAI indexes in the repository. The wizard also sets the Default Insensitivity property for these columns to DB Case & Accent.

The purpose of the enhanced CIAI features is to improve query effectiveness and performance. Running the Case Insensitivity Wizard is optional.

Overview of What the Case Insensitivity Wizard Does

The Case Insensitivity Wizard performs the following functions in the repository to configure columns to support CIAI queries. No columns or indexes are created in the Siebel database until you synchronize the repository to the Siebel database. The columns you want to configure for CIAI queries are called base columns:

Validates the syntax of all records if an input file is used.
Validates that all specified tables and columns are eligible for CIAI configuration.
For each eligible base column, defines a new CIAI column. The CIAI column contains the data in the base column converted to uppercase.
If you select the Single or Copy All index strategy, then the wizard defines an index on the CIAI column.
If you select the Copy All index strategy, then it defines a copy of all indexes that have the base column as a key. The new indexes reference the CIAI column instead of the base column.
Sets the Default Insensitivity property for the base column to DB Case & Accent.
Sets flags and performs other configuration operations in the repository to support CIAI queries.

The Case Insensitivity Wizard can also be run in a special mode to set the Default Insensitivity property on columns that do not have any indexes defined.

The main purpose of the CIAI query enhancements is to provide indexes that can be used for case insensitive searches. The database does not have to perform table scans to locate records. This allows the database to perform case insensitive searches more quickly.

For example, in S_CONTACT, you configure the column LAST_NAME for CIAI queries. The Case Insensitivity Wizard defines a column called LAST_NAME_CI. When you query for the name Smith, the Object Manager creates a query similar to the following (IBM DB2):

SELECT column list FROM S_CONTACT

WHERE LAST_NAME_CI = SMITH

CIAI Upgrade Issues for IBM DB2 Users

The Case-Insensitive and Accent-Insensitive search functionality requires changes on database schema levels (new columns, indexes, and triggers). When running the ddlimp utility on IBM DB2 databases, tables that have CIAI columns and triggers are not rebuilt. To resolve this issue, see 553429.1 (Article ID) on My Oracle Support.

Choosing the Correct Repository

If you are upgrading a development environment, then run the Case Insensitivity Wizard on the Siebel Repository. Later in the upgrade process, this repository will be renamed Prior Customer Repository.

If you have completed an upgrade of the development environment (upgrep, merge, upgphys), then run the Case Insensitivity Wizard on the Siebel Repository. You then must generate another schema.ddl file and use it to update your production test and production environments. Typically, you run the wizard after an upgrade is complete to revise the configuration of columns you have configured for case insensitive queries.

Running the Case Insensitivity Wizard Using an Input File

Oracle provides a recommended input file for Siebel Business Applications and for Siebel Industry Applications. The input files have a .csv extension and are located in the following directory:

Windows: SIEBEL_TOOLS_ROOT\objects

These files list columns that are frequently used for queries and are provided as a recommendation. You can edit these files or create new input files as desired.

Requirements: See About the Siebel Case Insensitivity Wizard for information on how the Case Insensitivity Wizard works, eligibility criteria, and how to edit input files.

To run the Case Insensitivity Wizard using an input file

Review the input file and verify the following:
- The syntax for all records is correct.
- The tables and columns are eligible.
- The specified configuration options are correct.
- The configuration defaults are acceptable if any configuration options are omitted.
In Siebel Tools, open the repository.
Lock the tables listed in the input file.
From the Tools menu, choose Utilities, then Case Insensitivity.
The Case Insensitivity Wizard displays.
Select "Administer the columns listed in this file."
Click Browse.
The SIEBEL_TOOLS_ROOT\objects directory displays, containing the default .csv input files.
Select the desired .csv file, and click Open.
In the wizard, click Next.
The Case Insensitivity Wizard validates the syntax of the file and validates the eligibility of all tables and columns. If the file contains errors or eligibility problems, then the wizard lists the records containing errors. If you continue, then the wizard skips records containing errors or eligibility problems.

If there are errors, then export the listing to a file, correct any errors in the input file, and restart the Case Insensitivity Wizard. Click Export to export the error listing to a text file.

If there are no errors, then click Next.

The Wizard displays the records in the input file.
Review the configuration settings, and verify that they are correct.
If you want to change any configuration settings, then click Export.
The Wizard exports the listing to a text file in input-file format.
Edit the text file, restart the Case Insensitivity Wizard, and specify the edited text file as the input file.
If the configuration settings are correct, then click Next.
The Wizard displays the changes it will make to repository tables and indexes.
If you want to save a record of the changes, then click Export.
The Wizard writes the changes to a text file.
Click Finish.
The Wizard configures the columns in the repository to support CIAI queries.

Running the Case Insensitivity Wizard by Selecting Columns

This procedure assumes the Object Explorer is in Types mode where objects are displayed hierarchically. An alternate way to perform the procedure is to display Object Explorer in Flat mode. Then choose the Column object and navigate to the desired columns.

Requirements: Review About the Siebel Case Insensitivity Wizard for information on how the Case Insensitivity Wizard works, eligibility criteria, and how to edit input files.

Running the Case Insensitivity Wizard by selecting columns

In Siebel Tools, open the repository.
In the Siebel Tools Object Explorer, select Table.
In the Tables list, lock the desired table, and highlight it.
In the Object Explorer, select Table, then Column.
In the Columns list, select the desired columns.
Right-click the highlighted columns, and choose Case Insensitivity from the pop-up menu.
The Case Insensitivity Wizard validates the eligibility of the selected columns. The Wizard lists any columns that have eligibility errors. If you continue, then the wizard skips columns containing errors.

Export the error listing to a text file for reference, correct any errors, and restart the Case Insensitivity Wizard. Click Export to export the error listing.

The Wizard displays the configuration settings it will use to configure the columns.
Review the configuration settings and verify they are correct.
If you want to change any configuration settings, then click Export.
The Wizard exports the listing to a text file in input-file format. Edit the text file, then run the Case Insensitivity Wizard and specify the text file as the input file.
If the configuration settings are correct, then click Next.
The Wizard displays the changes it will make to the repository tables and indexes.
If you want to save a record of the changes, then click Export.
The Wizard writes the changes to a text file.
Click Finish.
The Wizard configures the columns in the repository to support CIAI queries.

Configuring CIAI Support for Columns That Do Not Have Indexes Defined

If a column does not have any indexes defined, then the Case Insensitivity Wizard does not create new columns or indexes.

However, you can run the wizard in special mode that changes the column's Default Insensitivity property to DB Case & Accent. The Wizard performs these steps on all eligible columns in the repository. You cannot manually select columns.