Useful DQL

I love useful and clever DQL statements.  This post will be the first of many (I’m sure) where I post a collection of DQL statements I have collected or created.  This first batch is from my book.

Cabinet Content Size (Method 1)

Determine the amount of content in a particular cabinet.  Replace ‘/Temp’ with the cabinet of your choice.  You can also use the folder() DQL function instead of cabinet().

select sum(r_full_content_size)/1024 as cnt_size_kb from dm_sysobject (all) where cabinet('/Temp',descend);

Cabinet Content Size (Method 2)

A second way to determine the amount of content in a particular cabinet.  This one summarizes and displays the size of the content in each individual sub-folder.  Replace ‘/Temp’ with the cabinet of your choice.  You can also use the folder() DQL function instead of cabinet().  Be aware, this query can take a long time to run.

select f.object_name as folder_name_name, sum(d.r_full_content_size)/1024 as cnt_size_kb from dm_folder f, dm_sysobject (all) d where any d.i_folder_id = f.r_object_id and cabinet('/Temp', descend) group by f.object_name order by object_name;

This query would be much cooler if it could return the r_folder_path for the folder instead of the object_name.  As it is, with a lot of sub-folders, it is easy to lose context.  If you can figure out how to return r_folder_path I’ll post an update.

So, I found the answer to this problem by accident on an ECN post by dnvhariprasad.  Here is the query and the output:

SELECT fr.r_folder_path, count(*) as cnt, sum(r_full_content_size)/1024 as sizeKB FROM dm_sysobject s, dm_sysobject_r sr, dm_folder_r fr, dm_document dm WHERE s.i_is_deleted = 0 AND sr.i_position = -1 AND fr.i_position = -1 AND sr.r_object_id = s.r_object_id AND fr.r_object_id = sr.i_folder_id AND dm.r_object_id = s.r_object_id AND FOLDER('/Temp', descend) GROUP BY fr.r_folder_path

r_folder_path                           cnt  sizekb
/Temp                                   2     3
/Temp/Jobs/dce_Clean                    2     2
/Temp/Jobs/dm_ContentWarning            3     11
/Temp/Jobs/dm_DataDictionaryPublisher   14    36
/Temp/Jobs/dm_FTCreateEvents            2     5
/Temp/Jobs/dm_FTIndexAgentBoot          5     6
/Temp/Jobs/dm_FTStateOfIndex            1     1
/Temp/Jobs/dm_GwmClean                  3     0
/Temp/Jobs/dm_GwmTask_Alert             9     0
/Temp/Jobs/dm_QmPriorityAging           5     5

Get An Object’s Folder Path

Get an object’s folder path.  Replace ‘My Document’ with the object name you are looking for.

select r_folder_path from dm_folder where r_object_id in (select i_folder_id from dm_sysobject where object_name = 'My Document');

Find Deleted Content

Find all the deleted content in the repository.  This works well as long as the dm_Clean job has not run.  A technique for undeleting content will be the subject of a future post.

select * from dmr_content where any parent_id is NULL and content_size > 0 order by set_time;

Unlock (i.e., Cancel Checkout) of an Object

Cancel the check out of an object.  Replace ‘My Document’ with the object name you want to unlock.

update dm_sysobject object set r_lock_owner = '', set r_lock_machine = '', set r_lock_date = date('nulldate') where object_name = 'My Document';

Find the Components of a Virtual Document

Find the objects that belong to a virtual document.  Replace ’09…97′ with the object id of the root virtual document.

select * from dm_sysobject in document id('0900218d8000ad97') descend;

Find an Object’s Virtual Document Parent

Determine if an object is part of a virtual document.  Replace the object id,  ’09…71′, with the i_chronicle_id of the child object.

select parent_id from dmr_containment where component_id ='0900218d80008d71';

Of course, if you have some favorite cool DQL, send it in an I will include it in a future post.

UPDATE:  I just discovered that dnvhariprasad  has posted a whole handful of useful TaskSpace DQLs on his blog.  Check them out:


About Scott
I have been implementing Documentum solutions since 1997. In 2005, I published a book about developing Documentum solutions for the Documentum Desktop Client (ISBN 0595339689). In 2010, I began this blog as a record of interesting and (hopefully) helpful bits of information related to Documentum, and as a creative outlet.

10 Responses to Useful DQL

  1. hwt says:

    Cabinet Content Size (Method 1)

    is this really sufficient in all cases? A document might have more than one dmr_content objects, and as i started summing these I got larger results (as we have some kind of this documents)

    BTW: How to sum the size of virtual documents? I’m currently struggling with that…


    • scott says:

      I’ll have to look at the DQL for cabinet sizing with your question in mind. I don’t know if multiple dmr_content objects would skew the results or not.

      In regard to your question about determining the size of a virtual document, try this:
      select sum(r_full_content_size) from dm_document where i_chronicle_id in (select component_id from dmr_containment c where c.parent_id in (select r_object_id from dm_document where object_name = 'VD Root'))


      • hwt says:

        For the cabinet or folder size the most common example might be reditions, as I understand documentum right. (they aren’t considered in webtop or DA directly but obviously they need space) I made a join between dm_document objects in the folder/cabinet and the corresponding dmr_content objects, which seems to work well to get the size of renditions as well.

        For the virtual I tried to get the r_object_ids of virtual document parts with an IN DOCUMENT statement and check there which one is a vDoc as well and reslove the children there and then summed up the full_content_sizes of the related contents.

        I will also have a try with your statement on monday (as I’m in europe I’m not at work any more and have currently no access to the beast 😉 )


  2. scott says:

    hwt – I played around with this query this evening and it seems to produce the correct content size for a cabinet, including renditions. See what you think:

    select sum(full_content_size)/1024 from dmr_content where any parent_id in (select r_object_id from dm_document (all) where cabinet(‘/Temp’))


  3. doquent says:

    See for a discussion of exceptions when identifying the correct document size.


  4. hwt says:

    Sorry for answering a little late: I ended up with a similar query.
    Until I double-checked the object reference, which is pointing out that dmr_content’s parent_id property is a repeating-value property.
    After querying the docbase with some test query I found some cases where this aspect is important.

    Currently I’m using something like:
    select sum(full_content_size ) from dmr_content where r_object_id in (select distinct r.r_object_id from
    dm_document (All) d, dmr_content r where folder(‘/test’, descend) and d.r_object_id = r.parent_id)
    enable (ROW_BASED);

    What do you think?


    • scott says:

      Looks good to me!


      • hwt says:

        From the tests I’ve done the last days I also can’t tell anything different.

        BTW: Did you (or someone else) ever experience some abnormalities when sorting by full_content_size (when not summing up the sizes)? I see some 0–sized results at the top or quie near to that when sorting descending the other documents seem to be ordered accurately.


  5. Nandakumar says:

    Hi All,

    Need a query to find the total documents and its size in docbase


    • Scott says:

      Something like: select count(*), sum(r_full_content_size) from dm_sysobject (all)

      You can also find this information in the State of the Docbase report


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: