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.

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:  https://community.emc.com/blogs/dctmreference/2010/05/20/dqls-related-to-taskspace