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)”

 

Advertisements

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.

select  

   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

 

 

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.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)

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: DQL to get folders and permissions

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