Configuring Case-sensitive Mode

Oracle Setup

If you want to work in a case-insensitive environment, new and existing Oracle databases must be set up to use a function-based index.

In an MQL window, add the QUERY REWRITE system privilege:
```
grant query rewrite to Matrix;
```

In the init<SID>.ora file, add the following lines:

QUERY_REWRITE_ENABLES=TRUE
QUERY_REWRITE_INTEGRITY=INTEGRITY=TRUSTED

To configure Collaboration and Approvals to turn off case-sensitivity, see 3DSpace Setup.

SQL Server Setup

By default, SQL Server is supported as case-insensitive. Case sensitivity is set by configuring the character collation set of the underlying database.

Run the following SQL to get the available collations. Case-sensitive collations will have "CS" in the name.
```
select * from fn_helpcollations()
```
For example, Latin1_General_CS_AS is a case-sensitive Latin1 collation.
For a new database, use the selected collation to configure the database:
```
create database db_name collate collation
```
Where db_name is the name of your database and collation is the selected collation.
For an existing database, use the selected collation to configure the database:
```
alter database db_name collate collation
```
Where db_name is the name of your database and collation is the selected collation.
To verify that case-sensitivity is turned On, run the following commands in MQL:
```
set context user creator;
set system casesensitive on;
print system casesensitive;
```
The output should be: CaseSensitive=On.
Close MQL and restart it.
You must close and restart MQLafter setting the System case-sensitive setting to On.

3DSpace Setup

The MQL command validate unique identifies conflicts so that they can be resolved prior to turning off the setting. After resolving all conflicts, you can turn off the casesensitive system setting (its default is set to on), and reindex the vaults to switch to unique indices.

Duplicate records could exist if you used the database before turning off the casesensitive setting. For example, in a case sensitive environment you could have users name "bill" and "Bill". These user names would cause unique constraint violations if an attempt was made to make this database case insensitive (add unique indices).

To identify conflicts and resolve and issues by changing some names or deleting unneeded items. In MQL, run validate unique. This shows an example of the output of this command where conflicts exists:

Duplicate person 'Bill'
Duplicate program 'test'
Duplicate state 'Open' in policy 'Incident'
Duplicate signature 'Accept Quality Engineer Assignment' on state
'Assign' in policy 'Incident'
Duplicate signature 'Accept Quality Engineer Assignment' on state
'Assign' in policy 'Incident'
Duplicate business type 'CLASS'
Duplicate business object 'RequestReport' 'Unassigned' '1'
Duplicate business object 'Document' 'Financials' 'Q32003'
Duplicate business object 'Test Suite' 'Vault' ''
Duplicate business object 'Milestone' 'Covers For Manuals' ''
Duplicate business object 'Task' 'Regression testing' '0'

Resolve all conflicts by deleting or changing the name of one of the duplicates. Duplicate signatures might be reported more than once, depending on the number of unique signers on the signature for all actions (approve, reject, ignore).

You might also find duplicate Person workspace objects such as these:

Duplicate table 'Cost PRS' owned by ganley
Duplicate BusinessObjectQuery 'a' owned by coronella
Duplicate VisualCue 'committed' owned by maynes
Duplicate ObjectTip 'Description' owned by liu
Duplicate Filter 'feature' owned by zique
Duplicate BusinessObjectSet 'Current Software' owned by powers
Duplicate ProgramSet 'New Bug' owned by oconnor

These conflicts must be resolved by the user specified as the owner.

Important: All duplicates, including administrative objects, business objects, and workspace objects must be resolved before preceding.

Identify and resolve existing attribute and policy definitions to be sure the rules you set are what you want. For more information, see Collaboration and Approvals with Case-sensitive Off.
In MQL, run set system casesensitive off;
Reindex all vaults to create case insensitive indices. For very large vaults, this could take several hours. You can run validate index vault VAULTNAME; for information on what will occur. For more information, see MQL Command Reference: Index Vault.
If duplicates exist, an error occurs during the vault index.
Calculate or update statistics on all tables by running the following MQL command:
```
validate level 4;
```
Run the validate level 4 command after any substantial data load operation, and periodically thereafter, to update the statistics.

3DSpace is now ready to use in the case-insensitive mode.