Here are a few DQL tricks and behaviors that are not always obvious. Using r_object_id in the select statement changes the resultset. The following DQL for any r_folder_path will return multiple rows for the same object. select object_name, r_folder_path from dm_folder where any r_folder_path is not nullstring By adding r_object_id to the select statement, the Continue reading

I was recently asked to find all custom folders in a cabinet which may have other nested folders, but no documents. In other words, folders can have other folders, but not documents. In order to find empty folders, with no documents or sub-folders, is straightforward: select * from dm_folder where i_reference_cnt=0 But what we are Continue reading

Here’s a DQL to get a listing of Documents along with their folderpaths in the repository: select fld.r_folder_path, doc.r_object_id, doc.object_name, doc.r_content_size, doc.acl_name, doc.r_creation_date, doc.r_modify_date, doc.r_creator_name, doc.title, doc.subject, doc.keywords from dm_document doc, dm_folder fld where folder(‘/<CABINET_NAME>’, descend) and fld.r_object_id = doc.i_folder_id and any r_folder_path is not nullstring enable (ROW_BASED) Continue reading

Here is a DQL statement to find the total document size by cabinet. You may be aware that dm_document has two different attributes for content size: r_content_size, r_full_content_size The first one, r_content_size, can only store sizes upto 2 GB. r_full_content_size stores the correct size if the content is larger than 2 GB. Weird. I know. Continue reading

To retrieve the folder paths and permissions on each folder, use the following query: SELECT sr.r_folder_path, ar.r_accessor_name, ar.r_accessor_permit, ar.r_accessor_xpermit FROM dm_acl_s a, dm_acl_r ar, dm_folder s, dm_folder_r sr WHERE a.r_object_id = ar.r_object_id AND s.r_object_id=sr.r_object_id AND a.object_name = s.acl_name AND sr.r_folder_path like ‘/CABINET_NAME%’ AND sr.i_position = -1     Continue reading

Here is how to get a count of all the documents in a cabinet by folder: select count(*), f.r_folder_path from dm_document d, dm_folder f where d.i_folder_id = f.r_object_id and folder(‘/CABINET_NAME’, descend) and f.r_folder_path is not nullstring group by f.r_folder_path enable(row_based) Continue reading

In order to find the physical file location of any file: select i_contents_id from dm_document where folder(‘/<CABINET_NAME>’, descend) and object_name = ‘<File Name>’ execute GET_PATH for ‘<content_id returned from above>’   Continue reading

Documentum has the concept of a registered table. It is basically a regular database table accessible via DQL. This allows you to add information to a regular database table and access it via regular DQL. To find out if you have any custom registered tables, run the following query: select * from dm_registered where object_name Continue reading

If you are getting multiple results in your DQL results for single objects, modify the server.ini file on the Content Server to add the following line return_top_results_row_based=false in  the [SERVER_STARTUP] section and restart the Content Server. Here are the complete steps: Stop the following in order JMS, All running Docbases and Docbroker Locate the server.ini Continue reading

The DQL to query the list of groups that a user belongs to: select group_name from dm_group where any users_names = ‘<user id>’; To query all groups to which a user belongs whether directly or indirectly, use: select group_name from dm_group where any i_all_users_names = ‘<user id>’; To query for groups the logged in user Continue reading