Knowledgebase

How do I convert tables in a database to be case insensitive?

Sometimes you have data and index files that were created with the case-sensitive option and you want to make them case-insensitive. The SQL_OPTION DB_CASE_INSENSITIVE option enables case-insensitive comparisons, sorting, and identifiers within a database. This option is a database-level attribute that is set when a database is created, so it only affects databases that are created after it is enabled.

Notice that this option does not actually convert your existing tables to be case-insensitive. Instead it is used in a process of creating new, case-insensitive tables and importing the existing data into them.

This option affects the key segment definition. An index created in a database that has been created as case-insensitive uses the USCHSEG segment mode instead of SCHSEG.

How To

If you have data and index files that were created with the case-sensitive option and you want to use them in a case-insensitive database, follow these steps:

1. Add the SQL_OPTION DB_CASE_INSENSITIVE keyword to ctsrvr.cfg.
2. Use the ctsqlcdb utility to create the new database.
3. Use PUTIFIL() to change your segment modes to case-insensitive segment modes. You will need to write some code to do this (it is not available in a utility).
4. Rebuild the indexes.
5. Use the SQL import utility (ctsqlimp) to import the tables into the new database.

The new database will be case-insensitive.