1. Database Upgrade Guide
  2. Method and Index Strategy

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 the following table.

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.