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.


June 19, 2008

Query to find list of objects in folder along with its Folder Path

Filed under: dfc, documentum, dql — Tags: , , — Raj V @ 3:42 pm

Occasionally we require to find the list of all objects from a folder and also retrieve their exact folder path with in the same query.

This can be achieved easily through a DFC Program. But its a little tricky when you want it through a a DQL Query. As dm_sysobject stores only the folder id (i_folder_id) of the object instead of the folder path.

The folder path is hidden in the dm_folder object and is a repeating attribute. So we need to query dm_folder for r_folder_path. The issue in DQL is you can’t select repeating attributes when you join multiple types. You will hit DM_QUERY2_E_REPEAT_TYPE_JOIN if you do so.

Lets see what DFC can do and how to approach the same with DQL.

DFC code snippet looks as below:

IDfFolder folder = (IDfFolder) session.getObjectByPath(<<folderpath>> );
if (folder != null) {
     getContents(session, folder, docs);
     System.out.println("Total Number of Files : "+docs.getSize() );

private List getContents(IDfSession session, IDfFolder folder, List docs) throws DfException, IOException {
   // get all the r_object_id
   IDfCollection collection = folder.getContents("r_object_id");
   if (collection != null) {
      while ( {
	String objectId = collection.getString("r_object_id");
	IDfSysObject object = (IDfSysObject) session.getObject(new DfId(objectId));
	if (object.getTypeName().equals("dm_folder" ) || object.getType().isSubTypeOf("dm_folder" )) {
           getContents(session, (IDfFolder) object, docs, writer);
	} else {
           IDfFolder folderObj = (IDfFolder) session.getObjectByQualification("dm_folder where r_object_id = '+ object.getString("i_folder_id" ) + "'");
           if (folderObj != null) {
	      buffer = object.getObjectName() + "\t"+ object.getOwnerName() + "\t"+
                                  folderObj.getString("r_folder_path" ) + "\t"+ object.getModifyDate();

In DQL the same can be achieved as below:

DQL> select A.r_object_id, A.object_name, B.r_folder_path from dm_document A, dm_folder_r B where any A.i_folder_id = B.r_object_id and B.r_folder_path like ‘%/System/%’;

What we are trying to achieve is to join the dm_document type repeating attribute ‘i_folder_id’ and a dm_folder single value attribute table. This way we don’t end up querying the r_folder_path a repeating attribute. If we would have queried dm_folder type (instead of dm_folder_r) we would have hit the DQL restriction of DM_QUERY2_E_REPEAT_TYPE_JOIN error. However querying the underlying table enables us to pass through the DQL translator for _r table (just like any registered table concept).

June 5, 2008

Hierarchical list of Documentum types

Filed under: dql, notes — Tags: , , — Raj V @ 8:06 pm

Came across a simple (useful) query that displays the types and thier hierarchy.

DQL>describe hierarchy persistent.

Displays a hierarchial structure  of all “Persistent Objects”.

DQL> describe hierarchy dm_sysobject
Object hierarchy list
\ dmi_expr_code
\ dm_mount_point
\ dm_location
\ dm_docbase_config
\ dm_server_config
\ dm_policy
\ dm_registered
\ dm_folder
\ dm_cabinet
\ dm_xml_application
\ dm_category
\ dmc_topic
\ dmc_room
\ dmc_module
\ dmc_aspect_type
\ dmc_validation_module
P.S.: Found it through samson (Just below the toolbar : Query Topics -> Type Management + List tree of types known in docbase –> Generate Query)

Analyzing the above query, I gave a DQL to find out hierarchy of dm_sysobject and it works perfect. cool

DQL>describe hierarchy dm_sysobject

(lists hierarchically all the sub types of sysobject ).

This will be handy sometimes to see who all extend a custom object type.

Here is the DQL to find direct sub types of a given type
DQL> select name from dm_type where super_name =’dm_sysobject’;
(This query doesn’t list the indirect sub types)

December 28, 2007

DQL: List of groups a user belongs to ..

Filed under: dm_group, dql, notes — Tags: , , , — Raj V @ 9:44 pm

Here is the DQL to query the list of groups a user belongs to:

select group_name from dm_group where any i_all_users_names = ‘<user id>’;

This gives the list of all groups a user belongs to directly or indirectly.
i_all_users_names is a computed value.

If you want to query for list of groups the logged in user belongs to:

select group_name from dm_group where any i_all_users_names = USER ;

The USER is a place holder for the current logged in User.

Querying for ‘users_names’ attribute instead of i_all_users_names will return only the groups where the user is directly part of that group (no sub groups)

December 5, 2007

DQL to list of users in a group

Filed under: dm_group, dm_user, dql — Tags: , , — Raj V @ 5:42 pm

DQL to query the list of users in a group

  • select users_names from dm_group where group_name = ‘mygroup’;

DQL to query the list of active users (Users who can log in) in a group:

  • select user_name from dm_user where user_state = 0 and user_name in (select users_names from dm_group where group_name = ‘mygroup’);
    user_state of dm_user indicates the user’s current state.
    Valid alues are:

    • 0, indicating a user who can log in.
    • 1 indicating a user who cannot log in.
    • 2 indicating a user who is locked.

Blog at