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.

Oracle

On an Oracle database, CIAI columns are implemented as function-based indexes. For example, the following index would be created upon making S_CONTACT.FST_NAME a CIAI column:

create index S_CONTACT_M15_C1 on <tableowner>..S_CONTACT (NLS_UPPER("FST_NAME", 'NLS_SORT=GENERIC_BASELETTER'));

MSSQL

For Microsoft SQL Server databases, CIAI columns are implemented with the following two changes:

1. A new column with the suffix _CI is added to the table's definition for each column being enabled for CIAI queries. Note that MSSQL has a native capability to populate this column based on the source column.
2. A new index is created that leverages that column.

   For example, the following index would be created upon making S_CONTACT.FST_NAME a CIAI column:

   alter table dbo.S_CONTACT add FST_NAME_CI as FST_NAME COLLATE Latin1_General_CI_AI create nonclustered index S_CONTACT_M15_C1 on dbo.S_CONTACT ("FST_NAME")

DB2 (UDB and 390)

For DB2 databases, CIAI columns are implemented through the following steps:

1. A new column with the suffix _CI is added to the table's definition for each column being enabled for CIAI queries.
2. A one-time SQL statement is executed to populate this column for existing records.
   Note: If your implementation has Siebel Remote or Siebel Replication, you must re-extract all databases after running the CIAI Wizard.
3. An insert trigger prefixed with CTI_ is created to populate the _CI column when new records are inserted.
4. An update trigger prefixed with CTU_ is created to keep the _CI column up to date if a record is modified.
5. An index is created that leverages the _CI column.

DB2 UDB Example:

alter table SIEBEL.S_CONTACT add "FST_NAME_CI" vargraphic(350);
update SIEBEL.S_CONTACT set FST_NAME_CI = UPPER(FST_NAME);
create trigger SIEBEL.CTI_S_CONTACT before insert  on SIEBEL.S_CONTACT referencing new as n for each row
	begin atomic  set n.FST_NAME_CI = UPPER(n.FST_NAME) end;
create trigger SIEBEL.CTU_S_CONTACT before update of FST_NAME on SIEBEL.S_CONTACT referencing new as n for each row 
	begin atomic  set n.FST_NAME_CI = UPPER(n.FST_NAME); end
create  index SIEBEL.S_CONTACT_M15_C1 on SIEBEL.S_CONTACT ("FST_NAME_CI") PCTFREE 30 ALLOW REVERSE SCANS;

Note: The syntax for DB2/390 is very similar but includes additional storage information relevant only to the DBA.