DQL: Hidden DQL Tricks and Behaviors

Here are a few DQL tricks and behaviors that are not always obvious.

  1. 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 resultset will contain only distinct r_object_id’s. In other words, will only return a single row for each object.

select object_name, r_folder_path from dm_folder where any r_folder_path is not nullstring

2. When using joins with recurring attributes, you need to use the DQL hint “enable (ROW_BASED)”



DQL: How to find Documentum folders with 0 (zero) documents in any subfolders

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 looking for are folders which may contain other folders, but none of the sub-folders should have a document.

I came up with the following query. Keep in mind that this query can be improved upon, for example by using joins. I only need to run it once so I have no need for performance based improvements at this time.


   r_object_id, object_name, r_folder_path, r_creation_date, r_modify_date

from dm_folder //or custom dm_folder subtype

where r_object_id not in   //Returns all folders which are not in the list of ancestor folders (folder hierarchy) as returned below


    select distinct (i_ancestor_id) from dm_folder where r_object_id in   //Returns all ancestor folders in the hierarchy of the parent folders below


        select distinct(i_folder_id) from dm_document WHERE <your custom where clause>   //Returns all parent folders which contain documents 



and any r_folder_path is not nullstring

and folder('/Background Investigation', descend)

enable (ROW_BASED)  //Needed when querying repeating attributes such as r_folder_path above



Documentum: Software to Download Documents and Metadata

I have written a software in Java using DFC. This software performs the following tasks:

  1. Performs export or import of documentum data
  2. Download all documents given a folder/cabinet in Documentum
  3. Download all document metadata, including custom metadata without having to specify the specific fields
  4. All the metadata is saved in an xml file
  5. The field metadata, such as the attribute data type, attribute name and value are all saved in the xml to make importing into any other content management system easy.
  6. All the permissions levels for each document are saved to the xml
  7. The complete folder hierarchy under the given root hierarchy is exported
  8. Any custom folder metadata is also saved in the xml

DQL: Listing documents with their Folderpaths

Here’s a DQL to get a listing of Documents along with their folderpaths in the repository:

select fld.r_folder_path, 
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
folder('/<CABINET_NAME>', descend) and 
fld.r_object_id = doc.i_folder_id 
and any r_folder_path is not nullstring 
enable (ROW_BASED)

DQL: Total Document Content Size By Cabinet

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.

select cab.object_name, sum(doc.r_full_content_size) 
from dm_document doc, dm_cabinet cab
where doc.i_cabinet_id = cab.r_object_id
group by cab.object_name


Documentum DFC: How to get custom attributes

Here is a way to get all the custom attributes of an object in DFC without knowing them ahead of time:

                //Custom Attributes
                String allAttributes = dctmFileObject.dump();
                String customAttributes = allAttributes.substring(allAttributes.indexOf("language_code"), allAttributes.indexOf("SYSTEM ATTRIBUTES"));
                try (BufferedReader reader = new BufferedReader(new StringReader(customAttributes))) {
                    String line = reader.readLine().trim();
                    while (line != null && !line.isEmpty()) {
                        String attributeName = line.substring(0, line.indexOf(':')).trim();
                        String attributeValue = line.substring(line.indexOf(':')).trim();
                        int attributeDataType = dctmFileObject.getAttr(dctmFileObject.findAttrIndex(attributeName)).getDataType();

                        line = reader.readLine();
                } catch (IOException exc) {
                    // quit