Bookshelf v8.0: Running the Siebel Case Insensitivity Wizard

Bookshelf Home | Contents | Index | PDF

Siebel Database Upgrade Guide > Upgrading the Siebel Database >

Running the Siebel Case Insensitivity Wizard

Upgrades from: Siebel 8.x.

Environments: Development and production test.

Platforms: Windows, UNIX, IBM z/OS.

Databases: All databases.

This topic is part of an upgrade process. See How to Perform a Siebel Database Upgrade.

For Siebel CRM 8.0, query features are enhanced to provide indexes that directly support case and accent insensitive (CIAI) queries on eligible text columns. The CIAI 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 CIAI Wizard is optional.

Overview of What the CIAI Wizard Does

The CIAI 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, the Wizard defines an index on the CIAI column.
If you select the Copy All index strategy, 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 CIAI 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 CIAI 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

Choosing the Correct Repository

If you are upgrading a development environment, run the CIAI 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), run the CIAI Wizard on the Siebel Repository. You then need to 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 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: Tools_Install_dir\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.

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

To run the CIAI 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.
Select Tools > Utilities > Case Insensitivity.
The CIAI Wizard displays.
Select "Administer the columns listed in this file."
Click Browse.
The tools\objects directory displays, containing the default .csv input files.
Select the desired .csv file, and click Open.
In the Wizard, click Next.
The CIAI Wizard validates the syntax of the file and validates the eligibility of all tables and columns. If the file contains errors or eligibility problems, the Wizard lists the records containing errors. If you continue, the Wizard skips records containing errors or eligibility problems.

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

If there are no errors, 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, click Export.
The Wizard exports the listing to a text file in input-file format.
Edit the text file, restart the CIAI Wizard, and specify the edited text file as the input file.
If the configuration settings are correct, 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, 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 CIAI 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.

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

Running the CIAI 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 > Column.
In the Columns list, select the desired columns.
Right-click the highlighted columns, and choose Case Insensitivity from the pop-up menu.
The CIAI Wizard validates the eligibility of the selected columns. The Wizard lists any columns that have eligibility errors. If you continue, the Wizard skips columns containing errors.

Export the error listing to a text file for reference, correct any errors, and restart the CIAI 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, click Export.
The Wizard exports the listing to a text file in input-file format. Edit the text file, then run the CIAI Wizard and specify the text file as the input file.
If the configuration settings are correct, 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, 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, the CIAI 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.