Siebel Database Upgrade Guide > Siebel Case Insensitivity Wizard >

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 Server, 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.

Case Insensitivity Wizard Modes

You can run the Case Insensitivity 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 Case Insensitivity 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 Case Insensitivity Wizard uses default settings to configure these columns. If you want to modify the configuration options, then 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 Case Insensitivity 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 Case Insensitivity 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 Case Insensitivity 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 Case Insensitivity 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 Case Insensitivity Wizard inserts the default value for the items.

If you omit an item from a record, then 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 Case Insensitivity 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: SIEBEL_TOOLS_ROOT\objects

Eligibility Criteria

The Case Insensitivity 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, then the wizard accepts the column, but it does not create a CIAI column or any CIAI indexes for the column. The Case Insensitivity Wizard sets the Default Insensitivity to DB Case & Accent.

Table Name: Supported Table Types

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

Column Name: Supported Column Functional Types

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

Column Name: Supported Physical Column Types

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

Text and CLOB are accepted by the Case Insensitivity 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 Case Insensitivity 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 Case Insensitivity Wizard considers Force Case to be set for a column when Force Case indicates FirstUpper, Lower, or Upper. The Case Insensitivity 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.

  The index strategy in effect determines how indexes are defined for the CIAI column, as described in Table 52.

  Table 52. Index Strategies Used by the Database Method

  Index Strategy	Description
  None	The Case Insensitivity Wizard defines no new columns or indexes. The wizard sets the Default Insensitivity to DB Case & Accent.
  Single	The Case Insensitivity 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 Case Insensitivity 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 Case Insensitivity 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 Case Insensitivity 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 Case Insensitivity 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 Case Insensitivity 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.

Case Insensitivity Wizard Defaults

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

Methods

The Case Insensitivity 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 Case Insensitivity Wizard uses the following defaults for index strategy:

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

If the Case Insensitivity 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 Case Insensitivity 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, then the wizard does not define columns or indexes.

Note that in both cases, the Case Insensitivity 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, then the Case Insensitivity Wizard sets Operation to On, regardless of the method or index strategy.

Running the Case Insensitivity Wizard Multiple Times

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

Column and Index Naming Convention

The Case Insensitivity Wizard uses the same naming conventions as the Siebel Enterprise Integration Manager 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 Case Insensitivity Wizard defines CIAI column names by appending _CI to the parent column name. For example, if the parent column is LAST_NAME, then the CIAI column would be LAST_NAME_CI.

Index Names

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

base_table_name_C#

In this table name, # 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 IBM DB2), prior to running the CIAI, by selecting the "Limit schema object names to 18 characters" option by navigating to Tools, View, Options, and then Database.

If CIAI column names or index names exceed the maximum length, then the Case Insensitivity 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 Case Insensitivity Wizard does not truncate prefixes or postfixes.

Column and Index Name Uniqueness

After truncating a column or index name, the name might not be unique. When this occurs, the Case Insensitivity 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, then the wizard increments the integer. If the integer becomes two digits or larger, then the wizard truncates the name to make room for the digits. This process maintains the overall string length.

Error Reporting

The Case Insensitivity 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 Case Insensitivity 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.