Siebel Database Upgrade Guide > How the Siebel Database Upgrade Works >

About the Siebel Case Insensitivity Wizard

Upgrades from: All Supported Siebel releases.

Environments: Development environment only.

Platforms: Windows, UNIX, IBM z/OS.

Databases: All databases.

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.

You run the CIAI Wizard during the development environment upgrade, after the upgrep and before the repository merge.

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 enhancement 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 indicates SMITH

The database then uses the CIAI index on LAST_NAME_CI to locate the records.

For columns where Force Case is set, the data in the database is all in the same case. No CIAI columns or indexes are needed. The Object Manager uses the indexes already defined on the base column to retrieve records.

How CIAI Columns and Indexes Are Implemented in the Database

In a development environment upgrade, you apply the repository schema definition to the physical database. How CIAI columns and indexes in the repository are implemented in the physical database depends on the database type. For example, on Microsoft SQL, CIAI columns are implemented as calculated columns. On Oracle Database, CIAI columns are implemented as function-based indexes. On IBM DB2, CIAI columns are implemented as schema columns.

CIAI Wizard Modes

You can run the CIAI Wizard in one of two modes:

• Configure specified columns. You can specify the columns you want to configure for CIAI queries by using an input file or by selecting the files manually:
  • Use an input file. The Wizard reads the input file and configures the columns in the file for CIAI queries. Using an input file allows you to control which configuration options the Wizard uses. Oracle provides recommended input files. These files contain columns frequently used in searches. Use an input file if you want to enable large numbers of columns or to use methods or index strategies other than the defaults.
  • Select columns manually. You can use the Tools Object Explorer to manually select and configure specific columns for CIAI queries. The Wizard uses configuration defaults to configure these columns. If you want to modify the configuration options, you can export the configuration strings to a text file, edit them, and run the Wizard using the edited file as an input file.
• Specify how queries are built for columns without indexes. The CIAI Wizard defines CIAI columns and indexes only on columns that already have indexes defined. However, for columns without indexes that meet all other eligibility criteria, you can run the CIAI Wizard in a special mode to change the Default Insensitivity property from None to DB Case & Accent. In queries, the column values are then converted to uppercase before being compared. This allows searches to be both case and accent insensitive.

  For example, in S_CONTACT, assume the column LAST_NAME has no indexes defined on it. You run the CIAI Wizard to set Default Insensitivity to DB Case & Accent. When you query for the name Smith, the Object Manager uses a query similar to the following (IBM DB2):

  SELECT column list FROM S_CONTACT

  WHERE UPPER(LAST_NAME) LIKE UPPER(Smith)

Input File Format

For configuring eligible table columns, the CIAI Wizard can accept a comma-delimited csv file as input. Each line in the file is one record that defines one column to be configured for CIAI queries.

The record format is as follows:

<Table Name>,<Column Name>,<Method>,<Index Strategy>,<Operation>

The fields in the record are explained in the following example of a record:

S_CONTACT,EMAIL_ADDR,Database,Copy All,On

Only the first two items in the record are mandatory (Table Name and Column Name). For records where items are omitted, the CIAI Wizard inserts the default value for the items.

If you omit an item from a record, you must still provide the item's delimiting commas. Here is a record with the index strategy item omitted:

S_CONTACT,EMAIL_ADDR,Database,,On

The CIAI Wizard does not provide special handling for denormalized columns. To configure CIAI queries on denormalized columns, you must include them in 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

Eligibility Criteria

The CIAI Wizard verifies that all records in an input file or in columns that you manually select meet the following eligibility criteria:

• The table and column must exist in the repository.
• The column must be active and belong to the specified table.
• The table type must be supported for CIAI configuration.
• The column functional type must be supported for CIAI configuration.
• The column physical type must be supported for CIAI configuration.
• The column must have one or more indexes already defined on it. If no indexes are defined on the column, but the column is otherwise eligible, the Wizard accepts the column, but it does not create a CIAI column or any CIAI indexes for the column. The Wizard sets the Default Insensitivity to DB Case & Accent.

Table Name—Supported Table Types

Table 7 lists the table types that can be configured for CIAI queries.

Column Name—Supported Column Functional Types

Table 8 lists the column functional types that can be configured for CIAI queries.

Column Name—Supported Physical Column Types

Table 9 lists the physical column types that can be configured for CIAI queries.

Text and CLOB are accepted by the Wizard as valid. However, the Wizard does not create a CIAI column or CIAI indexes for these two types. The Wizard sets Default Insensitivity to DB Case & Accent.

Method and Index Strategy

The CIAI Wizard method and index strategy determine how the Wizard configures CIAI queries for a column.

There are two methods:

• Force Case. The Force Case method does not create a CIAI column or indexes. Use the Force Case method for columns where the Force Case property is set.

  The Force Case property causes column data to be set to the same case (FirstUpper, lower, upper) before being written to the database. Since all the data in the base column are in the same case, the base column and its indexes can be used for case insensitive queries. A CIAI column and indexes are not needed.

  The CIAI Wizard considers Force Case to be set for a column when Force Case indicates FirstUpper, Lower, or Upper. The CIAI Wizard does not consider Force Case to be set when Force Case indicates none or is null.

• Database. The Database method defines a CIAI column for the base column and uses an index strategy to create indexes. Index strategies determine how indexes are defined for the CIAI column, as described in Table 10.

  Table 10. Index Strategies Used by the Database Method

  Index Strategy	Description
  None	The Wizard defines no new columns or indexes. The Wizard sets the Default Insensitivity to DB Case & Accent.
  Single	The Wizard defines a new CIAI column and defines a single index on it. For every index the base column participates in, the Wizard does not create another index that references the CIAI column. The Wizard sets the Default Insensitivity to DB Case & Accent.
  Copy All	The Wizard defines a new CIAI column and a CIAI index for the column. In addition, for every index the base column participates in, the Wizard defines a copy of that index. The copy references the CIAI column instead of the base column. The Wizard sets the Default Insensitivity to DB Case & Accent.

  For indexes with multiple columns as keys, for the first key where the column becomes CIAI enabled, the Wizard defines a copy of the index. In the copy, the key references the CIAI column instead of base column. For each additional key that is CIAI enabled, the Wizard deletes the index copy in the repository and redefines it so that keys reference the additional CIAI columns.

  For example:

  • The S_CONTACT table has Base Column A with Index A that has two columns as keys, LAST_NAME and FST_NAME.
  • You then select the column LAST_NAME for CIAI queries and specify the Copy All index strategy.
  • The CIAI Wizard defines the column LAST_NAME_CI and a CIAI index for the new column.
  • The Wizard also defines Index B for Base Column A by copying Index A and specifying LAST_NAME_CI and FST_NAME as keys.
  • You then select the column FST_NAME for CIAI queries.
  • As part of configuring FST_NAME for CIAI queries, the Wizard does the following in the repository:
    • Defines a new column FST_NAME_CI.
    • Deletes Index B on Base Column A and redefines it with the keys LAST_NAME_CI and FST_NAME_CI.

Operation—Inactivating CIAI Configuration

The Operation field in the configuration syntax controls whether the columns and indexes created by the CIAI Wizard are active. The available settings are On and Off. The default is On.

Use the Operation field to deactivate the CIAI configuration of columns. When you run the CIAI Wizard against a column and specify Operation indicates Off, the wizard does the following:

• Inactivates the CIAI column created on the base column.
• Inactivates the CIAI index created for the CIAI column.
• For indexes that the base column participates in, sets the related CIAI indexes to inactive.

Note that the CIAI Wizard does not delete CIAI columns or CIAI indexes in the repository.

You cannot use Operation indicates Off to set the Default Insensitivity property on a column from DB Case & Accent to None.

CIAI Wizard Defaults

The only required inputs for the CIAI Wizard are Table Name and Column Name. If you omit the other fields, the CIAI Wizard uses the default settings.

Methods

The CIAI Wizard uses the following default settings for the methods:

• In the repository, if the Force Case property is set on the column, then the Wizard uses the Force Case method.
• If the Force Case property is not set, then the Wizard uses the Database method.

Index Strategy

The Wizard uses the following defaults for index strategy:

• If the method is Force Case, the Wizard sets the index strategy to None.
• If the method is Database, and the base column does not have indexes defined on it, the Wizard sets the index strategy to None.
• If the method is Database, and the base column has indexes defined on it, the Wizard uses the Copy All index strategy.

If the Wizard uses None as an index strategy, then the Wizard does not define new columns or indexes. It sets Default Insensitivity to DB Case & Accent.

This setting means that the Wizard has the following default behaviors:

• When the Force Case property is set on a column, the Wizard does not define columns or indexes.
• If the column has no indexes defined on it, the Wizard does not define columns or indexes.

Note that in both cases, the Wizard accepts the column as eligible but does not define columns or indexes. These default behaviors define the implicit eligibility requirements.

Operation

If you omit Operation, the Wizard sets Operation to On, regardless of the method, or index strategy.

Running the CIAI Wizard Multiple Times

You can run the CIAI Wizard multiple times. Typically, you would do this to tune the CIAI configuration of columns. If you have already run the CIAI Wizard once to configure columns, you may run it again to perform additional tasks. Table 11 lists operations that you can perform when running the CIAI Wizard again:

Column and Index Naming Convention

The CIAI Wizard uses the same naming conventions as the EIM Wizard when creating CIAI column names and index names. The naming conventions used by the Wizard are fixed and cannot be overridden.

Column Names

The Wizard defines CIAI column names by appending _CI to the parent column name. For example, if the parent column is LAST_NAME, the CIAI column would be LAST_NAME_CI.

Index Names

The Wizard defines CIAI index names by adding a string to the base table name:

base table Name_C#

where:

# is an integer incremented starting at 1 as needed to create a unique name.

An example of a CIAI index created on table S_CON_ADDR is S_CON_ADDR_C1.

Truncating Names

The default length for column names and index names is 30 characters. You can limit the length of names to 18 characters (to conform to the requirements of the DB2 database), prior to running the CIAI, by selecting the "Limit schema object names to 18 characters" option in Tools > View > Options > Database.

If CIAI column names or index names exceed the maximum length, the Wizard truncates the column base name or the table base name (for indexes) using the following strategy:

• Deletes underscores one at a time, beginning with the left-most
• Deletes vowels one at a time, beginning with the right-most
• Deletes characters (letters, numbers, and so on) one at a time, beginning with the right-most.

The Wizard does not truncate prefixes or postfixes.

Column and Index Name Uniqueness

After truncating a column or index name, the name may not be unique. When this occurs, the Wizard modifies the truncated column or index name by truncating the right most character in the base column name or base table name. The Wizard replaces the truncated character with an integer, starting at 1.

If this modification does not create a unique name, the Wizard increments the integer. If the integer becomes two digits or larger, the Wizard truncates the name to make room for the digits. This process maintains the overall string length.

Error Reporting

The CIAI Wizard reports errors in a pane in the Wizard. The error listing provides enough information so that you can identify the column and the cause of the problem.

You then have two options:

• You can correct the errors and rerun the Wizard.
• You can ignore the errors.

  When the Wizard configures columns, it skips all columns that have errors.

When the Wizard reports errors, you can export them to a text file for reference. Errors usually fall into one of the following categories:

• Input file syntax errors. These include punctuation and use of improper configuration options.
• Table and column eligibility problems. These errors occur when you choose tables and columns of types that the Wizard does not support.
• Project not locked. You must lock the tables you want to configure before running the Wizard. The Wizard displays the list of projects that must be locked.