Creating Index In Siebel Tools On Varchar Column Of 998 Length Is Not Allowed. (Doc ID 2234699.1)

Last updated on FEBRUARY 22, 2017

Applies to:

Siebel CRM - Version 15.8 [IP2015] and later
Information in this document applies to any platform.

Symptoms

Customer has an issue with creating index in Siebel Tools on Varchar Column of 998 length what is not being allowed.

Problem Description:
They have created a custom table which has a column MESSAGE_ID with Varchar(998).
The table was created successfully in the repository.

Need to have the message_id column indexed so the query on this column would be faster.
Tried to create a custom index in tools with the below columns combination
message_id(1 Asc), Activity_id(2 Asc), Row_id( 3 Asc).

Index is not created at all, when dll is generated or when table changes are applied.

Had the indexed created at the table level in the database and when index is applied from tools, it is dropping the index created at db level.
Also, had the message_id column removed from the index and generated the ddl - in this scenario it is OK.

Questions:
1. Is there a restriction on creating of the index on Column of lengthy varchar?
2. How to get the index created from tools for the table? is there any alternate approach?

This index has to be there in the repository. Otherwise it would drop the index whenever ddlsync is performed on the database.

Repro Steps:

1. Using Siebel Tools, create a custom table and fields such as Message_Id (998 Characters), Activity_Id (15 Characters) and Row_Id (15 Characters).
2. Create an Index, with Message_Id, Activity_Id and Row_Id.
3. Select Apply/DDL --> Generate DDL.
3. DDL file generated has no "create index" statement.
4. Remove Message_Id from index columns and generate ddl. Now DDL file has create index statement with activity_id and row_id.
5. Change Message_Id size from 998 to 500. Include Message_Id to Index Columns. Generate DDL and now DDL file has create index with Messag_Id column only.
6. Finally, change Message_Id size from 500 to 470. Index columns are Message_Id, Activity_Id and Row_Id. Generate DDL is invoked. DDL file now has create index statement with all the three columns - Message_Id, Activity_Id and Row_Id.
7. This indicates that when total size of columns (Message_Id, Activity_Id and Row_id) is 500 characters or less, DDL File contains create index statement with all the columns.

Issue happening with 15.18 and DB2 Database only. This issue doesn't occur with Oracle Database.
Corresponding Bug has webex recording of the 4 iterations mentioned above. Also, attached to the Bug are the 4 DDL files generated in the 4 iterations.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms