Best Practices for Creating Indexes in Documentum

Efficient data retrieval is critical in content management systems like Documentum, where large volumes of data are managed. One way to optimize query performance is by indexing key attributes. However, creating indexes on Documentum base tables requires careful consideration and proper methods. It is strongly recommended that all indexes be created within Documentum itself, not directly at the database level. Here’s why:

Why You Should Create Indexes from Within Documentum

1. Internal Optimization: Documentum has an internal process that converts DQL (Documentum Query Language) to SQL. During this process, it checks for the presence of a dmi_index object for attributes with repeating values. If Documentum finds the dmi_index object, it optimizes the SQL query accordingly.If you create indexes directly at the database level (using tools like SQL*Plus), Documentum remains unaware of these indexes and may assume that the attribute is unindexed. This can lead to less efficient SQL queries and poorer performance.
2. Automatic Index Recreation: Another key benefit of creating indexes from within Documentum is that the system automatically recreates them if they are dropped accidentally. The dm_DBWarning job will automatically recreate missing indexes during its next scheduled execution. If you create indexes outside of Documentum, the system will not be able to automatically restore them, which could cause performance degradation.

How to Create Indexes in Documentum

Documentum provides two methods for creating indexes: using DQL or the API.

Creating Indexes with DQL

The syntax for creating an index via DQL is straightforward. Here’s the format:

EXECUTE make_index WITH type_name = object_type,
                    attribute = attribute_name {, attribute = attribute_name, …}

Creating Indexes Using the Documentum API

Alternatively, you can use the Documentum API to create indexes. The following API call accomplishes the same task:

dmAPIGet("apply,session,NULL,MAKE_INDEX,TYPE_NAME,S,object_type,ATTRIBUTE,S,attribute_name(,ATTRIBUTE,S,attribute_name,…)")

In either case, these indexes will be created in the tablespace specified by the index_store parameter found in the server.ini file.

Example of Index Creation

Let’s assume you want to create an index for the keywords attribute on dm_document. It’s important to note that the keywords attribute is inherited from the dm_sysobject type. Hence, you should create the index on the appropriate type table (in this case, dm_sysobject) rather than just dm_document.

EXECUTE make_index WITH type_name = dm_sysobject,
                    attribute = keywords;

This will create an index for the keywords attribute, improving the performance of queries that involve it.

Dropping Indexes in Documentum

If the index does not result in a performance improvement, or if the database optimizer is not utilizing the index, you can drop it using DQL or the API.

Dropping Indexes with DQL

To drop an index using DQL, use the following syntax:

EXECUTE drop_index [[FOR] dmi_index_id] [WITH name = index_name];

Dropping Indexes with the API

Using the API, you can drop the index with the following command:

dmAPIGet("apply,session,dmi_index_id,DROP_INDEX [,NAME,S,index_name]");

This will remove the index from the system.

Conclusion

Creating indexes within Documentum is essential for optimizing query performance and ensuring the system can manage them efficiently. By using the internal dmi_index object, Documentum can optimize SQL generation and automatically handle index recreation if necessary. Always use the provided DQL or API methods to create and manage indexes to maintain system performance and avoid unnecessary troubleshooting down the road.