dm_notes: Documentum Notes

May 7, 2009

DQL to find duplicate objects

Filed under: documentum, dql — Raj V @ 5:11 pm

Over  a period of time due to some bugs in our Application, I came across a scenario where we found that few documents whose attribute is supposed to be unique for a given version tree (for all versions) is not unique.

The logic for generating the unique attribute has a flaw.

For every document we create in documentum, a unique identifier is be assigned, which would be the same for all versions of the document (similar to i_chronicle_id). This unique identifier would be used by the business users for accessing the document or for referring it in other applications.

To identify the list documents that have the same attribute in the repository and the no. of occurrences of each such document, I use the following query.

The below query can also be used to find duplicate documents(objects).

You need to replace with your corresponding unique attribute in the below dql:

DQL> SELECT <unique attribute>, count(*) FROM <doc type> GROUP BY <unique attribute> HAVING count(*) > 1
ORDER BY <unique attribute>

This gives the document identifier and the no. of  documents having the same unique identifier.

Also a technique I use to generate the unique identifier for a document with in the system is by using the following approach (may not suit all your business scenarios though)

Below is the i_chronicle_id of a document (Unique for all versions of the document)

i_chronicle_id : 0900b276800077f3

The first two characters specify the type of the document

The next six characters specify the docbase id to which the document belongs to

The rest of the 8 characters specify the unique object id of the object for a given document type and docbase.

If you want to generate a unique custom document no. with in this docbase you can use the unique document id in the following format

Unique document no. : <Business Prefix ><unique document id>

ex: DOC800077f3

Changing the to this new pattern has resolved the issue for new documents creation. We are yet to figure out a way how we can remove the existing duplicates as they are being used in other systems and we can’t straight away change the unique attributes.


Blog at